binlog 日志

binlog 就是 binary log，MySQL 的二进制日志文件，以二进制的形式记录了所有数据库的更新语句，包括表更新和记录更新，即数据操纵语言 (DML)，binlog 主要用于数据恢复和配置主从复制等；

  • 数据恢复：当数据库误删时，可以通过 binlog 恢复到某个时间点的数据。
  • 主从复制：当有数据库更新之后，主库通过 binlog 记录并通知从库进行更新，从而保证主从数据库数据一致；

mysql 按照功能分为 服务层模块 和 存储引擎层模块

  • 服务层负责客户端连接、SQL 语句处理优化等操作

  • 存储引擎层负责数据的存储和查询

binlog 属于服务层模块的日志，即引擎无关性，所有数据引擎的数据更改都会记录 binlog 日志。

当数据库发生崩溃时，如果使用 InnoDB 引擎，binlog 日志还可以检验 InnoDB 的 redo 日志的 commit 情况。

开启 binlog 日志

我使用的 MySQL 5.7 默认没有 binlog 日志，开启方式是找到 my.inf 主配置文件

1、找到配置文件

Windows 下找到 MySQL 使用的 my.ini 可以参考：修改 my.ini 配置文件跳过权限验证

Linux 下找到 MySQL 使用的 my.inf 可以通过命令，判断 MySQL 启动时是否指定了配置文件

1
ps aux|grep mysql|grep 'my.cnf'

如果没有输出的话，查看 MySQL 默认使用的配置文件顺序

1
mysql --verbose --help | grep -A 1 'Default options'

MySQL 是按照顺序先后读取配置文件，新安装的话大概率是 /etc/my.cnf

再或者直接查找这个配置文件

1
find / -name "my.cnf"

2、修改配置文件

找到配置文件后

在 [mysqld]下添加

1
2
3
4
5
6
7
8
9
10
# binlog日志默认就是在/var/lib/mysql目录
# Windows下默认是在 C:\ProgramData\MySQL\MySQL Server 5.7\Data 目录
# 也可以自己指定位置，我的是没有指定的
# 比如：log-bin=/var/lib/mysql/mysql-bin
log-bin=mysql-bin

# 配置serverid，在Windows下我的已经默认配置了就是1
# Linux下需要自己手动添加
# 如果是集群情况，需要修改数字不能重复
server-id=1

修改完后重启 MySQL 即可

其它配置参数

1
2
3
4
5
6
7
8
9
10
11
12
13
[mysqld]
# 设置日志三种格式：STATEMENT、ROW、MIXED
binlog_format = ROW
# 设置日志路径
log-bin=mysql-bin
# 设置binlog清理时间
expire_logs_days = 7
# binlog每个日志文件大小
max_binlog_size = 100m
# binlog缓存大小
binlog_cache_size = 4m
# 最大binlog缓存大小
max_binlog_cache_size = 512m

binlog 日志格式

通过参数 binlog_format 参数的值，可以设置 binlog 的格式，可选值有 statement、row、mixed

  • statement 格式：每一条会修改数据的 SQL 语句，都会记录到 binlog 中，可以直接看到原始执行的 SQL 语句
  • row 格式：仅记录哪条数据被修改了，修改成了什么样子。我目前使用的 MySQL5.7 版本目前默认值
  • mixed 格式：混合模式复制，以上两种模式的混合使用；一般的复制使用 statement 模式保存 binlog，对于 statement 模式无法复制的操作使用 row 模式保存 binlog，MySQL 会根据执行的 SQL 语句选择日志保存方式

binlog 日志重建

当开启 binlog 日志之后，mysql 会创建一个 log_bin_index 指定的 .index 文件和多个二进制日志文件，index 中按顺序记录了 mysql 使用的所有 binlog 文件。binlog 日志则会以指定的名称 (或默认值) 加自增的数字作为后缀，比如 mysql-bin.000001，当发生下述三种情况时，binlog 日志便会进行重建

  • 文件大小达到 max_binlog_size 参数的值
  • 执行 flush logs 刷新命令
  • 重启 mysql 服务

命令查询 BINLOG

1、MySQL Cli 查询 BINLOG

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 查询log-bin是否开启
show variables like 'log_bin';

# 查询 BINLOG 日志格式
show VARIABLES like 'binlog_format';

# 查询 BINLOG 日志保存位置
show VARIABLES like 'datadir';

# 查询当前数据库中 BINLOG 名称及大小
show binary logs;

# 查询 master 正在写入的 BINLOG 信息
show master status\G

查看 binlog 日志内容，语法

1
show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
  • IN ‘log_name’ 指定要查询的 binlog 文件名（不指定就是第一个 binlog 文件）
  • FROM pos 指定从哪个 pos 起始点开始查起（不指定就是从整个文件首个 pos 点开始算）
  • LIMIT [offset,] 偏移量（不指定就是 0）
  • row_count 查询总条数（不指定就是所有行）

比如

1
2
3
4
5
# 通过 offset 查看 BINLOG 信息
show BINLOG events in 'mysql-bin.000001' limit 0,  10;

# 通过 position 查看 binlog 信息
show BINLOG events in 'mysql-bin.000001' from 123 limit 10;

2、mysqlbinlog 查询 BINLOG

因为 binlog 是二进制文件，不能像其他文件一样，直接打开查看。但 mysql 提供了 binlog 查看工具 mysqlbinlog，可以解析二进制文件。当然不同格式的日志解析结果是不一样的；

  1. statement 格式日志，可以直接看到原始执行的 SQL 语句
  2. row 格式日志，则可读性没有那么好，但仍可通过加参数 -v 使文档更加可读

用法

1
mysqlbinlog [options] log-files

mysqlbinlog 的常用参数：

  • -d 仅仅列出配置的数据库信息

  • --no-defaults 读取没有选项的文件，指定的原因是由于 mysqlbinlog 无法识别 BINLOG 中的 default-character-set=utf8 指令，或者可以将 MySQL 配置文件中的default-character-set=utf8 修改为character-set-server = utf8，但是需要重启 MySQL 服务

  • --offset 跳过 log 中 N 个条目

  • --verbose 将日志信息重建为原始的 SQL 陈述

    • -v 仅仅解释行信息
    • -vv 不但解释行信息，还将 SQL 列类型的注释信息也解析出来

  • --base64-output=decode-rows 将 BINLOG 语句中事件以 base-64 的编码显示，对一些二进制的内容进行屏蔽

    • AUTO 默认参数，自动显示 BINLOG 中的必要的语句
    • NEVER 不会显示任何的 BINLOG 语句，如果遇到必须显示的 BINLOG 语言，则会报错退出
    • DECODE-ROWS 显示通过 -v 显示出来的 SQL 信息，过滤到一些 BINLOG 二进制数据

范围选择

  • --start-datetime 显示从指定的时间或之后的时间的事件
    • 接收 DATETIME 或者 TIMESTRAMP 格式
  • --stop-datetime 配合--start-datetime 解析某一个时间段内的 binlog
  • --start-position 配合 --stop-position 解析在两个 position 之间的 binlog

使用

1
2
# 查询 BINLOG 的信息，路径为自己设置的 binlog 路径
mysqlbinlog -v /var/lib/mysql/mysql-bin.000001

截取其中的一部分

1
2
3
# at 219
#220827 18:03:53 server id 1  end_log_pos 1204 CRC32 0x58a84fe1 	Query	thread_id=3	exec_time=0	error_code=0
use `typecho`/*!*/;

上面输出包括如下要素：

  • position：事件的起点，是以 219 字节开始。即第一行的（# at 219，说明该事件记录从文件第 219 个字节开始
  • timestamp：事件发生的时间戳，即第二行的（#220827 18:03:53）
  • server id：服务器标识为 1
  • end_log_pos：1204，为事件的终点，是以 1204 字节位置结束。
  • Query：事件类型为 Query
  • thread_id: 3。代理线程 id 为 3
  • exec_time：事件执行的花费时间
  • error_code：错误码