一、概述

mysql主從是常用的高可用架構(gòu)之一，也是使用最廣泛的的系統(tǒng)架構(gòu)。在生產(chǎn)環(huán)境中mysql主從復(fù)制有時(shí)會(huì)出現(xiàn)復(fù)制錯(cuò)誤問(wèn)題。MySQL主從復(fù)制中的問(wèn)題(Coordinator stopped beacause there were errors in the workers......)

二、mysql主從復(fù)制原理

mysql主從復(fù)制是一個(gè)異步復(fù)制過(guò)程(總體感覺(jué)是實(shí)時(shí)同步的),mysql主從復(fù)制整個(gè)過(guò)程是由三個(gè)線程完成。slave端有兩個(gè)線程(SQL線程和IO線程)，Master端有另一個(gè)(IO線程)。

MYSQL主從復(fù)制過(guò)程

• 在Slave服務(wù)器上執(zhí)行start slave，開啟主從復(fù)制開關(guān)。
• 此時(shí)，Slave 服務(wù)器上的 IO 線程通過(guò) Master 服務(wù)器上授權(quán)復(fù)制用戶的請(qǐng)求連接到 Master 服務(wù)器。它還請(qǐng)求從 binlog 日志文件的指定位置發(fā)送 binlog 日志內(nèi)容。 (配置主從復(fù)制任務(wù)時(shí)執(zhí)行change master命令時(shí)指定日志文件名和位置)
• Master服務(wù)器收到Slave服務(wù)器IO線程的請(qǐng)求后，Master服務(wù)器上的IO線程是基于Slave的。 服務(wù)器的IO線程請(qǐng)求的信息在指定binlog日志文件的指定位置后讀取binlog日志信息，然后返回給Slave端IO線程。除了binlog日志內(nèi)容，在日志內(nèi)容返回后Master服務(wù)器端還有一個(gè)新的binlog。 binlog 中的文件名和下一個(gè)指定的更新位置。
• 當(dāng) Slave 服務(wù)器的 IO 線程從 Master 服務(wù)器獲取 IO 線程發(fā)送的日志內(nèi)容、日志文件和位置點(diǎn)時(shí)，添加 binlog。日志內(nèi)容依次寫入Slave端自身的relay log文件(mysql-relay-bin.xxxxxx)的末尾。并將新的binlog文件名和位置記錄到master-info文件中，以便下次讀取Master端新的binlog日志時(shí)，可以告訴Master服務(wù)器從新的binlog日志中從哪個(gè)文件以及從哪里開始請(qǐng)求新的binlog日志內(nèi)容.
• Slave server端的SQL線程實(shí)時(shí)檢測(cè)本地relay log中新增的日志內(nèi)容，及時(shí)relay log。 該文件的內(nèi)容被解析成在Master端執(zhí)行的SQL語(yǔ)句的內(nèi)容，在Slave服務(wù)器本身按照語(yǔ)句的順序執(zhí)行SQL的應(yīng)用。
• 經(jīng)過(guò)上述過(guò)程，可以保證在Master和Slave端執(zhí)行相同的SQL語(yǔ)句。當(dāng)復(fù)制狀態(tài)正常時(shí)，Master 端和lave端的數(shù)據(jù)是完全一致的。

三、問(wèn)題及解決方法

1、show slave status \G 顯示如下報(bào)錯(cuò)信息：

Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction ...

2、根據(jù)提示信息定位報(bào)錯(cuò)位置

情況一："**Delete_rows"**

select * from performance_schema.replication_applier_status_by_worker \G

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

解決方法： 由于master要?jiǎng)h除一條記錄，而slave上找不到故報(bào)錯(cuò)，這種情況主上都將其刪除了，那么從機(jī)可以直接跳過(guò)。

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

如上命令若報(bào)錯(cuò)：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里設(shè)置gtid_next，即跳過(guò)這個(gè)GTID
BEGIN; COMMIT;       --設(shè)置空事物
SET SESSION GTID_NEXT = AUTOMATIC;     -- 恢復(fù)GTID
START SLAVE;xxxx

情況二："Duplicate "

Last_SQL_Error: Could not execute Write_rows event on table xxx;
Duplicate entry 'xxx' for key 'PRIMARY',

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

解決方法：在從庫(kù)上刪除該記錄，或者跳過(guò)該記錄。然后在master上和slave上再分別確認(rèn)一下。

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

Last_SQL_Error: Could not execute Update_rows event on table xxx;
Can't find record in 'xxx',

參考原因：在master上更新一條記錄，而slave上找不到，丟失了數(shù)據(jù)。

參考方法：在master上，用mysqlbinlog 分析下出錯(cuò)的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上，查找下更新后的那條記錄，應(yīng)該是不存在的。

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)

把丟失的數(shù)據(jù)在slave上填補(bǔ)，然后跳過(guò)報(bào)錯(cuò)即可。

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主從復(fù)制，經(jīng)常會(huì)遇到錯(cuò)誤而導(dǎo)致slave端復(fù)制中斷，這個(gè)時(shí)候一般就需要人工干預(yù)，跳過(guò)錯(cuò)誤才能繼續(xù) 跳過(guò)錯(cuò)誤有兩種方式

4.1 跳過(guò)指定數(shù)量的事務(wù)

mysql>slave stop;
mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1        #跳過(guò)一個(gè)事務(wù)
mysql>slave start

4.2 跳所有錯(cuò)誤或指定類型的錯(cuò)誤

修改mysql的配置文件，通過(guò)slave_skip_errors參數(shù)來(lái)跳所有錯(cuò)誤或指定類型的錯(cuò)誤

vi /etc/my.cnf
[mysqld]
#slave-skip-errors=1062,1053,1146 #跳過(guò)指定error no類型的錯(cuò)誤
#slave-skip-errors=all #跳過(guò)所有錯(cuò)誤