slave 同步报错处理

经检查周六、日，同步报警是由于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)

本文转自 liang3391 51CTO博客，原文链接:http://blog.51cto.com/liang3391/732196