欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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

程序员文章站 2022-05-21 10:13:23
...
在从机查看当前执行出错的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)停止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

以上就是mysql中使 gtid 复制跳过错误的方法的详细内容,更多请关注其它相关文章!

相关标签: mysql gtid 跳过