萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 二進制日誌(Binlog)備份與恢復:全量+增量、時間點恢復、自動清理

啟用 Binlog(/etc/my.cnf)

[mysqld]
log-bin=mysql-bin
binlog_format=MIXED          -- ROW/STATEMENT/MIXED,推薦 MIXED
server-id=1                  -- 主從必須唯一
expire_logs_days=7           -- 自動清理 7 天前日誌
max_binlog_size=1G           -- 單文件上限,超過自動滾動
sync_binlog=1                -- 每次提交刷盤,最安全(性能稍損)

常用管理命令

SHOW BINARY LOGS;                    -- 列出所有 binlog
SHOW MASTER STATUS;                  -- 當前寫入文件與位置
FLUSH LOGS;                          -- 手動滾動生成新文件
PURGE BINARY LOGS TO 'mysql-bin.000010';
PURGE BINARY LOGS BEFORE '2024-01-01';
RESET MASTER;                        -- 危險:刪所有、歸零,慎用!

mysqlbinlog 工具:二進制轉 SQL

# 全量導出
mysqlbinlog /var/lib/mysql/mysql-bin.000001 > /backup/binlog_000001.sql

# 指定庫、時間範圍、位置範圍
mysqlbinlog --database=mydb --start-datetime='2024-01-15 10:00:00' --stop-datetime='2024-01-15 12:00:00' mysql-bin.000001 > inc.sql

# 遠程直接回放
mysqlbinlog --read-from-remote-server -h 10.0.0.1 -u root -p mysql-bin.000001 | mysql -u root -p mydb

備份策略:全量 + 增量

  1. 週日 23:00 全量備份 + 刷新日誌
    mysqldump --single-transaction --flush-logs --master-data=2 --all-databases > full_sun_23.sql
    輸出文件含 CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154; —— **增量起點**
  2. 每日 23:00 增量備份
    mysqladmin flush-logs → 產生新 binlog,舊 binlog 即為昨日增量,拷貝異地存儲

災難恢復演練

  1. 恢復全量:mysql -u root -p < full_sun_23.sql
  2. 按順序回放增量:
    mysqlbinlog mysql-bin.000002 mysql-bin.000003 | mysql -u root -p
  3. 精準到秒:mysqlbinlog --stop-datetime='2024-01-15 13:15:00' mysql-bin.000004 | mysql -u root -p

主從同步斷點續傳

從庫 SHOW SLAVE STATUS\G 記住 Relay_Master_Log_File 和 Exec_Master_Log_Pos,重建從庫時用 CHANGE MASTER TO MASTER_LOG_FILE=..., MASTER_LOG_POS=...。

Binlog 三種格式選型

格式優點缺點適用
STATEMENT文件小、易閱讀非確定性函數(NOW、UUID)導致主從不一致簡單業務、老版本
ROW絕對一致性、支援所有場景文件大、不可讀、大事務延遲高金融、核心交易、異構同步
MIXED自動選擇:確定性用 STATEMENT,其餘 ROW極少數邊界情況難預測**通用推薦**
copyright © 萬盛學電腦網 all rights reserved