主机A(二期MySQL01):172.25.48.169,
mysql Ver
14.14 Distrib 5.7.19, for Linux (x86_64) using
EditLine wrapper
主机B(二期MySQL01):172.25.48.170
mysql Ver
14.14 Distrib 5.7.19, for Linux (x86_64) using
EditLine wrapper
1、配置主机A的my.cnf配置,添加以下内容:
log-bin=mysql-bin
server-id=181
2、配置主机B的my.cnf配置,添加一下内容:
log-bin=mysql-bin
server-id=180
3、为主机A添加数据同步用户“user/123456789”
create user 'user'@'%'
identified by '123456789';
GRANT REPLICATION SLAVE ON *.*
TO 'user'@'%';
grant replication
client,replication slave on *.* to 'user'@'172.25.48.170'
identified by '123456789';
4、为主机B添加数据同步用户“user/123456789”
create user 'user'@'%'
identified by '123456789';
GRANT REPLICATION SLAVE ON *.*
TO 'user'@'%';
grant replication
client,replication slave on *.* to 'user'@'172.25.48.169'
identified by '123456789';
5、查看两台主机的mysql bin
log,查看之前先要重启MySQL库
/etc/init.d/mysqld
restart
首先将两台主机mysql中的表锁定
FLUSH TABLES WITH READ
LOCK;
FLUSH TABLES WITH READ LOCK;
代表锁定表,禁止所有操作。防止bin log位置发生变化。
查看A主机bin log位置
SHOW MASTER STATUS;
mysql> show master
status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB |
Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |
154 |
| | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00
sec)
查看B主机bin log位置
SHOW MASTER STATUS;
mysql> show master
status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB |
Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |
154 |
| | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00
sec)
记录下A主机结果,和B主机结果
然后再解除两台主机mysql
table的锁定
Unlock Tables;
6、开始设置 Slave
Replication
A主机执行如下命令:
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST =
'172.25.48.170', MASTER_USER = 'user',
MASTER_PASSWORD = '123456789',
MASTER_LOG_FILE = 'mysql-bin.000001',
MASTER_LOG_POS = 154;
START SLAVE;
B主机执行如下命令:
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST =
'172.25.48.169', MASTER_USER = 'user',
MASTER_PASSWORD = '123456789',
MASTER_LOG_FILE = 'mysql-bin.000001',
MASTER_LOG_POS = 154;
START SLAVE;
7、查看同步状态是否成功
主机A执行:show slave
status\G
观察
Slave_IO_Running: Yes
Slave_SQL_Running:
Yes
主机B执行:show slave
status\G
观察
Slave_IO_Running: Yes
Slave_SQL_Running:
Yes
Ok 成功!