一、写在前面
上一篇文章中，我们介绍了MySQL的主主复制，由于时间仓促，并未完整、清晰地验证主主复制的全过程，缺少从DCGH-DB1同步DCGH-DB2的验证步骤。如果没有本篇，上一篇文章实际就是主从复制，DCGH-DB1为Master，DCGH-DB2为从服务器。本文承接上文，本文完整阐述MySQL的主从复制、主主复制。
二、系统架构简图
按照之前思路：数据库服务器DCGH-DB1及DCGH-DB2互为主备；DCGH-DB1与DCGH-DB3中则以前者为主，后者为备。
三、清理环境、对DCGH-DB3进行初始化
1.关闭DCGH-DB1，克隆DCGH-DB1得到DCGH-DB3。按照架构图设置好DCGH-DB3的IP主机名。更改DCGH-DB3的UUID及server-id（前文已阐述具体方法），重启MySQL服务。
[root@DCGH-DB3 ~]# vi /var/lib/mysql/auto.cnf [root@DCGH-DB3 ~]# vi /etc/my.cnf [root@DCGH-DB3 ~]# systemctl restart mysqld
2.登录DCGH-DB3，修改密码，删除之前用户。
[root@DCGH-DB3 ~]# mysql -u root -p
Enter password:
mysql> set password for 'root'@localhost=password('DCGH-test-db3'); #修改指定用户的密码
mysql> use mysql;
mysql> delete from user where User='copy';
mysql> flush privileges;
mysql> select User from user;
+---------------+
| User |
+---------------+
| mysql.session |
| mysql.sys |
| root |
+---------------+四、完成DCGH-DB1上的相关设置
修改配置文件/etc/my.cnf（在mysqld字段下面新增:sql_mode=NO_ENGINE_SUBSTITUTION），重启MySQL服务，增加DCGH-test-db3对DCGH-DB1的访问权限。
[root@DCGH-DB1 ~]# systemctl restart mysqld
[root@DCGH-DB1 ~]# mysql -u root -p -A
Enter password:
mysql> use mysql;
mysql> insert into user(User,Host,authentication_string) values('copy','10.1.1.33','*B1723EF614F166C57C9236F1EAE6A3B91A6C9199');#authentication_string这一串通过查询DCGH-DB3得到，明文密码也就是：DCGH-test-db3。等效于：grant replication slave,replication client on *.* to 'copy'@10.1.1.33 identified by 'DCGH-test-db3';
mysql> grant replication slave,replication client on *.* to 'copy'@10.1.1.33;五、完成从DCGH-DB2同步到DCGH-DB1的主从配置
1.登录DCGH-DB2，锁表，备份数据（上一篇文章中，由于使用的是完整克隆，主备环境完全一致，因此并未进行备份，在同步之前要求两端数据一致，因此之前的操作如果在生产环境中操作是非常不严谨的，备份之前的文章有介绍，此处只提不赘述）
[root@DCGH-DB2 ~]# mysql -u root -p Enter password: mysql> flush tables with read lock; mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000003 | 755 | | | | +------------------+----------+--------------+------------------+-------------------+
2.把DCGH-DB2设置为DCGH-DB1的Master服务器。
mysql> CHANGE MASTER TO MASTER_HOST='10.1.1.32',MASTER_USER='copy',MASTER_PASSWORD='DCGH-test-db1',MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=755; mysql> start slave ; mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.1.1.32 Master_User: copy Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 755 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 755 Relay_Log_Space: 521 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 32 Master_UUID: ba5f1c18-3d70-11e8-891f-000c2986a1f0 Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: mysql> flush tables with read lock; #锁表，查出position备用。 mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000004 | 755 | | | | +------------------+----------+--------------+------------------+-------------------+
至此，主主复制配置全部完成。
六、完成从DCGH-DB1复制到DCGH-DB2的主从复制配置
1.登录到DCGH-DB3，进行主从配置。
[root@DCGH-DB3 ~]# mysql -u root -p -A Enter password: mysql>CHANGE MASTER TO MASTER_HOST='10.1.1.31',MASTER_USER='copy',MASTER_PASSWORD='DCGH-test-db3',MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=755; mysql> start slave; mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.1.1.31 Master_User: copy Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 755 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 755 Relay_Log_Space: 521 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 31 Master_UUID: ba5f1c18-3d70-11e8-891f-000c2986a1f9 Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version:
解锁DCGH-DB1及DCGH-DB2。
七、验证
在DCGH-DB2按照上一篇文章建库、增、删、改、查，然后分别在DCGH-DB1、DCGH-DB3上查看数据是否同步。按照本文的步骤，操作成功，为了节省篇幅，不在阐述。
八、注意事项
1.主主同步需要2个MySQL用户，主从同步只需一个MySQL用户，用户满足条件之后还需要注意权限。
2.在同步之前需要保证两端数据一致，涉及到备份及还原。
3.排除不需要复制的数据库需要在my.cnf的mysqld字段下加replicate-ignore-db,多个库需要使用逗号隔开。如排除mysql、test两个库，replicate-ignore-db=mysql,test。文章中未加此字段，需要特此注意，在我们公司的生产环境中我加了该字段的。
点击查看更多内容
为 TA 点赞
评论
共同学习，写下你的评论
评论加载中...
作者其他优质文章
正在加载中
感谢您的支持，我会继续努力的～
扫码打赏，你说多少就多少
赞赏金额会直接到老师账户
支付方式
打开微信扫一扫，即可进行扫码打赏哦