MySQL mistakenly deletes data using binlog2sql flashback

Query database-related configuration parameters

Root [test] > show global variables like 'binlog%format%';+-+-+ | Variable_name | Value | +-+ | binlog_format | ROW | +-+-+ 1 row in set (0.00 sec) root [test] > show global variables like' binlog%row%image%' +-+-+ | Variable_name | Value | +-+-+ | binlog_row_image | FULL | +-+-+ 1 row in set (0.00 sec) root [test] > show global variables like'% log%bin%' +-- +-- + | Variable_name | Value | +- -+ | log_bin | ON | | log_bin_basename | / data1/mysql_log_23306/binlog/mysql-bin | | log_bin_index | / data1/mysql_log_23306/binlog/mysql-bin.index | | log_bin_trust_function_creators | ON | | log_bin_use_v1_row_events | OFF | | | log_statements_unsafe_for_binlog | ON | + -+ 6 rows in set (0.01 sec)

Install binlog2sql

Being cloned into 'binlog2sql'...remote: Counting objects: 298, done.remote: Compressing objects: 100% (4 KiB/s 4), done.remote: Total 298 (delta 0), reused 1 (delta 0), pack-reused 294 receiver: 100% (298 KiB), 147.01 KiB | 49.00 KiB/s, done. Done. [root@mysql-server binlog2sql] # source. / venv4archer/bin/activate (venv4archer) [root@mysql-server binlog2sql] # pip install-r requirements.txtRequirement already satisfied: PyMySQL==0.7.11 in/ data1/venv4archer/lib/python3.6/site-packages (from-r requirements.txt (line 1)) (0.7.11) Collecting wheel==0.29.0 (from-r requirements.txt (line 2)) Cache entry deserialization failed Entry ignored Downloading https://files.pythonhosted.org/packages/8a/e9/8468cd68b582b06ef554be0b96b59f59779627131aad48f8a5bce4b13450/wheel-0.29.0-py2.py3-none-any.whl (66kB) 100% | ██ | 71kB 103kB/s Collecting mysql-replication==0.13 (from-r requirements.txt (line 3)) Downloading https://files.pythonhosted.org/packages/dd/23/384047702e694139e9fe75a8ba7ad007e8942fd119ebadabc32ce19f70f2 / mysql-replication-0.13.tar.gzBuilding wheels for collected packages: mysql-replication Running setup.py bdist_wheel for mysql-replication... Done Stored in directory: / root/.cache/pip/wheels/91/33/05/32b16ccadd4fc566ff38af96afdeb5d57d49c2f1eff0402164Successfully built mysql-replicationInstalling collected packages: wheel, mysql-replication Found existing installation: wheel 0.31.1 Uninstalling wheel-0.31.1: Successfully uninstalled wheel-0.31.1Successfully installed mysql-replication-0.13 wheel-0.29.0

Create test data and perform erroneous deletion

Root [(none)] > flush logs;Query OK, 0 rows affected (0.00 sec) root [(none)] > use testDatabase changedroot [test] > create table user (id int (12) unsigned auto_increment comment 'id' primary key, name varchar (15), add_time timestamp) Query OK, 0 rows affected (0.01sec) root [test] > insert into user (name, add_time) values ('neo',' 2018-09-01'), ('trinity',' 2018-09-02'), ('jason',' 2018-09-05'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0root [test] > delete from user where add_time

< '2018-09-05';Query OK, 2 rows affected (0.01 sec) 查看二进制日志文件 root [test]>

Show binary logs +-+-+ | Log_name | File_size | +-+-+ | mysql-bin.000002 | 2236 | mysql-bin.000003 | | mysql-bin.000004 | 1218 | +- -+-+ 3 rows in set (0.00 sec)

Parse out the standard SQL

(venv4archer) [root@mysql-server binlog2sql] # python binlog2sql/binlog2sql.py-uroot-paired root'-h 127.0.0.1-P 23306-dtest-tuser-- start-file='mysql-bin.000004'-- start-datetime='2018-09-14 17lange 0000'-stop-datetime='2018-09-14 18lange 25lv 00' > / tmp/20180914_raw.sql (venv4archer) [root@mysql-server binlog2sql] # cat / tmp/20180914_raw.sqlUSE bachelotest' Create table user (id int (12) unsigned auto_increment comment 'id' primary key, name varchar (15), add_time timestamp); INSERT INTO `test`.user` (`id`, `name`, `add_ time`) VALUES (1,' neo', '2018-09-01 0000start time 2018-09-14 18:22:33INSERT INTO `test`.`user` (`id`, `name`, `add_ time`) VALUES (2,' trinity', '2018-09-02 0000unsigned auto_increment comment) # start 411 end 824 time 2018-09-14 18:22:33INSERT INTO `test`.user` (`id`, `name`, `add_ time`) VALUES (3, 'jason',' 2018-09-05 00time 2018-09-14 18:22:33DELETE FROM `test`.`user`WHERE `id` = 1 AND `name` = 'neo' AND `add_ time` =' 2018-09-01 00time 00' LIMIT 1 # start 855 end 1187 time 2018-09-14 18:23:31DELETE FROM `test`.`user`WHERE `id` = 2 AND `name` = 'trinity' AND `add_ time` =' 2018-09-02 00 end 0000' LIMIT 1; # start 855 end 1187 time 2018-09-14 18:23:31

Parse out rollback SQL

(venv4archer) [root@mysql-server binlog2sql] # python binlog2sql/binlog2sql.py-uroot-paired root'-h 127.0.0.1-P 23306-dtest-tuser-- start-file='mysql-bin.000004'-- start-datetime='2018-09-14 17purl 0000'-stop-datetime='2018-09-14 18Vera 25python binlog2sql/binlog2sql.py 00'-B > / tmp/20180914_rollback.sql (venv4archer) [root@mysql-server binlog2sql] # cat / / tmp / 20180914_rollback.sql INSERT INTO `test`.`user` (`id` `name`, `trinity', time`) VALUES (2, 'trinity',' 2018-09-02 00 add_ 00') # start 855 end 1187 time 2018-09-14 18:23:31INSERT INTO `test`.user` (`id`, `name`, `add_ time`) VALUES (1, 'neo',' 2018-09-01 00time 00'); # start 855 end 1187 time 2018-09-14 18:23:31DELETE FROM `test`.user`WHERE `id` = 3 AND `name` = 'jason' AND `add_ time` =' 2018-09-05 000018:23:31INSERT INTO 00' LIMIT 1 # start 411 end 824 time 2018-09-14 18:22:33DELETE FROM `test`.`user`WHERE `id` = 2 AND `name` = 'trinity' AND `add_ time` =' 2018-09-02 0000 time 00' LIMIT 1; # start 411 end 824 time 2018-09-14 18:22:33DELETE FROM `test`.`userWHERE `id` = 1 AND `name` = 'neo' AND `add_ time` =' 2018-09-01 0000Ranger 0000' LIMIT 1; # start 411 end 824 time 2018-09-14