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
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》
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
