传统解法
用全量备份重搭实例,再利用增量binlog备份,恢复到误操作之前的状态。然后跳过误操作的SQL,再继续应用binlog。此法费时费力,不值得再推荐。
利用binlog2sql快速闪回
首先,确认你的MySQL server开启了binlog,设置了以下参数:
[mysqld]
server-id=1
log_bin=/var/log/mysql/mysql-bin.log
max_binlog_size=1000M
binlog-format=row
如果没有开启binlog,也没有预先生成回滚SQL,那真的无法快速恢复数据了。对存放重要业务数据的MySQL,强烈建议开启binlog。
随后,安装开源工具binlog2sql。binlog2sql是一款简单易用的binlog解析工具,其中一个功能就是利用binlog进行闪回。
gitclonehttps://github.com/danfengcao/binlog2sql.git
pipinstall-rrequirements.txt
然后,我们就可以生成回滚SQL了。
背景:误删了test库tbl表整张表的数据,需要紧急回滚。
test库tbl表原有数据
mysql>select*fromtbl;
+----+--------+---------------------+
|id|name|addtime|
+----+--------+---------------------+
|1|小赵|2016-12-1000:04:33|
|2|小钱|2016-12-1000:04:48|
|3|小孙|2016-12-1000:04:51|
|4|小李|2016-12-1000:04:56|
+----+--------+---------------------+
4rowsinset(0.00sec)
mysql>deletefromtbl;
QueryOK,4rowsaffected(0.00sec)
tbl表被清空
mysql>select*fromtbl;
Emptyset(0.00sec)
恢复数据步骤:
登录mysql,查看目前的binlog文件
mysql>showmaster logs;
+------------------+-----------+
|Log_name|File_size|
+------------------+-----------+
|mysql-bin.000046|12262268|
|mysql-bin.000047|3583|
+------------------+-----------+
最新的binlog文件是mysql-bin.000047,我们再定位误操作SQL的binlog位置
$pythonbinlog2sql/binlog2sql.py-h127.0.0.1-P3306-uadmin-p'admin'-dtest-ttbl--start-file='mysql-bin.000047'
输出:
DELETEFROM`test`.`tbl`WHERE`addtime`='2016-12-10 00:04:33'AND`id`=1AND`name`='小赵'LIMIT1;#start 3346 end 3556
DELETEFROM`test`.`tbl`WHERE`addtime`='2016-12-10 00:04:48'AND`id`=2AND`name`='小钱'LIMIT1;#start 3346 end 3556
DELETEFROM`test`.`tbl`WHERE`addtime`='2016-12-10 00:04:51'AND`id`=3AND`name`='小孙'LIMIT1;#start 3346 end 3556
DELETEFROM`test`.`tbl`WHERE`addtime`='2016-12-10 00:04:56'AND`id`=4AND`name`='小李'LIMIT1;#start 3346 end 3556
生成回滚sql,并检查回滚sql是否正确
$pythonbinlog2sql/binlog2sql.py-h127.0.0.1-P3306-uadmin-p'admin'-dtest-ttbl--start-file='mysql-bin.000047'--start-pos=3346--end-pos=3556-B
输出:
INSERTINTO`test`.`tbl`(`addtime`,`id`,`name`)VALUES('2016-12-10 00:04:56',4,'小李');#start 3346 end 3556
INSERTINTO`test`.`tbl`(`addtime`,`id`,`name`)VALUES('2016-12-10 00:04:51',3,'小孙');#start 3346 end 3556
INSERTINTO`test`.`tbl`(`addtime`,`id`,`name`)VALUES('2016-12-10 00:04:48',2,'小钱');#start 3346 end 3556
INSERTINTO`test`.`tbl`(`addtime`,`id`,`name`)VALUES('2016-12-10 00:04:33',1,'小赵');#start 3346 end 3556
确认回滚sql正确,执行回滚语句。登录mysql确认,数据回滚成功。
$pythonbinlog2sql.py-h127.0.0.1-P3306-uadmin-p'admin'-dtest-ttbl--start-file='mysql-bin.000047'--start-pos=3346--end-pos=3556-B|mysql-h127.0.0.1-P3306-uadmin-p'admin'
mysql>select*fromtbl;
+----+--------+---------------------+
|id|name|addtime|
+----+--------+---------------------+
|1|小赵|2016-12-1000:04:33|
|2|小钱|2016-12-1000:04:48|
|3|小孙|2016-12-1000:04:51|
|4|小李|2016-12-1000:04:56|
+----+--------+---------------------+
软件测试免费视频观看链接:https://ke.qq.com/course/159919#tuin=ba4122
松勤网:www.songqinnet.com