主从跳过事务
普通主从:
跳过一个事务
mysql> set global sql_slave_skip_counter=1;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
跳过一个事务
mysql> set global sql_slave_skip_counter=1;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
GTID主从:
跳过一个GTID
(root@localhost) [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.201
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000013
Read_Master_Log_Pos: 3163
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 2348
Relay_Master_Log_File: mysql-bin.000013
Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_Errno: 1062
Last_Error: Could not execute Write_rows event on table test.t; Duplicate entry '3' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000013, end_log_pos 2611
Skip_Counter: 0
Exec_Master_Log_Pos: 2381
Relay_Log_Space: 3371
Until_Condition: None
Last_SQL_Errno: 1062
Last_SQL_Error: Could not execute Write_rows event on table test.t; Duplicate entry '3' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000013, end_log_pos 2611
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1921681201
Master_UUID: 766d5362-c923-11e6-85b5-000c29de38de
Master_Info_File: /app/mysqldata/3306/data/master.info
Retrieved_Gtid_Set: 766d5362-c923-11e6-85b5-000c29de38de:4-14
Executed_Gtid_Set: 766d5362-c923-11e6-85b5-000c29de38de:1-11,
ff3d06c1-c923-11e6-8fbf-000c295f1558:1-2
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
跳过一个GTID
(root@localhost) [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.201
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000013
Read_Master_Log_Pos: 3163
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 2348
Relay_Master_Log_File: mysql-bin.000013
Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_Errno: 1062
Last_Error: Could not execute Write_rows event on table test.t; Duplicate entry '3' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000013, end_log_pos 2611
Skip_Counter: 0
Exec_Master_Log_Pos: 2381
Relay_Log_Space: 3371
Until_Condition: None
Last_SQL_Errno: 1062
Last_SQL_Error: Could not execute Write_rows event on table test.t; Duplicate entry '3' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000013, end_log_pos 2611
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1921681201
Master_UUID: 766d5362-c923-11e6-85b5-000c29de38de
Master_Info_File: /app/mysqldata/3306/data/master.info
Retrieved_Gtid_Set: 766d5362-c923-11e6-85b5-000c29de38de:4-14
Executed_Gtid_Set: 766d5362-c923-11e6-85b5-000c29de38de:1-11,
ff3d06c1-c923-11e6-8fbf-000c295f1558:1-2
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
(root@localhost) [(none)]> stop slave;
(root@localhost) [(none)]> set GTID_NEXT='766d5362-c923-11e6-85b5-000c29de38de:12';
(root@localhost) [(none)]> begin;commit;
(root@localhost) [(none)]> set GTID_NEXT='AUTOMATIC';
(root@localhost) [(none)]> start slave;
(root@localhost) [(none)]> set GTID_NEXT='766d5362-c923-11e6-85b5-000c29de38de:12';
(root@localhost) [(none)]> begin;commit;
(root@localhost) [(none)]> set GTID_NEXT='AUTOMATIC';
(root@localhost) [(none)]> start slave;