【MySQL】數據庫閃回工具--binlog2sql

[root@wallet01?~]#?cat?/etc/redhat-release?
CentOS?Linux?release?7.5.1804?(Core)?

[root@wallet01?~]#?python?-V
Python?2.7.5

[root@wallet01?~]#?yum?install?-y?python-pip

[root@wallet01?~]#?pip?-V
pip?8.1.2?from?/usr/lib/python2.7/site-packages?(python?2.7)

[root@wallet01?~]#?git?clone?https://github.com/danfengcao/binlog2sql.git
Initialized?empty?Git?repository?in?/root/binlog2sql/.git/
remote:?Enumerating?objects:?323,?done.
remote:?Total?323?(delta?0),?reused?0?(delta?0),?pack-reused?323
Receiving?objects:?100%?(323/323),?151.51?KiB?|?245?KiB/s,?done.
Resolving?deltas:?100%?(170/170),?done.

[root@wallet01?~]#?cd?binlog2sql
[root@wallet01?binlog2sql]#?pip?install?-r?requirements.txt
[root@wallet01?binlog2sql]#?cd?binlog2sql/
[root@wallet01?binlog2sql]#?python?binlog2sql.py?--help
usage:?binlog2sql.py?[-h?HOST]?[-u?USER]?[-p?[PASSWORD?[PASSWORD?...]]]
?????????????????????[-P?PORT]?[--start-file?START_FILE]
?????????????????????[--start-position?START_POS]?[--stop-file?END_FILE]
?????????????????????[--stop-position?END_POS]?[--start-datetime?START_TIME]
?????????????????????[--stop-datetime?STOP_TIME]?[--stop-never]?[--help]
?????????????????????[-d?[DATABASES?[DATABASES?...]]]
?????????????????????[-t?[TABLES?[TABLES?...]]]?[--only-dml]
?????????????????????[--sql-type?[SQL_TYPE?[SQL_TYPE?...]]]?[-K]?[-B]
?????????????????????[--back-interval?BACK_INTERVAL]
?????????????????????
--start-file?--起始解析文件
--stop-file??--終止解析文件
--start-position?--起始解析位置
--stop-position??--終止解析位置
--start-datetime?--起始解析時間，格式'%Y-%m-%d?%H:%M:%S'。
--stop-datetime?--終止解析時間，格式'%Y-%m-%d?%H:%M:%S'。
-d?--僅解析目標db的sql
-t?--僅解析目標table的sql
--only-dml?--僅解析dml，忽略ddl。
--sql-type?--僅解析指定類型，支持insert，update，delete。
-B?--生成回滾SQL

[root@wallet01?~]#?mysql?-uroot?-p
Enter?password:?
mysql>?grant?select,replication?client,replication?slave?on?*.*?to?'fb'@'%'?identified?by?'fb@2019';????
Query?OK,?0?rows?affected?(0.05?sec)

mysql>?flush?privileges;
Query?OK,?0?rows?affected?(0.11?sec)

mysql>?show?master?status;
+------------------+----------+--------------+------------------+-------------------+
|?File?????????????|?Position?|?Binlog_Do_DB?|?Binlog_Ignore_DB?|?Executed_Gtid_Set?|
+------------------+----------+--------------+------------------+-------------------+
|?mysql-bin.000008?|??????120?|??????????????|??????????????????|???????????????????|
+------------------+----------+--------------+------------------+-------------------+
1?row?in?set?(0.00?sec)

mysql>?select?now();
+---------------------+
|?now()???????????????|
+---------------------+
|?2019-10-12?17:18:22?|
+---------------------+
1?row?in?set?(0.06?sec)

mysql>?use?gsoa

mysql>?select?count(*)?from?bd_city;
+----------+
|?count(*)?|
+----------+
|??????372?|
+----------+
1?row?in?set?(0.00?sec)

mysql>?begin;
Query?OK,?0?rows?affected?(0.00?sec)

mysql>?delete?from?bd_city;
Query?OK,?372?rows?affected?(0.09?sec)

mysql>?commit;
Query?OK,?0?rows?affected?(0.04?sec)

mysql>?select?count(*)?from?bd_city;
+----------+
|?count(*)?|
+----------+
|????????0?|
+----------+
1?row?in?set?(0.01?sec)

mysql>?select?now();
+---------------------+
|?now()???????????????|
+---------------------+
|?2019-10-12?17:22:26?|
+---------------------+
1?row?in?set?(0.00?sec)

恢復已提交事務中刪除的記錄
[root@wallet01?binlog2sql]#?python?binlog2sql.py?-hlocalhost?-P3306?-ufb?-p'fb@2019'?-dgsoa?-tbd_city?\
--start-file='mysql-bin.000008'?--only-dml?--sql-type=delete?\
--start-datetime='2019-10-12?17:18:22'?--stop-datetime='2019-10-12?17:22:26'?>fb.log

[root@wallet01?binlog2sql]#?more?fb.log?
DELETE?FROM?`gsoa`.`bd_city`?WHERE?`status`='1'?AND?`parent_name`='中國'?AND?`code`='110000'?AND?`name`='北京市'?AND?`parent_code`='CN'?LIMIT?1;?
#start?423?end?8756?time?2019-10-12?17:21:26

[root@wallet01?binlog2sql]#?tail?-n?1?fb.log?
DELETE?FROM?`gsoa`.`bd_city`?WHERE?`status`='1'?AND?`parent_name`=''?AND?`code`='CN'?AND?`name`='中國'?AND?`parent_code`=''?LIMIT?1;?
#start?423?end?19319?time?2019-10-12?17:21:26

[root@wallet01?binlog2sql]#?python?binlog2sql.py?-hlocalhost?-P3306?-ufb?-p'fb@2019'?-dgsoa?-tbd_city?\
--start-file='mysql-bin.000008'?--start-position=423?--stop-position=19319?-B?>?rollback.sql

[root@wallet01?binlog2sql]#?more?rollback.sql?|?grep?'北京'
INSERT?INTO?`gsoa`.`bd_city`(`status`,?`parent_name`,?`code`,?`name`,?`parent_code`)?VALUES?('1',?'中國',?'110000',?'北京市',?'CN');?
#start?423?end?8756?time?2019-10-12?17:21:26

[root@wallet01?binlog2sql]#?mysql?-uroot?-p?<?rollback.sql?
Enter?password:?

[root@wallet01?~]#?mysql?-uroot?-p
Enter?password:?
mysql>?use?gsoa

mysql>?select?count(*)?from?bd_city;
+----------+
|?count(*)?|
+----------+
|??????372?|
+----------+
1?row?in?set?(0.00?sec)