MySQL學習筆記11複製錯誤處理(二)刪除不存在的行的問題

標籤：bin   pos   connect   space   handler   relay_log   where   導致   int   

 

(1)問題情況

在master上刪除某個資料表的某一行，而該行在slave上並不存在，則slave上的複製過程會出錯。

MySQL的log檔案中發現如下錯誤資訊：

2017-08-15T04:52:19.529509Z 13 [ERROR] Slave SQL for channel ‘‘: Could not execute Delete_rows event on table test.test; Can‘t find record in ‘test‘, Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event‘s master log mysql-bin.000007, end_log_pos 1958, Error_code: 1032

2017-08-15T04:52:19.529575Z 13 [Warning] Slave: Can‘t find record in ‘test‘ Error_code: 1032

2017-08-15T04:52:19.529588Z 13 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log ‘mysql-bin.000007‘ position 1732

 

 

(2)重現問題情境。

(a)在slave上刪除資料表test的某一行資料。

mysql> delete from test where name2=‘001‘;

Query OK, 1 row affected (0.07 sec)

 

mysql> select * from test;

+-------+

| name2 |

+-------+

| 002   |

| 003   |

+-------+

2 rows in set (0.00 sec)

 

(b)在master上刪除資料表test的該行資料。

mysql> select * from test;

+-------+

| name2 |

+-------+

| 001   |

| 002   |

| 003   |

+-------+

3 rows in set (0.00 sec)

 

mysql> delete from test where name2=‘001‘;

Query OK, 1 row affected (0.02 sec)

 

mysql> select * from test;

+-------+

| name2 |

+-------+

| 002   |

| 003   |

+-------+

2 rows in set (0.00 sec)

 

刪除後再插入新的資料。

mysql> insert into test (name2) values( ‘004‘ );

Query OK, 1 row affected (0.02 sec)

 

mysql> select * from test;

+-------+

| name2 |

+-------+

| 002   |

| 003   |

| 004   |

+-------+

3 rows in set (0.00 sec)

 

 

(c)查看slave上的資料。

mysql> select * from test;

+-------+

| name2 |

+-------+

| 002   |

| 003   |

+-------+

2 rows in set (0.00 sec)

可以看到slave中並沒有發現master上新插入的行’004’。

 

(d)查看slave的複製狀態。

mysql> show slave status\G

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: mysql101.coe2coe.me

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000007

          Read_Master_Log_Pos: 2246

               Relay_Log_File: mysql103-relay-bin.000017

                Relay_Log_Pos: 320

        Relay_Master_Log_File: mysql-bin.000007

             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: mysql.%,information_schema.%,performance_schema.%,sys.%

                   Last_Errno: 1032

                   Last_Error: Could not execute Delete_rows event on table test.test; Can‘t find record in ‘test‘, Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event‘s master log mysql-bin.000007, end_log_pos 1958

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 1732

              Relay_Log_Space: 1835

              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: 1032

               Last_SQL_Error: Could not execute Delete_rows event on table test.test; Can‘t find record in ‘test‘, Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event‘s master log mysql-bin.000007, end_log_pos 1958

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 101

                  Master_UUID: a2392929-6dfb-11e7-b294-000c29b1c101

             Master_Info_File: /opt/mysql/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: 170815 12:52:19

               Master_SSL_Crl:

           Master_SSL_Crlpath:

           Retrieved_Gtid_Set:

            Executed_Gtid_Set:

                Auto_Position: 0

         Replicate_Rewrite_DB:

                 Channel_Name:

           Master_TLS_Version:

1 row in set (0.00 sec)

 

 

可以看到，出現了錯誤碼未1032的錯誤資訊，刪除的行不存在。

 

(3)解決辦法。

由於導致出錯的原因是slave中不存在待刪除的行，此時可以直接忽略該錯誤。

mysql> stop slave;

Query OK, 0 rows affected (0.01 sec)

 

mysql> set global sql_slave_skip_counter=1;

Query OK, 0 rows affected (0.00 sec)

 

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

 

注意：在使用基於GTID的複製(GTID_MODE = ON)時，不能使用sql_slave_skip_counter的方法來忽略錯誤；而是應該使用reset master重設mysql.gtid_executed資料表。

 

此時查看複製狀態：

mysql> show slave status\G

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: mysql101.coe2coe.me

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000007

          Read_Master_Log_Pos: 2246

               Relay_Log_File: mysql103-relay-bin.000018

                Relay_Log_Pos: 320

        Relay_Master_Log_File: mysql-bin.000007

             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: mysql.%,information_schema.%,performance_schema.%,sys.%

                   Last_Errno: 0

                   Last_Error:

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 2246

              Relay_Log_Space: 1210

              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: 101

                  Master_UUID: a2392929-6dfb-11e7-b294-000c29b1c101

             Master_Info_File: /opt/mysql/data/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:

            Executed_Gtid_Set:

                Auto_Position: 0

         Replicate_Rewrite_DB:

                 Channel_Name:

           Master_TLS_Version:

1 row in set (0.00 sec)

 

已經恢複到正常狀態了。

查看資料表的資料，也可以看到跟master中的資料完全一樣了，即可以看到新增的行。

mysql> select * from test;

+-------+

| name2 |

+-------+

| 002   |

| 003   |

| 004   |

+-------+

3 rows in set (0.00 sec)

 

至此，刪除不存在的行導致的複製錯誤，已經成功得到解決。

 

MySQL學習筆記11複製錯誤處理(二)刪除不存在的行的問題