知識星球MySQL主從複製中若是出現錯誤，一般有哪些解決方法？通讀本文，相信你會有答案。
主從複製中若是出現錯誤可以透過幾個方法來進行解決：
1. 如果主從複製時發生了主鍵衝突，從而阻止了主從複製，可以使用sql_slave_skip_counter這個變數來忽略錯誤將其排除
2. 如果發生了較大的錯誤，可以考慮使用reset slave的方法重新配置從伺服器來恢復錯誤
以下演示如何使用這兩種方法解決錯誤，及相關操作的詳細說明
- reset slave的使用方法
- 環境準備搭建主從同步
- 主節點配置
1. 修改配置檔案
[root@Master ~]# vim /etc/my.cnf[mysqld]log-bin=/data/bin/mysql-binbinlog-format=rowserver-id=1
2. 建立二進位制日誌目錄
[][]
3. 啟動mysqld服務
[]
4. 檢視主伺服器日誌位置
[root@Master ~]# mysql -e "SHOW MASTER LOGS;"+------------------+-----------+| Log_name | File_size |+------------------+-----------+| mysql-bin.000001 | 26753 || mysql-bin.000002 | 921736 || mysql-bin.000003 | 245 |+------------------+-----------+
5. 建立一個用來複制資料的賬戶
[root@Master ~]# mysql -e "GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'192.168.73.%' IDENTIFIED BY 'CentOS';"
從節點配置
1. 修改配置檔案
[][]read-onlyserver-id=2
2. 啟動服務
[]
此處開始構建錯誤配置，以下所有CHANGE MASTER TO配置均為錯誤
3. 配置CHANGE MASTER TO
MariaDB [(none)]> CHANGE MASTER TO-> MASTER_HOST='master2.mycompany.com',-> MASTER_USER='replication',-> MASTER_PASSWORD='bigs3cret',-> MASTER_PORT=3306,-> MASTER_LOG_FILE='master2-bin.001',-> MASTER_LOG_POS=4,-> MASTER_CONNECT_RETRY=10;Query OK, 0 rows affected (0.00 sec)
4. 檢視下SLAVE STATUS
MariaDB [(none)]> SHOW SLAVE STATUS\G;*************************** 1. row ***************************Slave_IO_State:Master_Host: master2.mycompany.comMaster_User: replicationMaster_Port: 3306Connect_Retry: 10Master_Log_File: master2-bin.001Read_Master_Log_Pos: 4Relay_Log_File: mariadb-relay-bin.000001Relay_Log_Pos: 4Relay_Master_Log_File: master2-bin.001Slave_IO_Running: NoSlave_SQL_Running: No...以下省略...
5. 啟動複製執行緒
MariaDB [(none)]> START SLAVE;
6. 再次檢視SLAVE STATUS
MariaDB [(none)]> SHOW SLAVE STATUS\G;1. row ***************************Slave_IO_State: Connecting to masterMaster_Host: master2.mycompany.comMaster_User: replicationMaster_Port: 3306Connect_Retry: 10Master_Log_File: master2-bin.001Read_Master_Log_Pos: 4Relay_Log_File: mariadb-relay-bin.000001Relay_Log_Pos: 4Relay_Master_Log_File: master2-bin.001Slave_IO_Running: ConnectingSlave_SQL_Running: Yes...以下省略...
執行緒已經正常啟動
主伺服器匯入資料進行測試
[root@Master ~]# mysql < hellodb_innodb.sql[root@Master ~]# mysql -e "SHOW DATABASES;"+--------------------+| Database |+--------------------+| information_schema || hellodb || mysql || performance_schema || test |+--------------------+
從伺服器檢視是否同步(CHANGE MASTER TO資訊不對怎麼可能同步)
MariaDB [(none)]> SHOW DATABASES;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || test |+--------------------+4 rows in set (0.00 sec)
以下為錯誤解決方法
由於錯誤發生在CHANGE MASTER TO所以此處將CHANG MASTER TO部分糾正就行
1. 首先將從伺服器的複製執行緒停止
MariaDB [(none)]> STOP SLAVE;Query OK, 0 rows affected (17.48 sec)
2. 將從伺服器上的SLAVE資訊重置
MariaDB [(none)]> RESET SLAVE;Query OK, 0 rows affected (0.01 sec)
3. 重新輸入正確的CHANGE MASTER TO資訊
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.73.110',MASTER_USER='repluser',MASTER_PASSWORD='centos',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=245;Query OK, 0 rows affected (0.01 sec)
4. 檢視SLAVE STATUS;
MariaDB [(none)]> SHOW SLAVE STATUS\G;*************************** 1. row ***************************Slave_IO_State:Master_Host: 192.168.73.110Master_User: repluserMaster_Port: 3306Connect_Retry: 10Master_Log_File: mysql-bin.000003Read_Master_Log_Pos: 245Relay_Log_File: mariadb-relay-bin.000001Relay_Log_Pos: 4Relay_Master_Log_File: mysql-bin.000003Slave_IO_Running: NoSlave_SQL_Running: NoReplicate_Do_DB:#此處資訊已經改為正確
5. 重新啟動執行緒
MariaDB [(none)]> START SLAVE;Query OK, 0 rows affected (0.00 sec)
6. 再次檢視SLAVE STATUS;
MariaDB [(none)]> SHOW SLAVE STATUS\G;1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.73.110Master_User: repluserMaster_Port: 3306Connect_Retry: 10Master_Log_File: mysql-bin.000003Read_Master_Log_Pos: 7384 #已經有資料複製過來了Relay_Log_File: mariadb-relay-bin.000002Relay_Log_Pos: 7668Relay_Master_Log_File: mysql-bin.000003Slave_IO_Running: YesSlave_SQL_Running: Yes#IO和SQL執行緒已經啟動
7. 檢視下從節點內的庫是否已經同步
MariaDB [(none)]> SHOW DATABASES;+--------------------+| Database |+--------------------+| information_schema || hellodb | #hellodb庫已經從主節點中複製過來了| mysql || performance_schema || test |+--------------------+5 rows in set (0.01 sec)
- 其他說明：
如果生產中，發生主從節點之間的資料偏差較大並且遲遲不能同步，可以考慮將從伺服器全部清除從新配置從伺服器。
關於sql_slave_skip_counter的使用方法
當發生主鍵衝突時，從伺服器會卡在出錯的位置不再進行服務，此種錯誤一般會出現在主主複製或者從伺服器已經佔用了某條記錄的情況下，此時可以使用此選項來忽略錯誤。
構建錯誤
此處繼續沿用剛才的主從複製環境
1. 在從伺服器上建立一條記錄
MariaDB [(none)]> INSERT hellodb.teachers VALUE (5,'Li Xiaolong',30,'M');Query OK, 1 row affected (0.00 sec)
2. 在主伺服器上也建立一條主鍵相同的記錄
MariaDB [(none)]> INSERT hellodb.teachers VALUE (5,'Xiao Yan',20,'M');Query OK, 1 row affected (0.00 sec)
3. 傳回從節點檢視SLAVE STATUS
MariaDB [(none)]> SHOW SLAVE STATUS\G;1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.73.110Master_User: repluserMaster_Port: 3306Connect_Retry: 10Master_Log_File: mysql-bin.000003Read_Master_Log_Pos: 7576Relay_Log_File: mariadb-relay-bin.000002Relay_Log_Pos: 7668Relay_Master_Log_File: mysql-bin.000003Slave_IO_Running: YesSlave_SQL_Running: NoReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 1062Last_Error: Could not execute Write_rows event on table hellodb.teachers; Duplicate entry '5' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000003, end_log_pos 7549Skip_Counter: 0Exec_Master_Log_Pos: 7384Relay_Log_Space: 8156Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: NULLMaster_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 1062Last_SQL_Error: Could not execute Write_rows event on table hellodb.teachers; Duplicate entry '5' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000003, end_log_pos 7549Replicate_Ignore_Server_Ids:Master_Server_Id: 11 row in set (0.00 sec)
4. 從節點已經出錯，在主節點繼續新增記錄
MariaDB [(none)]> INSERT hellodb.teachers VALUE (6,'Xiao Xuner',20,'M');Query OK, 1 row affected (0.00 sec)
5. 此時從節點已經不會再繼續從主節點複製資訊
MariaDB [(none)]> SELECT * FROM hellodb.teachers WHERE tid>4;+-----+-------------+-----+--------+| TID | Name | Age |Gender |+-----+-------------+-----+--------+| 5 | Li Xiaolong | 30 | M | #此為剛才從節點新增的記錄+-----+-------------+-----+--------+1 row in set (0.00 sec)
排錯
1. 使用sql_slave_skip_counter變數忽略錯誤
MariaDB [(none)]> SET GLOBAL sql_slave_skip_counter=1;Query OK, 0 rows affected (0.00 sec)
2. 停止執行緒並重新啟動
MariaDB [(none)]> STOP SLAVE;Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> START SLAVE;Query OK, 0 rows affected (0.00 sec)
3. 檢視slave status狀態，此時已經沒有報錯的資訊
MariaDB [(none)]> SHOW SLAVE STATUS\G;1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.73.110Master_User: repluserMaster_Port: 3306Connect_Retry: 10Master_Log_File: mysql-bin.000003Read_Master_Log_Pos: 7770Relay_Log_File: mariadb-relay-bin.000003Relay_Log_Pos: 529Relay_Master_Log_File: mysql-bin.000003Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 7770Relay_Log_Space: 8634Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 11 row in set (0.00 sec)
4. 在從伺服器上檢視teachers表
MariaDB [(none)]> SELECT * FROM hellodb.teachers WHERE tid>4;+-----+-------------+-----+--------+| TID | Name | Age | Gender |+-----+-------------+-----+--------+| 5 | Li Xiaolong | 30 | M || 6 | Xiao Xuner | 20 | M | #此時剛才在主節點插入的6號記錄已經複製過來+-----+-------------+-----+--------+2 rows in set (0.00 sec)
以上為主從複製時出錯的一些相關的修複方法，如果有幫助，感謝分享+在看；大家對什麼內容感興趣，也歡迎大家在留言區評論哦。
出處：https://www.linuxidc.com/Linux/2019-05/158646p2.htm
編輯：尹文敏
朋友會在“發現-看一看”看到你“在看”的內容