您的位置:金沙游乐场85155 > 大数据库 > MySQL GTID复制错误处理之跳过错误,mysqlgtid

MySQL GTID复制错误处理之跳过错误,mysqlgtid

发布时间:2019-10-09 11:58编辑:大数据库浏览(85)

    某Slave报错信息:

    MySQL GTID复制错误处理之跳过错误,mysqlgtid

    某Slave报错信息:

    mysql> show slave statusG;
    

    图片 1

    mysql> show slave statusG;
    *************************** 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
    

    View Code

    GTID的复制对于错误信息的可读性不是很好,但可以通过错误代码(1007)从监控表replication_applier_status_by_worker查看:

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

    图片 2

    mysql> select * from performance_schema.replication_applier_status_by_worker where LAST_ERROR_NUMBER=1007G
    *************************** 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)
    

    View Code

    使用GTID跳过错误的方法:找到错误的GTID跳过(通过Exec_Master_Log_Pos去binlog里找GTID,或则通过上面监控表replication_applier_status_by_worker找到GTID,也可以通过Executed_Gtid_Set算出GTID),这里使用监控表来找到错误的GTID。找到GTID之后,跳过错误的步骤

    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 statusG;
    

    图片 3

    mysql> show slave statusG;
    *************************** 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
    

    View Code

    打完收工

    本文地址:http://www.cnblogs.com/ajiangg/p/6558714.html

    http://www.bkjia.com/Mysql/1200429.htmlwww.bkjia.comtruehttp://www.bkjia.com/Mysql/1200429.htmlTechArticleMySQL GTID复制错误处理之跳过错误,mysqlgtid 某Slave报错信息: mysql show slave statusG; mysql show slave statusG; *************************** 1 . row **********...

    mysql> show slave statusG;
    

    图片 4图片 5

    mysql> show slave statusG;
    *************************** 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
    

    View Code

    GTID的复制对于错误信息的可读性不是很好,但可以通过错误代码(1007)从监控表replication_applier_status_by_worker查看:

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

    图片 6图片 7

    mysql> select * from performance_schema.replication_applier_status_by_worker where LAST_ERROR_NUMBER=1007G
    *************************** 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)
    

    View Code

    本文由金沙游乐场85155发布于大数据库,转载请注明出处:MySQL GTID复制错误处理之跳过错误,mysqlgtid

    关键词:

上一篇:没有了

下一篇:使用FriendFeed来提升MySQL性能的方法