MySQL数据闪回工具my2sql的使用

场景：
当你或者其它人员误操作数据库不小心删除或者更新了一批数据，但是是当时又没事先备份时，你可以 用这个 my2sql工具快速帮你找回数据。就是如此的丝滑。但是要注意的是只限于dml语句，所以我们在操作数据库前必需先备份哦，备份！备份！备份！ 重要的事说三遍。

工具名称：my2sql #my2sql是用go语言写的。
#N年前我用过Binlog2SQL,它需要安装依赖包，有点麻烦，binlog2sql是用python写的。

工具github地址:
https://github.com/liuhr/my2sql

一， 解析出原始执行的语句

/data/my2sql-master/releases/centOS_release_7.x/my2sql -user xxxxx -password xxxx -host xx.xx.xx.xx -mode repl -work-type 2sql -start-file mysql-bin.000974 -start-datetime “2021-12-08 13:25:21” -stop-datetime “2021-12-08 14:54:53” -output-dir ./tmpdir

二， 生成回滚sql

1)　delete测试
root@xx.xx.xx.xx:3306 17:38: [test]>delete from api_account where id = 1001;
Query OK, 1 row affected (0.01 sec)

root@xx.xx.xx.xx:3306 17:38: [test]>select now();
±--------------------+
| now() |
±--------------------+
| 2021-12-09 17:39:08 |
±--------------------+
1 row in set (0.00 sec)

root@xx.xx.xx.xx:3306 17:39: [test]>select * from api_account where id = 1001;
Empty set (0.00 sec)

/data/my2sql-master/releases/centOS_release_7.x/my2sql -user yl_xxxx -password xxxxxx -host x -mode repl -work-type rollback -start-file mysql-bin.000230 -start-datetime “2021-12-09 17:38:00” -stop-datetime “2021-12-09 17:42:00” -output-dir ./tmpdir

cat tmpdir/rollback.230.sql
INSERT INTO test.api_account (id,lmdm_customer_id,lmdm_customer_code,private_key,full_name,short_name,mobile,forbidden,create_time,remark) VALUES (1001,1,‘J0086000417’,‘c7895f3579804a93aff8e5c977004da9’,‘多多打单’,‘D15’,‘13537867075’,1,‘2020-02-14 13:24:30’,null);

2. update测试

root@xx.xx.xx.xx:3306 17:48: [test]>select * from api_account where id = 1001;
±-----±-----------------±-------------------±---------------------------------±-------------±-----------±------------±----------±--------------------±-------+
| id | lmdm_customer_id | lmdm_customer_code | private_key | full_name | short_name | mobile | forbidden | create_time | remark |
±-----±-----------------±-------------------±---------------------------------±-------------±-----------±------------±----------±--------------------±-------+
| 1001 | 1 | J0086000417 | c7895f3579804a93aff8e5c977004da9 | xx打单 | D15 | 13537867075 | 1 | 2020-02-14 13:24:30 | NULL |
±-----±-----------------±-------------------±---------------------------------±-------------±-----------±------------±----------±--------------------±-------+
1 row in set (0.00 sec)

root@xx.xx.xx.xx:3306 17:48: [test]>update api_account set lmdm_customer_code=‘J0086000419’ where id = 1001;select now();
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

±--------------------+
| now() |
±--------------------+
| 2021-12-09 17:50:00 |
±--------------------+
1 row in set (0.00 sec)

root@xx.xx.xx.xx:3306 17:50: [test]>select * from api_account where id = 1001;
±-----±-----------------±-------------------±---------------------------------±-------------±-----------±------------±----------±--------------------±-------+
| id | lmdm_customer_id | lmdm_customer_code | private_key | full_name | short_name | mobile | forbidden | create_time | remark |
±-----±-----------------±-------------------±---------------------------------±-------------±-----------±------------±----------±--------------------±-------+
| 1001 | 1 | J0086000419 | c7895f3579804a93aff8e5c977004da9 | 多多打单 | D15 | 13537867075 | 1 | 2020-02-14 13:24:30 | NULL |
±-----±-----------------±-------------------±---------------------------------±-------------±-----------±------------±----------±--------------------±-------+
1 row in set (0.00 sec)

[root@sharding-9-150 1]# /data/my2sql-master/releases/centOS_release_7.x/my2sql -user xxxxx -password xxxxxx -host xx.xx.xx.xx -mode repl -work-type rollback -start-file mysql-bin.000230 -start-datetime “2021-12-09 17:49:00” -stop-datetime “2021-12-09 17:51:00” -output-dir ./tmpdir

[2021/12/09 17:51:12] [info] events.go:210 start thread to write redo/rollback sql into file
[2021/12/09 17:51:12] [info] binlogsyncer.go:360 begin to sync binlog from position (mysql-bin.000230, 4)
[2021/12/09 17:51:12] [info] events.go:60 start thread 1 to generate redo/rollback sql
[2021/12/09 17:51:12] [info] events.go:60 start thread 2 to generate redo/rollback sql
[2021/12/09 17:51:12] [info] stats_process.go:166 start thread to analyze statistics from binlog
[2021/12/09 17:51:12] [info] repl.go:16 start to get binlog from mysql
[2021/12/09 17:51:12] [info] binlogsyncer.go:777 rotate to (mysql-bin.000230, 4)
[2021/12/09 17:51:16] [info] events.go:244 finish processing mysql-bin.000230 183072284
[2021/12/09 17:51:16] [info] com.go:71 stop to get event. StopDateTime set. current event Timestamp 1639043462 Stop DateTime Timestamp 1639043460
[2021/12/09 17:51:16] [info] repl.go:18 finish getting binlog from mysql
[2021/12/09 17:51:16] [info] stats_process.go:266 exit thread to analyze statistics from binlog
[2021/12/09 17:51:16] [info] events.go:185 exit thread 2 to generate redo/rollback sql
[2021/12/09 17:51:16] [info] events.go:185 exit thread 1 to generate redo/rollback sql
[2021/12/09 17:51:16] [info] events.go:259 finish writing rollback sql into tmp files, start to revert content order of tmp files
[2021/12/09 17:51:16] [info] rollback_process.go:15 start thread 1 to revert rollback sql files
[2021/12/09 17:51:16] [info] rollback_process.go:41 start to revert tmp file tmpdir/.rollback.230.sql into tmpdir/rollback.230.sql
[2021/12/09 17:51:16] [info] rollback_process.go:156 finish reverting tmp file tmpdir/.rollback.230.sql into tmpdir/rollback.230.sql
[2021/12/09 17:51:16] [info] rollback_process.go:25 exit thread 1 to revert rollback sql files
[2021/12/09 17:51:16] [info] events.go:272 finish reverting content order of tmp files
[2021/12/09 17:51:16] [info] events.go:277 exit thread to write redo/rollback sql into file

[root@sharding-9-150 1]# ls tmpdir/
biglong_trx.txt binlog_status.txt rollback.230.sql
[root@sharding-9-150 1]# cat tmpdir/rollback.230.sql
UPDATE test.api_account SET lmdm_customer_code=‘J0086000417’ WHERE id=1001;