MySQL 数据库双主单写有快速切换、维护简单等特点， 但是双主双写确有很多问题，有时为了提高服务器资源使用率，使用双主双写， 使用过程中经常遇到各种各样同步中断、数据不一致等问题，不得不再改回单写，常见的问题如下: 问题一:
Slave_SQL_Running: No Last_Errno: 1146 Last_Error: Error executing row event: 'Table 'cjcdb.t1' doesn't exist'
问题场景: 节点一 在对 cjc库t1表执行DML或DDL操作时(例如delete from cjc.t1 where id=xxx;)，同一时间，另一个节点执行了drop table t1;或drop database cjc;等操作，两个节点都执行成功。 由于双主架构，节点一，二执行成功的语句会发送给对方并执行，所以两个节点SQL执行顺序是: 节点一:
1.delete from cjc.t1 where id=xxx; 2.drop table t1;
节点二:
1.drop table t1; 2.delete from cjc.t1 where id=xxx;
由于 节点2 先删除了表，所以执行到delete时会报错，提示表不存在，主从同步Slave_SQL_Running 中断。 问题二:
Slave_SQL_Running: No Last_Errno: 1032 Last_Error: Could not execute Delete_rows event on table cjcdb.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000006, end_log_pos 7157
问题场景:节点一 在对 cjc库t1表执行DML操作时(例如delete from cjc.t1 where id=xxx; 或 update等)，同一时间，另一个节点执行了 truncate table t1;或delete from cjc.t1 where id=xxx;等操作，两个节点都执行成功。 节点一:
1.delete from cjc.t1 where id=xxx; 2.truncate table t1;
节点二:
1.truncate table t1; 2.delete from cjc.t1 where id=xxx;
由于 节点1 执行 delete from cjc.t1 where id=xxx; 语句时成功删除了1条数据，但节点2由于先执行了truncate table t1,导致第二条delete 删除0条数据，和节点1执行结果不一致，所以报错，主从同步Slave_SQL_Running 中断。 问题三: 主键冲突
Slave_SQL_Running: No Last_Errno: 1062 Last_Error: Could not execute Write_rows event on table cjcdb.t2; Duplicate entry '3' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000001, end_log_pos 32635
问题场景: cjcdb.t2表id列是主键，节点1 和 节点2 同时向 cjcdb.t2表id列 插入了相同的主键值，并都插入成功了，导致同步的主键值冲突，主从同步失败。 为什么会有这些问题，难道没有冲突检测吗，比如MySQL MGR 冲突检测、分布式数据库的二阶段提交、全局事物等， MySQL双主还真没有这些冲突检测的功能，所以不适合双写，通常情况下只适合单写，例如通过VIP连接其中一个节点(也就是所谓的主节点)进行写操作，严禁两个节点双写，在主、从节点同时对同一数据操作，或存在延时的情况下执行操作时，可能会出现各种数据不一致的问题。 数据同步中断还算是个小问题，双主双写最大的问题是当数据同步中断后，继续有新数据分别插入两个两个节点，如果重新配置主从同步，以哪个节点数据为主？怎么能不丢数据？这是个难题？ 下面模拟上面三个问题，看下效果： 环境说明:
DB:MySQL 5.7.41 OS:kylin v10 SP1 节点1:192.168.0.73 节点2:192.168.0.73
说明: 下面的故障场景比较简单，不一致的数据很清晰，所以采用跳过事务解决的此问题，但是在实际场景中，情况会比较复杂，因为在主从同步失败后，两个节点可能会继续进行了大量的增、删、改操作，无法直接通过跳过事务或使用binlog解决此问题，甚至会导致丢失数据， 稳妥的方式是重建从库，比如以其中一个节点数据为准，重建从库，如果另一个节点数据有要保留的，需要提前做好数据合并，然后重新配置同步，双写改成单写。 这里最大的误区是，一提到要重建从库总会被人质疑你能力的问题，你到底行不行啊，一出问题就重建？但很多场景下，重建是最安全、最负责人的，如果不考虑数据一致性，跳过不一致的事务肯定比重建数据库更快，但需要谨慎使用。 场景一:
错误如下: Last_Errno: 1146 Last_Error: Error executing row event: 'Table 'cjcdb.t1' doesn't exist' 73节点 创建测试数据 mysql> create database cjcdb; use cjcdb; create table t1(id int); insert into t1 values(1); insert into t1 values(2); 225节点 select * from cjcdb.t1; mysql> select * from cjcdb.t1; +------+ | id | +------+ | 1 | | 2 | +------+ 2 rows in set (0.00 sec) 两个节点，分别停止 sql_thread，模拟同步延时 73: mysql> stop slave sql_thread; 225: mysql> stop slave sql_thread; 73:删除数据 delete from cjcdb.t1 where id=1; 225:删除表 drop table cjcdb.t1; 两个节点，启动 sql_thread 73: mysql> start slave sql_thread; 225: mysql> start slave sql_thread; 225 节点数据同步失败，以为表已经删除，无法继续执行delete操作。 mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.73 Master_User: repl Master_Port: 13307 Connect_Retry: 10 Master_Log_File: mysql-bin.000006 Read_Master_Log_Pos: 4935 Relay_Log_File: mysql-relay-bin.000011 Relay_Log_Pos: 4566 Relay_Master_Log_File: mysql-bin.000006 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: 1146 Last_Error: Error executing row event: 'Table 'cjcdb.t1' doesn't exist' Skip_Counter: 0 Exec_Master_Log_Pos: 4433 Relay_Log_Space: 5175 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: 1146 Last_SQL_Error: Error executing row event: 'Table 'cjcdb.t1' doesn't exist' Replicate_Ignore_Server_Ids: Master_Server_Id: 13113278 Master_UUID: 26bb7a10-3788-11ef-a068-fa163eaf634b 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: 240715 12:17:29 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 26bb7a10-3788-11ef-a068-fa163eaf634b:8-83 Executed_Gtid_Set: 26bb7a10-3788-11ef-a068-fa163eaf634b:8-82, e40c498f-3838-11ef-9d1f-fa163e0fbb49:1-17 Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified 解决方法： 225: 跳过错误 mysql> stop slave; Query OK, 0 rows affected (0.01 sec) GTID复制下，不支持这种跳过方法 mysql> set global sql_slave_skip_counter=1; 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 支持跳过事务 也就是将执行失败的事务，通过begin;commit;的方式替换为一个空事务。 set gtid_next='26bb7a10-3788-11ef-a068-fa163eaf634b:83'; begin; commit; set gtid_next=automatic; start slave; 恢复同步 mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.73 Master_User: repl Master_Port: 13307 Connect_Retry: 10 Master_Log_File: mysql-bin.000006 Read_Master_Log_Pos: 4935 Relay_Log_File: mysql-relay-bin.000012 Relay_Log_Pos: 360 Relay_Master_Log_File: mysql-bin.000006 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: 4935 Relay_Log_Space: 5341 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: 0 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: 13113278 Master_UUID: 26bb7a10-3788-11ef-a068-fa163eaf634b Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 26bb7a10-3788-11ef-a068-fa163eaf634b:8-83 Executed_Gtid_Set: 26bb7a10-3788-11ef-a068-fa163eaf634b:8-83, e40c498f-3838-11ef-9d1f-fa163e0fbb49:1-17 Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)
场景二:
错误如下: Last_Errno: 1032 Last_Error: Could not execute Delete_rows event on table cjcdb.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000006, end_log_pos 7157 Skip_Counter: 0 73节点: mysql> create database cjcdb; use cjcdb; create table t1(id int); insert into t1 values(1); insert into t1 values(2); 225节点: select * from cjcdb.t1; mysql> select * from cjcdb.t1; +------+ | id | +------+ | 1 | | 2 | +------+ 2 rows in set (0.00 sec) 73: mysql> stop slave sql_thread; 225: mysql> stop slave sql_thread; 73： delete from cjcdb.t1 where id=1; 225： truncate table t1; 73: mysql> start slave sql_thread; 225: mysql> start slave sql_thread; 225:同步失败 mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.73 Master_User: repl Master_Port: 13307 Connect_Retry: 10 Master_Log_File: mysql-bin.000006 Read_Master_Log_Pos: 7343 Relay_Log_File: mysql-relay-bin.000012 Relay_Log_Pos: 2298 Relay_Master_Log_File: mysql-bin.000006 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: Could not execute Delete_rows event on table cjcdb.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000006, end_log_pos 7157 Skip_Counter: 0 Exec_Master_Log_Pos: 6873 Relay_Log_Space: 7594 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: Could not execute Delete_rows event on table cjcdb.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000006, end_log_pos 7157 Replicate_Ignore_Server_Ids: Master_Server_Id: 13113278 Master_UUID: 26bb7a10-3788-11ef-a068-fa163eaf634b 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: 240715 12:35:19 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 26bb7a10-3788-11ef-a068-fa163eaf634b:8-90, e40c498f-3838-11ef-9d1f-fa163e0fbb49:18 Executed_Gtid_Set: 26bb7a10-3788-11ef-a068-fa163eaf634b:8-89, e40c498f-3838-11ef-9d1f-fa163e0fbb49:1-19 Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified 73: SQL线程 回放到 主库 的 mysql-bin.000006 的 6873位置时失败 Relay_Master_Log_File: mysql-bin.000006 Exec_Master_Log_Pos: 6873 失败的事务，结束 pos 是 7157。 :the event's master log mysql-bin.000006, end_log_pos 7157 对应的失败的事务ID是：26bb7a10-3788-11ef-a068-fa163eaf634b:90 Retrieved_Gtid_Set: 26bb7a10-3788-11ef-a068-fa163eaf634b:8-90,e40c498f-3838-11ef-9d1f-fa163e0fbb49:18 Executed_Gtid_Set: 26bb7a10-3788-11ef-a068-fa163eaf634b:8-89,e40c498f-3838-11ef-9d1f-fa163e0fbb49:1-19 查看对应事件: mysql> show binlog events in 'mysql-bin.000006' from 6873; +------------------+------+-------------+-----------+-------------+--------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+------+-------------+-----------+-------------+--------------------------------------------------------------------+ | mysql-bin.000006 | 6873 | Gtid | 13113278 | 6938 | SET @@SESSION.GTID_NEXT= '26bb7a10-3788-11ef-a068-fa163eaf634b:90' | | mysql-bin.000006 | 6938 | Query | 13113278 | 7016 | BEGIN | | mysql-bin.000006 | 7016 | Rows_query | 13113278 | 7071 | # delete from cjcdb.t1 where id=1 | | mysql-bin.000006 | 7071 | Table_map | 13113278 | 7117 | table_id: 190 (cjcdb.t1) | | mysql-bin.000006 | 7117 | Delete_rows | 13113278 | 7157 | table_id: 190 flags: STMT_END_F | | mysql-bin.000006 | 7157 | Xid | 13113278 | 7188 | COMMIT /* xid=1314 */ | | mysql-bin.000006 | 7188 | Gtid | 13113225 | 7253 | SET @@SESSION.GTID_NEXT= 'e40c498f-3838-11ef-9d1f-fa163e0fbb49:19' | | mysql-bin.000006 | 7253 | Query | 13113225 | 7343 | use `cjcdb`; truncate table t1 | +------------------+------+-------------+-----------+-------------+--------------------------------------------------------------------+ 8 rows in set (0.00 sec) 跳过 26bb7a10-3788-11ef-a068-fa163eaf634b:90 事务 grid复制 跳过错误 set gtid_next='26bb7a10-3788-11ef-a068-fa163eaf634b:90'; begin; commit; set gtid_next=automatic; start slave; 同步恢复正常: mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.73 Master_User: repl Master_Port: 13307 Connect_Retry: 10 Master_Log_File: mysql-bin.000006 Read_Master_Log_Pos: 7343 Relay_Log_File: mysql-relay-bin.000012 Relay_Log_Pos: 2613 Relay_Master_Log_File: mysql-bin.000006 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: 7343 Relay_Log_Space: 7594 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: 0 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: 13113278 Master_UUID: 26bb7a10-3788-11ef-a068-fa163eaf634b Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 26bb7a10-3788-11ef-a068-fa163eaf634b:8-90, e40c498f-3838-11ef-9d1f-fa163e0fbb49:18 Executed_Gtid_Set: 26bb7a10-3788-11ef-a068-fa163eaf634b:8-90, e40c498f-3838-11ef-9d1f-fa163e0fbb49:1-19 Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)
场景三：主键冲突
错误如下: Last_SQL_Errno: 1062 Last_SQL_Error: Could not execute Write_rows event on table cjcdb.t2; Duplicate entry '3' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000006, end_log_pos 8498 73节点: create table t2(id int,time datetime,primary key(id)); insert into t2 values(1,now()); insert into t2 values(2,now()); 73: stop slave sql_thread; 225: stop slave sql_thread; 插入相同的主键值 73： insert into t2 values(3,now()); 225： insert into t2 values(3,now()); 73: start slave sql_thread; 225: start slave sql_thread; 73:同步失败 mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.225 Master_User: repl Master_Port: 13307 Connect_Retry: 10 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 32666 Relay_Log_File: mysql-relay-bin.000004 Relay_Log_Pos: 1630 Relay_Master_Log_File: mysql-bin.000001 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: 1062 Last_Error: Could not execute Write_rows event on table cjcdb.t2; Duplicate entry '3' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000001, end_log_pos 32635 Skip_Counter: 0 Exec_Master_Log_Pos: 32337 Relay_Log_Space: 2325 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: 1062 Last_SQL_Error: Could not execute Write_rows event on table cjcdb.t2; Duplicate entry '3' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000001, end_log_pos 32635 Replicate_Ignore_Server_Ids: Master_Server_Id: 13113225 Master_UUID: e40c498f-3838-11ef-9d1f-fa163e0fbb49 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: 240715 17:15:09 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 26bb7a10-3788-11ef-a068-fa163eaf634b:83:87:90, e40c498f-3838-11ef-9d1f-fa163e0fbb49:8-20 Executed_Gtid_Set: 26bb7a10-3788-11ef-a068-fa163eaf634b:1-94, cdd7b531-3787-11ef-9cc2-fa163e0fbb49:1-7, e40c498f-3838-11ef-9d1f-fa163e0fbb49:1-19 Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified 225:同步失败 mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.73 Master_User: repl Master_Port: 13307 Connect_Retry: 10 Master_Log_File: mysql-bin.000006 Read_Master_Log_Pos: 8529 Relay_Log_File: mysql-relay-bin.000012 Relay_Log_Pos: 3470 Relay_Master_Log_File: mysql-bin.000006 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: 1062 Last_Error: Could not execute Write_rows event on table cjcdb.t2; Duplicate entry '3' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000006, end_log_pos 8498 Skip_Counter: 0 Exec_Master_Log_Pos: 8200 Relay_Log_Space: 8780 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: 1062 Last_SQL_Error: Could not execute Write_rows event on table cjcdb.t2; Duplicate entry '3' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000006, end_log_pos 8498 Replicate_Ignore_Server_Ids: Master_Server_Id: 13113278 Master_UUID: 26bb7a10-3788-11ef-a068-fa163eaf634b 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: 240715 17:15:08 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 26bb7a10-3788-11ef-a068-fa163eaf634b:8-94, e40c498f-3838-11ef-9d1f-fa163e0fbb49:18 Executed_Gtid_Set: 26bb7a10-3788-11ef-a068-fa163eaf634b:8-93, e40c498f-3838-11ef-9d1f-fa163e0fbb49:1-20 Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified 查看对应事件: 225:Exec_Master_Log_Pos: 32337 mysql> show binlog events in 'mysql-bin.000001' from 32337; +------------------+-------+------------+-----------+-------------+--------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-------+------------+-----------+-------------+--------------------------------------------------------------------+ | mysql-bin.000001 | 32337 | Gtid | 13113225 | 32402 | SET @@SESSION.GTID_NEXT= 'e40c498f-3838-11ef-9d1f-fa163e0fbb49:20' | | mysql-bin.000001 | 32402 | Query | 13113225 | 32488 | BEGIN | | mysql-bin.000001 | 32488 | Rows_query | 13113225 | 32542 | # insert into t2 values(3,now()) | | mysql-bin.000001 | 32542 | Table_map | 13113225 | 32590 | table_id: 133 (cjcdb.t2) | | mysql-bin.000001 | 32590 | Write_rows | 13113225 | 32635 | table_id: 133 flags: STMT_END_F | | mysql-bin.000001 | 32635 | Xid | 13113225 | 32666 | COMMIT /* xid=435 */ | +------------------+-------+------------+-----------+-------------+--------------------------------------------------------------------+ 6 rows in set (0.00 sec) 73:Exec_Master_Log_Pos: 8200 mysql> show binlog events in 'mysql-bin.000006' from 8200; +------------------+------+------------+-----------+-------------+--------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+------+------------+-----------+-------------+--------------------------------------------------------------------+ | mysql-bin.000006 | 8200 | Gtid | 13113278 | 8265 | SET @@SESSION.GTID_NEXT= '26bb7a10-3788-11ef-a068-fa163eaf634b:94' | | mysql-bin.000006 | 8265 | Query | 13113278 | 8351 | BEGIN | | mysql-bin.000006 | 8351 | Rows_query | 13113278 | 8405 | # insert into t2 values(3,now()) | | mysql-bin.000006 | 8405 | Table_map | 13113278 | 8453 | table_id: 192 (cjcdb.t2) | | mysql-bin.000006 | 8453 | Write_rows | 13113278 | 8498 | table_id: 192 flags: STMT_END_F | | mysql-bin.000006 | 8498 | Xid | 13113278 | 8529 | COMMIT /* xid=1347 */ | +------------------+------+------------+-----------+-------------+--------------------------------------------------------------------+ 6 rows in set (0.00 sec) 删除各自插入的数据 73,225: set sql_log_bin=0; delete from t2 where id=3; set sql_log_bin=1; 73: Retrieved_Gtid_Set: 26bb7a10-3788-11ef-a068-fa163eaf634b:83:87:90,e40c498f-3838-11ef-9d1f-fa163e0fbb49:8-20 Executed_Gtid_Set: 26bb7a10-3788-11ef-a068-fa163eaf634b:1-94,cdd7b531-3787-11ef-9cc2-fa163e0fbb49:1-7,e40c498f-3838-11ef-9d1f-fa163e0fbb49:1-19 grid复制 跳过错误 set gtid_next='e40c498f-3838-11ef-9d1f-fa163e0fbb49:20'; begin; commit; set gtid_next=automatic; start slave; 225: Retrieved_Gtid_Set: 26bb7a10-3788-11ef-a068-fa163eaf634b:8-94,e40c498f-3838-11ef-9d1f-fa163e0fbb49:18 Executed_Gtid_Set: 26bb7a10-3788-11ef-a068-fa163eaf634b:8-93,e40c498f-3838-11ef-9d1f-fa163e0fbb49:1-20 set gtid_next='26bb7a10-3788-11ef-a068-fa163eaf634b:94'; begin; commit; set gtid_next=automatic; start slave; 同步恢复: 73: mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.225 Master_User: repl Master_Port: 13307 Connect_Retry: 10 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 32888 Relay_Log_File: mysql-relay-bin.000004 Relay_Log_Pos: 2181 Relay_Master_Log_File: mysql-bin.000001 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: 32888 Relay_Log_Space: 2547 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: 0 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: 13113225 Master_UUID: e40c498f-3838-11ef-9d1f-fa163e0fbb49 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 26bb7a10-3788-11ef-a068-fa163eaf634b:83:87:90:94, e40c498f-3838-11ef-9d1f-fa163e0fbb49:8-20 Executed_Gtid_Set: 26bb7a10-3788-11ef-a068-fa163eaf634b:1-94, cdd7b531-3787-11ef-9cc2-fa163e0fbb49:1-7, e40c498f-3838-11ef-9d1f-fa163e0fbb49:1-20 Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified 225: mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.73 Master_User: repl Master_Port: 13307 Connect_Retry: 10 Master_Log_File: mysql-bin.000006 Read_Master_Log_Pos: 8751 Relay_Log_File: mysql-relay-bin.000012 Relay_Log_Pos: 4021 Relay_Master_Log_File: mysql-bin.000006 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: 8751 Relay_Log_Space: 9002 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: 0 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: 13113278 Master_UUID: 26bb7a10-3788-11ef-a068-fa163eaf634b Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 26bb7a10-3788-11ef-a068-fa163eaf634b:8-94, e40c498f-3838-11ef-9d1f-fa163e0fbb49:18:20 Executed_Gtid_Set: 26bb7a10-3788-11ef-a068-fa163eaf634b:8-94, e40c498f-3838-11ef-9d1f-fa163e0fbb49:1-20 Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified
###chenjuchao 20240720###
欢迎关注我的公众号《IT小Chen》
