MySQL误操作后如何快速还原数据

发布时间:2019-05-05  栏目:SQL  评论:0 Comments

基本上每一种跟数据库打交道的程序猿(当然也大概是您同事)都会碰贰个难点,MySQL误操作后如何高效回滚?举个例子,delete一张表,忘加限制条件,整张表没了。如果那依旧线上情况基本业务数据,那那事就闹大了。误操作后,能便捷回滚数据是尤其首要的。

摘要: 利用binlog闪回误操作数据。

binlog二sql神速回滚

首先,确认你的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。binlog二sql是1款轻易易用的binlog解析工具,其中3个职能就是调换回滚SQL。

git clone https://github.com/danfengcao/binlog2sql.git
pip install -r requirements.txt

 

然后,大家就可以调换回滚SQL了。

背景:误删了test库tbl整张表的数量,供给火急回滚。

test库tbl表原有数据
mysql> select * from tbl;
+----+--------+---------------------+
| id | name   | addtime             |
+----+--------+---------------------+
|  1 | 小赵   | 2016-12-10 00:04:33 |
|  2 | 小钱   | 2016-12-10 00:04:48 |
|  3 | 小孙   | 2016-12-10 00:04:51 |
|  4 | 小李   | 2016-12-10 00:04:56 |
+----+--------+---------------------+
4 rows in set (0.00 sec)

mysql> delete from tbl;
Query OK, 4 rows affected (0.00 sec)

tbl表被清空
mysql> select * from tbl;
Empty set (0.00 sec)

苏醒手续

  1. 签到mysql,查看近期的binlog文件

    mysql> show master logs;
    +------------------+-----------+
    | Log_name         | File_size |
    +------------------+-----------+
    | mysql-bin.000046 |  12262268 |
    | mysql-bin.000047 |      3583 |
    +------------------+-----------+ 
    
  2. 新式的binlog文件是mysql-bin.0000四七,我们再定位误操作SQL的binlog地方

    $ python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttbl --start-file='mysql-bin.000047'
    
    输出:
        DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-10 00:04:33' AND `id`=1 AND `name`='小赵' LIMIT 1; #start 3346 end 3556
        DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-10 00:04:48' AND `id`=2 AND `name`='小钱' LIMIT 1; #start 3346 end 3556
        DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-10 00:04:51' AND `id`=3 AND `name`='小孙' LIMIT 1; #start 3346 end 3556
        DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-10 00:04:56' AND `id`=4 AND `name`='小李' LIMIT 1; #start 3346 end 3556 
    
  3. 变化回滚sql,并检讨回滚sql是不是准确

    $ python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttbl --start-file='mysql-bin.000047' --start-pos=3346 --end-pos=3556 -B
    
    输出:
    INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-10 00:04:56', 4, '小李'); #start 3346 end 3556
    INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-10 00:04:51', 3, '小孙'); #start 3346 end 3556
    INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-10 00:04:48', 2, '小钱'); #start 3346 end 3556
    INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-10 00:04:33', 1, '小赵'); #start 3346 end 3556
    
  4. 肯定回滚sql正确,奉行回滚语句。登入mysql确认,数据回滚成功。

    $ python binlog2sql.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 * from tbl;
    +----+--------+---------------------+
    | id | name   | addtime             |
    +----+--------+---------------------+
    |  1 | 小赵   | 2016-12-10 00:04:33 |
    |  2 | 小钱   | 2016-12-10 00:04:48 |
    |  3 | 小孙   | 2016-12-10 00:04:51 |
    |  4 | 小李   | 2016-12-10 00:04:56 |
    +----+--------+---------------------+
    

至此,不用再顾忌被解雇了。

大概每种跟数据库打交道的程序猿(当然也说不定是你同事)都会碰贰个主题素材,MySQL误操作后怎么迅速回滚?比方,delete一张表,忘加限制标准,整张表没了。即使那照旧线上意况中央业务数据,那那事就闹大了。误操作后,能高效回滚数据是足够主要的。

大面积难点

  • 有人会问,笔者DDL误操作了怎么快速回滚?举个例子drop了一张大表。

    很难成功。因为就是在在row形式下,DDL操作也不会把每行数据的改动记录到binlog,所以DDL无法透过binlog回滚。完成DDL回滚,必须求在实施DDL前先备份老多少。确实有人经过退换mysql
    server源码完成了DDL的非常的慢回滚,我找到Ali的xiaobin
    lin提交了一个patch。但据笔者所知,国内很少有互连网集团使用了那些特点。原因来讲,笔者认为最根本照旧懒的去折腾,没要求搞那些低频功用,次要原因是会大增一些附加存款和储蓄。

    据此,DDL误操作的话一般只可以通过备份来平复。如果厂商连备份也无法用了,那的确建议去买张益德机票了。干啥?跑呗

  • mysql除了binlog2sql,是不是还有其他回滚工具?

    当然有。Ali彭立勋对mysqlbinlog增添了flashback的风味,这应当是mysql最早有的flashback作用,彭解决的是DML的回滚,并证实了选择binlog实行DML闪回的布置性思路。DDL回滚本性也是由Ali团队提议并完结的。那多少个功效是有立异精神的,此后出现的闪回工具基本都以对地点两者的依样葫芦。其余,去何方开源的英斯ption是一套MySQL自动化运行工具,这一个就相比较重了,援助DML回滚,还不是从binlog回滚的,是从备份回滚的,也扶助DDL回滚表结构,数据是回滚不了滴~

如有mysql回滚相关的地道工具优秀小说遗漏,更烦请告知~

本人的信箱 danfengcao.info@gmail.com

观念解法

参考资料

[1]
彭立勋, MySQL下促成闪回的宏图思路

[2] Lixun Peng, Provide the flashback feature by
binlog

[3]
丁奇, MySQL闪回方案讨论及完毕

[4] xiaobin lin, flashback from binlog for
MySQL

[5]
王竹峰, 去哪儿inception

[6] danfengcao, binlog2sql: Parse MySQL binlog to SQL you
want

用全量备份重搭实例,再利用增量binlog备份,恢复生机到误操作在此以前的景色。然后跳过误操作的SQL,再持续应用binlog。此法费时困难,不值得再引入。

应用binlog2sql异常快闪回

首先,确认你的MySQL server开启了binlog,设置了以下参数:

固然没有开启binlog,也不曾预先生成回滚SQL,那实在爱莫能助神速回滚了。对存放首要事务数据的MySQL,强烈建议开启binlog。

随之,安装开源工具binlog2sql。binlog2sql是一款轻便易用的binlog解析工具,个中三个意义便是生成回滚SQL。

接下来,大家就可以转移回滚SQL了。

背景:小明在20点多时误删了test库tbl表整张表的数量,要求紧迫回滚。

复苏数据步骤:

签到mysql,查看近期的binlog文件

二.
风靡的binlog文件是mysql-bin.00005二,大家再定位误操作SQL的binlog地方。误操作人只可以知道大概的误操作时间,大家依据差不多时间过滤数据。

3.
我们赢得了误操作sql的规范地点在72八-93八里头,再依据岗位进一步过滤,使用flashback方式转换回滚sql,检查回滚sql是不是准确

  1. 分明回滚sql准确,实行回滚语句。登六mysql确认,数据回滚成功。

迄今,不用再顾忌被解雇了。

常见难点

  1. 有人会问,小编DDL误操作了怎么快速回滚?举个例子drop了一张大表。

很难做到。因为即便在在row情势下,DDL操作也不会把每行数据的变化记录到binlog,所以DDL不能透过binlog回滚。完成DDL回滚,供给求在执行DDL前先备份老多少。确实有人由此改变mysql
server源码达成了DDL的敏捷回滚,我找到Ali的xiaobin
lin提交了1个patch。但据小编所知,国内很少有互连网公司选拔了这几个特点。原因来讲,小编觉着最珍视依旧懒的去折腾,没供给搞那么些低频功用,次要原因是会扩充一些万分存款和储蓄。

就此,DDL误操作的话一般只好通过备份来回复。假使公司连备份也无法用了,这的确提出去买张翼德机票了。干啥?跑呗

  1. mysql除了binlog二sql,是或不是还有别的回滚工具?

理之当然有。Ali彭立勋对mysqlbinlog扩大了flashback的性状,那应该是mysql最早有的flashback功用,彭化解的是DML的回滚,并表达了应用binlog进行DML闪回的规划思路。DDL回滚脾气也是由Ali团队建议并促成的。那八个效益是有更新精神的,此后出现的闪回工具基本都是对地方两者的效仿。其它,去哪里开源的英斯ption是一套MySQL自动化运转工具,那一个就相比重了,支持DML回滚,还不是从binlog回滚的,是从备份回滚的,也扶助DDL回滚表结构,数据是回滚不了滴~
还有一种做法叫slave延时备份,搞台不加业务流量的slave,故意推迟壹段时间,那实在是在守旧办法的功底上去除了实例恢复生机那步。此法会额外消耗壹台机器,大家不引入这么做。

图片 1图片 2

留下评论

网站地图xml地图