Mysql主从同步异常处理

Mysql主从同步异常处理
参考文档：https://blog.csdn.net/weixin_34684094/article/details/113204192 mysql 主从修复_MySQL主从不一致的修复过程



执行恢复
tar -xvf xtrfullbackup-20230206.tar.gz
innobackupex --apply-log --user-memory=1G /opt/PRO89.40/dbback/backup-db/FullBakup/xtrfullbackup-20230208
innobackupex --datadir=/data/mysql/data/ --copy-back /opt/PRO89.40/dbback/backup-db/FullBakup/xtrfullbackup-20230208
chown -R mysql.mysql /data/mysql/data/
启动数据库
su - mysql -c "/data/mysql/install/bin/mysqld_safe --defaults-file=/data/mysql/conf/my.cnf &"

错误日志 show slave status： Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 0 failed executing transaction '345d83dc-81d7-11ed-a7e4-0242ac120002:2964683' at master log mysql-bin.001686, end_log_pos 25548042. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.


错误日志 mysql error.log：[Warning] Slave SQL for channel '': Worker 0 failed executing transaction '345d83dc-81d7-11ed-a7e4-0242ac120002:2964684' at master log mysql-bin.001686, end_log_pos 26086812; Could not execute Delete_rows event on table rqwl-wms-prod.location_inventory_his; Unknown error 1032, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log FIRST, end_log_pos 26086812, Error_code: 1032


处理办法：
办法1：逐个处理 因错误太多，使用办法2
mysql> STOP SLAVE;
mysql> SET @@SESSION.GTID_NEXT = '345d83dc-81d7-11ed-a7e4-0242ac120002:2964682';
mysql> BEGIN; COMMIT;
mysql> SET @@SESSION.GTID_NEXT = AUTOMATIC;
mysql> START SLAVE;

办法2：set global slave_exec_mode=IDEMPOTENT; stop slave;start slave;
关闭： set global slave_exec_mode=STRICT;

处理结果：
select count(1) from location_inventory_his;
89.39：58006
89.40：58006
1.12：58806 数据不一致 方法1 方法2处理的结果都是这样


@@@@@@@@@@@@@@@@@@@@@@错误日志及处理步骤明细 begin@@@@@@@@@@@@@@@@@@@@@@
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Queueing master event to the relay log
Master_Host: 172.16.89.39
Master_User: slave
Master_Port: 30001
Connect_Retry: 60
Master_Log_File: mysql-bin.001686
Read_Master_Log_Pos: 453953003
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 414
Relay_Master_Log_File: mysql-bin.001686
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1032
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 0 failed executing transaction '345d83dc-81d7-11ed-a7e4-0242ac120002:2964682' at master log mysql-bin.001686, end_log_pos 25183195. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Skip_Counter: 0
Exec_Master_Log_Pos: 25174637
Relay_Log_Space: 428778987
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1032
Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 0 failed executing transaction '345d83dc-81d7-11ed-a7e4-0242ac120002:2964682' at master log mysql-bin.001686, end_log_pos 25183195. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Replicate_Ignore_Server_Ids:
Master_Server_Id: 100
Master_UUID: 345d83dc-81d7-11ed-a7e4-0242ac120002
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 230208 13:13:19
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 345d83dc-81d7-11ed-a7e4-0242ac120002:2964682-2965868
Executed_Gtid_Set: 345d83dc-81d7-11ed-a7e4-0242ac120002:1-2964681,
72fd9912-81d7-11ed-a9f2-0242ac120002:1
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.01 sec)

mysql> STOP SLAVE;
Query OK, 0 rows affected (0.01 sec)

mysql> SET @@SESSION.GTID_NEXT = '345d83dc-81d7-11ed-a7e4-0242ac120002:2964682';
Query OK, 0 rows affected (0.00 sec)

mysql> BEGIN; COMMIT;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> SET @@SESSION.GTID_NEXT = AUTOMATIC;
Query OK, 0 rows affected (0.00 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.89.39
Master_User: slave
Master_Port: 30001
Connect_Retry: 60
Master_Log_File: mysql-bin.001692
Read_Master_Log_Pos: 324040258
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 414
Relay_Master_Log_File: mysql-bin.001686
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1032
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 0 failed executing transaction '345d83dc-81d7-11ed-a7e4-0242ac120002:2964683' at master log mysql-bin.001686, end_log_pos 25548042. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Skip_Counter: 0
Exec_Master_Log_Pos: 25174637
Relay_Log_Space: 3521684610
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1032
Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 0 failed executing transaction '345d83dc-81d7-11ed-a7e4-0242ac120002:2964683' at master log mysql-bin.001686, end_log_pos 25548042. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Replicate_Ignore_Server_Ids:
Master_Server_Id: 100
Master_UUID: 345d83dc-81d7-11ed-a7e4-0242ac120002
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 230208 13:18:17
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 345d83dc-81d7-11ed-a7e4-0242ac120002:2964682-2974351
Executed_Gtid_Set: 345d83dc-81d7-11ed-a7e4-0242ac120002:1-2964682,
72fd9912-81d7-11ed-a9f2-0242ac120002:1
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

mysql> stop slave;
Query OK, 0 rows affected (0.04 sec)

mysql> STOP SLAVE;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql> SET @@SESSION.GTID_NEXT = '345d83dc-81d7-11ed-a7e4-0242ac120002:2964683';
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> BEGIN; COMMIT;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> SET @@SESSION.GTID_NEXT = AUTOMATIC;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> START SLAVE;
Query OK, 0 rows affected (0.03 sec)

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.89.39
Master_User: slave
Master_Port: 30001
Connect_Retry: 60
Master_Log_File: mysql-bin.001692
Read_Master_Log_Pos: 361990794
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 365261
Relay_Master_Log_File: mysql-bin.001686
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1032
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 0 failed executing transaction '345d83dc-81d7-11ed-a7e4-0242ac120002:2964684' at master log mysql-bin.001686, end_log_pos 25912889. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Skip_Counter: 0
Exec_Master_Log_Pos: 25539484
Relay_Log_Space: 3559635653
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1032
Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 0 failed executing transaction '345d83dc-81d7-11ed-a7e4-0242ac120002:2964684' at master log mysql-bin.001686, end_log_pos 25912889. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Replicate_Ignore_Server_Ids:
Master_Server_Id: 100
Master_UUID: 345d83dc-81d7-11ed-a7e4-0242ac120002
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 230208 13:20:03
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 345d83dc-81d7-11ed-a7e4-0242ac120002:2964682-2974455
Executed_Gtid_Set: 345d83dc-81d7-11ed-a7e4-0242ac120002:1-2964683,
72fd9912-81d7-11ed-a9f2-0242ac120002:1
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

mysql> set global slave_exec_mode=IDEMPOTENT;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.89.39
Master_User: slave
Master_Port: 30001
Connect_Retry: 60
Master_Log_File: mysql-bin.001692
Read_Master_Log_Pos: 389723878
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 365261
Relay_Master_Log_File: mysql-bin.001686
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1032
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 0 failed executing transaction '345d83dc-81d7-11ed-a7e4-0242ac120002:2964684' at master log mysql-bin.001686, end_log_pos 25912889. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Skip_Counter: 0
Exec_Master_Log_Pos: 25539484
Relay_Log_Space: 3587368737
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1032
Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 0 failed executing transaction '345d83dc-81d7-11ed-a7e4-0242ac120002:2964684' at master log mysql-bin.001686, end_log_pos 25912889. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Replicate_Ignore_Server_Ids:
Master_Server_Id: 100
Master_UUID: 345d83dc-81d7-11ed-a7e4-0242ac120002
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 230208 13:20:03
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 345d83dc-81d7-11ed-a7e4-0242ac120002:2964682-2974531
Executed_Gtid_Set: 345d83dc-81d7-11ed-a7e4-0242ac120002:1-2964683,
72fd9912-81d7-11ed-a9f2-0242ac120002:1
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.89.39
Master_User: slave
Master_Port: 30001
Connect_Retry: 60
Master_Log_File: mysql-bin.001692
Read_Master_Log_Pos: 391913332
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 365261
Relay_Master_Log_File: mysql-bin.001686
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1032
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 0 failed executing transaction '345d83dc-81d7-11ed-a7e4-0242ac120002:2964684' at master log mysql-bin.001686, end_log_pos 25912889. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Skip_Counter: 0
Exec_Master_Log_Pos: 25539484
Relay_Log_Space: 3589558191
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1032
Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 0 failed executing transaction '345d83dc-81d7-11ed-a7e4-0242ac120002:2964684' at master log mysql-bin.001686, end_log_pos 25912889. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Replicate_Ignore_Server_Ids:
Master_Server_Id: 100
Master_UUID: 345d83dc-81d7-11ed-a7e4-0242ac120002
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 230208 13:20:03
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 345d83dc-81d7-11ed-a7e4-0242ac120002:2964682-2974537
Executed_Gtid_Set: 345d83dc-81d7-11ed-a7e4-0242ac120002:1-2964683,
72fd9912-81d7-11ed-a9f2-0242ac120002:1
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.89.39
Master_User: slave
Master_Port: 30001
Connect_Retry: 60
Master_Log_File: mysql-bin.001692
Read_Master_Log_Pos: 403590420
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 9486436
Relay_Master_Log_File: mysql-bin.001686
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: 34660659
Relay_Log_Space: 3601235786
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: 9844
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: 100
Master_UUID: 345d83dc-81d7-11ed-a7e4-0242ac120002
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Reading event from the relay log
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 345d83dc-81d7-11ed-a7e4-0242ac120002:2964682-2974569
Executed_Gtid_Set: 345d83dc-81d7-11ed-a7e4-0242ac120002:1-2964708,
72fd9912-81d7-11ed-a9f2-0242ac120002:1
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

mysql> set global slave_exec_mode=STRICT;
Query OK, 0 rows affected (0.00 sec)

mysql> stop slave;
Query OK, 0 rows affected (0.10 sec)

mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.89.39
Master_User: slave
Master_Port: 30001
Connect_Retry: 60
Master_Log_File: mysql-bin.001692
Read_Master_Log_Pos: 413807872
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 106170891
Relay_Master_Log_File: mysql-bin.001686
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: 131345114
Relay_Log_Space: 3611453745
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: 9606
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: 100
Master_UUID: 345d83dc-81d7-11ed-a7e4-0242ac120002
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Waiting for dependent transaction to commit
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 345d83dc-81d7-11ed-a7e4-0242ac120002:2964682-2974597
Executed_Gtid_Set: 345d83dc-81d7-11ed-a7e4-0242ac120002:1-2964976,
72fd9912-81d7-11ed-a9f2-0242ac120002:1
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
@@@@@@@@@@@@@@@@@@@@@@错误日志及处理步骤明细 begin@@@@@@@@@@@@@@@@@@@@@@