DB:5.6.15 主从库没有采用GTID
案例1:从库对象不存在
mysql> show slave status G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.111
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000007
Read_Master_Log_Pos: 53229237
Relay_Log_File: slave-relay.000010
Relay_Log_Pos: 1109210
Relay_Master_Log_File: binlog.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.%
Last_Errno: 1539
Last_Error: Error 'Unknown event 'job_insert_data'' on query. Default database: 'hxl'. Query: 'drop event job_insert_data'
Skip_Counter: 0
Exec_Master_Log_Pos: 53228760
Relay_Log_Space: 1110514
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: 1539
Last_SQL_Error: Error 'Unknown event 'job_insert_data'' on query. Default database: 'hxl'. Query: 'drop event job_insert_data'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: cf833519-ecde-11e6-9b73-080027e001de
Master_Info_File: /db/m2m/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: 170209 09:19:58
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
解决办法:
修改mysql配置文件my.cnf 在 [mysqld]下加一行 slave_skip_errors = 1539 ,保存,
重启mysql. 以正常同步了.
案例2:主库删除的记录从库不存在
Replicate_Wild_Ignore_Table:
Last_Errno: 1032
Last_Error: Could not execute Delete_rows event on table rdcs_yx.copy_yx_realtime; Can't find record in 'copy_yx_realtime', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.013309, end_log_pos 983388907
Skip_Counter: 0
Exec_Master_Log_Pos: 983333883
Relay_Log_Space: 769306587064
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 rdcs_yx.copy_yx_realtime; Can't find record in 'copy_yx_realtime', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.013309, end_log_pos 983388907
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
解决办法1:
mysql>slave stop;
mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql>slave start;
5.6版本:
stop slave;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
start slave;
解决办法2:通案例1,配置文件配置跳过错误号:1032