今天登陆到一台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文件这一位置点的数据：
[root@TEST2 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'
/*!*/;
[root@TEST2 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条评论