您当前的位置:首页 > 电脑百科 > 数据库 > MYSQL

MySQL发生全表更新后如何快速恢复数据

时间:2020-08-21 14:42:16  来源:  作者:
MySQL发生全表更新后如何快速恢复数据

数据恢复

上一篇文章MySQL基于binlog实现数据增量恢复实践 我们大概讲解了下当数据表发生全表更新后,如何使用冷备份数据和基于MySQL的binlog实现增量式恢复数据,这种增量恢复数据可能存在一些弊端,效率可能也是不是太高,主要存在如下缺点:

  • 基于备份的数据进行恢复,如果对数据备份不及时,可能达不到理想的效果;
  • 可能会需要停止线上业务进行数据恢复,因为对表有drop操作;
  • 如果全表更新发现不及时,恢复数据可能需要更长的时间;
  • 如果有多个binlog文件,需要对每一个binlog进行恢复;
  • 恢复数据效率不高;

通过查阅资料和阅读mysql官方文档,还有一种应该算比较高效和可靠的方式来恢复全表更新后的数据。这种方法同样还是基于mysql的binlog和sed命令可以提取出当时执行全表更新的sql,然后对update的sql语句进行逆向操作,将更新后的数据再更新回之前的数据。

下面我们就通过一个例子来研究下,这种方法如何到达快速恢复数据的目的。同时把恢复数据的过程记录下来,方便以后遇到类似问题可以查阅参考,快速解决问题,提高工作效率。


恢复数据

我们还是基于上一篇文章MySQL基于binlog实现数据增量恢复实践 的环境,创建一个user表:

mysql> select * from user;
+----+-----------+------+-----------+
| id | name      | age  | address   |
+----+-----------+------+-----------+
|  1 | test      |    1 | test      |
|  2 | zhangsan  |    2 | address1  |
|  3 | lisi      |    3 | addr1     |
|  4 | test1     |    4 | addr2     |
|  5 | test2     |    5 | addr3     |
|  6 | test3     |    6 | address4  |
|  7 | b1        |    7 | bb        |
|  8 | a1        |   10 | add1      |
|  9 | a2        |   11 | add2      |
| 10 | a3        |   12 | add3      |
| 11 | a4        |   13 | add4      |
| 12 | a5        |   14 | add5      |
+----+-----------+------+-----------+
12 rows in set (0.00 sec)

执行mysql命令重新生成一个binlog文件:

mysql> flush logs;
Query OK, 0 rows affected (0.08 sec)

mysql> show binlog events in 'binlog.000003';
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                              |
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
| binlog.000003 |   4 | Format_desc    |         1 |         125 | Server ver: 8.0.21, Binlog ver: 4 |
| binlog.000003 | 125 | Previous_gtids |         1 |         156 |                                   |
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
2 rows in set (0.00 sec)

新插入一条数据:

mysql> INSERT INTO `demo`.`user`(`name`,`age`,`address`) VALUES ('c1',10,'c1');
Query OK, 1 row affected (0.03 sec)

mysql> show binlog events in 'binlog.000003';
+---------------+-----+----------------+-----------+-------------+--------------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                 |
+---------------+-----+----------------+-----------+-------------+--------------------------------------+
| binlog.000003 |   4 | Format_desc    |         1 |         125 | Server ver: 8.0.21, Binlog ver: 4    |
| binlog.000003 | 125 | Previous_gtids |         1 |         156 |                                      |
| binlog.000003 | 156 | Anonymous_Gtid |         1 |         235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000003 | 235 | Query          |         1 |         310 | BEGIN                                |
| binlog.000003 | 310 | Table_map      |         1 |         370 | table_id: 101 (demo.user)            |
| binlog.000003 | 370 | Write_rows     |         1 |         422 | table_id: 101 flags: STMT_END_F      |
| binlog.000003 | 422 | Xid            |         1 |         453 | COMMIT /* xid=174 */                 |
+---------------+-----+----------------+-----------+-------------+--------------------------------------+
7 rows in set (0.00 sec)

下面我们模拟一个误操作全表更新,全表更新后再插入一条数据:

mysql> update user set age=100;
Query OK, 13 rows affected (0.03 sec)
Rows matched: 13  Changed: 13  Warnings: 0

mysql> INSERT INTO `demo`.`user`(`name`,`age`,`address`) VALUES ('d1',20,'d1');
Query OK, 1 row affected (0.01 sec)

mysql> select * from user;
+----+----------+-----+----------+
| id | name     | age | address  |
+----+----------+-----+----------+
|  1 | test     | 100 | test     |
|  2 | zhangsan | 100 | address1 |
|  3 | lisi     | 100 | addr1    |
|  4 | test1    | 100 | addr2    |
|  5 | test2    | 100 | addr3    |
|  6 | test3    | 100 | address4 |
|  7 | b1       | 100 | bb       |
|  8 | a1       | 100 | add1     |
|  9 | a2       | 100 | add2     |
| 10 | a3       | 100 | add3     |
| 11 | a4       | 100 | add4     |
| 12 | a5       | 100 | add5     |
| 18 | c1       | 100 | c1       |
| 19 | d1       |  20 | d1       |
+----+----------+-----+----------+
14 rows in set (0.00 sec)

查看mysql的binlog发生的变化:

mysql> show binlog events in 'binlog.000003';
+---------------+------+----------------+-----------+-------------+--------------------------------------+
| Log_name      | Pos  | Event_type     | Server_id | End_log_pos | Info                                 |
+---------------+------+----------------+-----------+-------------+--------------------------------------+
| binlog.000003 |    4 | Format_desc    |         1 |         125 | Server ver: 8.0.21, Binlog ver: 4    |
| binlog.000003 |  125 | Previous_gtids |         1 |         156 |                                      |
| binlog.000003 |  156 | Anonymous_Gtid |         1 |         235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000003 |  235 | Query          |         1 |         310 | BEGIN                                |
| binlog.000003 |  310 | Table_map      |         1 |         370 | table_id: 101 (demo.user)            |
| binlog.000003 |  370 | Write_rows     |         1 |         422 | table_id: 101 flags: STMT_END_F      |
| binlog.000003 |  422 | Xid            |         1 |         453 | COMMIT /* xid=174 */                 |
| binlog.000003 |  453 | Anonymous_Gtid |         1 |         532 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000003 |  532 | Query          |         1 |         616 | BEGIN                                |
| binlog.000003 |  616 | Table_map      |         1 |         676 | table_id: 101 (demo.user)            |
| binlog.000003 |  676 | Update_rows    |         1 |        1490 | table_id: 101 flags: STMT_END_F      |
| binlog.000003 | 1490 | Xid            |         1 |        1521 | COMMIT /* xid=176 */                 |
| binlog.000003 | 1521 | Anonymous_Gtid |         1 |        1600 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000003 | 1600 | Query          |         1 |        1675 | BEGIN                                |
| binlog.000003 | 1675 | Table_map      |         1 |        1735 | table_id: 101 (demo.user)            |
| binlog.000003 | 1735 | Write_rows     |         1 |        1787 | table_id: 101 flags: STMT_END_F      |
| binlog.000003 | 1787 | Xid            |         1 |        1818 | COMMIT /* xid=178 */                 |
+---------------+------+----------------+-----------+-------------+--------------------------------------+
17 rows in set (0.00 sec)

一个基本的误操作全表更新已经完成,它还是比较符合我们在实际工作中的场景,当发生数据误操作后,可能还有其他业务在误操作之后对数据进行了修改,我们在这里模拟一种全表更新后,还插入了一条新数据,接下来我们的预期目的是将全表更新的数据恢复到更新前的状态。


恢复数据

首先,备份mysql的binlog文件,重新生成一个binlog:

mysql> flush logs;
Query OK, 0 rows affected (0.05 sec)

然后,从mysql的binlog中找出发生全表更新的事件点

MySQL发生全表更新后如何快速恢复数据

mysql的binlog

从binlog中可以看到,发生全表更新的事件点发生在453,1521

mysqlbinlog  --base64-output=decode-rows -v -v --start-position=532 --stop-position=1521 /var/lib/mysql/binlog.000003 | grep -C 30 'UPDATE `demo`.`user`'
MySQL发生全表更新后如何快速恢复数据

全表更新sql

记住# at 676这个关键字,然后使用sed命令从这里开始提取全表更新的sql语句:

mysqlbinlog --no-defaults --base64-output=decode-rows -v -v /var/lib/mysql/binlog.000003 | sed -n '/# at 676/,/COMMIT/p' > update.sql

使用sed命令对update sql进行替换:

sed '/WHERE/{:a;N;/SET/!ba;s/([^n]*)n(.*)n(.*)/3n2n1/}' update.sql | sed 's/### //g;s//*.*/,/g' | sed  /@4/s/,//g | sed '/WHERE/{:a;N;/@4/!ba;s/,/AND/g};s/#.*//g;s/COMMIT,//g' | sed '/^$/d' > rollback.sql
 root@a157a03eb2a7:~# cat rollback.sql
UPDATE `demo`.`user`
SET
  @1=1 ,
  @2='test     ' ,
  @3=1 ,
  @4='test     ' ,
WHERE
  @1=1 ,
  @2='test     ' ,
  @3=100 ,
  @4='test     ' ,
UPDATE `demo`.`user`
SET
  @1=2 ,
  @2='zhangsan ' ,
  @3=2 ,
  @4='address1 ' ,
WHERE
  @1=2 ,
  @2='zhangsan ' ,
  @3=100 ,
  @4='address1 ' ,

可以看到进行替换后的sql语句变的基本可读了,再次使用sed命令进行替换和优化:

root@a157a03eb2a7:~# sed  -i -r  '/WHERE/{:a;N;/@4/!ba;s/(@4=.*)/1;/g}' rollback.sql
root@a157a03eb2a7:~# cat rollback-1.sql
UPDATE `demo`.`user`
SET
  @1=1 ,
  @2='test     ' ,
  @3=1 ,
  @4='test     '
WHERE
  @1=1 AND
  @2='test     ' AND
  @3=100 AND
  @4='test     ' ;

这是为了给@4后面的“,”替换成“;”

root@a157a03eb2a7:~# sed -i 's/@1/id/g;s/@2/name/g;s/@3/age/g;s/@4/address/g' rollback.sql
root@a157a03eb2a7:~# cat rollback.sql
UPDATE `demo`.`user`
SET
  id=1 ,
  name='test     ' ,
  age=1 ,
  address='test     '
WHERE
  id=1 AND
  name='test     ' AND
  age=100 AND
  address='test     ' ;

使用这个sed将@1,@2,@3,@4替换成实际的字段名(表列名)。这个时候rollback.sql文件中的sql语句已经被优化成全表更新之前的update语句了,并且sql语句的可读性更强了。

接下来我们需要将rollback.sql导入到mysql中,把发生全表更新的数据再次更新回之前的数据状态

mysql> source /root/rollback.sql
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0
MySQL发生全表更新后如何快速恢复数据

恢复后的数据

通过上面截图我们可以看到,当rollback.sql文件导入到user表后,被全表更新的数据已经恢复到更新前的数据,而且发生全表更新后,我们还插入了一条数据,恢复数据后对新插入的数据并没有影响。


这种恢复数据的方法,相比上一篇MySQL基于binlog实现数据增量恢复实践的方法有以下优势:

  • 恢复数据影响范围更小,只恢复误操作的sql语句;
  • 恢复数据更安全,不会影响误操作前的数据,也不会影响误操作后的数据;
  • 可能对线上的业务不会造成太多的影响;
  • 这种恢复数据的方式不依赖备份数据,只依赖mysql的binlog;
  • 这种恢复数据的方式相当于对全表更新后的一次逆向更新数据,完全使用sql的update语句;

这种全表更新的误操作可以通过两种方法进行数据恢复:

  1. 备份数据+mysql的binlog事件;
  2. mysql的binlog事件+update sql语句;

但是如果发生删库和删表误操作,可能第一种方法是最优的恢复数据的方式;如果是发生全表更新后第二种方法应该是最优的恢复数据的方式。

参考文档:

https://dev.mysql.com/doc/refman/5.7/en/point-in-time-recovery.html



Tags:MySQL 恢复数据   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
数据恢复上一篇文章MySQL基于binlog实现数据增量恢复实践 我们大概讲解了下当数据表发生全表更新后,如何使用冷备份数据和基于mysql的binlog实现增量式恢复数据,这种增量恢复...【详细内容】
2020-08-21  Tags: MySQL 恢复数据  点击:(54)  评论:(0)  加入收藏
本文适用于,mysql无法启动,但数据文件未丢失的情况。Mysql因意外情况,导致无法启动,数据库未做备份的情况下,如何将数据迁移至其他数据库中。原数据库地址:192.168.1.100(以下简称A...【详细内容】
2019-08-07  Tags: MySQL 恢复数据  点击:(244)  评论:(0)  加入收藏
▌简易百科推荐
作者:雷文霆 爱可生华东交付服务部 DBA 成员,主要负责Mysql故障处理及相关技术支持。爱好看书,电影。座右铭,每一个不曾起舞的日子,都是对生命的辜负。 本文来源:原创投稿 *爱可生...【详细内容】
2021-12-24  爱可生    Tags:MySQL   点击:(6)  评论:(0)  加入收藏
生成间隙(gap)锁、临键(next-key)锁的前提条件 是在 RR 隔离级别下。有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过,详细内容可以看这篇文章...【详细内容】
2021-12-14  python数据分析    Tags:MySQL记录锁   点击:(17)  评论:(0)  加入收藏
binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二...【详细内容】
2021-12-14  linux上的码农    Tags:mysql   点击:(13)  评论:(0)  加入收藏
为查询优化你的查询 大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查...【详细内容】
2021-12-09  元宇宙iwemeta    Tags:mysql   点击:(15)  评论:(0)  加入收藏
测试的目的和原因,公司有很多程序员,每个程序员对数据库和表结构都有自己的理解。而且每个程序员的理解往往是以效率考虑。既然都是为了效率考虑,那么我就来测试一下究竟哪种使...【详细内容】
2021-12-08  吴彬的分享    Tags:Mysql数据库   点击:(14)  评论:(0)  加入收藏
当你们考虑项目并发的时候,我在部署环境,当你们在纠结使用ArrayList还是LinkedArrayList的时候,我还是在部署环境。所以啊,技术不止境,我在部环境。今天这篇文章缕一下在同一台服...【详细内容】
2021-12-08  秃头码哥    Tags:MySQL数据库   点击:(16)  评论:(0)  加入收藏
对于数据分析来说,MySQL使用最多的是查询,比如对数据进行排序、分组、去重、汇总及字符串匹配等,如果查询的数据涉及多个表,还需要要对表进行连接,本文就来说说MySQL中常用的查询...【详细内容】
2021-12-06  笨鸟学数据分析    Tags:MySQL   点击:(20)  评论:(0)  加入收藏
在学习SQL语句之前,首先需要区分几个概念,我们常说的数据库是指数据库软件,例如MySQL、Oracle、SQL Server等,而本文提到的数据库是指数据库软件中的一个个用于存储数据的容器。...【详细内容】
2021-11-24  笨鸟学数据分析    Tags:SQL语句   点击:(23)  评论:(0)  加入收藏
概述以前参加过一个库存系统,由于其业务复杂性,搞了很多个应用来支撑。这样的话一份库存数据就有可能同时有多个应用来修改库存数据。比如说,有定时任务域xx.cron,和SystemA域...【详细内容】
2021-11-05  Java云海    Tags:分布式锁   点击:(31)  评论:(0)  加入收藏
MySQL的进阶查询 一、 按关键字排序 使用ORDERBY语句来实现排序排序可针对一个或多个字段ASC:升序,默认排序方式 【升序是从小到大】DESC:降序 【降序是从大到小】ORDER BY的...【详细内容】
2021-11-05  Java热点    Tags:SQL语句   点击:(27)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条