MySQL Replication 遇到 Got fatal error 1236 from master 修復

MySQL Replication 遇到 error 1236 就有點麻煩了~ 通常都是 binlog 出問題造成的~ (Master 或 Slave 的 binlog 壞掉都有可能造成此錯誤)

通常遇到這個狀況, 都是 Slave 的 binlog 壞掉, 就 Slave DB 的資料重倒來解決, 但是此次遇到是 Master 的 binlog 壞掉, 就有點苦了~

錯誤訊息如下:

• Last_IO_Errno: 1236
• Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position; the first event 'mysql-bin.003583' at 45997491, the last event read from './mysql-bin.003583' at 4, the last byte read from './mysql-bin.003583' at 4.'

MySQL Replication 遇到 Got fatal error 1236 from master 修復

mysql> SHOW SLAVE STATUS\G

錯誤訊息如下: (已經把不必要得刪除)

Master_Log_File: mysql-bin.003583
Read_Master_Log_Pos: 45997491
Relay_Log_File: mysqld-relay-bin.006789
Relay_Log_Pos: 103569723
Relay_Master_Log_File: mysql-bin.003361
Slave_IO_Running: No
Slave_SQL_Running: Yes
Last_Errno: 0
Skip_Counter: 0
Last_IO_Errno: 1236 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position; the first event 'mysql-bin.003583' at 45997491, the last event read from './mysql-bin.003583' at 4, the last byte read from './mysql-bin.003583' at 4.'

註: Debian / Ubuntu Linux 預設 MySQL data path: /var/lib/mysql, 所以下述 binlog 可於此路徑尋找.

Slave binlog 壞掉的解法

1. 一般常見是 Slave binlog 壞掉, 所以都會先去 Slave 找 mysql-bin.003583 檔案.
2. 再把 mysql-bin.003583 的資料用 mysqlbinlog dump 出來, 指到下一個 position 即可.
3. Slave: ls /var/lib/mysql/mysql-bin.003583 # not found.
4. Slave 不存在檔案, 才發現到壞的是 Master.

Master binlog 壞掉的解法

• Master: # mysqlbinlog /var/lib/mysql/mysql-bin.003583 > ~/3583
  

  ERROR: Error in Log_event::read_log_event(): 'read error', data_len: 27, event_type: 16

• 最苦的狀況是 Master 的 binlog 壞掉了, 所以所有 Slave 都死光光. XD

此時可以考慮幾種作法:

1. 全部 Slave 重倒 (最保險, no data lost)
2. 先讓 Replication 可以啟動, 再來 重倒 / sync 一定需要同步的資料即可. (Cache ... 那種就可以不用理他)

下面是採用第二種作法:

1. mysql> STOP SLAVE;
2. mysql> CHANGE MASTER TO master_log_file = 'mysql-bin.003584', master_log_pos = 0; # 先指到下一個 binlog 檔, pos 指 0 或 4, 都是一樣意思.
3. mysql> START SLAVE;
4. mysql> SHOW SLAVE STATUS \G # 於此應該就恢復了.
5. 然後在對重要的 Table 用 pt-table-sync 來補一補即可. (apt-get install percona-toolkit)

相關網頁

• Last_IO_Error: Got fatal error 1236 - Percona Community Forums
• Virtual ORACLE & MySQL Database Support: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
• Schema Sync › a MySQL Schema Versioning and Migration Utility
• MySQL :: Re: Got fatal error 1236 from master when reading data from binary log
• MySQL :: Re: Got Fatal Error 1236 From Master Reading Data from binary log possibility to loose data/?
• MySQL 5.5 'Binary log is not open', Error_code: 1236