從庫同步中斷,錯誤日志:
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log:
'Could not find first log file name in binary log index file'
主庫二進制日志文件被手動刪除、或 expire_logs_days 自動清理、或磁盤故障導致日志丟失,從庫請求的起始日志文件在主庫已不存在。主庫 mysql-bin.index 索引文件記錄的第一個文件已丟失。
mysql> STOP SLAVE;
mysql> FLUSH LOGS;
# 生成新的 binlog 文件,如 mysql-bin.000005
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 | 154 | | |
+------------------+----------+--------------+------------------+
mysql> CHANGE MASTER TO
MASTER_LOG_FILE='mysql-bin.000005',
MASTER_LOG_POS=154;
-- 如果使用 GTID 模式:
-- MASTER_AUTO_POSITION=1;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G
# 確認:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
若主庫所有日志均已清理,需重新全量備份建立從庫:
# 主庫:備份並記錄位置
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS; -- 記錄 File, Position
mysqldump --all-databases --master-data=2 --single-transaction > /tmp/full.sql
mysql> UNLOCK TABLES;
# 從庫:導入備份
mysql < /tmp/full.sql
# 從庫:配置同步(使用備份中記錄的位置)
mysql> CHANGE MASTER TO
MASTER_HOST='master_ip',
MASTER_USER='repl',
MASTER_PASSWORD='pass',
MASTER_LOG_FILE='mysql-bin.00000X',
MASTER_LOG_POS=XXX;
mysql> START SLAVE;
[mysqld]
expire_logs_days = 14 # 保留 14 天(視磁盤容量)
max_binlog_size = 500M # 控制單文件大小
binlog_expire_logs_seconds = 1209600 # 8.0+ 推薦
# 監控腳本邏輯
MASTER_FILE=$(mysql -h master -e "SHOW MASTER STATUS" | awk 'NR==2 {print $1}')
SLAVE_FILE=$(mysql -h slave -e "SHOW SLAVE STATUS\G" | grep Master_Log_File | awk '{print $2}')
# 解析文件編號對比
MASTER_NUM=$(echo $MASTER_FILE | sed 's/.*\.//')
SLAVE_NUM=$(echo $SLAVE_FILE | sed 's/.*\.//')
DIFF=$((MASTER_NUM - SLAVE_NUM))
if [ $DIFF -gt 5 ]; then
alert "主從日志差距超過 5 個文件,請檢查同步延遲"
fi
rm mysql-bin.*mysql-bin.indexPURGE MASTER LOGS 或自動過期[mysqld]
gtid_mode = ON
enforce_gtid_consistency = ON
# 從庫配置只需:
CHANGE MASTER TO MASTER_AUTO_POSITION=1;
# 無需指定文件和位置,自動計算差集同步