Binlog详解
binlog，即二进制日志，以二进制的格式记录了对数据库所做的修改
，包含所有的DDL和DML语句（除查询语句外)
.
binlog日志的开启
只需要在MySQL的my.cnf文件中配置如下变量：
log-bin=file_nameserver_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.sqlmysqlbinlog binlog.000002 >> /tmp/statements.sqlmysql -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 binlog2sqlpip install -r requirements.txt复制
使用前置：
• MySQL配置需求：
[mysqld]server_id = 1log_bin = /var/log/mysql/mysql-bin.logmax_binlog_size = 1Gbinlog_format = rowbinlog_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。多个类型用空格隔开。
既然已经看到这里，请随手点个赞和“在看”吧。
欢迎关注我的公众号“IT的世界”，原创技术文章第一时间推送。
