MySQL 的 binlog 日志文件,记录了数据库表的全部修改操作。本文简单整理 MySQL binlog 相关知识,以及如何使用 binlog 恢复或闪回数据库数据。
STATEMENT 格式的 binlog
要想开启 binlog,需要在启动 MySQL 时传入 --log-bin 参数。或者也可以在 MySQL 配置文件 /etc/my.cnf
,设置 log_bin
开启 binlog。MySQL 5.7 开始,开启 binlog 后,--server-id
参数也必须指定,否则 MySQL 服务器会启动失败。
binlog_format
支持 STATEMENT
, ROW
, MIXED
三种格式,MySQL 5.5 和 5.6 默认为 STATEMENT
,MySQL 5.7.7 开始默认为 ROW
。若 SQL 使用 UUID(), RAND(), VERSION() 等函数,或者使用存储过程、自定义函数,基于 STATEMENT 的主从复时,是不安全的(很多人可能会认为 NOW(), CURRENT_TIMESTAMP 这些函数也是不安全的,事实上是安全的)[ doc1, doc2 ]。基于 ROW
的主从复制,是最安全的复制方式。
现在先来看下 STATEMENT
格式的 binlog,/etc/my.cnf
文件修改的内容如下:
1 | server_id = 1 |
重启 MySQL 后,在数据目录 datadir 下,比如 /var/lib/mysql/
,将会生成相应的 binlog 文件,mysql-bin.index
和 mysql-bin.000001
。.index
后缀的文件保存全部 binlog 文件名。mysql-bin.000001
文件记录 binlog 内容。每次 MySQL 启动或者 flush 日志,都将按序号创建一个新的日志文件。另外,当日志文件大小超过 max_binlog_size
时,也会创建一个新的日志文件。
现在来试一试 binlog 功能。假设在 testdb
库在有 hello
表,并对其中某行做修改操作:
1 | mysql> select * from hello; |
binlog 为二进制文件,需要使用 mysqlbinlog
(doc, man)命令查看:
1 | $ sudo mysqlbinlog /var/lib/mysql/mysql-bin.000001 # 直接在 mysql 服务器上读取 binlog 文件 |
执行 update
后相应新增的 binlog 文件内容:
1 | # at 154 |
ROW 格式的 binlog
修改 /etc/my.cnf
的 binlog_format
为 ROW
,再重启 MySQL。格式修改后,会生成一个新的 binlog 文件 mysql-bin.000002
。
1 | mysql> show create table hello; |
查看 ROW
格式的 binlog,需要使用 sudo mysqlbinlog -v --base64-output=DECODE-ROWS /var/lib/mysql/mysql-bin.000002
命令。执行 update
后相应新增的 binlog 内容:
1 | # at 154 |
若执行如下 SQL:
1 | mysql> insert hello (name) values ('Frank'); |
相应生成的 binlog 内容:
1 | # at 442 |
若执行如下 SQL:
1 | mysql> delete from hello where id = 2; |
相应生成的 binlog 内容:
1 | # at 715 |
使用 binlog 增量恢复
MySQL 逻辑备份通常会结合全量备份和增量备份,使用 mysqldump
定期全量备份数据库,然后利用 binlog 保存增量数据。恢复数据时,就是用 mysqldump
备份的数据恢复到备份的时间点。数据库在备份时间点到当前时间的增量修改,则通过 mysqlbinlog
将 binlog 中的增量数据恢复到数据库。现在假设已经使用 mysqldump
将数据库还原到:
1 | mysql> select * from hello; |
之后执行的 SQL:
1 | update hello set name = 'David' where id = 3; |
不管是使用 STATEMENT
还是 ROW
,mysqlbinlog
命令都可以将 binlog 增量恢复到数据库 [ doc ]。
观察 binlog
可以看到,从最开始的 update hello set name = 'David' where id = 3;
到最终的 delete from hello where id = 2;
,时间上从 "2018-06-17 22:54:13" 到 "2018-06-17 22:56:44",所以基于时间点恢复,命令如下:
1 | $ sudo mysqlbinlog --start-datetime="2018-06-17 22:54:13" --stop-datetime="2018-06-17 22:56:44" mysql-bin.000002 | mysql -uroot -p123456 |
binlog
的事件位置号是从 "154" 到 "956",但需要注意的是 用 --start-position
和 --stop-position
指定位置点范围,逻辑上对应的是 start <= position < stop
,所以基于时间点恢复,命令如下:
1 | $ sudo mysqlbinlog --start-position=154 --stop-position=957 mysql-bin.000002 | mysql -uroot -p123456 |
两种方式任意执行,都能将数据恢复到:
1 | mysql> select * from hello; |
使用 binlog2sql 闪回
binlog2sql,作者为曹单锋,大众点评 DBA。binlog2sql
,从 MySQL binlog 解析出你要的 SQL。根据不同选项,你可以得到原始 SQL、回滚 SQL、去除主键的 INSERT SQL 等。binlog2sql
,底层实现依赖 python-mysql-replication,由该库完成 MySQL 复制协议和 binlog 格式的解析。
1 | python binlog2sql/binlog2sql.py -h192.168.2.107 -uroot -p123456 --start-position=154 --stop-position=957 --start-file='mysql-bin.000002' |
生成回滚 sql:
1 | python binlog2sql/binlog2sql.py --flashback -h192.168.2.107 -uroot -p123456 --start-position=154 --stop-position=956 --start-file='mysql-bin.000002' |
闪回的现实原理很简单,先通过 MySQL 复制协议的 com-binlog-dump 命令 dump 出 binlog,然后按照 binlog 的格式规范解析 binlog,将 binlog 转换成 SQL,再将这些 SQL 转换反向逻辑的 SQL,最后再倒序执行。具体可以看,binlog2sql
作者的文章 [ ref ]。
Java 解析 binlog
上文中的 binlog2sql
其实底层依赖 python-mysql-replication
库,这是 Python 库。如果想使用 Java 解析 binlog 可以使用 mysql-binlog-connector-java
(github)库。目前开源的 CDC 工具,如 Zendesk maxwell、Redhat debezium、LinkedIn Databus 等都底层依赖 mysql-binlog-connector-java
或者其前身 open-replicator。使用 mysql-binlog-connector-java
的示例代码如下:
1 | BinaryLogClient client = new BinaryLogClient("192.168.2.107", 3306, "root", "123456"); |
输出(省略部分内容):
1 | ... |
参考资料
- MySQL Replication: 5.1.1 Advantages and Disadvantages of Statement-Based and Row-Based Replication https://dev.mysql.com/doc/mysql-replication-excerpt/5.7/en/replication-sbr-rbr.html
- Unsafe Statements for Statement-based Replication https://mariadb.com/kb/en/library/unsafe-statements-for-statement-based-replication/
- MySQL 5.7 Reference Manual: 4.6.7 mysqlbinlog https://dev.mysql.com/doc/refman/5.7/en/mysqlbinlog.html
- MySQL Internals Manual: 14.9 Replication Protocol https://dev.mysql.com/doc/internals/en/replication-protocol.html
- MySQL Internals Manual: 20 The Binary Log https://dev.mysql.com/doc/internals/en/binary-log.html
- MySQL闪回原理与实战 https://github.com/danfengcao/binlog2sql/blob/master/example/mysql-flashback-priciple-and-practice.md
- https://dev.mysql.com/doc/dev/mysql-server/8.0.11/classbinary__log_1_1Table__map__event.html#details
- https://dev.mysql.com/doc/dev/mysql-server/8.0.11/classbinary__log_1_1Rows__event.html#details