MySQL binlog详解与解析工具指南

Binlog详解

binlog，即二进制日志，以二进制的格式记录了对数据库所做的修改，包含所有的DDL和DML语句（除查询语句外).

binlog日志的开启只需要在MySQL的my.cnf文件中配置如下变量：

log-bin=file_name
server_id=1

log-bin: 配置binlog日志文件名称的基础名，在这个变量的后面加上数字后缀作为文件名。

日志文件的存储路径默认是MySQL的data目录，如果想自定义目录，则给这个变量加上绝对路径即可。

开启binlog需要同时配置server_id，如果没有配置无法启动服务器。

通过一下命令可以查询binlog日志是否开启：

binlog日志的相关选项配置，可以参照MySQL官网了解详情。

其中需要特别关注的几项有：

binlog_format: 设置binlog日志的格式。分别有STATEMENT、ROW、MIXED三种可选，每种格式都有着不同优缺点。

STATEMENT：日志记录的是sql语句，该格式写入日志量少，但是不够精确，存在很多限制。
ROW：日志记录的的行数据的变化，该格式写入日志量大，精确且灵活，恢复数据的时间会更长。
MIXED：以上两种的混合，根据sql语句选择不同的记录格式。

关于不同格式优缺点可以查看官网关于SBR与RBR的对比说明。

expire_logs_days：binlog日志自动过期时间，到期删除。
max_binlog_size：单个binlog日志文件的最大值，超过该值会重新生成一个新文件。

binlog是以事件形式进行记录。使用如下命令可以查看到binlog中写入的事件：

SHOW BINLOG EVENTS
   [IN 'log_name']
   [FROM pos]
   [LIMIT [offset,] row_count]

根据事件所在的Log，开始位置Pos，结束位置End_log_pos，我们可以解析当前事件所执行的操作。

binlog在MySQL中的主要作用包括：

数据恢复：对数据库的修改都会被记录到binlog中，所以在数据库发生故障或因误删导致数据丢失，可以通过回放binlog来进行恢复。
主从复制：MySQL的主从模式中，从库通过读取主库的binlog文件获取数据变更，再在从库上进行操作，达到主从一致。
审计与监控：分析binlog文件，可以了解对数据库的访问情况，查找异常的更新和删除操作。在生产中，在没有做好操作日志落地的情况下，该功能很有用。

Binlog解析工具

binlog日志以二进制格式存储，为了方面阅读，需要使用相应的binlog解析工具将其解析成文本格式。

我常用的工具有两种，一种是MySQL自带的解析工具mysqlbinlog，一种是开源工具binlog2sql。

mysqlbinlog

mysqlbinlog的使用命令十分简单：

mysqlbinlog [options] log_file ...

上述命令将mysql_bin_0160.000018日志文件所包含的事件全部输出为文本格式。

在一般情况下，我们只需要相应日志文件中的部分日志，或者我们就不知道具体的日志位置。

这就需要我们在日志文件上进行相应的过滤筛选，mysqlbinlog也提供了多种选项供使用。完整详细介绍可以见MySQL官网。

我常用的命令选项如下：

mysqlbinlog --no-defaults --base64-output=DECODE-ROWS -vv --start-datetime='your time' --stop-datetime="your time" --database="your database" log_file > temp.sql

--no-defaults：不读取默认选项。避免读取my.cnf中默认的参数导致解析binlog失败。

--base64-output：使用 base-64 编码打印二进制日志条目;

--start-datetime：从大于等于该时间开始读取事件日志；

--stop-datetime：读取事件日志到大于等于该时间为止；

-vv: 即verbose，将日志重构成sql输出，两个v输出的sql会加上字段的注释。区别如下图：

我日常的业务场景是知道大概的时间范围，查询相关的更改操作。所以上述的命令选项刚好满足。

根据不同的需求场景，需要选择不同的选项，如日志回放需要注意事务id，知道事务的起止位置等。

当使用mysqlbinlog进行数据恢复时，只需要使用输出管道将mysqlbinlog的输出作为mysql输入：

mysqlbinlog binlog_files | mysql -u root -p;

当我们需要修改binlog中的语句时，可以将mysqlbinlog的输出重定向到文本文件中（通常binlog日志可能很大，推荐使用重定向到指定的目录下的文件中），

在修改文件后作为输入进行重放：

mysqlbinlog binlog_files > tmpfile
... edit tmpfile ...
mysql -u root -p < tmpfile

mysqlbinlog处理多个文件时，推荐使用如下的命令：

mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p

使用单个进程处理所有的binlog日志文件。

或者将所有binlog日志写入到同一个文件后，在进行重放：

mysqlbinlog binlog.000001 >  /tmp/statements.sql
mysqlbinlog binlog.000002 >> /tmp/statements.sql
mysql -u root -p -e "source /tmp/statements.sql"

binlog2sql

开源项目地址：https://github.com/danfengcao/binlog2sql

mysqlbinlog 只支持到database级别，在很多情况下，我们需要到table级别。

同时存在将日志中的sql转换成回滚sql的的需求，mysqlbinlog不支持。

binlog2sql的主要功能就包括解析binlog日志（精确到table级别，sql类型级别）、生成回滚日志等。

安装：

git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
pip install -r requirements.txt

使用前置：

MySQL配置需求：

[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full

MySQL权限需求：
在MySQL服务端配置账号，并赋予select, super/replication client, replication slave权限。

用法：

解析sql:

    python binlog2sql.py -hxxxxx -Pxxx -uxxxx -pxxxx -ddb_name -ttable_name --start-file='log_file' --stop-file='log_file' 
    --start-datetime="your time" --stop-datetime="your time" > tmp.sql

解析回滚sql：

    python binlog2sql.py -hxxxxx -Pxxx -uxxxx -pxxxx -ddb_name -ttable_name --flashback --start-file='log_file' 
    --stop-file='log_file' --start-datetime="your time" --stop-datetime="your time" > tmp.sql

选项：

-K, --no-primary-key：对INSERT语句去除主键。可选。默认False。
-B, --flashback：生成回滚SQL。
--start-file/--stop-file: 起始/终止解析文件。
--start-position/--stop-position：起始/终止解析位置。
--start-datetime/--stop-datetime：起始/终止解析时间，格式'%Y-%m-%d %H:%M:%S'。
-d, --databases：只解析目标db的sql，多个库用空格隔开。
-t, --tables：只解析目标table的sql，多张表用空格隔开。
--only-dml：只解析dml，忽略ddl
--sql-type：只解析指定类型，支持INSERT, UPDATE, DELETE。多个类型用空格隔开。

MySQL binlog详解与解析工具指南