文章目录
一、搭建环境
主(Master) :192.168.199.103
从(Slave) :192.168.199.23
[root@localhost ~]# cat /etc/redhat-release
CentOS Linux release 7.5.1804 (Core)
MySQL版本:Server version: 5.7.24 MySQL Community Server (GPL)
学习环境下最好关闭防火墙:https://blog.csdn.net/qq_39680564/article/details/84940221
卸载自带的Mariadb
yum -y remove mariadb-libs
下载mysql-5.7.27
yum install -y wget
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.27-1.el7.x86_64.rpm-bundle.tar
tar -xvf mysql-5.7.27-1.el7.x86_64.rpm-bundle.tar
安装mysql-5.7.27
yum install -y libaio net-tools perl \
&& rpm -ivh mysql-community-common-5.7.27-1.el7.x86_64.rpm \
&& rpm -ivh mysql-community-libs-5.7.27-1.el7.x86_64.rpm \
&& rpm -ivh mysql-community-client-5.7.27-1.el7.x86_64.rpm \
&& rpm -ivh mysql-community-server-5.7.27-1.el7.x86_64.rpm \
&& rpm -ivh mysql-community-libs-compat-5.7.27-1.el7.x86_64.rpm
启动并设置开机自启
systemctl start mysqld && systemctl enable mysqld
获取初始密码
grep password /var/log/mysqld.log | sed 's/.*\(............\)$/\1/'
进入MySQL命令行,修改密码,设置访问权限
[root@localhost ~]# mysql -uroot -p
Enter password:
mysql> set password for root@localhost = password('123456Aa.');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant all privileges on *.* to 'root'@'%' identified by '123456Aa.';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
二、修改配置文件
2.1 修改Master的my.cnf文件,在末尾增加如下内容
log-bin=mysql-bin
server-id=2
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=mysql
binlog-ignore-db=sys
binlog-do-db=test
2.2 修改Slave的my.cnf文件,在末尾增加如下内容
log-bin=mysql-bin
server-id=3
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=mysql
binlog-ignore-db=sys
replicate-do-db=test
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
slave-net-timeout=60
参数 | 说明 |
---|---|
log-bin | 开启bin-log |
server-id | 数据库唯一ID,主从不可重复 |
binlog-ignore-db | Master忽略同步的库 |
binlog-do-db | Master需要同步的库(如果没有此项,表示同步所有的库) |
replicate-do-db | 指定Slave需要同步的库 |
log-slave-updates | 链式更新 |
slave-skip-errors | 跳过出错 |
slave-net-timeout | 心跳时间 |
2.3 分别重启两台MySQL
[root@localhost ~]# systemctl restart mysqld
三、修改数据库设置
3.1 Master设置
登录mysql赋予Slave(192.168.199.23)有读取日志的File权限
mysql -uroot -p123456Aa.
mysql> grant FILE on *.* to 'root'@'192.168.199.23' identified by '123456Aa.';
mysql> grant replication slave on *.* to 'root'@'192.168.199.23' identified by '123456Aa.';
mysql> flush privileges;
重启mysql,查看Master状态获取File与Position的值
mysql -uroot -p123456Aa.
mysql> show master status;
+------------------+----------+--------------+----------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+----------------------------------+-------------------+
| mysql-bin.000002 | 154 | test | information_schema,cluster,mysql | |
+------------------+----------+--------------+----------------------------------+-------------------+
1 row in set (0.00 sec)
3.2 Slave设置
mysql -uroot -p123456Aa.
mysql> stop slave;
mysql> change master to master_host='192.168.199.103',master_user='root',master_password='123456Aa.',master_log_file='mysql-bin.000002', master_log_pos=154;
mysql> start slave;
参数 | 说明 |
---|---|
master_host | Master地址 |
master_user | Master用户名 |
master_password | Master密码 |
master_log_file | Master的File值 |
master_log_pos | Master的Position |
重启mysql,查看是否与MasterFile与Position的值一直
service mysqld restart
mysql> show slave status;
+----------------------------------+-----------------+-------------+-------------+---------------+------------------+---------------------+----------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-----------------------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID | Master_Info_File | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position | Replicate_Rewrite_DB | Channel_Name | Master_TLS_Version |
+----------------------------------+-----------------+-------------+-------------+---------------+------------------+---------------------+----------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-----------------------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+
| Waiting for master to send event | 192.168.199.103 | root | 3306 | 60 | mysql-bin.000002 | 154 | localhost-relay-bin.000004 | 320 | mysql-bin.000002 | Yes | Yes | test | mysql | | | | | 0 | | 0 | 154 | 531 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | | | 2 | 1f25ba62-fc45-11e8-910e-000c2955ec3b | /usr/local/mysql5.7.24/data/master.info | 0 | NULL | Slave has read all relay log; waiting for more updates | 86400 | | | | | | | | 0 | | | |
+----------------------------------+-----------------+-------------+-------------+---------------+------------------+---------------------+----------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-----------------------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+
1 row in set (0.00 sec)
四、验证
4.1 Master创建test库,并创建一个info表
mysql -uroot -p123456Aa.
mysql> create database test;
mysql> use test;
mysql> create table info (name varchar(20),sex varchar(20));
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| info |
+----------------+
1 row in set (0.00 sec)
4.2 Slave查看是否有此库和info表
mysql -uroot -p123456Aa.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
mysql> use test;
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| info |
+----------------+
1 row in set (0.00 sec)
主从搭建成功!!!