跳过一个GTID

1.如下开启了Auto_Position情况下,如何跳过一个GTID

show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.30.130
                  Master_User: rep
                  Master_Port: 3308
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000017
          Read_Master_Log_Pos: 20009695
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 19799878
        Relay_Master_Log_File: mysql-bin.000017
             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: 1396
                   Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'd74faa2d-5819-11e8-b248-ac853db70398:10603' at master log mysql-bin.000017, end_log_pos 19940428. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 19940096
              Relay_Log_Space: 19869718
              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: 1396
               Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'd74faa2d-5819-11e8-b248-ac853db70398:10603' at master log mysql-bin.000017, end_log_pos 19940428. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 201307908
                  Master_UUID: d74faa2d-5819-11e8-b248-ac853db70398
             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: 180704 16:04:58
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: d74faa2d-5819-11e8-b248-ac853db70398:86-10639
            Executed_Gtid_Set: aa3c46a0-3f9a-11e7-b63e-00e0ed23cd78:1-3,
d74faa2d-5819-11e8-b248-ac853db70398:1-10602
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

2.解析主库的binlog 位置

# at 19940261
#180704 15:58:28 server id 201307908  end_log_pos 19940326 CRC32 0x66a29234     GTID    last_committed=10571    sequence_number=10572   rbr_only=no
SET @@SESSION.GTID_NEXT= 'd74faa2d-5819-11e8-b248-ac853db70398:10603'/*!*/;
# at 19940326
#180704 15:58:28 server id 201307908  end_log_pos 19940428 CRC32 0x869a6e67     Query   thread_id=12528772      exec_time=0     error_code=0
SET TIMESTAMP=1530691108/*!*/;
drop user user_taq@'10.10.100.74'   -- 做了一个drop user 操作,但是从库没有这个用户
/*!*/;
# at 19940428
#180704 15:58:28 server id 201307908  end_log_pos 19940493 CRC32 0x3ad1f0eb     GTID    last_committed=10572    sequence_number=10573   rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'd74faa2d-5819-11e8-b248-ac853db70398:10604'/*!*/;

3.从库跳过这个gtid ,再启动正常(在同步GTID基础上+1)

stop slave;
set gtid_next='d74faa2d-5819-11e8-b248-ac853db70398:10603';  #注意是一个值,不是一个段
begin;commit;
set gtid_next='automatic';
start slave;

 

跳过案例:

           Retrieved_Gtid_Set: 4a133e36-fefa-11ea-ab80-1866daf99b20:551787-14934071
            Executed_Gtid_Set: 4a133e36-fefa-11ea-ab80-1866daf99b20:1-14889655,
511a5428-fef9-11ea-8d21-1866daf9e814:1-6140484535
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

stop slave;
set gtid_next='4a133e36-fefa-11ea-ab80-1866daf99b20:14889656';  #注意是一个值,不是一个段, 在 14889655 基础上加1 
begin;commit;
set gtid_next='automatic';
start slave;

　　

补充如果是传统方式复制,在配置文件加入

slave-skip-errors=1032

然后重启实例

 其他跳过gtid方法:https://mp.weixin.qq.com/s/HuWSBiczaWpc11Ts6rm30w

root@imysql.com [test]> STOP SLAVE;
root@imysql.com [test]> RESET MASTER;
root@imysql.com [test]> SET @@GLOBAL.GTID_PURGED = "3a16ef7a:1-283,f2b6c829:1-32";
root@imysql.com [test]> START SLAVE;

#忽略所有1062错误，并再次启动SLAVE进程
[yejr@imysql.com ]# pt-slave-resetart -S./mysql.sock --error-numbers=1062

#检查到错误信息只要包含 test.yejr，就一概忽略，并再次启动 SLAVE 进程
[yejr@imysql.com ]# pt-slave-resetart -S./mysql.sock --error-text="test.yejr"