MySQL发生全表更新后如何快速恢复数据
2020-08-21
加入收藏
数据恢复
上一篇文章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的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(.*)/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
恢复后的数据
通过上面截图我们可以看到，当rollback.sql文件导入到user表后，被全表更新的数据已经恢复到更新前的数据，而且发生全表更新后，我们还插入了一条数据，恢复数据后对新插入的数据并没有影响。
这种恢复数据的方法，相比上一篇MySQL基于binlog实现数据增量恢复实践的方法有以下优势：
- 恢复数据影响范围更小，只恢复误操作的sql语句；
- 恢复数据更安全，不会影响误操作前的数据，也不会影响误操作后的数据；
- 可能对线上的业务不会造成太多的影响；
- 这种恢复数据的方式不依赖备份数据，只依赖mysql的binlog；
- 这种恢复数据的方式相当于对全表更新后的一次逆向更新数据，完全使用sql的update语句；
这种全表更新的误操作可以通过两种方法进行数据恢复：
- 备份数据+mysql的binlog事件；
- mysql的binlog事件+update sql语句；
但是如果发生删库和删表误操作，可能第一种方法是最优的恢复数据的方式；如果是发生全表更新后第二种方法应该是最优的恢复数据的方式。
参考文档：
https://dev.mysql.com/doc/refman/5.7/en/point-in-time-recovery.html
