MySQL binlog 恢复数据

概述

本文将会带大家使用 mysql binlog 恢复被误删的数据，或者恢复某个粗心大意的同事手抖而更新错的数据。没有你说不定他就要提桶跑路了，答应我为了你的同事好好学习下如何使用 mysql binlog 恢复数据。

查看 binlog 是否开启以及 binlog 格式

既然是利用 binlog 来恢复数据，那么肯定要开启 binlog，先连上数据库，然后查看 binlog 是否开启，以及当前 binlog 的格式。

查看 binlog 是否开启，执行下面的 sql，如果是 ON 则表示开启，OFF 表示未开启。

show variables like 'log_bin';

查看当前 binlog 格式，可使用下面的 sql

show variables like 'binlog_format';

binlog 总共有三种格式，三种格式各有优缺点，本文不会讲述这三个格式的区别，感兴趣的请自行查阅。我们希望使用的格式为 ROW。

如果数据库没有备份，binlog 也没打开，那么基本可以提桶跑路了。

开启 binlog

下面以 linux 发行版 Ubuntu 为例说明如何开启 binlog，找到 /etc/mysql/my.cnf 中加入：

[mysqld]
# Must be unique integer from 1-2^32
server-id        = 1
# Row format required for ZongJi
binlog_format    = row
# Directory must exist. This path works for Linux. Other OS may require
# different path.
log_bin          = /var/log/mysql/mysql-bin.log

然后便是重启 mysql 使其生效

service mysql stop
service mysql start

使用 binlog 恢复误删数据

为了方便说明，假设有一张 vip 表，经过日积月累，已经累计高达 3 条宝贵数据：

现在轮到你的粗心同事张大宽粉墨登场。他在执行删除时，忘加条件了，当他执行完下面的语句才反应过来，于是慌慌张张找到你，让你无论如何拉兄弟一把。

delete from vip;

想到这个高达几个小目标的宝贵数据，你赶紧得想办法恢复数据。先执行以下 sql，看看 binlog 目前的情况，由于刚刚事发，通常找到最新的那个 binlog 即可

接下来便是下载 binlog，下载 binlog 可以利用 mysqlbinlog 备份命令完成

mysqlbinlog --read-from-remote-server --raw --host=192.168.8.8 --port=3306 --user=repl -p  mysql-bin.000001
# 或者
mysqlbinlog -R --raw --host=192.168.8.8 --port=3306 --user=root -p  mysql-bin.000001

注：这里的 host、user 和 binlog 文件名称等参数需要根据实际情况修改。

下载完 binlog 之后，便是使用 mysqlbinlog 命令将 binlog 转换成可读的 sql 形式，命令如下：

# 转换的结果存放在 dump.sql 中
mysqlbinlog -v mysql-bin.000001 > ./dump.sql

使用编辑器，打开 dump.sql，并询问你的同事误删除大概发生的时间，确定删除的内容。经过一番搜索，最终找到了发生这个不幸的时间在 21:28:20，你心想原来都这么晚了，脑子迷糊了也是有可能的。

# at 47663
#230924 21:28:20 server id 1  end_log_pos 47737 CRC32 0xd2311fee 	Query	thread_id=40	exec_time=0	error_code=0
SET TIMESTAMP=1695562100/*!*/;
BEGIN
/*!*/;
# at 47737
#230924 21:28:20 server id 1  end_log_pos 47792 CRC32 0xf93c16fc 	Table_map: `vip`.`vip` mapped to number 101
# has_generated_invisible_primary_key=0
# at 47792
#230924 21:28:20 server id 1  end_log_pos 47875 CRC32 0xd80b456b 	Delete_rows: table id 101 flags: STMT_END_F

BINLOG '
dDkQZRMBAAAANwAAALC6AAAAAGUAAAAAAAEAA3ZpcAADdmlwAAMDDwMCUAAGAQEAAgEt/BY8+Q==
dDkQZSABAAAAUwAAAAO7AAAAAGUAAAAAAAEAAgAD/wABAAAABuW8oOS4iRQAAAAAAgAAAAbmnY7l
m5sVAAAAAAMAAAAG546L5LqUFgAAAGtFC9g=
'/*!*/;
### DELETE FROM `vip`.`vip`
### WHERE
###   @1=1
###   @2='张三'
###   @3=20
### DELETE FROM `vip`.`vip`
### WHERE
###   @1=2
###   @2='李四'
###   @3=21
### DELETE FROM `vip`.`vip`
### WHERE
###   @1=3
###   @2='王五'
###   @3=22
# at 47875
#230924 21:28:20 server id 1  end_log_pos 47906 CRC32 0x35a7f812 	Xid = 602
COMMIT/*!*/;

这不就好了吗，定位到了删除的地方，删除的具体内容全都记录在案，剩下的工作便是着手恢复了。如果真的只有三条数据，那便可以手动恢复了。实际工作中往往删除的数据量很大，那么可以借助 MyUndelete.py 完成恢复。

可以看出开始删除的语句起始位置为 47663，是一个 BEGIN 语句，终止位置为 47906，为 COMMIT 语句。有了开始和结束位置，便可以使用 MyUndelete 命令恢复。

./MyUndelete.py -b mysql-bin.000001 -s 47663 -e 47906

这个工具除了可以恢复误删的数据，也可以对误更新和误插入的数据进行恢复。个人感觉这个工具有点虎，你用上面的命令之后，数据被直接恢复到数据库，你没有机会检查恢复数据的 sql 究竟是否 OK。

另外工具稍显久远，使用 python 2.x 写的。另外需要提一句，使用本命令之前需要先配置好 Mysql 的用户名和密码，不支持通过命令行传递用户名和密码。配置通常在 /etc/mysql/my.cnf，在该文件中加入：

[client]
user=yourusername
password=yourpwd

其他数据恢复工具

这里只是列出一些 binlog 数据恢复工具，不会做过多说明，如有需要，直接去看工具文档即可。

1、binlog2sql

可以使用下面的命令生成回滚 sql，注意下面的这个命令不需要下载 binlog，直接连 mysql 生成回滚 sql。

# 生成回滚 sql
python ./binlog2sql.py --flashback -h127.0.0.1 -P3306  -uroot -p --start-file='mysql-bin.000001' --start-position=47663 --stop-position=47906 > rollback.sql
# 导入 mysql
mysql -h127.0.0.1 -P3306 -uroot -p'123456' < rollback.sql

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

2、my2sql

my2sql 是 go 实现的，速度比 binlog2sql 快很多，使用 my2sql 生成回滚 sql 的方法如下：

./my2sql -user root -password 123456 -mode file -local-binlog-file /path/to/mysql-bin.000001 -work-type rollback --start-file /path/to/mysql-bin.000001 -start-pos 47663 -stop-pos 47906 -output-dir /tmp/abc

-worktype 还支持 2sql，2sql 不会生成回滚 sql，而是直接展示原始 sql。

项目地址：https://github.com/liuhr/my2sql