您所在的位置:首页 > 新闻中心 > GTID复制错误处理之跳过错误,mysql主从不同步
公司要闻
GTID复制错误处理之跳过错误,mysql主从不同步
发布时间:2020-01-05 23:19
访问量:359

某Slave报错新闻:

翻开丛库状态show slave status\G

mysql> show slave status\G;

从库原来的书文提示:Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '864e6992-0a34-11e7-a98a-7cd30ac6c9ec:148408' at master log mysql-bin.000010, end_log_pos 920578920. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

图片 1图片 2

图片 3

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.206.140
                  Master_User: u_repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 499
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000001
             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: 1007
                   Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '9e2c7c0f-0908-11e7-8230-000c29ab7544:1' at master log mysql-bin.000001, end_log_pos 313. 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: 154
              Relay_Log_Space: 1513
              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: 1007
               Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '9e2c7c0f-0908-11e7-8230-000c29ab7544:1' at master log mysql-bin.000001, end_log_pos 313. 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: 140
                  Master_UUID: 9e2c7c0f-0908-11e7-8230-000c29ab7544
             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: 170316 04:25:29
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 9e2c7c0f-0908-11e7-8230-000c29ab7544:1-2
            Executed_Gtid_Set: 347cbac6-0906-11e7-b957-000c2981a46e:1,
c59a2526-08fd-11e7-a5c7-000c296f2953:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

1、  依据丛库的提示找原因,输入指令

View Code

select * from performance_schema.replication_applier_status_by_worker\G

GTID的复制对于错误消息的可读性不是很好,但足以由此错误代码(1007)从监察和控制表replication_applier_status_by_worker查看:

得到

mysql> select * from performance_schema.replication_applier_status_by_worker where LAST_ERROR_NUMBER=1007\G

图片 4

图片 5图片 6

明亮这些职业爆发在表 r_com_patent 上,定位到表,不过不知道哪一条记下。

mysql> select * from performance_schema.replication_applier_status_by_worker where LAST_ERROR_NUMBER=1007\G
*************************** 1. row ***************************
         CHANNEL_NAME: 
            WORKER_ID: 2
            THREAD_ID: NULL
        SERVICE_STATE: OFF
LAST_SEEN_TRANSACTION: 9e2c7c0f-0908-11e7-8230-000c29ab7544:1
    LAST_ERROR_NUMBER: 1007
   LAST_ERROR_MESSAGE: Worker 1 failed executing transaction '9e2c7c0f-0908-11e7-8230-000c29ab7544:1' at master log mysql-bin.000001, end_log_pos 313; Error 'Can't create database 'mydb'; database exists' on query. Default database: 'mydb'. Query: 'create database mydb'
 LAST_ERROR_TIMESTAMP: 2017-03-16 04:25:29
1 row in set (0.00 sec)

2、  到主库找二进制文件发出了怎么着事。输入指令

View Code

Mysqlbinlog --no-defaults –v –v --base64-output=decode-rows /usr/local/mysql/data/master-bin.000010 | grep –A ‘10’ 920578920

使用GTID跳过不当的法子:找到错误的GTID跳过(通过Exec_Master_Log_Pos去binlog里找GTID,或则通过下面监察和控制表replication_applier_status_by_worker找到GTID,也能够通过Executed_Gtid_Set算出GTID),这里运用监察和控制表来找到错误的GTID。找到GTID之后,跳过错误的手续

 图片 7

mysql> stop slave; #停止同步
Query OK, 0 rows affected (0.02 sec)

mysql> set @@session.gtid_next='9e2c7c0f-0908-11e7-8230-000c29ab7544:1';  #跳过错误的GTID
Query OK, 0 rows affected (0.00 sec)

mysql> begin; #提交一个空事务,因为设置gtid_next后,gtid的生命周期就开始了,必须通过显性的提交一个事务来结束,否则报错:ERROR 1790 (HY000): @@SESSION.GTID_NEXT cannot be changed by a client that owns a
Query OK, 0 rows affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> set @@session.gtid_next=automatic; #设置回自动模式  
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

 

重新分明slave同步景况

到底牢固到记录了。

mysql> show slave status\G;

主库在表r_com_patent做了履新操作,而丛库找不到立异的记录。

图片 8图片 9

具体来讲正是,主库将表r_com_patent中patent_id为45的笔录,字段cid从NULL更正为3253026.而丛库表r_com_patent中patent_id为45的记录,字段cid原来为3253026,由于复制机制就非得找丛库表r_com_patent中patent_id为45况且id为NULL的笔录,所以没找到。。。

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.206.140
                  Master_User: u_repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 499
               Relay_Log_File: localhost-relay-bin.000004
                Relay_Log_Pos: 454
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 499
              Relay_Log_Space: 2024
              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: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 140
                  Master_UUID: 9e2c7c0f-0908-11e7-8230-000c29ab7544
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 9e2c7c0f-0908-11e7-8230-000c29ab7544:1-2
            Executed_Gtid_Set: 347cbac6-0906-11e7-b957-000c2981a46e:1,
9e2c7c0f-0908-11e7-8230-000c29ab7544:1-2,
c59a2526-08fd-11e7-a5c7-000c296f2953:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

3、  消除格局

View Code

1卡塔尔(قطر‎         在master查看那条记下。

打完收工

Select * from r_com_patent where patent_id = 45;

正文地址:

2State of Qatar         在slave上,查找下更新后的这条记下,应该是子虚乌有的。

    Select * from r_com_patent where patent_id = 45;

3卡塔尔         把错过的数量在Slave上补偿大概改过。

    Insert into r_com_patent values(3253026,45);

4State of Qatar         在slave跳过报错的事情。

Stop slave;

Set @@SESSION.GTID_NEXT=’ 864e6992-0a34-11e7-a98a-7cd30ac6c9ec:148408’

Begin;

Commit;

Set @@SESSION.GTID_NEXT = AUTOMATIC;

Start slave;

尔后,再自己研讨壹回

  Show slave status\G