今天碰到同步出错提示：
引用
140614 14:33:02 [Note] Slave I/O thread killed while reading event
140614 14:33:02 [Note] Slave I/O thread exiting, read up to log ‘mysql-bin.000343’, position 262037824
140614 14:33:02 [Note] Slave SQL thread initialized, starting replication in log ‘mysql-bin.000343’ at position 210829116, relay log ‘/xy/3306/logs/relay-bin.000328’ position: 210607951
140614 14:33:02 [Note] Slave I/O thread: connected to master ‘slave@x.x.x.x:3306’,replication started in log ‘mysql-bin.000343’ at position 262037824
140614 14:33:02 [ERROR] Error in Log_event::read_log_event(): ‘read error’, data_len: 1018, event_type: 2
140614 14:33:02 [ERROR] Error reading relay log event: slave SQL thread aborted because of I/O error
140614 14:33:02 [ERROR] Slave SQL: 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. Error_code: 1594
140614 14:33:02 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with “SLAVE START”. We stopped at log ‘mysql-bin.000343’ position 210829116
140614 14:33:02 [Note] Slave I/O thread exiting, read up to log ‘mysql-bin.000343’, position 262037824
140614 14:33:02 [Note] Slave SQL thread initialized, starting replication in log ‘mysql-bin.000343’ at position 210829116, relay log ‘/xy/3306/logs/relay-bin.000328’ position: 210607951
140614 14:33:02 [Note] Slave I/O thread: connected to master ‘slave@x.x.x.x:3306’,replication started in log ‘mysql-bin.000343’ at position 262037824
140614 14:33:02 [ERROR] Error in Log_event::read_log_event(): ‘read error’, data_len: 1018, event_type: 2
140614 14:33:02 [ERROR] Error reading relay log event: slave SQL thread aborted because of I/O error
140614 14:33:02 [ERROR] Slave SQL: 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. Error_code: 1594
140614 14:33:02 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with “SLAVE START”. We stopped at log ‘mysql-bin.000343’ position 210829116
注意两个地方：
1 Slave I/O thread exiting, read up to log ‘mysql-bin.000343’, position 262037824
2 We stopped at log ‘mysql-bin.000343’ position 210829116
看上面的提示是：I/O是从master读取日志的，SQL进程是执行sql的，下载日志已经到了262037824，执行日志到了210829116
解决方式可以是重新指定同步的位置
slave stop;
change master to
master_log_file=’mysql-bin.000343′,
master_log_pos=210829116;
slave start;
change master to
master_log_file=’mysql-bin.000343′,
master_log_pos=210829116;
slave start;
这样的操作是可以恢复正常了
紧接着，发现另外一个数据库也通知了，是I/O进程出错：
（master意外断电重启会容易发生此问题）
引用
140614 15:02:25 [ERROR] Error reading packet from server: Client requested master to start replication from impossible position ( server_errno=1236)
140614 15:02:25 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: ‘Client requested master to start replication from impossible position’, Error_code: 1236
140614 15:02:25 [Note] Slave I/O thread exiting, read up to log ‘mysql-bin.000097’, position 570181940
140614 15:02:25 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: ‘Client requested master to start replication from impossible position’, Error_code: 1236
140614 15:02:25 [Note] Slave I/O thread exiting, read up to log ‘mysql-bin.000097’, position 570181940
查看master的97号日志：
引用
mysqlbinlog mysql-bin.000097|tail
ERROR: Error in Log_event::read_log_event(): ‘read error’, data_len: 2693, event_type: 2
/*!*/;
# at 569944220
#140614 13:50:28 server id 201 end_log_pos 569944431 Query thread_id=438664782 exec_time=4294967273 error_code=0
SET TIMESTAMP=1402725028/*!*/;
UPDATE xyz…
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
ERROR: Error in Log_event::read_log_event(): ‘read error’, data_len: 2693, event_type: 2
/*!*/;
# at 569944220
#140614 13:50:28 server id 201 end_log_pos 569944431 Query thread_id=438664782 exec_time=4294967273 error_code=0
SET TIMESTAMP=1402725028/*!*/;
UPDATE xyz…
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
看日志最后的位置是 569944220，也就是需要从新的日志开始同步了
可以尝试这样重新同步：
change master to
master_log_file=’mysql-bin.000098′,
master_log_pos=0;
master_log_file=’mysql-bin.000098′,
master_log_pos=0;
不过应该是从107开始的？0好像也没错，就是它了
转载请注明：爱开源 » mysql同步出错的修正