3分钟解决MySQL 1032 主从错误

3分钟解决MySQL 1032主从错误

Part1:写在最前

1032错误----现在生产库中好多数据，在从库误删了，生产库更新后找不到了，现在主从不同步了，再跳过错误也没用，因为没这条，再更新还会报错

解决方案

Part1:临时方案

mysql> stop slave; Query OK, 0 rows affected (0.00 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.00 sec)

Part2:永久方案

end_log_pos 有了它，根据pos值，直接就能找到，找到delete那条数据，反做（变成insert）

故障模拟

HE1从库误删

mysql> delete from helei where id=3; Query OK, 1 row affected (0.29 sec)   mysql> select * from helei; +----+------+ | id | text | +----+------+ |  1 | aa   | |  2 | bb   | |  4 | ee   | |  5 | ff   | |  6 | gg   | |  7 | hh   | +----+------+ 6 rows in set (0.00 sec)   mysql> show slave status\G; *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 192.168.1.250                   Master_User: mysync                   Master_Port: 2503306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000005           Read_Master_Log_Pos: 3711                Relay_Log_File: HE1-relay-bin.000007                 Relay_Log_Pos: 484         Relay_Master_Log_File: mysql-bin.000005              Slave_IO_Running: Yes             Slave_SQL_Running: Yes

此时从库状态是正常的，但一旦主库对该条记录进行操作

 

 

HE3主库更新从库刚刚误删的数据

mysql> update helei set text='ccc' where id=3; Query OK, 1 row affected (0.01 sec) Rows matched: 1  Changed: 1  Warnings: 0   mysql> select * from helei; +----+------+ | id | text | +----+------+ |  1 | aa   | |  2 | bb   | |  3 | ccc  | |  4 | ee   | |  5 | ff   | |  6 | gg   | |  7 | hh   | +----+------+ 7 rows in set (0.00 sec)

HE1从库报错

mysql> show slave status\G; *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 192.168.1.250                   Master_User: mysync                   Master_Port: 2503306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000005           Read_Master_Log_Pos: 3918                Relay_Log_File: HE1-relay-bin.000007                 Relay_Log_Pos: 484         Relay_Master_Log_File: mysql-bin.000005              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: 1032                    Last_Error: Could not execute Update_rows event on table test.helei; Can't find record in 'helei', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000005, end_log_pos 3887                  Skip_Counter: 0           Exec_Master_Log_Pos: 3711               Relay_Log_Space: 1626               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 Update_rows event on table test.helei; Can't find record in 'helei', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000005, end_log_pos 3887（这个mysql-bin.000005,end_log_pos 3887是主库的）   Replicate_Ignore_Server_Ids:              Master_Server_Id: 2503306                   Master_UUID: f7c96432-f665-11e5-943f-000c2967a454              Master_Info_File: /data/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: 160331 09:25:02                Master_SSL_Crl:            Master_SSL_Crlpath:            Retrieved_Gtid_Set:             Executed_Gtid_Set:                 Auto_Position: 0 1 row in set (0.00 sec)

 

 

此时主从又不同步了,如果还去执行跳过错误操作，主从恢复同步，而且状态均为yes，但！这并不能解决该问题，如果主库又更新该条记录，那么还是会报相同错误，而且pos号还会变，这就导致了恢复时你不知道前一条的pos号，导致丢失数据。

mysql> stop slave; Query OK, 0 rows affected (0.00 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.00 sec)   mysql> select * from helei; +----+--------+ | id | text   | +----+--------+ |  1 | aa     | |  2 | bb     | |  4 | ee     | |  5 | ff     | |  6 | gg     | |  7 | hh     | |  8 | helei1 | +----+--------+ 7 rows in set (0.00 sec)   mysql> show slave status\G; *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 192.168.1.250                   Master_User: mysync                   Master_Port: 2503306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000005           Read_Master_Log_Pos: 4119                Relay_Log_File: HE1-relay-bin.000008                 Relay_Log_Pos: 283         Relay_Master_Log_File: mysql-bin.000005              Slave_IO_Running: Yes             Slave_SQL_Running: Yes

 

这里虽然通过跳过错误达到恢复主从同步，但如果主库又对该条记录更新

mysql> update helei set text='cccc' where id=3; Query OK, 1 row affected (0.00 sec) mysql> show slave status\G; *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 192.168.1.250                   Master_User: mysync                   Master_Port: 2503306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000005           Read_Master_Log_Pos: 4328                Relay_Log_File: HE1-relay-bin.000008                 Relay_Log_Pos: 283         Relay_Master_Log_File: mysql-bin.000005              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: 1032                    Last_Error: Could not execute Update_rows event on table test.helei; Can't find record in 'helei', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000005, end_log_pos 4297                  Skip_Counter: 0           Exec_Master_Log_Pos: 4119               Relay_Log_Space: 1435               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 Update_rows event on table test.helei; Can't find record in 'helei', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000005, end_log_pos 4297   Replicate_Ignore_Server_Ids:              Master_Server_Id: 2503306                   Master_UUID: f7c96432-f665-11e5-943f-000c2967a454              Master_Info_File: /data/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: 160331 09:33:34                Master_SSL_Crl:            Master_SSL_Crlpath:            Retrieved_Gtid_Set:             Executed_Gtid_Set:                 Auto_Position: 0