主从SQL线程报错处理

场景：

MySQL主从复制环境中，从库无法进行数据同步，Slave_SQL_Running: No

---

问题描述

在主库进行插入语句insert into 00db2.00bak21 values (10,'0021110');后，从库未查到这条数据信息,并且Slave_SQL_Running状态为No

查看从库状态，报错如下：

Last_Errno: 1396
                   Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '857d06e9-a091-11ec-b901-000c29d0e59f:37' at master log 00db-binlog.000002, end_log_pos 15138. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

---

原因分析：

根据 Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '857d06e9-a091-11ec-b901-000c29d0e59f:37' at master log 00db-binlog.000002, end_log_pos 15138. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.信息查看主库二进制文件

主库操作如下：

show binlog events in '00db-binlog.000002';

信息如下：

CREATE USER '0005'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*009D0D2518F4CA3282E7ACF594DE35B1CD43FC31'

---------------------------------------------------------------------------------------------

根据Last_Errno: 1396查询错误号1396

从库查询sql如下：

select LAST_ERROR_MESSAGE from performance_schema.replication_applier_status_by_worker where LAST_ERROR_NUMBER=1396;

信息如下：

Worker 1 failed executing transaction '857d06e9-a091-11ec-b901-000c29d0e59f:37' at master log 00db-binlog.000002, end_log_pos 15138; Error 'Operation CREATE USER failed for '0005'@'%'' on query. Default database: ''. Query: 'CREATE USER '0005'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*009D0D2518F4CA3282E7ACF594DE35B1CD43FC31''

发现数据不同步原因是有人在从库创建用户，而在主库也从新创建了一次，从库先创建用户，而后主库又创建用户，导致事务一直过不去，直至后来在主库插入数据后，从库数据不同步。

---

解决方案：

手工处理使从库跳过创建用户的事务，具体操作如下：

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

mysql> set @@session.gtid_next='857d06e9-a091-11ec-b901-000c29d0e59f:37';
Query OK, 0 rows affected (0.00 sec)

需要手工提交一下空事务

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

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

再把那个session.gtid_next改回automatic

mysql> set @@session.gtid_next=automatic;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
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: 192.168.16.51
                  Master_User: repuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: 00db-binlog.000002
          Read_Master_Log_Pos: 15832
               Relay_Log_File: 00db-relay.000013
                Relay_Log_Pos: 460
        Relay_Master_Log_File: 00db-binlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

跳过错误事务后  Slave_SQL_Running为Yes，主库插入的数据在从库也可以查到。