MySQL主從複製問題總結及排查過程分享

一、概述

mysql主從是常用的高可用架構之一，也是使用最廣泛的的系統架構。在生產環境中mysql主從複製有時會出現複製錯誤問題。

MySQL主從複製中的問題（Coordinator stopped beacause there were errors in the workers......）

二、mysql主從複製原理

mysql主從複製是一個非同步複製過程（總體感覺是實時同步的），mysql主從複製整個過程是由三個執行緒完成。slave端有兩個執行緒（SQL執行緒和IO執行緒），Master端有另一個（IO執行緒）。

MYSQL主從複製過程

在Slave伺服器上執行start slave，開啟主從複製開關。

此時，Slave 伺服器上的 IO 執行緒透過 Master 伺服器上授權複製使用者的請求連線到 Master 伺服器。它還請求從 binlog 日誌檔案的指定位置傳送 binlog 日誌內容。（配置主從複製任務時執行change master命令時指定日誌檔名和位置）

Master伺服器收到Slave伺服器IO執行緒的請求後，Master伺服器上的IO執行緒是基於Slave的。伺服器的IO執行緒請求的資訊在指定binlog日誌檔案的指定位置後讀取binlog日誌資訊，然後返回給Slave端IO執行緒。除了binlog日誌內容，在日誌內容返回後Master伺服器端還有一個新的binlog。 binlog 中的檔名和下一個指定的更新位置。

當 Slave 伺服器的 IO 執行緒從 Master 伺服器獲取 IO 執行緒傳送的日誌內容、日誌檔案和位置點時，新增 binlog。日誌內容依次寫入Slave端自身的relay log檔案（mysql-relay-bin。xxxxxx）的末尾。並將新的binlog檔名和位置記錄到master-info檔案中，以便下次讀取Master端新的binlog日誌時，可以告訴Master伺服器從新的binlog日誌中從哪個檔案以及從哪裡開始請求新的binlog日誌內容。

Slave server端的SQL執行緒實時檢測本地relay log中新增的日誌內容，及時relay log。該檔案的內容被解析成在Master端執行的SQL語句的內容，在Slave伺服器本身按照語句的順序執行SQL的應用。

經過上述過程，可以保證在Master和Slave端執行相同的SQL語句。當複製狀態正常時，Master 端和lave端的資料是完全一致的。

三、問題及解決方法

1、show slave status \G 顯示如下報錯資訊：

Coordinator stopped because there were error（s） in the worker（s）。 The most recent failure being： Worker 1 failed executing transaction 。。。

2、根據提示資訊定位報錯位置

情況一："*

*Delete_rows“**

select * from performance_schema。replication_applier_status_by_worker \G

原因

：在master上刪除一條記錄，而slave上找不到。

解決方法

：由於master要刪除一條記錄，而slave上找不到故報錯，這種情況主上都將其刪除了，那麼從機可以直接跳過。

stop slave；set global sql_slave_skip_counter=1；start slave；

如上命令若報錯：ERROR 1858 （HY000）： sql_slave_skip_counter can not be set when the server is running with @@GLOBAL。GTID_MODE = ON。 Instead， for each transaction that you want to skip， generate an empty transaction with the same GTID as the transaction或者可以換用如下命令：

STOP SLAVE；SET @@SESSION。GTID_NEXT= ‘f396f867-d755-11xxx85-005xxxxxb5a：264261655’ ——在session裡設定gtid_next，即跳過這個GTIDBEGIN； COMMIT； ——設定空事物SET SESSION GTID_NEXT = AUTOMATIC； —— 恢復GTIDSTART SLAVE；xxxx

情況二："Duplicate "

Last_SQL_Error： Could not execute Write_rows event on table xxx；Duplicate entry ‘xxx’ for key ‘PRIMARY’，

原因：在slave已經有該記錄，又在master上插入了同一條記錄

解決方法：在從庫上刪除該記錄，或者跳過該記錄。然後在master上和slave上再分別確認一下。

情況三："Update_rows" (還未碰到待驗證)

Last_SQL_Error： Could not execute Update_rows event on table xxx；Can‘t find record in ’xxx‘，

參考原因：在master上更新一條記錄，而slave上找不到，丟失了資料。

參考方法：在master上，用mysqlbinlog 分析下出錯的binlog日誌在幹什麼。

/usr/local/mysql/bin/mysqlbinlog ——no-defaults -v -v ——base64-output=DECODE-ROWS mysql-bin。000010 | grep -A ’10‘ 794 #120302 12：08：36 server id 22 end_log_pos 794 Update_rows： table id 33 flags： STMT_END_F### UPDATE hcy。t1### WHERE### @1=2 /* INT meta=0 nullable=0 is_null=0 */### @2=’bbc‘ /* STRING（4） meta=65028 nullable=1 is_null=0 */### SET### @1=2 /* INT meta=0 nullable=0 is_null=0 */### @2=’BTV‘ /* STRING（4） meta=65028 nullable=1 is_null=0 */# at 794#120302 12：08：36 server id 22 end_log_pos 821 Xid = 60COMMIT/*！*/；DELIMITER ；# End of log fileROLLBACK /* added by mysqlbinlog */；/*！50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/；

在slave上，查詢下更新後的那條記錄，應該是不存在的。

mysql> select * from t1 where id=2； Empty set （0。00 sec）

然後再到master檢視

ysql> select * from t1 where id=2；+——+————+| id | name |+——+————+| 2 | BTV |+——+————+1 row in set （0。00 sec）

把丟失的資料在slave上填補，然後跳過報錯即可。

mysql> insert into t1 values （2，’BTV‘）；Query OK， 1 row affected （0。00 sec） mysql> select * from t1 where id=2；+——+————+| id | name |+——+————+| 2 | BTV |+——+————+1 row in set （0。00 sec） mysql> stop slave ；set global sql_slave_skip_counter=1；start slave；Query OK， 0 rows affected （0。01 sec）Query OK， 0 rows affected （0。00 sec）Query OK， 0 rows affected （0。00 sec） mysql> show slave status\G；……Slave_IO_Running： YesSlave_SQL_Running： Yes

四、通用解決方法