mysql数据库 - 15701266382/- GitHub Wiki
误删数据 -- 数据回滚
一 、需要安装工具
-
pip ： 是Python有它自己的包管理工具，与yum和apt-get相似
下载get-pip.py：https://bootstrap.pypa.io/get-pip.py 以root用户运行命令：python get-pip.py,即可完成安装。
2.binlog2sql： 安装开源工具binlog2sql。binlog2sql是一款简单易用的binlog解析工具，其中一个功能就是生成回滚SQL。
git clone https://github.com/danfengcao/binlog2sql.git
pip install -r requirements.txt
二 、恢复步骤
1.登录mysql，查看目前的binlog文件
mysql> show master status;
+------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000051 | 967 | | mysql-bin.000052 | 965 | +------------------+-----------+
mysql>show variables like 'log_%'; 这条命令是查看数据库的日志
2.最新的binlog文件是mysql-bin.000052，我们再定位误操作SQL的binlog位置。误操作人只能知道大致的误操作时间，我们根据大致时间过滤数据。
python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -u账号 -p密码 -d数据库名 -t表明 --start-file='日志名' --start-datetime='开始时间' --stop-datetime='结束时间'
例如：python binlog2sql/binlog2sql.py -hlocalhost -P3306 -uroot -p123456 -dcjj -t ttb_task --start-file='mysql-bin.000022' --start-datetime='2016-12-13 20:25:00' --stop-datetime='2016-12-13 20:30:00'
输出：
INSERT INTO test.tbl(addtime, id, name) VALUES ('2016-12-13 20:25:00', 3, '小孙'); #start 4 end 290 time 2016-12-13 20:25:46
INSERT INTO test.tbl(addtime, id, name) VALUES ('2016-12-13 20:26:00', 4, '小李'); #start 317 end 487 time 2016-12-13 20:26:26
UPDATE test.tbl SET addtime='2016-12-12 00:00:00', id=4, name='小李' WHERE addtime='2016-12-13 20:26:00' AND id=4 AND name='小李' LIMIT 1; #start 514 end 701 time 2016-12-13 20:27:07
DELETE FROM test.tbl WHERE addtime='2016-12-10 00:04:33' AND id=1 AND name='小赵' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05
DELETE FROM test.tbl WHERE addtime='2016-12-10 00:04:48' AND id=2 AND name='小钱' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05
DELETE FROM test.tbl WHERE addtime='2016-12-13 20:25:00' AND id=3 AND name='小孙' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05
DELETE FROM test.tbl WHERE addtime='2016-12-12 00:00:00' AND id=4 AND name='小李' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05
3.我们得到了误操作sql的准确位置在728-938之间，再根据位置进一步过滤，使用flashback模式生成回滚sql，检查回滚sql是否正确
python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttbl --start-file='mysql-bin.000052' --start-pos=3346 --end-pos=3556 -B
输出：
INSERT INTO test.tbl(addtime, id, name) VALUES ('2016-12-12 00:00:00', 4, '小李'); #start 728 end 938 time 2016-12-13 20:28:05
INSERT INTO test.tbl(addtime, id, name) VALUES ('2016-12-13 20:25:00', 3, '小孙'); #start 728 end 938 time 2016-12-13 20:28:05
INSERT INTO test.tbl(addtime, id, name) VALUES ('2016-12-10 00:04:48', 2, '小钱'); #start 728 end 938 time 2016-12-13 20:28:05
INSERT INTO test.tbl(addtime, id, name) VALUES ('2016-12-10 00:04:33', 1, '小赵'); #start 728 end 938 time 2016-12-13 20:28:05
4.确认回滚sql正确，执行回滚语句。登录mysql确认，数据回滚成功。 shell> python binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttbl --start-file='mysql-bin.000052' --start-pos=3346 --end-pos=3556 -B | mysql -h127.0.0.1 -P3306 -uadmin -p'admin'
mysql> select * from tbl; +----+--------+---------------------+ | id | name | addtime | +----+--------+---------------------+ | 1 | 小赵 | 2016-12-10 00:04:33 | | 2 | 小钱 | 2016-12-10 00:04:48 | | 3 | 小孙 | 2016-12-13 20:25:00 | | 4 | 小李 | 2016-12-12 00:00:00 |