未開啟 Crash-Safe 配置,導致兩類典型故障。
Slave_IO_Running: No
Slave_SQL_Running: Yes
Last_IO_Error: 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.000342' at 1028687822,
the last event read from './mysql-bin.000342' at 4'
Read_Master_Log_Pos: 1028687822 超過 Master 當前 Binlog 文件大小。
Slave 掉電時,master.info(記錄 Master 位點)已寫入磁盤,但 relay-log.info(記錄 SQL 線程執行位點)未刷新。重啟後 IO 線程嘗試從舊位點拉取,但 Master 已經 Rotate 到新 Binlog 文件,舊位點不存在。
SHOW BINLOG EVENTS IN 'mysql-bin.000342' 取最後一行 End_log_pos)。STOP SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000342', MASTER_LOG_POS=最大位點;
START SLAVE;
mysqlbinlog 分析確認具體執行到哪條 SQL,避免數據不一致。Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_SQL_Error: Relay log read failure: Could not parse relay log event entry.
...
Exec_Master_Log_Pos: 439512771
mysqlbinlog localhost-relay-bin.000137 直接報 ERROR: Error in Log_event::read_log_event(): 'read error',確認 Relay Log 文件損壞。
Exec_Master_Log_Pos(SQL 線程已成功執行到的 Master 位點)重新拉取:STOP SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000076', MASTER_LOG_POS=439512771;
START SLAVE;
relay_log_recovery=ON 重啟 Slave,自動丟棄損壞 Relay Log 並從 Master 重拉(需 MySQL 5.6+)。[mysqld]
relay_log_info_repository = TABLE -- 將 relay-log.info 存入 mysql.slave_relay_log_info 表
master_info_repository = TABLE -- 將 master.info 存入 mysql.slave_master_info 表
relay_log_recovery = ON -- 啟動時自動修復損壞 Relay Log
sync_relay_log = 1 -- 每次寫入 Relay Log 刷盤
sync_relay_log_info = 1 -- 每次更新位點刷盤
sync_binlog=1、innodb_flush_log_at_trx_commit=1,實現雙端 Crash-Safe。SHOW SLAVE STATUS\G、mysqlbinlog 輸出,便於事後複盤。