Binlog（二進制日志）記錄所有數據變更操作，是 MySQL 複製、時間點恢復（PITR）、增量備份的核心基礎。本文以 Windows MySQL 5.6 社區版為例，演示 Binlog 全生命周期管理。

環境與文件位置

• 系統：Windows
• MySQL 5.6 社區版
• 數據/日志目錄：C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.6\data
• 配置文件：同目錄下 my.ini

啟用 Binlog

確認當前狀態

SHOW VARIABLES LIKE 'log_bin';

修改 my.ini 啟用

[mysqld]
log-bin="log_bin"
# 生成文件：log_bin.000001, log_bin.000002 ...

修改後重啟 MySQL 服務生效。

Binlog 核心操作工具：mysqlbinlog

二進制轉文本

# 在 data 目錄下執行
mysqlbinlog log_bin.000001 > log_bin.txt

生成新 Binlog 文件（切換）

FLUSH LOGS;
-- 生成 log_bin.000002，便於按時間段管理

時間點恢復（PITR）實戰

全量恢復某個 Binlog 文件

mysqlbinlog log_bin.000001 | mysql -u root -p

按位置點恢復

# 從位置 325 到 567
mysqlbinlog --start-position=325 --stop-position=567 log_bin.000001 | mysql -u root -p

按時間點恢復

# 從 2013-09-29 16:30:46 到 16:31:22
mysqlbinlog --start-datetime="2013-09-29 16:30:46" --stop-datetime="2013-09-29 16:31:22" log_bin.000001 | mysql -u root -p

Binlog 內容解讀示例

/*!*/;
# at 325
#130929 16:30:46 server id 1  end_log_pos 490 CRC32 0x9501d4ba  Query  thread_id=16  exec_time=0  error_code=0
SET TIMESTAMP=1380443446/*!*/;
ALTER TABLE `sdghdb`.`test` ADD COLUMN `name` VARCHAR(45) NULL COMMENT '名稱' AFTER `info`
/*!*/;
# at 490
#130929 16:31:22 server id 1  end_log_pos 567 CRC32 0xefc463e6  Query  thread_id=17  exec_time=0  error_code=0
SET TIMESTAMP=1380443482/*!*/;
BEGIN
/*!*/;
# at 567
#130929 16:31:22 server id 1  end_log_pos 706 CRC32 0xfa48b5d3  Query  thread_id=17  exec_time=0  error_code=0
SET TIMESTAMP=1380443482/*!*/;
INSERT INTO `sdghdb`.`test` (`info`, `name`) VALUES ('aaa', 'bbbb')
/*!*/;

關鍵字段含義

• # at 325：事件在 Binlog 中的起始位置
• end_log_pos 490：事件結束位置（下一事件起始位置）
• server id：來源服務器 ID
• CRC32：校驗和（5.6+ 支援）
• Query/Write/Delete/Update：事件類型
• SET TIMESTAMP：保證復制時函數確定性

增量備份策略建議

1. 每日全量備份（mysqldump --master-data=2 記錄 Binlog 位置）
2. 每小時 FLUSH LOGS 產生新 Binlog，配合腳本備份增量文件
3. 異地存儲 Binlog 備份（防機房級故障）
4. 定期演練 PITR 恢復流程，驗證備份有效性

常見問題

• mysqlbinlog 路徑未加入 PATH：使用完整路徑或進入 bin 目錄執行
• Windows 路徑含空格：雙引號包裹路徑
• Binlog 格式 ROW 時文本輸出可讀性較差，建議配合 --verbose 參數

掌握 Binlog 管理與 PITR，是 DBA 從「備份者」進階為「數據守護者」的關鍵技能。