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，即跳過這個GTID
BEGIN; COMMIT; --設置空事物
SET SESSION GTID_NEXT = AUTOMATIC; -- 恢復GTID
START 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 = 60
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* 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: Yes
Slave_SQL_Running: Yes
四、通用解決方法
mysql主從復制，經常會遇到錯誤而導致slave端復制中斷，這個時候一般就需要人工干預，跳過錯誤才能繼續 跳過錯誤有兩種方式
4.1 跳過指定數量的事務
mysql>slave stop;
mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 #跳過一個事務
mysql>slave start
4.2 跳所有錯誤或指定類型的錯誤
修改mysql的配置文件，通過slave_skip_errors參數來跳所有錯誤或指定類型的錯誤
vi /etc/my.cnf
[mysqld]
#slave-skip-errors=1062,1053,1146 #跳過指定error no類型的錯誤
#slave-skip-errors=all #跳過所有錯誤
