MySQL主从同步错误1594处理

今天登陆到一台MySQL从库机上查看状态。

发现其SQL线程出错。报1594错误:

如下所示:

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.220.226
                  Master_User: repl
                  Master_Port: 1990
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000020
          Read_Master_Log_Pos: 194242576
               Relay_Log_File: mysql-relay-bin.000028
                Relay_Log_Pos: 9883363
        Relay_Master_Log_File: mysql-bin.000013
             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: 1594
                   Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 9883200
              Relay_Log_Space: 2724083333
              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: 1594
               Last_SQL_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 6d2c64d6-8436-11e5-aba9-14dda9e99c53
             Master_Info_File: /data/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: 160527 14:04:07
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

由上可以再出,其是到Master上面的mysql-bin.000020日志中194242576位置点执行SQL出错,网上很多建议是重做从库;这里我看了一下主库上此log-bin文件这一位置点的数据:

[[email protected] log]# mysqlbinlog mysql-bin.000020 | grep -C 10 194242576 
UPDATE T_CUSTOM_BUY_RECORD set TOTAL_BUY= NAME_CONST('totalBuy',_utf8'0' COLLATE 'utf8_general_ci'),
                                                                                                                                    CUM_TOTAL_BUY= NAME_CONST('cumTotalBuy',_utf8'1' COLLATE 'utf8_general_ci'),
                                                                                                                                    REDEMPTION_PRO= NAME_CONST('redemptionPro',_utf8'1' COLLATE 'utf8_general_ci'),
                                                                                                                                    EXPIRED_PRO= NAME_CONST('expiredPro',_utf8'0' COLLATE 'utf8_general_ci'),
                                                                                                                                    WIT_AMOUNT= NAME_CONST('witAmount',0.000),
                                                                                                                                    REQREDEEM_PRO= NAME_CONST('reqredeemPro',_utf8'0' COLLATE 'utf8_general_ci'),
                                                                                                                                    UPDATE_DATE=NOW() 
                                                                where ID= NAME_CONST('buyRecordId',_utf8'68' COLLATE 'utf8_general_ci')
/*!*/;
# at 194242545
#160607 11:15:01 server id 1  end_log_pos 194242576 CRC32 0x3cc7a69f    Xid = 5704894
COMMIT/*!*/;
# at 194242576
#160607 11:15:48 server id 1  end_log_pos 194242661 CRC32 0x46f8bb7d    Query   thread_id=25281 exec_time=0     error_code=0
SET TIMESTAMP=1465269348/*!*/;
SET @@session.sql_auto_is_null=0/*!*/;
BEGIN
/*!*/;
# at 194242661
#160607 11:15:48 server id 1  end_log_pos 194242864 CRC32 0x00ec91ac    Query   thread_id=25281 exec_time=0     error_code=0
SET TIMESTAMP=1465269348/*!*/;
UPDATE T_ADV SET `AD_STATUS`='SHOW' WHERE (UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(ONLINE_DATE)) > 0 AND `AD_STATUS`='WAITE'
/*!*/;
[[email protected] log]#

可以看到194242576位置点并无执行具体对数据库的操作,故重启Slave将其跳过后正常:

mysql> stop slave;
Query OK, 0 rows affected (0.06 sec)

mysql> change master to MASTER_LOG_POS=194242661;
Query OK, 0 rows affected (0.39 sec)

mysql> start slave;
Query OK, 0 rows affected (0.07 sec)

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.220.226
                  Master_User: repl
                  Master_Port: 1990
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000020
          Read_Master_Log_Pos: 194555786
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 112310
        Relay_Master_Log_File: mysql-bin.000020
             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: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 194354688
              Relay_Log_Space: 313581
              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: 912
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: 1
                  Master_UUID: 6d2c64d6-8436-11e5-aba9-14dda9e99c53
             Master_Info_File: /data/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Reading event from the relay log
           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
1 row in set (0.00 sec)

《MySQL主从同步错误1594处理》有1条留言

  1. Pingback引用通告: Cıvata

留下评论

error: Content is protected !!