MySQL的主從複制配置

一、寫在前面

上一篇文章中，我們介紹了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。文章中未加此字段，需要特此注意，在我們公司的生産環境中我加了該字段的。