slave 同步报错处理
原创
©著作权归作者所有:来自51CTO博客作者liang3391的原创作品,请联系作者获取转载授权,否则将追究法律责任
经检查周六、日,同步报警是由于IICUPDB.UP_SmsCount 这个表丢数据造成同步报警。
现我总结了一个处理方法,贴出来,供以后遇到同样问题做参考。就不用再跳过了。
===========================================================================================
[root@p08-up-01 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17179
Server version: 5.1.43sp1-br38368-enterprise-gpl-pro-log MySQL Enterprise Server - Pro Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.241.39
Master_User: repl
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: mysql-bin.011735
Read_Master_Log_Pos: 16193784
Relay_Log_File: relay-bin.000570
Relay_Log_Pos: 99500046
Relay_Master_Log_File: mysql-bin.011734
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table: mysql.inventory
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1032
Last_Error: Could not
execute Update_rows event on table IICUPDB.UP_SmsCount; Can't find
record in 'UP_SmsCount', Error_code: 1032; handler error
HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.011734,
end_log_pos 99500110
Skip_Counter: 0
Exec_Master_Log_Pos: 99499901
Relay_Log_Space: 150412587
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 IICUPDB.UP_SmsCount; Can't find
record in 'UP_SmsCount', Error_code: 1032; handler error
HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.011734,
end_log_pos 99500110
1 row in set (0.00 sec)
ERROR:
No query specified
然后我们到P8 UP主上,找到同步出错的日志,并分析正在执行了什么
[root@p08-up-02 logs]# mysqlbinlog --no-defaults
--base64-output=decode-rows -v -v mysql-bin.011734 | grep -A 20
'99500110' |more
#110703 17:48:26 server id 393306 end_log_pos 99500110 Update_rows: table id 19 flags: STMT_END_F
### UPDATE IICUPDB.UP_SmsCount
### WHERE
### @1=314559577 /* INT meta=0 nullable=0 is_null=0 */
### @2=275 /* INT meta=0 nullable=0 is_null=0 */
### @3=0 /* INT meta=0 nullable=0 is_null=0 */
### @4=0 /* INT meta=0 nullable=0 is_null=0 */
### @5=0 /* INT meta=0 nullable=0 is_null=0 */
### @6=0 /* INT meta=0 nullable=0 is_null=0 */
### SET
### @1=314559577 /* INT meta=0 nullable=0 is_null=0 */
### @2=280 /* INT meta=0 nullable=0 is_null=0 */
### @3=0 /* INT meta=0 nullable=0 is_null=0 */
### @4=0 /* INT meta=0 nullable=0 is_null=0 */
### @5=0 /* INT meta=0 nullable=0 is_null=0 */
### @6=0 /* INT meta=0 nullable=0 is_null=0 */
# at 99500110
#110703 17:48:26 server id 393306 end_log_pos 99500137 Xid = 2135251604
COMMIT/*!*/;
# at 99500137
[root@p08-up-02 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 39986
Server version: 5.1.43sp1-br38368-enterprise-gpl-pro-log MySQL Enterprise Server - Pro Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select * from IICUPDB.UP_SmsCount where UserId=314559577;
+-----------+---------+--------------+---------------+---------------+---------------+
| UserId | FreeSms | FreeGroupSms | ReservedFlag1 | ReservedFlag2 | ReservedFlag3 |
+-----------+---------+--------------+---------------+---------------+---------------+
| 314559577 | 280 | 0 | 0 | 0 | 0 |
+-----------+---------+--------------+---------------+---------------+---------------+
1 row in set (0.00 sec)
mysql>
再到从上,看下
mysql> select * from IICUPDB.UP_SmsCount where UserId=314559577;
Empty set (0.00 sec)
没有这条记录
手工插入
mysql> insert into UP_SmsCount values (314559577,275,0,0,0,0);
Query OK, 1 row affected (0.00 sec)
然后再进行同步就OK了。
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.241.39
Master_User: repl
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: mysql-bin.011735
Read_Master_Log_Pos: 50662466
Relay_Log_File: relay-bin.000570
Relay_Log_Pos: 100289844
Relay_Master_Log_File: mysql-bin.011734
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table: mysql.inventory
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 100289699
Relay_Log_Space: 184881563
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: 653
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章