<form id="hz9zz"></form>
  • <form id="hz9zz"></form>

      <nobr id="hz9zz"></nobr>

      <form id="hz9zz"></form>

    1. 明輝手游網中心:是一個免費提供流行視頻軟件教程、在線學習分享的學習平臺!

      mysql中使 gtid 復制跳過出錯的方法

      [摘要]在從機查看當前執行出錯的gtid mysql> show slave status \G*************************** 1. row *****************...
      在從機查看當前執行出錯的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 復制跳過錯誤的方法的詳細內容,更多請關注php中文網其它相關文章!


      學習教程快速掌握從入門到精通的SQL知識。




      日韩精品一区二区三区高清