主库异常宕机---从库复制报fatal error 1236

主库异常宕机，启动主库mysql服务后，从库基本上一个小时报一次如下（error log内容 ）

2015-06-11 13:16:12 4958 [ERROR] Error reading packet from server: Client requested master to start replication from position > file size; the first event 'mysql-bin.000206' at 392903528, the last event read from './mysql-bin.000206' at 4, the last byte read from './mysql-bin.000206' at 4. ( server_errno=1236)

2015-06-11 13:16:12 4958 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from position > file size; the first event 'mysql-bin.000206' at 392903528, the last event read from './mysql-bin.000206' at 4, the last byte read from './mysql-bin.000206' at 4.', Error_code: 1236

2015-06-11 13:16:12 4958 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000206', position 392903528

由于主库异常宕机，最后一个bin log没有正常结束，而重启后打开一个新的bin log。从库在读取新的日志信息时，却还从之前最后一个bin log中读取。所以无法读取到而报错。

从库要读取的log pos为392903528

mysql> show slave status\G

*********************1. row*****************************

      Slave_IO_State:

       Master_Host: 10.128.35.17

       Master_User: slave

      Masater_Port: 3306

     Connect_Retry: 60

    Master_Log_File: mysql-bin.000206

  Read_Master_Log_Pos: 392903528 

     Relay_Log_File: mysqld-relay-bin.000508

     Relay_Log_Pos: 392993691

Relay_Master_Log_File: mysql-bin.000206

    Slave_IO_Running: No

   Slave_SQL_Running: Yes

查看主库

查看mysql-bin.000206的最后log file pos：392903089
mysqlbinlog mysql-bin.000206 > diag.log （此处可以指定参数从某个位置开始，而不用将整个日志文件内容导出）

vi diag.log

/*!*/

# at  392903089

#150611 12:16:12 server id 1 end_log_pos 392903120 CRC32 0x22c7ea84 Xid = 14851801

COMMIT/*!*/;

DELIMITER;

#End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;   

从库

mysql> stop slave;

Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_log_file='mysql-bin.000206',master_log_pos=392903089;

Query OK, 0 rows affected (0.11 sec)

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

再次查看恢复正常


参考：
http://blog.csdn.net/wulantian/article/details/8598803

疑问：主库每次异常重启都会开启新的bin log，那么每次都要手动将从库change一下吗？

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26831783/viewspace-1696242/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26831783/viewspace-1696242/