MySQL使用binlog2sql闪回误删除数据-创新互联
查询数据库相关配置参数
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)
安装binlog2sql
正克隆到 'binlog2sql'... remote: Counting objects: 298, done. remote: Compressing objects: 100% (4/4), done. remote: Total 298 (delta 0), reused 1 (delta 0), pack-reused 294 接收对象中: 100% (298/298), 147.01 KiB | 49.00 KiB/s, done. 处理 delta 中: 100% (151/151), done. [root@mysql-server binlog2sql]# source ../venv4archer/bin/activate (venv4archer) [root@mysql-server binlog2sql]# pip install -r requirements.txt Requirement 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 /tupian/20230522/mysql-replication-0.13.tar.gz Building 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/32b16ccadd4fc566ff38af96afdeb5d57d49c2f1eff0402164 Successfully built mysql-replication Installing collected packages: wheel, mysql-replication Found existing installation: wheel 0.31.1 Uninstalling wheel-0.31.1: Successfully uninstalled wheel-0.31.1 Successfully installed mysql-replication-0.13 wheel-0.29.0
创建测试数据，并执行误删除
root [(none)]> flush logs;
Query OK, 0 rows affected (0.00 sec)
root [(none)]> use test
Database changed
root [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.01 sec)
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: 0
root [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 | 201 | | mysql-bin.000004 | 1218 | +------------------+-----------+ 3 rows in set (0.00 sec)
解析出标准SQL
(venv4archer) [root@mysql-server binlog2sql]# python binlog2sql/binlog2sql.py -uroot -p'root' -h 127.0.0.1 -P 23306 -dtest -tuser --start-file='mysql-bin.000004' --start-datetime='2018-09-14 17:00:00' --stop-datetime='2018-09-14 18:25:00' > /tmp/20180914_raw.sql (venv4archer) [root@mysql-server binlog2sql]# cat /tmp/20180914_raw.sql USE b'test'; 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 00:00:00'); #start 411 end 824 time 2018-09-14 18:22:33 INSERT INTO `test`.`user`(`id`, `name`, `add_time`) VALUES (2, 'trinity', '2018-09-02 00:00:00'); #start 411 end 824 time 2018-09-14 18:22:33 INSERT INTO `test`.`user`(`id`, `name`, `add_time`) VALUES (3, 'jason', '2018-09-05 00:00:00'); #start 411 end 824 time 2018-09-14 18:22:33 DELETE FROM `test`.`user` WHERE `id`=1 AND `name`='neo' AND `add_time`='2018-09-01 00:00:00' LIMIT 1; #start 855 end 1187 time 2018-09-14 18:23:31 DELETE FROM `test`.`user` WHERE `id`=2 AND `name`='trinity' AND `add_time`='2018-09-02 00:00:00' LIMIT 1; #start 855 end 1187 time 2018-09-14 18:23:31
解析出回滚SQL
(venv4archer) [root@mysql-server binlog2sql]# python binlog2sql/binlog2sql.py -uroot -p'root' -h 127.0.0.1 -P 23306 -dtest -tuser --start-file='mysql-bin.000004' --start-datetime='2018-09-14 17:00:00' --stop-datetime='2018-09-14 18:25:00' -B > /tmp/20180914_rollback.sql (venv4archer) [root@mysql-server binlog2sql]# cat //tmp/20180914_rollback.sql INSERT INTO `test`.`user`(`id`, `name`, `add_time`) VALUES (2, 'trinity', '2018-09-02 00:00:00'); #start 855 end 1187 time 2018-09-14 18:23:31 INSERT INTO `test`.`user`(`id`, `name`, `add_time`) VALUES (1, 'neo', '2018-09-01 00:00:00'); #start 855 end 1187 time 2018-09-14 18:23:31 DELETE FROM `test`.`user` WHERE `id`=3 AND `name`='jason' AND `add_time`='2018-09-05 00:00:00' LIMIT 1; #start 411 end 824 time 2018-09-14 18:22:33 DELETE FROM `test`.`user` WHERE `id`=2 AND `name`='trinity' AND `add_time`='2018-09-02 00:00:00' LIMIT 1; #start 411 end 824 time 2018-09-14 18:22:33 DELETE FROM `test`.`user` WHERE `id`=1 AND `name`='neo' AND `add_time`='2018-09-01 00:00:00' LIMIT 1; #start 411 end 824 time 2018-09-14 18:22:33
网站题目：MySQL使用binlog2sql闪回误删除数据-创新互联
链接地址：http://pwwzsj.com/article/ceceeo.html
