【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)
另外有需要云服务器可以了解下创新互联scvps.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。
本文题目：【MySQL】数据库闪回工具--binlog2sql-创新互联
当前URL：http://scjbc.cn/article/djegod.html
