mysql中使 gtid 复制跳过错误的方法

mysql> show slave status G  *************************** 1. row ***************************                 Slave_IO_State: Waiting for master to send event                    Master_Host: 192.168.10.77                    Master_User: lixiaomeng                    Master_Port: 6000                  Connect_Retry: 60                Master_Log_File: mysql-bin.000006            Read_Master_Log_Pos: 334                 Relay_Log_File: db2-relay-bin.000002                  Relay_Log_Pos: 367          Relay_Master_Log_File: mysql-bin.000006               Slave_IO_Running: Yes              Slave_SQL_Running: No                Replicate_Do_DB:             Replicate_Ignore_DB: mysql,information_schema,performance_schema             Replicate_Do_Table:          Replicate_Ignore_Table:         Replicate_Wild_Do_Table:     Replicate_Wild_Ignore_Table:                      Last_Errno: 1051                     Last_Error: Error 'Unknown table 'dba.test'' on query. Default database: 'dba'. Query: 'DROP TABLE `test` /* generated by server */'                   Skip_Counter: 0            Exec_Master_Log_Pos: 154                Relay_Log_Space: 752                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: 1051                 Last_SQL_Error: Error 'Unknown table 'dba.test'' on query. Default database: 'dba'. Query: 'DROP TABLE `test` /* generated by server */'    Replicate_Ignore_Server_Ids:                Master_Server_Id: 1077                    Master_UUID: 3ad791d8-93a4-11e7-bc8f-00163e088e93               Master_Info_File: /data0/mysql/6000/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: 170907 17:38:15                 Master_SSL_Crl:              Master_SSL_Crlpath:              Retrieved_Gtid_Set: 3ad791d8-93a4-11e7-bc8f-00163e088e93:1  ------------------------这一行为执行出错的gtid              Executed_Gtid_Set: 6b344a45-93a8-11e7-9c7c-00163e08914e:1-2                  Auto_Position: 1           Replicate_Rewrite_DB:                    Channel_Name:              Master_TLS_Version:

mysql> STOP SLAVE;  (2)设置事务号，事务号从Retrieved_Gtid_Set获取  在session里设置gtid_next，即跳过这个GTID  mysql> SET @@SESSION.GTID_NEXT= '8f9e146f-0a18-11e7-810a-0050568833c8:4'  （3）设置空事物  mysql> BEGIN; COMMIT;  （4）恢复事物号  mysql> SET SESSION GTID_NEXT = AUTOMATIC;  (5)启动slave进程  mysql> START SLAVE;

mysql>  STOP SLAVE;  mysql> RESET MASTER;  mysql>   SET @@GLOBAL.GTID_PURGED ='8f9e146f-0a18-11e7-810a-0050568833c8:1-4'  mysql>  START SLAVE;

pt-slave-restart工具的作用是监视某些特定的复制错误，然后忽略，并且再次启动SLAVE进程（Watch and restart MySQL replication after errors）。  忽略所有1062错误，并再次启动SLAVE进程  [root@dgt mysql]# pt-slave-resetart -S /var/lib/mysql/mysql.sock —error-numbers=1062  检查到错误信息只要包含 test.t1，就一概忽略，并再次启动 SLAVE 进程  [root@dgt mysql]# pt-slave-resetart -S /var/lib/mysql/mysql.sock  —error-text=”test.t1”  下面举例解决错误问题号              Last_SQL_Error: Could not execute Delete_rows event on table test.t; Can't find record in 't', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000028, end_log_pos 1862    Replicate_Ignore_Server_Ids:                Master_Server_Id: 2                    Master_UUID: 8f9e146f-0a18-11e7-810a-0050568833c8               Master_Info_File: /var/lib/mysql/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: 170421 17:45:11                 Master_SSL_Crl:              Master_SSL_Crlpath:              Retrieved_Gtid_Set: 8f9e146f-0a18-11e7-810a-0050568833c8:1-7              Executed_Gtid_Set: 8f9e146f-0a18-11e7-810a-0050568833c8:1-6,  f7c86e19-24fe-11e7-a66c-005056884f03:1                  Auto_Position: 0           Replicate_Rewrite_DB:                    Channel_Name:              Master_TLS_Version:   1 row in set (0.00 sec)  [root@dgt mysql]# pt-slave-restart  -S  /var/lib/mysql/mysql.sock   --error-numbers=1032   --user=root --password='bc.123456'  2017-04-21T17:53:27 S=/var/lib/mysql/mysql.sock,p=...,u=root mysql-bin.000015         620 1032   2017-04-21T17:54:31 S=/var/lib/mysql/mysql.sock,p=...,u=root mysql-bin.000015        1140 1032

--slave-password=s         Sets the password to be used to connect to the slaves  --slave-user=s         Sets the user to be used to connect to the slaves  --sleep=i            Initial sleep seconds between checking the slave ( default 1)  --socket=s             -S  Socket file to use for connection=  --password=s             -p  Password to use when connecting  pt-slave-resetart             -S./mysql.sock —error-numbers=1032  --error-numbers=h         Only restart this comma-separated list of errors  --host=s             -h  Connect to host  --user=s             -u  User for login if not current user