binlog2sql闪回
概述
在生产环境中经常会遇到误删除、改错数据的情况，现在介绍一款很方便、又省时的MySQL闪回工具binlog2sql，它可以实现数据的快速回滚，从binlog中提取SQL,并能生成回滚SQL语句。binlog以event作为单位记录数据库变更的数据信息，闪回就是可以重现这些变化数据信息之前的操作。也就是说，对于insert操作，会生成delete吾句，反之delete操作会生成insert吾句。对于update操作，也会生成相反的update语句。这款工具只能在binlog格式为row模式下才能使用。
安装必需的工具包
安装准备各种依赖的工具包
python-pip PyMYSQL python-mysql-replication wheel argparse
tar -xzf pip-9.0.1.tar.gz
cd pip-9.0.1
python setup.py install
tar -xzf PyMySQL-0.7.11.tar.gz
cd PyMySQL-0.7.11
python setup.py install
tar -xzf mysql-replication-0.13.tar.gz
cd mysql-replication-0.13
python setup.py install
tar -xzf wheel-0.29.0.tar.gz
cd wheel-0.29.0
python setup.py install
tar -xzf argparse-1.4.0.tar.gz
cd argparse-1.4.0
安装binlog2sql工具
binlog2sql闪回工具下载地址：
https://github.com/danfengcao/binlog2sql
安装binlog2sql工具：
unzip binlog2sql-master.zip
cd binlog2sql-master
pip install -r requirements.txt
查看binlog2sql命令解释
python binlog2sql.py --help
可通过python binlog2sql.py -help命令查看参数的使用方式。
-B,--flashback:生成回滚语句。
--start-file:需要解析的binlog文件。
--start-position:解析 binlog 的起始位置。
--stop-position:解析 binlog 的结束位置。
--start-datetime:从哪个时间点的binlog开始解析，格式必须为datetime。
--stop-datetime:到哪个时间点的binlog停止解析，格式必须为datetime。
-d,--databases:只输出目标 db 的 SQL。
-t,--tables:只输出目标 tables 的 SQL。
binlog2sql的注意事项：
(1) 要保证MySQL服务是开启的，离线无法进行分析binlog。
(2) binlog_format 必须是 row 格式。
(3) DDL语句无法做到闪回，只能解析DML语句。
闪回测试步骤
(1) 创建用户，用于闪回
grant select,replication slave,replication client on *.* to 'flashback'@'%' identified by 'XXXXX';
flush privileges;(2) 创建测试表并准备数据
use test;
CREATE TABLE TEST_FLASHBACK
(EMPNO INT(10),
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR INT(10),
HIREDATE DATETIME,
SAL INT(10),
COMM INT(10),
DEPTNO INT(10));
INSERT INTO TEST_FLASHBACK VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO TEST_FLASHBACK VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO TEST_FLASHBACK VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);(3) 删除数据
delete from TEST_FLASHBACK;(4) 闪回数据
show master status;
python binlog2sql.py -h192.168.0.13 -P3306 -uflashback -pXXXXX -dtest -tTEST_FLASHBACK --start-file='mysql-bin.000005' --start-datetime='2020-02-22 15:40:00' --stop-datetime='2020-02-22 15:50:00'
python binlog2sql.py -h192.168.0.13 -P3306 -uflashback -pXXXXX -dtest -tTEST_FLASHBACK --start-file='mysql-bin.000005' --start-position=1845 --stop-position=2201 -B > TEST_FLASHBACK_ROLLBACK.sql闪回测试记录
[root@source mysql]# mysql -p
root@db 15:40: [(none)]> grant select,replication slave,replication client on *.* to 'flashback'@'%' identified by 'XXXXX';
Query OK, 0 rows affected, 1 warning (0.01 sec)
root@db 15:42: [(none)]> flush privileges;
Query OK, 0 rows affected (0.02 sec)
root@db 15:42: [(none)]> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 | 622 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)root@db 15:42: [(none)]> use test;
Database changed
root@db 15:43: [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| BONUS |
| DEPT |
| EMP |
| SALGRADE |
+----------------+
4 rows in set (0.00 sec)root@db 15:43: [test]> CREATE TABLE TEST_FLASHBACK
-> (EMPNO INT(10),
-> ENAME VARCHAR(10),
-> JOB VARCHAR(9),
-> MGR INT(10),
-> HIREDATE DATETIME,
-> SAL INT(10),
-> COMM INT(10),
-> DEPTNO INT(10));
Query OK, 0 rows affected (0.58 sec)root@db 15:44: [test]> INSERT INTO TEST_FLASHBACK VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
Query OK, 1 row affected (0.02 sec)
root@db 15:44: [test]> INSERT INTO TEST_FLASHBACK VALUES (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
Query OK, 1 row affected (0.02 sec)
root@db 15:44: [test]> INSERT INTO TEST_FLASHBACK VALUES (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
Query OK, 1 row affected (0.00 sec)root@db 15:44: [test]> SELECT * FROM TEST_FLASHBACK;
+-------+-------+----------+------+---------------------+------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+----------+------+---------------------+------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30 |
+-------+-------+----------+------+---------------------+------+------+--------+
3 rows in set (0.00 sec)root@db 15:44: [test]> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 | 1845 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)root@db 15:45: [test]> delete from TEST_FLASHBACK;
Query OK, 3 rows affected (0.00 sec)
root@db 15:45: [test]> select * from TEST_FLASHBACK;
Empty set (0.00 sec)root@db 15:45: [test]> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 | 2232 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)[root@source binlog2sql]# python binlog2sql.py -h192.168.0.13 -P3306 -uflashback -pXXXXX -dtest -tTEST_FLASHBACK --start-file='mysql-bin.000005' --start-datetime='2020-02-22 15:40:00' --stop-datetime='2020-02-22 15:50:00'[root@source binlog2sql]# python binlog2sql.py -h192.168.0.13 -P3306 -uflashback -pXXXXX -dtest -tTEST_FLASHBACK --start-file='mysql-bin.000005' --start-position=1845 --stop-position=2201 -B > TEST_FLASHBACK_ROLLBACK.sql[root@source binlog2sql]# ll
total 40
-rwxr-xr-x 1 root root 7747 Oct 12 2018 binlog2sql.py
-rwxr-xr-x 1 root root 11581 Oct 12 2018 binlog2sql_util.py
-rw-r--r-- 1 root root 10616 Feb 22 16:40 binlog2sql_util.pyc
-rw-r--r-- 1 root root 92 Oct 12 2018 __init__.py
-rw-r--r-- 1 root root 689 Feb 22 17:06 TEST_FLASHBACK_ROLLBACK.sql[root@source binlog2sql]# more TEST_FLASHBACK_ROLLBACK.sql
INSERT INTO `test`.`TEST_FLASHBACK`(`ENAME`, `SAL`, `EMPNO`, `MGR`, `JOB`, `COMM`, `HIREDATE`, `DEPTNO`) VALUES ('WARD', 1250, 7521, 7698, 'SALESMAN', 500, '1981-02-22 00:00:00', 30);
INSERT INTO `test`.`TEST_FLASHBACK`(`ENAME`, `SAL`, `EMPNO`, `MGR`, `JOB`, `COMM`, `HIREDATE`, `DEPTNO`) VALUES ('ALLEN', 1600, 7499, 7698, 'SALESMAN', 300, '1981-02-20 00:00:00', 30);
INSERT INTO `test`.`TEST_FLASHBACK`(`ENAME`, `SAL`, `EMPNO`, `MGR`, `JOB`, `COMM`, `HIREDATE`, `DEPTNO`) VALUES ('SMITH', 800, 7369, 7902, 'CLERK', NULL, '1980-12-17 00:00:00', 20);root@db 17:08: [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| BONUS |
| DEPT |
| EMP |
| SALGRADE |
| TEST_FLASHBACK |
+----------------+
5 rows in set (0.00 sec)root@db 17:08: [test]> select * from TEST_FLASHBACK;
Empty set (0.00 sec)
root@db 17:08: [test]> system pwd
/opt/soft/binlog2sql-master/binlog2sql
root@db 17:08: [test]> source /opt/soft/binlog2sql-master/binlog2sql/TEST_FLASHBACK_ROLLBACK.sqlroot@db 17:08: [test]> select * from TEST_FLASHBACK;
+-------+-------+----------+------+---------------------+------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+----------+------+---------------------+------+------+--------+
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30 |
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | NULL | 20 |
+-------+-------+----------+------+---------------------+------+------+--------+
3 rows in set (0.00 sec)my2sql闪回
使用限制
- my2sql 是模拟一个从库去在线获取主库 binlog，然后进行解析，因此执行操作的数据库用户需要具有 SELECT，REPLICATION SALVE，REPLICATION CLIENT 的权限。
- 与 binlog2sql、MyFlash 差不多，my2sql 目前也不支持 8.0；闪回功能需要开启 binlog_format=row，binlog_row_image=full；只能闪回 DML 操作，不支持 DDL 的闪回。
- 无法离线解析 binlog（MyFlash 支持）。
- 不能以 GTID 事务为单位进行解析（MyFlash 支持），具体 file+pos 点位需要先通过手工解析 binlog 后确认。
- 闪回/前滚 SQL 中，没有提供具体的 begin/commit 的位置，使用时无法分隔事务，需要人工判断。
- 使用事务分析功能时，只能给出具体的大/长事务发生时间、点位、涉及的对象和操作类型，不能给出具体的 SQL 语句，完整的语句仍然需要去 binlog 中进行查看（需设置 binlog_rows_query_log_events=on）
实施步骤
一、下载及安装已编译版本
软件下载:https://github.com/liuhr/my2sql
将my2sql-master.zip软件传到tmp目录下
cd /tmp
cp /tmp/my2sql-master.zip /usr/local/bin
unzip /usr/local/bin/my2sql-master.zip得到/usr/local/bin/my2sql-master/releases/centOS_release_7.x
将软件添加到系统变量~/.bash_profile
export PATH=$PATH:/soft/mysql/bin:/usr/local/bin/my2sql-master/releases/centOS_release_7.x二、查看 lsih.test 表数据,删除nm小于80的数据
mysql --login-path=root -A -e "select * from lsih.text;"
mysql --login-path=root -A
delete from lsih.text where nm<80;
select * from lsih.text;三、解析binlog找到闪回的binlog及其pos
修改下面命令中的database,datetime,binlog目录,保存文件路径
mysqlbinlog --base64-output=decode-rows -v --database=lsih --start-datetime="2023-04-01 20:00:00" --stop-datetime="2023-04-01 20:25:00" "/mysql/mysqldata/binlog/bin.000xx" > huifu.sql查找 delete 语句起始及截至的 at xxxx号 如 at 8344894
less 或 cat huifu.sql |grep “230401 20:00” -B5 方式查找
四、生成闪回语句
mkdir /tmp/my2sql_test
my2sql -user lsih -password lsih -host 192.xxx.x.xxx -port 3306 -databases lsih -tables test -server-id 8344894 -mode repl -sql delete -work-type rollback -start-file mysql-bin.000301 -start-pos 8348684 -stop-file mysql-bin.000301 -stop-pos 9987 --add-extraInfo -output-dir /tmp/my2sql_test五、查看反向生成的回滚语句,确认其准确性
cat /tmp/my2sql_test/rollback.1.sql参考文档
https://github.com/liuhr/my2sql
my2sql补充
my2sql工具介绍
说起MySQLbinlog解析工具，相信大家第一反应是binlog2sql，在日常工作中对于逻辑的误操作，误删除等可以通过此工具进行rollback操作。今天为大家介绍的是另一个binlog解析工具my2sql,他是一款是一款基于GO语言、参考my2fback工具所二次开发的MySQL回滚工具。其原理和binlog2sql一样都是通过解析binlog生成原始SQL与回滚SQL以数据数据的回滚或者主从切换后的数据补偿等功能，其核心特点如下：
1.解析效率上my2sql速度更快，可以设置多个线程进行解析
2.支持对DML操作进程统计，当服务器出现IO高，TPS高的情况时，可以通过此工具统计出更新批复的库表
3.支持查找某个时间段数据库是否有大事务或者长事务
下载地址及注意事项
下载地址：
wget https://raw.githubusercontent.com/liuhr/my2sql/master/releases/centOS_release_7.x/my2sql
下载到本地后给此文件执行权限即可使用：
chmod +x ./my2sq
主要事项：
1.my2sql需要伪装成从库获取主库 binlog进行解析，因此执行操作的数据库用户需要具有 SELECT，REPLICATION SALVE，REPLICATION CLIENT 的权限
2.使用回滚/闪回功能时，binlog 格式必须为 row ，且 binlog_row_image=full ，DML 统计以及大事务分析不受影响
3. 使用rollback功能时，要解析的binlog段表结构要保持一致
4.MySQL8.0版本需要在配置文件中加入default_authentication_plugin =mysql_native_password，用户密码认证必须是mysql_native_password才能解析
my2sql常用参数介绍：
--help 查看帮助
-mode：默认为repl，代表伪装成从库获取主库的binlog文件，如果为file则是从本地文件系统获取 binlog 文件
-local-binlog-file：当-mode=file时所指定的binlog文件路径
-sql：要解析的SQL类型，可选参数有insert 、update、delete，默认全部解析
-file-per-table：为每个表生成一个 sql 文件
-output-dir： 将生成的结果存放到指定目录
-threads：线程数，默认2个，支持并发
-work-type：如果为2sql则是生成原始sql，如果为rollback则生成回滚SQL，如果为stats则只统计DML信息
-databases 、 -tables：指定库表
my2sql使用简介
1.根据时间点解析binlog生成执行SQL或回滚SQL
伪装成从库方式
./my2sql -user root -password xxxx -host 127.0.0.1 -port 3306 -mode repl -work-type 2sql -start-file mysql-bin.000087 -start-datetime "2023-03-16 16:20:00" -stop-datetime "2023-03-16 18:00:00" -output-dir /root/test
./my2sql -user root -password xxxx -host 127.0.0.1 -port 3306 -mode repl -work-type rollback -start-file mysql-bin.000087 -start-pos 4 -stop-file mysql-bin.000087 -stop-pos 694 -output-dir /root/test
直接读取binlog方式
./my2sql -user root -password xxxx -host 127.0.0.1 -port 3306 -mode file -local-binlog-file /data/mysql/binlog_3306/mysql-bin.000087 -work-type 2sql -start-file mysql-bin.000087 -start-datetime "2023-03-16 16:20:00" -stop-datetime "2023-03-16 18:00:00" -output-dir /root/test
./my2sql -user root -password xxxx -host 127.0.0.1 -port 3306 -mode file -local-binlog-file /data/mysql/binlog_3306/mysql-bin.000087 -work-type rollback -start-file mysql-bin.000087 -start-datetime "2023-03-16 16:20:00" -stop-datetime "2023-03-16 18:00:00" -output-dir /root/test
备注：mode类型是repl时如果只指定-start-file则会解析指定的binlog和其之后的binlog，如果只想解析87号binlog则需要加-stop-file mysql-bin.000088(stop file必须大于start file，stop file写成88不会分析此binlog)
可以不加start datatime，stop datatime参数来解析这个binlog2.根据binlog pos点解析生长执行SQL或回滚SQL
伪装成从库方式
./my2sql -user root -password 123456 -host 127.0.0.1 -port 3306 -mode repl -work-type 2sql -start-file mysql-bin.000087 -start-pos 4 -stop-file mysql-bin.000087 -stop-pos 67386 -output-dir /root/test
./my2sql -user root -password 123456 -host 127.0.0.1 -port 3306 -mode repl -work-type rollback -start-file mysql-bin.000087 -start-pos 4 -stop-file mysql-bin.000087 -stop-pos 67386 -output-dir /root/test
直接读取binlog方式
./my2sql -user root -password 123456 -host 127.0.0.1 -port 3306 -mode file -local-binlog-file /data/mysql/binlog_3306/mysql-bin.000087 -work-type 2sql -start-file mysql-bin.000087 -start-pos 4 -stop-file mysql-bin.000087 -stop-file mysql-bin.000087 -stop-pos 67386 -output-dir /root/test
./my2sql -user root -password 123456 -host 127.0.0.1 -port 3306 -mode file -local-binlog-file /data/mysql/binlog_3306/mysql-bin.000087 -work-type rollback -start-file mysql-bin.000087 -start-pos 4 -stop-file mysql-bin.000087 -stop-file mysql-bin.000087 -stop-pos 67386 -output-dir /root/test3.统计大事务：
./my2sql -user root -password 123456 -host 127.0.0.1 -port 3306 -mode file -local-binlog-file /data/mysql/binlog_3306/mysql-bin.000087 -work-type stats -start-file mysql-bin.000087 -big-trx-row-limit 500 -long-trx-seconds 300 -output-dir /root/test
上述是统计整个binlog信息，也可以统计一段时间内的大事务情况，增加-start-datetime -stop-datetime参数即可实验操作：
1）伪装从库方式解析sql（测试表名为allstar）：
解析sql：
./my2sql -user root -password 123456 -host 127.0.0.1 -port 3306 -mode repl -work-type 2sql -start-file mysql-bin.000089 -start-datetime "2023-03-29 14:20:00" -stop-datetime "2023-03-29 14:30:00" -output-dir /root/test
输出的结果在指定的/root/test目录下（只需要指定目录即可，my2sql会自动创建相关的文件）：
2）伪装从库方式生成flashback语句：
将韦德和科比的总冠军数改成6个后发现该错了，需要找到回滚SQL
./my2sql -user root -password 123456 -host 127.0.0.1 -port 3306 -mode repl -work-type rollback -start-file mysql-bin.000089 -start-datetime "2023-03-29 14:30:00" -stop-datetime "2023-03-29 15:30:00" -output-dir /root/test
