萬盛學電腦網

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

MySQL 二進制日志完整指南:啟用、查看與 mysqlbinlog 時間點恢復實戰

一、啟用二進制日志

編輯 /etc/my.cnf(Windows 為 my.ini),在 [mysqld] 區段加入:

[mysqld]
log-bin=mysql-bin          # 啟用二進制日志,指定檔案前綴
binlog_format=ROW          # 推薦 ROW 格式(MIXED/STATEMENT 亦可)
expire_logs_days=7         # 自動清理 7 天前日志
max_binlog_size=100M       # 單個日志檔案最大值
server-id=1                # 主從架構必須,單機也建議設定

重啟 MySQL 服務生效。

二、常用日志查看命令

SHOW VARIABLES LIKE 'log_bin';SHOW MASTER STATUS;SHOW BINARY LOGS;mysqlbinlog mysql-bin.000001mysqlbinlog mysql-bin.000001 | tail
操作命令
檢查是否啟用
查看當前日志文件與位置
查看所有二進制日志列表
解析指定日志文件
查看日志尾部

三、MySQL 日志類型對照

  • 錯誤日志:log-error - 記錄啟動/關閉/錯誤資訊
  • 通用查詢日志:log - 記錄所有連接與查詢(生產環境慎開)
  • 慢查詢日志:slow_query_log + long_query_time - SQL 調優利器
  • 二進制日志:log-bin - 主從同步、時間點恢復核心
  • 中繼日志:relay-log - 從庫儲存主庫同步來的日志

四、mysqlbinlog 時間點恢復(PITR)實戰

前提:擁有全量備份(如 mysqldump)+ 完整二進制日志鏈。

方法 1:按時間區間恢復

場景:今天 10:00 誤刪大表,需恢復至 09:59:59。

# 1. 先恢復昨晚全量備份
mysql -u root -p < full_backup.sql

# 2. 重放二進制日志至 09:59:59
mysqlbinlog --stop-datetime="2025-04-20 09:59:59" /var/log/mysql/bin.123456 | mysql -u root -p

若事後發現 10:01 後還有正常業務,再補放:

mysqlbinlog --start-datetime="2025-04-20 10:01:00" /var/log/mysql/bin.123456 | mysql -u root -p

方法 2:按 Position 精確恢復(推薦)

時間戳可能重複,Position 唯一精確。

  1. 導出可疑時段日志到文本檔分析:
    mysqlbinlog --start-datetime="2025-04-20 09:55:00" --stop-datetime="2025-04-20 10:05:00" \
    /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql
  2. 用編輯器搜尋誤操作 SQL(如 DROP TABLE),記錄其 log_pos(如 368312)。
  3. 分兩段重放:
    # 恢復至誤操作前
    mysqlbinlog --stop-position="368312" /var/log/mysql/bin.123456 | mysql -u root -p
    
    # 跳過誤操作,恢復後續
    mysqlbinlog --start-position="368315" /var/log/mysql/bin.123456 | mysql -u root -p

五、注意事項

  • 二進制日志不記錄 SELECT,只記錄數據變更(DML/DDL)。
  • 恢復時需確保 sql_mode、字元集與備份時一致。
  • 大日志管道傳入 mysql 可能因 max_allowed_packet 截斷,建議先導出 SQL 檔再 source。
  • GTID 模式下建議使用 mysqlbinlog --include-gtids=...。
copyright © 萬盛學電腦網 all rights reserved