一、传统binlog主从复制，跳过报错方法

一、传统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)

通过以上步骤，就跳过了这次的GTID报错的事务，如果 start slave 之后还是有报错，那么就按照此步骤继续跳过；

经验丰富的话，基本不用查询事务，通过 Executed_Gtid_Set 就可以判断出报错的事务是 fea89052-11ef-11eb-b241-00163e00a190:5615 了；因为执行事务，到 fea89052-11ef-11eb-b241-00163e00a190:1-5614 的时候报错了，应该可以判断是 5615事务出现的错误；

stop slave;
set @@session.gtid_next='5c450c79-20d1-11eb-a6c1-c4ff1f7664a7:291104367';
begin;
COMMIT;
set @@session.gtid_next=automatic;
start slave;

1：和 sql_slave_skip_counter 比，有什么好处？
2：和 slave-skip-errors = N比，有什么好处？