主庫異常當機---從庫複製報fatal error 1236

xishuai236發表於2015-06-11
Error
主庫異常當機，啟動主庫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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章