一套Master-Master Replication的MySQL叢集，版本5.1.37。其中一個節點A出現OS異常重啟，資料庫啟動後表現正常。但是沒過多久另外一個節點B報錯：

091127 21:50:21 [ERROR] Error reading packet from server: Client requested master to start replication
from impossible position ( server_errno=1236)
091127 21:50:21 [ERROR] Got fatal error 1236: 'Client requested master to start replication
from impossible position' from master when reading data from binary log
091127 21:50:21 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000535', position 193022771

Slave_IO_Running執行緒終止。仔細看上面的報錯資訊，說slave程式試圖從mysql-bin.000535日誌的 position 193022771開始啟動恢復，但是該日誌中是沒有這個position的。

跑到A上透過mysqlbinlog檢視該日誌，發現最後一個有效position是193009460。而要求的193022771已經大於最後有效的position了。這個原因就搞不明白了，難道是因為A庫異常關閉後導致A節點的binlog沒有來得及刷到磁碟，而B節點slave已經恢復到前面去了？

$mysqlbinlog mysql-bin.000535 > 1.txt

$tail -n 7 1.txt
# at 193009460
#091127 20:50:21 server id 1 end_log_pos 193009487 Xid = 194299849
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

嘗試將B節點change master到最後一個有效的position處，問題暫時得到解決：

change master to master_log_file='mysql-bin.000535', master_log_pos=193009460

網上搜尋了一把，發現logzgh之前也碰到過同樣的問題，版本是5.0.51。

=================================================================

轉：http://denniswwh.itpub.net/post/15174/471080

今天幾臺MySQL伺服器突然停電，重啟後複製無法啟動，總是報如下錯誤：

080922 10:29:01 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000778' at position 2874108, relay log '
./relay-bin.003287' position: 2874245
080922 10:29:01 [Note] Slave I/O thread: connected to master 'replication@192.168.0.10:3306', replication started in log 'mysql-b
in.000778' at position 2874108
080922 10:29:01 [ERROR] Error reading packet from server: Client requested master to start replication from impossible position ( se
rver_errno=1236)
080922 10:29:01 [ERROR] Got fatal error 1236: 'Client requested master to start replication from impossible position' from master wh
en reading data from binary log

為什麼會報impossible position呢？開啟mysql-bin.000778看看。

mysqlbinlog mysql-bin.000778 > log.sql

tail -f log.sql

# at 2871574
#080922 10:20:27 server id 2 end_log_pos 2871601 Xid = 2516638
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/

最後一個position是2871574，而從庫要從2874108開始，難怪報impossible position，難道是斷電時有部分log在快取中還沒來得及寫入binglog？無論如何先恢復再說吧

stop slave;

CHANGE MASTER TO
MASTER_LOG_FILE='mysql-bin.000778',
MASTER_LOG_POS=2871574;

start slave;

show slave statusG

同步正常。。

後來檢視主庫的啟動日誌中這樣一段

InnoDB: Last MySQL binlog file position 0 2874108, file name ./mysql-bin.000778
080922 10:22:09 InnoDB: Started; log sequence number 6 2534366248
080922 10:22:09 [Note] Recovering after a crash using mysql-bin
080922 10:22:09 [Note] Starting crash recovery...
080922 10:22:09 [Note] Crash recovery finished.
080922 10:22:09 [Note] /usr/sbin/mysqld: ready for connections.

這裡Last MySQL binlog file position 0 2874108也是錯誤的，這個資訊到底是哪裡來的呢？為什麼會這樣呢？不明白。

為了保證以後binglog及時寫入，將主庫 sync_binlog變數設定1。

MySQL Replication Fatal Error 1236

相關文章