MySQL中的binlog日志操作示例

MySQL的 binlog（二进制日志） 是记录所有修改数据库数据的SQL语句（或实际数据变更）的日志文件，用于主从复制和数据恢复。以下是一些常用的binlog操作示例：

1. 查看binlog配置

-- 查看是否开启binlog
SHOW VARIABLES LIKE 'log_bin';
-- 查看binlog格式（ROW/STATEMENT/MIXED）
SHOW VARIABLES LIKE 'binlog_format';
-- 查看binlog文件路径
SHOW VARIABLES LIKE 'log_bin_basename';
-- 查看binlog过期时间（默认0，不过期）
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';

2. 查看binlog文件列表

SHOW BINARY LOGS;
-- 或
SHOW MASTER LOGS;

输出示例：

+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |      1075 |
+------------------+-----------+

3. 查看当前正在写入的binlog文件

SHOW MASTER STATUS;

输出示例：

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      107 |              |                  |
+------------------+----------+--------------+------------------+

4. 使用mysqlbinlog工具解析binlog

查看原始binlog内容

# 查看指定binlog文件
mysqlbinlog /var/lib/mysql/mysql-bin.000001

# 查看特定时间段的binlog
mysqlbinlog \
  --start-datetime="2024-01-01 00:00:00" \
  --stop-datetime="2024-01-02 00:00:00" \
  mysql-bin.000001

# 查看特定位置范围的binlog
mysqlbinlog \
  --start-position=100 \
  --stop-position=500 \
  mysql-bin.000001

# 输出为可读SQL（ROW格式时需要）
mysqlbinlog -v mysql-bin.000001

# 输出到文件
mysqlbinlog mysql-bin.000001 > binlog.sql

5. 在MySQL客户端中查看binlog事件

-- 查看第一个binlog的所有事件
SHOW BINLOG EVENTS IN 'mysql-bin.000001';

-- 从指定位置开始查看事件
SHOW BINLOG EVENTS IN 'mysql-bin.000001' FROM 100 LIMIT 10;

-- 查看特定位置范围的事件
SHOW BINLOG EVENTS IN 'mysql-bin.000001' FROM 100 TO 500;

6. 刷新和切换binlog文件

-- 手动创建新的binlog文件（当前日志归档，新建一个）
FLUSH BINARY LOGS;

-- 重启MySQL服务也会创建新的binlog文件

7. 清理binlog文件

-- 删除指定文件之前的binlog（保留最新）
PURGE BINARY LOGS TO 'mysql-bin.000010';

-- 删除指定时间之前的binlog
PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';

-- 删除所有binlog（重置）
RESET MASTER;  -- 注意：会清除所有binlog，主从复制需重新配置

8. 使用binlog恢复数据

恢复整个binlog到数据库

# 通过mysqlbinlog导出SQL并执行
mysqlbinlog mysql-bin.000001 | mysql -u root -p

# 恢复多个binlog文件
mysqlbinlog mysql-bin.000001 mysql-bin.000002 | mysql -u root -p

# 恢复指定时间段的数据
mysqlbinlog \
  --start-datetime="2024-01-01 00:00:00" \
  --stop-datetime="2024-01-01 12:00:00" \
  mysql-bin.000001 | mysql -u root -p

先导出为SQL文件再恢复

# 导出为SQL文件
mysqlbinlog mysql-bin.000001 > recovery.sql

# 编辑SQL文件（可选，比如删除误操作语句）
vi recovery.sql

# 执行恢复
mysql -u root -p < recovery.sql

9. binlog相关配置示例（my.cnf）

[mysqld]
# 启用binlog
log_bin = /var/lib/mysql/mysql-bin
# binlog格式（推荐ROW）
binlog_format = ROW
# 每个binlog文件大小（默认1G）
max_binlog_size = 100M
# binlog过期时间（7天）
binlog_expire_logs_seconds = 604800
# 缓存大小（提高性能）
binlog_cache_size = 32K
# 同步策略（1=最安全，0=性能最好）
sync_binlog = 1

10. 特殊场景

查看ROW格式的详细SQL

# -v 显示伪SQL，-vv 显示更详细信息
mysqlbinlog -vv mysql-bin.000001

# 仅显示特定数据库的binlog
mysqlbinlog \
  --database=your_db_name \
  mysql-bin.000001

从远程服务器读取binlog

mysqlbinlog \
  --read-from-remote-server \
  --host=192.168.1.100 \
  --user=repl \
  --password \
  mysql-bin.000001

注意事项

常用命令速查

这些操作可以帮助你有效管理MySQL的binlog日志，实现数据审计、故障恢复和主从复制等功能。