Mysql配置 主主同步（下）

B数据库操作

1.导入数据 mysql -uroot -p < alldb.sql

2.修改配置文件 vim /etc/my.cnf

1. [mysqld]
2. log-bin=mysql-bin #开启二进制日志
3. server-id=2 #设置server-id，必须唯一

3.重启mysql，配置同步 systemctl restart mysql

需要A服务器主机名，登陆凭据，二进制文件的名称和位置 CHANGE MASTER TO MASTER_HOST='182.92.172.80', MASTER_USER='test', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=567; start slave;

4.查看slave状态： show slave status\G;

1. *************************** 1. row ***************************
2. Slave_IO_State: Waiting for master to send event
3. Master_Host: 182.92.172.80
4. Master_User: rep1
5. Master_Port: 3306
6. Connect_Retry: 60
7. Master_Log_File: mysql-bin.000013
8. Read_Master_Log_Pos: 11662
9. Relay_Log_File: mysqld-relay-bin.000022
10. Relay_Log_Pos: 11765
11. Relay_Master_Log_File: mysql-bin.000013
12. Slave_IO_Running: Yes
13. Slave_SQL_Running: Yes
14. Replicate_Do_DB:
15. Replicate_Ignore_DB:
16. ...

Slave_IO_Running: Yes Slave_SQL_Running: Yes 都是yes即可

5.配置作为A的主 创建用户并授权：用户：test密码：123456，ip配置为A的IP CREATE USER 'test'@'182.92.172.80' IDENTIFIED BY '123456';

分配权限 GRANT REPLICATION SLAVE ON *.* TO 'test'@'182.92.172.80'; flush privileges;

这次不用锁表了，因为B在同步A的数据，当前保持一致了。

6.新窗口操作，查看master状态，记录二进制文件名(mysql-bin.000004)和位置(254)： SHOW MASTER STATUS;

1. ------------------+----------+--------------+------------------+
2. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
3. +------------------+----------+--------------+------------------+
4. | mysql-bin.000004 | 254 | | |
5. +------------------+----------+--------------+------------------+

A数据库操作

需要B服务器主机名，登陆凭据，二进制文件的名称和位置 CHANGE MASTER TO MASTER_HOST='123.57.44.85', MASTER_USER='test', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=254; start slave;

4.查看slave状态： show slave status\G;

1. *************************** 1. row ***************************
2. Slave_IO_State: Waiting for master to send event
3. Master_Host: 123.57.44.85
4. Master_User: rep1
5. Master_Port: 3306
6. Connect_Retry: 60
7. Master_Log_File: mysql-bin.000013
8. Read_Master_Log_Pos: 11662
9. Relay_Log_File: mysqld-relay-bin.000022
10. Relay_Log_Pos: 11765
11. Relay_Master_Log_File: mysql-bin.000013
12. Slave_IO_Running: Yes
13. Slave_SQL_Running: Yes
14. Replicate_Do_DB:
15. Replicate_Ignore_DB:
16. ...
17. Slave_IO_Running: Yes

Slave_SQL_Running: Yes

都是yes即可