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: 0mysql> 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的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`'

全表更新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\(.*\)/\3\n\2\n\1/}' 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.sqlUPDATE `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.sqlroot@a157a03eb2a7:~# cat rollback-1.sqlUPDATE `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.sqlroot@a157a03eb2a7:~# cat rollback.sqlUPDATE `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.sqlQuery OK, 1 row affected (0.02 sec)Rows matched: 1  Changed: 1  Warnings: 0Query OK, 1 row affected (0.02 sec)Rows matched: 1  Changed: 1  Warnings: 0Query OK, 1 row affected (0.02 sec)Rows matched: 1  Changed: 1  Warnings: 0

恢复后的数据

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

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

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

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

备份数据+mysql的binlog事件；
mysql的binlog事件+update sql语句；

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

参考文档：

不积跬步，无以至千里；不积小流，无以成江海！

本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。