binlog数据恢复 mysql mysql binlog 恢复

mysql之binlog数据恢复

• mysql之binlog数据恢复

• 概览
• 事故背景
• 分析及解决步骤

• 查看mysql的配置

• 一. 查看数据库是否开启bin_log
• 二. 查看数据库binlog_format的类型
• 三. 找到binlog路径
• 四. 查看业务数据库是否有定时备份

• 解决办法
• 利用binlog2sql逆向操作回复数据

• 一. 修改数据库binlog_format,从 MIXED 修改为 ROW
• 二. 数据修复前的准备
• 三. 利用binlog2sql逆向修复数据

• 总结与反思

• 一. 总结
• 二. 反思

• 感谢
• 参考和资料

mysql之binlog数据恢复

概览

在执行批量业务数据处理时，sql语句的where条件写错了，导致全表50万多条数据被更新错了。整篇文章主要记录分析问题、解决问题的过程，以及自己在整个过程中的收获。

事故背景

错误sql语句: update poi_img_src set checked=2 where faces=0;
 正确sql语句: update poi_img_src set checked=2 where faces>0;

早上10点到公司时，执行了上述”错误的sql语句“，导致全表50多万条数据的checked状态被强制更新，导致之前业务4万多条数据被废弃。本想通过上述的语句提高业务的工作效率，没想到反而给业务造成了较大的损失。

在出现问题的第一时间，除了懊悔自己的粗心大意，同时也想尽可能的恢复mysql数据表的数据。

分析及解决步骤

查看mysql的配置

一. 查看数据库是否开启bin_log

show global variables like ‘%log_bin%’;

很幸运的发现，我们开启了binlog，这是我们后续做数据恢复的基础。

二. 查看数据库binlog_format的类型

show global variables like ‘%format%’;

我们发现binlog_format居然是MIXED，这个就未我们后续进行数据恢复埋下了一个小坑。因为只有binlog_format=ROW才能进行数据恢复。

三. 找到binlog路径

我们通过上面的分析，没有找到Binlog的路径。我们通过以下的方法找binlog路径：

1. 登录到数据库所在的服务器 ssh 172.1.4.245
3. 查看当前服务器进程中运行的mysql实列 ps -ef|grep mysql



4. 通过上述的命令找到业务实列的配置文件地址 /etc/my.cnf
6. 打开/etc/my.cnf，找到binlog的地址



8. ll /Datta/apps/mysql/data/, 找到mysql-bin.001400,即是我们要找的binlog日志



9. 导出指定时间段的binlog日志

`/Data/apps/mysql/bin/mysqlbinlog --no-defaults -v -v --base64-output=decode-rows --start-datetime=‘2021-03-02 9:31:23’ --stop-datetime=‘2021-03-02 10:41:23’ /Data/apps/mysql/data/mysql-bin.001400 > /tmp/binlog557.sql

命令解析：
–no-defaults -v -v --base64-output=decode-rows 指定导出数据格式，不设定导出格式，将会看到乱码
–start-datetime 指定了日志的开始时间节点
–stop-datetime 指定了日志的结束时间节点
/Data/apps/mysql/data/mysql-bin.001400 是我们的Binlog日志路径
/tmp/binlog557.sql 导出结果重定向到指定的文件

binlog打开后，结果类似如下图：

几个非常有用的信息

• " at 734278818 "这个是binlog执行的position，这个很重要，在后续的数据恢复中会用到。
• 查看日志文件中的sql，确认是否是我们业务更新所生成的
• sql语句中 @1 @2 @x ,其中的数字代表的是数据表中的字段
• 之前我们执行update语句，执行时更新了N条记录，那在binlog中就会生成N条新的update语句。
• 新的update语句中，其中SET是包含将要修改的所有字段的数据，WHERE中的数据则是更新前的原始数据。 这里就为我们后续数据逆向恢复奠定了基础。

四. 查看业务数据库是否有定时备份

登录业务数据库机器上，执行crontab -l后，我们发现并没有任何的定时备份脚本在执行。 这是一个巨坑！
建议： 业务数据库表，无论是线上环境、线下环境、测试环境等，都要做好定期备份。

解决办法

通过上述的分析，我们看到了数据恢复的可能性。目前已知的数据恢复有如下两种方式：

1. 基于已有的数据库表的备份，再加上binlog日志进行数据恢复。
2. 直接解析binlog，通过binlog2sql，进行逆向sql数据恢复。

利用binlog2sql逆向操作回复数据

一. 修改数据库binlog_format,从 MIXED 修改为 ROW

set global binlog_format=row

修改这个配置的时候，我有点犯傻，我看见语句中的global，我当时再想有没有针对单库单表的设置，最后一咨询和细想，这个问题很蠢。binlog都是针对一个物理实列去存储的，所以根本就不用去多想这个。

验证修改后数据库的配置：

show global variables like ‘%format%’;

二. 数据修复前的准备

1、用户需要的权限：
select , replication slave ,replication client on .

2、binlog_format必须是row

3、确定pos号，快速定位，避免大量不必要的信息
show binlog events in ‘mysql-bin.000039’;或mysqlbinlog --base64-output

4、解析指定binlog中执行过的sql，注意，–start-file直接填binlog名称就可以，不要加路径
命令如下：
python binlog2sql.py -h192.168.1.165 -P3306 -uflashuser -p111111 -dtest -t tbl --start-file=‘mysql-bin.000039’ --start-position=133130 --stop-position=1683306

5、反转sql语句
命令如下：
python binlog2sql.py --flashback -h192.168.1.165 -P3306 -uflashuser -p111111 -dtest -t tbl --start-file=‘mysql-bin.000039’ --start-position=133130 --stop-position=1683306
再把这些语句中需要的放回mysql执行

6、安装
需要安装python和pip

• 已测试环境

• Python 2.6, 2.7, 3.4
• MySQL 5.6

7、安装binlog2sql

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

8、binlog2sql的基本用法

解析出标准SQL

shell> python binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -t test3 test4 --start-file='mysql-bin.000002'

输出：
INSERT INTO `test`.`test3`(`addtime`, `data`, `id`) VALUES ('2016-12-10 13:03:38', 'english', 4); #start 570 end 736
UPDATE `test`.`test3` SET `addtime`='2016-12-10 12:00:00', `data`='中文', `id`=3 WHERE `addtime`='2016-12-10 13:03:22' AND `data`='中文' AND `id`=3 LIMIT 1; #start 763 end 954
DELETE FROM `test`.`test3` WHERE `addtime`='2016-12-10 13:03:38' AND `data`='english' AND `id`=4 LIMIT 1; #start 981 end 1147

解析出回滚SQL

shell> python binlog2sql.py --flashback -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttest3 --start-file='mysql-bin.000002' --start-position=763 --stop-position=1147

输出：
INSERT INTO `test`.`test3`(`addtime`, `data`, `id`) VALUES ('2016-12-10 13:03:38', 'english', 4); #start 981 end 1147
UPDATE `test`.`test3` SET `addtime`='2016-12-10 13:03:22', `data`='中文', `id`=3 WHERE `addtime`='2016-12-10 12:00:00' AND `data`='中文' AND `id`=3 LIMIT 1; #start 763 end 954

三. 利用binlog2sql逆向修复数据

1、导出数据sql，查看数据是否正确

python binlog2sql.py  -h172.1.4.245 -P3306 -uroot -ppwd -dtestdatabase -txxx_img_src --only-dml --start-file='mysql-bin.001400' --start-position=735176982   --stop-position=935357078 > /tmp/mysqlxxxxxxx2021.log&

2、导出回滚sql

python binlog2sql.py --flashback -h172.1.4.245 -P3306 -uroot -ppwd -dtestdatabase -txxx_img_src --only-dml --start-file='mysql-bin.001400' --start-position=735176982   --stop-position=935357078 > /tmp/mysqlrolback2021.log&

3、登录业务数据库服务器，执行回滚sql

mysql -h -uroot -p
mysql> use 数据库
mysql> source /tmp/mysqlrolback2021.log

在source执行完成后，数据库里数据才会被刷新。如果在执行source过程中，无法查看到已经被修改的数据。

---

总结与反思

一. 总结

1. 在进行批量数据更新时，需要格外的仔细和小心，建议能找同事帮忙code reivew后才执行。
2. 深刻理解了update批量执行过程及代价。一个影响N行的update语句，在binlog中会被生成N条sql语句。本文中1条update的sql,就在binlog中生成了50万行sql，这对主从同步带来了极大的风险，从库会产生较大的数据延迟。
3. binlog日志有多种形式：STATEMENT模式、ROW模式、MIXED模式

STATEMENT模式（SBR）
每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数，
last_insert_id()，以及user-defined functions(udf)等会出现问题)

ROW模式（RBR）
不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter
table的时候会让日志暴涨。

MIXED模式（MBR）
以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

4. 一个好工具将事半功倍。强烈推荐binlog2sql

二. 反思

1. 为什么我们的binlog_format必须要修改为Row?
原因：binlog_format=ROW，执行update时，一定会记录哪些数据被修改了，修改成什么样了。但是这个模式下，特别是alter table、update等执行时，会产生大量的日志。
通过上面分析，可以看到数据库的默认模式是MIXED，这个模式兼用了STATEMENT与ROW模式的优点。这个模式在update等语句执行时，会启用ROW模式。

2. 我们为什么能恢复数据？
原因：数据库记录了binlog日志，且记录了每行数据被修改前和修改后的记录。我们利用binlog2sql逆向导出日志就能恢复数据。

感谢

非常感谢最好的DBA,我的前同事，我最好的朋友吕娟。
上面都是在他的指导下完成的。向他致敬！

参考和资料

1. Mysql数据库导入命令Source详解
2. mysql的binlog日志的binlog_format模式与配置详解