場景：
當你或者其它人員誤操作數據庫不小心刪除或者更新了一批數據，但是是當時又沒事先備份時，你可以 用這個 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);
- 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;
)
