MySQL数据误删flashback
使用binlog2sql(Python版)
解析binlog文件来创建回滚的sql
准备工作
需要提前安装Python环境
git clone https://github.com/danfengcao/binlog2sql.git
cd binlog2sql
pip3 install -r requirements.txt
MySQL必须 开启binlog，且binlog_format=ROW，且binlog_row_image=FULL
show variables like 'log_bin';
show variables like '%binlog_format%';
show variables like '%binlog_row_image%';
具体使用
解析出标准SQL
python3 binlog2sql.py -hip -P3310 -ubinlog2sql -p'pwd1' -d testdb -t t --start-file='mysql-bin.000004' --only-dml
仅解析testdb库的t表，只解析dml，忽略ddl，结果中也会包含格式为statement的日志
INSERT INTO `testdb`.`t`(`id`, `c`, `d`) VALUES (0, 0, 0); #start 4 end 438 time 2022-12-15 13:52:48
INSERT INTO `testdb`.`t`(`id`, `c`, `d`) VALUES (5, 5, 5); #start 4 end 438 time 2022-12-15 13:52:48
INSERT INTO `testdb`.`t`(`id`, `c`, `d`) VALUES (10, 10, 10); #start 4 end 438 time 2022-12-15 13:52:48
DELETE FROM `testdb`.`t` WHERE `id`=0 AND `c`=0 AND `d`=0 LIMIT 1; #start 469 end 737 time 2022-12-15 13:53:03
DELETE FROM `testdb`.`t` WHERE `id`=5 AND `c`=5 AND `d`=5 LIMIT 1; #start 469 end 737 time 2022-12-15 13:53:03
解析出回滚SQL
python3 binlog2sql.py --flashback -hip -P3310 -ubinlog2sql -p'pwd1' -d testdb -t t --start-file='mysql-bin.000001' --only-dml
- 添加--only-dml参数，结果会过滤格式为statement的日志
- 不添加--only-dml参数，格式为statement的日志原样输出
INSERT INTO `testdb`.`t`(`id`, `c`, `d`) VALUES (0, 0, 0); #start 4 end 438 time 2022-12-15 13:52:48
INSERT INTO `testdb`.`t`(`id`, `c`, `d`) VALUES (5, 5, 5); #start 4 end 438 time 2022-12-15 13:52:48
INSERT INTO `testdb`.`t`(`id`, `c`, `d`) VALUES (10, 10, 10); #start 4 end 438 time 2022-12-15 13:52:48
DELETE FROM `testdb`.`t` WHERE `id`=0 AND `c`=0 AND `d`=0 LIMIT 1; #start 469 end 737 time 2022-12-15 13:53:03
DELETE FROM `testdb`.`t` WHERE `id`=5 AND `c`=5 AND `d`=5 LIMIT 1; #start 469 end 737 time 2022-12-15 13:53:03
INSERT INTO `testdb`.`t`(`id`, `c`, `d`) VALUES (60, 60, 60); #start 19012 end 19267 time 2022-12-15 14:21:46
DELETE FROM `testdb`.`t` WHERE `id`=60 AND `c`=60 AND `d`=60 LIMIT 1; #start 19298 end 19553 time 2022-12-15 14:22:18
出现的问题
Traceback (most recent call last):
File "binlog2sql/binlog2sql.py", line 149, in <module>
back_interval=args.back_interval, only_dml=args.only_dml, sql_type=args.sql_type)
File "binlog2sql/binlog2sql.py", line 46, in __init__
self.connection = pymysql.connect(**self.conn_setting)
File "/usr/local/lib/python3.6/site-packages/pymysql/__init__.py", line 90, in Connect
return Connection(*args, **kwargs)
File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 706, in __init__
self.connect()
File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 931, in connect
self._get_server_information()
File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 1269, in _get_server_information
self.server_charset = charset_by_id(lang).name
File "/usr/local/lib/python3.6/site-packages/pymysql/charset.py", line 38, in by_id
return self._by_id[id]
KeyError: 255
升级PyMySQL
pip3 install --upgrade PyMySQL
Traceback (most recent call last):
File "binlog2sql/binlog2sql.py", line 7, in <module>
from pymysqlreplication import BinLogStreamReader
File "/usr/local/lib/python3.6/site-packages/pymysqlreplication/__init__.py", line 23, in <module>
from .binlogstream import BinLogStreamReader
File "/usr/local/lib/python3.6/site-packages/pymysqlreplication/binlogstream.py", line 8, in <module>
from pymysql.util import int2byte
ModuleNotFoundError: No module named 'pymysql.util'
pip3 uninstall pymysql
pip3 install pymysql==0.9.3
binlog中没有记录新的日志
原因：账号权限不对
解决：创建账号，重新授权
CREATE USER 'binlog2sql'@'%' IDENTIFIED WITH mysql_native_password BY 'pwd1';
GRANT SELECT,REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'binlog2sql'@'%';
flush privileges;
参考
使用binlog2sql(Java版)
<dependency>
<groupId>com.github.shyiko</groupId>
<artifactId>mysql-binlog-connector-java</artifactId>
<version>0.18.0</version>
</dependency>
仅能监听新的binlog，不能解析一个文件的所有event
参考
使用美团MyFlash
该方式不像 binlog2sql 一样转换binlog为易读的sql语句，而是直接截取复制并修改二进制 binlog 文件实现SQL的反转，然后使用mysqlbinlog 命令读取新生成的二进制binlog闪回文件，将闪回操作导入数据库实现数据的恢复。
git clone https://github.com/Meituan-Dianping/MyFlash.git
gcc -w `pkg-config --cflags --libs glib-2.0` source/binlogParseGlib.c -o binary/flashback
获取开始和结束位置
show binlog events in 'mysql-bin.000001';
获取要闪回事务的开始和结束位置
生成闪回binlog文件
这里使用的是docker的mysql，需要将docker内部的binlog文件拷贝出来
docker cp xxx:/var/lib/mysql/mysql-bin.000001 .
binlogFileNames指定binlog文件的具体路径
rm -rf MyFlash-master/binlog_output_base.flashback
MyFlash-master//binary/flashback --binlogFileNames=mysql-bin.000001 --sqlTypes=delete --start-position=21247 --stop-position=21552
通过开始和结束位置来具体确定一个事务(删除 tb_user 中 uid 为7的行)
执行闪回
将生成的闪回文件拷贝到docker内部
docker cp binlog_output_base.flashback xxx:/
在docker内部执行此命令
mysqlbinlog --skip-gtids binlog_output_base.flashback | mysql -uroot -p
select * from tb_user;
可以看到结果中uid为7的行已经加回来了
参考
[美团] Myflash 的安装使用
MyFlash——美团点评的开源MySQL闪回工具
MyFlash-github地址
