mysql gtid 复制跳过错误

桃扇骨 2022-06-09 14:30 167阅读 0赞

在从机查看当前执行出错的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:

**方法1：**

(1)停止slave进程

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;

**解决办法二：重置master方法跳过错误**  
  
  
mysql>  STOP SLAVE;  
mysql> RESET MASTER;  
mysql>   SET @@GLOBAL.GTID\_PURGED ='8f9e146f-0a18-11e7-810a-0050568833c8:1-4'  
mysql>  START SLAVE;  
  
  
上面这些命令的用意是，忽略8f9e146f-0a18-11e7-810a-0050568833c8:1-4 这个GTID事务，下一次事务接着从 5 这个GTID开始，即可跳过上述错误。

**解决办法三：使用pt-slave-restart工具**  
  
  
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