[技术干货] mysql 主从复制如何跳过报错

一、传统binlog主从复制,跳过报错方法
mysql> stop slave;
mysql> set global sql_slave_skip_counter = 1;
mysql> start slave;
mysql> show slave status \G

二、GTID主从复制,跳过报错方法

mysql> stop slave; #先关闭slave复制;
mysql> change master to ...省略... #配置主从复制;
mysql> show slave status\G #查看主从状态;

发现报错:

mysql> show slave status\G
*************************** 1. row ***************************
       Slave_IO_State: Waiting for master to send event
        Master_Host: 172.19.195.212
        Master_User: master-slave
        Master_Port: 3306
       Connect_Retry: 60
      Master_Log_File: mysql-bin.000021
    Read_Master_Log_Pos: 194
       Relay_Log_File: nginx-003-relay-bin.000048
       Relay_Log_Pos: 454
   Relay_Master_Log_File: mysql-bin.000016
      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: 1007
         Last_Error: Error 'Can't create database 'code'; database exists' on query. Default database: 'code'. Query: 'create database code'
        Skip_Counter: 0
    Exec_Master_Log_Pos: 8769118
      Relay_Log_Space: 3500
      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: 1007
       Last_SQL_Error: Error 'Can't create database 'code'; database exists' on query. Default database: 'code'. Query: 'create database code'
Replicate_Ignore_Server_Ids:
      Master_Server_Id: 100
        Master_UUID: fea89052-11ef-11eb-b241-00163e00a190
      Master_Info_File: /usr/local/mysql/data/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: 201022 09:31:29
       Master_SSL_Crl:
     Master_SSL_Crlpath:
     Retrieved_Gtid_Set: fea89052-11ef-11eb-b241-00163e00a190:8-5617
     Executed_Gtid_Set: a56c9b04-11f1-11eb-a855-00163e128853:1-11224,
fea89052-11ef-11eb-b241-00163e00a190:1-5614
       Auto_Position: 1
    Replicate_Rewrite_DB:
        Channel_Name:
     Master_TLS_Version:
1 row in set (0.01 sec)

可以看到 Slave_SQL_Running 为 NO,表示运行取回的二进制日志出了问题;
在 Last_Error 中也可以看到大概的报错;(因为我之前的操作,大概可以判断出 是因为主库的二进制日志中有创建code库的sql,而从库上我已经创建了这个库,应该是产生了冲突;)

解决方法:

1、如果清楚自己之前的操作,可以将从库中产生冲突的库删除;
2、或者通过跳过GTID报错的事务的方法

--- 通过 Last_SQL_Errno 报错编号查询具体的报错事务
mysql> select * from performance_schema.replication_applier_status_by_worker where LAST_ERROR_NUMBER=1007\G
*************************** 1. row ***************************
    CHANNEL_NAME:
     WORKER_ID: 0
     THREAD_ID: NULL
   SERVICE_STATE: OFF
LAST_SEEN_TRANSACTION: fea89052-11ef-11eb-b241-00163e00a190:5615
 LAST_ERROR_NUMBER: 1007
 LAST_ERROR_MESSAGE: Error 'Can't create database 'code'; database exists' on query. Default database: 'code'. Query: 'create database code'
LAST_ERROR_TIMESTAMP: 2020-10-22 09:31:29
1 row in set (0.00 sec)

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

--- 跳过查找到报错的事务(LAST_SEEN_TRANSACTION 的值)
mysql> set @@session.gtid_next='fea89052-11ef-11eb-b241-00163e00a190:5615';
Query OK, 0 rows affected (0.00 sec)

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

--- 提交一个空的事务,因为设置gtid_next后,gtid的生命周期开始了,必须通过显性的提交一个事务来结束;
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

--- 设置回自动模式;
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)