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

MySQL 5.7 update误操作后数据恢复详解

时间：2020-06-24 17:27:01 来源：作者：

+ 加入收藏

原文链接：
https://www.modb.pro/db/26098（复制链接至浏览器，即可查看）

本文详述MySQL 5.7 模拟update误操作后进行数据恢复的全过程，希望对大家有帮助。

背景介绍

MySQL目前还没有像Oracle数据库那样强大有闪回的功能，MySQL只能通过挖去binlog日志的方法来获取数据，但是有个前提就是binlog_format必须设置成row。下面通过一个案例来演示。

注意：严禁在生产环境测试

表结构

测试的表结构如下：

CREATE TABLE `update_test` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `user_id` varchar(20) NOT NULL DEFAULT '',
    `vote_num` int(10) unsigned NOT NULL DEFAULT '0',
    `group_id` int(10) unsigned NOT NULL DEFAULT '0',
    `status` tinyint(2) unsigned NOT NULL DEFAULT '1',
    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' ,
    PRIMARY KEY (`id`),
    KEY `index_user_id` (`user_id`) USING HASH
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

修改数据

插入和修改测试数据，对user_id字段的内容进行了修改。

insert into update_test (Select * from recordss_memory limit 20);

查看修改前的数据：

root@localhost#mysql.sock : tc0110:56:12>select * from update_test;
+----+-------------+----------+----------+--------+---------------------+
| id | user_id     | vote_num | group_id | status | create_time         |
+----+-------------+----------+----------+--------+---------------------+
|  1 | ddddddddddd |     4502 |        2 |      1 | 2020-06-04 11:34:17 |
|  2 | ddddddddddd |     5564 |        1 |      1 | 2020-06-04 11:34:17 |
|  3 | ddddddddddd |     3521 |        2 |      1 | 2020-06-04 11:34:17 |
|  4 | ddddddddddd |     1414 |        0 |      1 | 2020-06-04 11:34:17 |
|  5 | ddddddddddd |     8047 |        1 |      1 | 2020-06-04 11:34:17 |
|  6 | ddddddddddd |     5556 |        1 |      1 | 2020-06-04 11:34:17 |
|  7 | ddddddddddd |     7166 |        1 |      2 | 2020-06-04 11:34:17 |
|  8 | ddddddddddd |     3277 |        2 |      2 | 2020-06-04 11:34:17 |
|  9 | ddddddddddd |     8658 |        2 |      1 | 2020-06-04 11:34:17 |
| 10 | ddddddddddd |     4146 |        0 |      2 | 2020-06-04 11:34:17 |
| 11 | ddddddddddd |     7906 |        2 |      1 | 2020-06-04 11:34:17 |
| 12 | ddddddddddd |      512 |        0 |      2 | 2020-06-04 11:34:17 |
| 13 | ddddddddddd |     7493 |        0 |      1 | 2020-06-04 11:34:17 |
| 14 | ddddddddddd |     5583 |        1 |      1 | 2020-06-04 11:34:17 |
| 15 | ddddddddddd |     4273 |        2 |      1 | 2020-06-04 11:34:17 |
| 16 | ddddddddddd |     1117 |        0 |      1 | 2020-06-04 11:34:17 |
| 17 | ddddddddddd |     3936 |        2 |      1 | 2020-06-04 11:34:17 |
| 18 | ddddddddddd |     4735 |        2 |      1 | 2020-06-04 11:34:17 |
| 19 | ddddddddddd |     2505 |        0 |      1 | 2020-06-04 11:34:17 |
| 20 | ddddddddddd |     2523 |        2 |      1 | 2020-06-04 11:34:17 |
+----+-------------+----------+----------+--------+---------------------+
20 rows in set (0.00 sec)

root@localhost#mysql.sock : tc0110:56:49>show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 | 536871032 |
| mysql-bin.000002 | 536871341 |
| mysql-bin.000003 | 197210338 |
+------------------+-----------+
3 rows in set (0.00 sec)

root@localhost#mysql.sock : tc0110:57:29> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 | 536871032 |
| mysql-bin.000002 | 536871341 |
| mysql-bin.000003 | 197210338 |
+------------------+-----------+
3 rows in set (0.00 sec)

root@localhost#mysql.sock : tc0110:57:54> update update_test set user_id='ture';

root@localhost#mysql.sock : tc0110:59:05>select * from update_test;
+----+---------+----------+----------+--------+---------------------+
| id | user_id | vote_num | group_id | status | create_time         |
+----+---------+----------+----------+--------+---------------------+
|  1 | ture    |     4502 |        2 |      1 | 2020-06-04 11:34:17 |
|  2 | ture    |     5564 |        1 |      1 | 2020-06-04 11:34:17 |
|  3 | ture    |     3521 |        2 |      1 | 2020-06-04 11:34:17 |
|  4 | ture    |     1414 |        0 |      1 | 2020-06-04 11:34:17 |
|  5 | ture    |     8047 |        1 |      1 | 2020-06-04 11:34:17 |
|  6 | ture    |     5556 |        1 |      1 | 2020-06-04 11:34:17 |
|  7 | ture    |     7166 |        1 |      2 | 2020-06-04 11:34:17 |
|  8 | ture    |     3277 |        2 |      2 | 2020-06-04 11:34:17 |
|  9 | ture    |     8658 |        2 |      1 | 2020-06-04 11:34:17 |
| 10 | ture    |     4146 |        0 |      2 | 2020-06-04 11:34:17 |
| 11 | ture    |     7906 |        2 |      1 | 2020-06-04 11:34:17 |
| 12 | ture    |      512 |        0 |      2 | 2020-06-04 11:34:17 |
| 13 | ture    |     7493 |        0 |      1 | 2020-06-04 11:34:17 |
| 14 | ture    |     5583 |        1 |      1 | 2020-06-04 11:34:17 |
| 15 | ture    |     4273 |        2 |      1 | 2020-06-04 11:34:17 |
| 16 | ture    |     1117 |        0 |      1 | 2020-06-04 11:34:17 |
| 17 | ture    |     3936 |        2 |      1 | 2020-06-04 11:34:17 |
| 18 | ture    |     4735 |        2 |      1 | 2020-06-04 11:34:17 |
| 19 | ture    |     2505 |        0 |      1 | 2020-06-04 11:34:17 |
| 20 | ture    |     2523 |        2 |      1 | 2020-06-04 11:34:17 |
+----+---------+----------+----------+--------+---------------------+
20 rows in set (0.00 sec)
root@localhost#mysql.sock : tc0110:59:11>show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 | 536871032 |
| mysql-bin.000002 | 536871341 |
| mysql-bin.000003 | 197211765 |
+------------------+-----------+
3 rows in set (0.00 sec)

挖取binlog日志

通过mysqlbinlog命令来查看修改的内容。

/usr/local/mysql/bin/mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS  mysql-bin.000003 | grep -B 15 'ture'| more

内容如下

# at 197210475
#200610 10:59:05 server id 1023306  end_log_pos 197210536 CRC32 0xee919b04      Rows_query
# update update_test set user_id='ture'
# at 197210536
#200610 10:59:05 server id 1023306  end_log_pos 197210598 CRC32 0xeb431251      Table_map: `tc01`.`update_test` mApped to number 120
# at 197210598
#200610 10:59:05 server id 1023306  end_log_pos 197211734 CRC32 0x5f211a8d      Update_rows: table id 120 flags: STMT_END_F

### UPDATE `tc01`.`update_test`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='ddddddddddd' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
###   @3=4502 /* INT meta=0 nullable=0 is_null=0 */
###   @4=2 /* INT meta=0 nullable=0 is_null=0 */
###   @5=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @6='2020-06-04 11:34:17' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
### SET
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='ture' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
###   @3=4502 /* INT meta=0 nullable=0 is_null=0 */
###   @4=2 /* INT meta=0 nullable=0 is_null=0 */
###   @5=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @6='2020-06-04 11:34:17' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
### UPDATE `tc01`.`update_test`
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='ddddddddddd' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
###   @3=5564 /* INT meta=0 nullable=0 is_null=0 */
###   @4=1 /* INT meta=0 nullable=0 is_null=0 */
###   @5=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @6='2020-06-04 11:34:17' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
### SET
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='ture' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
###   @3=5564 /* INT meta=0 nullable=0 is_null=0 */
###   @4=1 /* INT meta=0 nullable=0 is_null=0 */
###   @5=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @6='2020-06-04 11:34:17' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
..........................

找出位置后，把binlog的记录导出来。

/usr/local/mysql/bin/mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS  mysql-bin.000003 | sed -n '/# at 197210598/,/COMMIT/p' > ./update_test.txt

这些是误操作之前的数据

###   @1=19 /* INT meta=0 nullable=0 is_null=0 */
###   @2='ddddddddddd' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
###   @3=2505 /* INT meta=0 nullable=0 is_null=0 */
###   @4=0 /* INT meta=0 nullable=0 is_null=0 */
###   @5=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @6='2020-06-04 11:34:17' /* DATETIME(0) meta=0 nullable=0 is_null=0 */

这些是误操作之后的数据

###   @1=19 /* INT meta=0 nullable=0 is_null=0 */
###   @2='ture' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
###   @3=2505 /* INT meta=0 nullable=0 is_null=0 */
###   @4=0 /* INT meta=0 nullable=0 is_null=0 */
###   @5=1 /* TINYINT meta=0 nullable=0 is_null=0 */
###   @6='2020-06-04 11:34:17' /* DATETIME(0) meta=0 nullable=0 is_null=0 */

替换成需要的SQL语句

下一步转换成需要的SQL语句：

sed '/WHERE/{:a;N;/SET/!ba;s/([^n]*)n(.*)n(.*)/3n2n1/}' update_test.txt|sed -r '/WHERE/{:a;N;/@6/!ba;s/###   @2.*//g}'|sed 's/### //g;s//*.*/,/g' | sed '/WHERE/{:a;N;/@1/!ba;s/,/;/g};s/#.*//g;s/COMMIT,//g'|sed '/^$/d' > ./update_test_recover.sql

SQL内容如下：

[mysql@tc02 binlog]$ cat update_test_recover.sql
UPDATE `tc01`.`update_test`
SET
  @1=1 ,
  @2='ddddddddddd' ,
  @3=4502 ,
  @4=2 ,
  @5=1 ,
  @6='2020-06-04 11:34:17' ,
WHERE
  @1=1 ;
UPDATE `tc01`.`update_test`
SET
  @1=2 ,
  @2='ddddddddddd' ,
  @3=5564 ,
  @4=1 ,
  @5=1 ,
  @6='2020-06-04 11:34:17' ,
WHERE
  @1=2 ;
UPDATE `tc01`.`update_test`
SET
  @1=3 ,
  @2='ddddddddddd' ,
  @3=3521 ,
  @4=2 ,
  @5=1 ,
  @6='2020-06-04 11:34:17' ,
WHERE
  @1=3 ;
.........................
.........................

需要进一步替换@1，@2，@3…@6.

CREATE TABLE `update_test` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `user_id` varchar(20) NOT NULL DEFAULT '',
    `vote_num` int(10) unsigned NOT NULL DEFAULT '0',
    `group_id` int(10) unsigned NOT NULL DEFAULT '0',
    `status` tinyint(2) unsigned NOT NULL DEFAULT '1',
    `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' ,
    PRIMARY KEY (`id`),
    KEY `index_user_id` (`user_id`) USING HASH
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

根据这个表结构替换：

sed -i 's/@1/id/g;s/@2/user_id/g;s/@3/vote_num/g;s/@4/group_id/g;s/@5/status/g;s/@6/create_time/g' update_test_recover.sql

修改后的内容如下：

UPDATE `tc01`.`update_test`
SET
  id=1 ,
  user_id='ddddddddddd' ,
  vote_num=4502 ,
  group_id=2 ,
  status=1 ,
  create_time='2020-06-04 11:34:17' ,
WHERE
  id=1 ;
UPDATE `tc01`.`update_test`
SET
  id=2 ,
  user_id='ddddddddddd' ,
  vote_num=5564 ,
  group_id=1 ,
  status=1 ,
  create_time='2020-06-04 11:34:17' ,
WHERE
  id=2 ;
UPDATE `tc01`.`update_test`
SET
  id=3 ,
  user_id='ddddddddddd' ,
  vote_num=3521 ,
  group_id=2 ,
  status=1 ,
  create_time='2020-06-04 11:34:17' ,
WHERE
  id=3 ;
  ....................................
  .....................................

生成恢复用的SQL语句

还有一个问题,就是需要把create_time后面的逗号去掉。使用下面的命令：

sed -i -r 's/(create_time=.*),/1/g' update_test_recover.sql

查看内容如下：

UPDATE `tc01`.`update_test`
SET
  id=1 ,
  user_id='ddddddddddd' ,
  vote_num=4502 ,
  group_id=2 ,
  status=1 ,
  create_time='2020-06-04 11:34:17'
WHERE
  id=1 ;
UPDATE `tc01`.`update_test`
SET
  id=2 ,
  user_id='ddddddddddd' ,
  vote_num=5564 ,
  group_id=1 ,
  status=1 ,
  create_time='2020-06-04 11:34:17'
WHERE
  id=2 ;
UPDATE `tc01`.`update_test`
SET
  id=3 ,
  user_id='ddddddddddd' ,
  vote_num=3521 ,
  group_id=2 ,
  status=1 ,
  create_time='2020-06-04 11:34:17'
WHERE
  id=3 ;
UPDATE `tc01`.`update_test`
SET
  id=4 ,
  user_id='ddddddddddd' ,
  vote_num=1414 ,
  group_id=0 ,
  status=1 ,
  create_time='2020-06-04 11:34:17'
WHERE
  id=4 ;
  ............................
  ..............................

满足了条件之后，我们执行语句：

root@localhost#mysql.sock : tc0111:33:29>source update_test_recover.sql

root@localhost#mysql.sock : tc0111:34:14>select * from `tc01`.`update_test`;
+----+-------------+----------+----------+--------+---------------------+
| id | user_id     | vote_num | group_id | status | create_time         |
+----+-------------+----------+----------+--------+---------------------+
|  1 | ddddddddddd |     4502 |        2 |      1 | 2020-06-04 11:34:17 |
|  2 | ddddddddddd |     5564 |        1 |      1 | 2020-06-04 11:34:17 |
|  3 | ddddddddddd |     3521 |        2 |      1 | 2020-06-04 11:34:17 |
|  4 | ddddddddddd |     1414 |        0 |      1 | 2020-06-04 11:34:17 |
|  5 | ddddddddddd |     8047 |        1 |      1 | 2020-06-04 11:34:17 |
|  6 | ddddddddddd |     5556 |        1 |      1 | 2020-06-04 11:34:17 |
|  7 | ddddddddddd |     7166 |        1 |      2 | 2020-06-04 11:34:17 |
|  8 | ddddddddddd |     3277 |        2 |      2 | 2020-06-04 11:34:17 |
|  9 | ddddddddddd |     8658 |        2 |      1 | 2020-06-04 11:34:17 |
| 10 | ddddddddddd |     4146 |        0 |      2 | 2020-06-04 11:34:17 |
| 11 | ddddddddddd |     7906 |        2 |      1 | 2020-06-04 11:34:17 |
| 12 | ddddddddddd |      512 |        0 |      2 | 2020-06-04 11:34:17 |
| 13 | ddddddddddd |     7493 |        0 |      1 | 2020-06-04 11:34:17 |
| 14 | ddddddddddd |     5583 |        1 |      1 | 2020-06-04 11:34:17 |
| 15 | ddddddddddd |     4273 |        2 |      1 | 2020-06-04 11:34:17 |
| 16 | ddddddddddd |     1117 |        0 |      1 | 2020-06-04 11:34:17 |
| 17 | ddddddddddd |     3936 |        2 |      1 | 2020-06-04 11:34:17 |
| 18 | ddddddddddd |     4735 |        2 |      1 | 2020-06-04 11:34:17 |
| 19 | ddddddddddd |     2505 |        0 |      1 | 2020-06-04 11:34:17 |
| 20 | ddddddddddd |     2523 |        2 |      1 | 2020-06-04 11:34:17 |
+----+-------------+----------+----------+--------+---------------------+
20 rows in set (0.00 sec)

恢复到了修改之前的记录，本次恢复测试完成。

Tags：MySQL 数据恢复点击:() 评论:()

声明：本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com)，我们将及时更正、删除，谢谢。

▌相关推荐

MySQL 5.7 update误操作后数据恢复详解

本文详述MySQL 5.7 模拟update误操作后进行数据恢复的全过程，希望对大家有帮助。...【详细内容】

2020-06-24　　Tags: MySQL 数据恢复点击:(53)　　评论:(0)　　加入收藏

详解MySQL数据恢复

日常工作中，总会有因手抖、写错条件、写错表名、错连生产库造成的误删库表和数据的事情发生，那么，如果连数据都恢复不了，还要什么 DBA。1.前言数据恢复的前提的做好备份，且开启 b...【详细内容】

2019-09-06　　Tags: MySQL 数据恢复点击:(125)　　评论:(0)　　加入收藏

▌简易百科推荐

MySQL 使用 Mysqldump 备份导入数据导致主从异常

作者：雷文霆爱可生华东交付服务部 DBA 成员，主要负责Mysql故障处理及相关技术支持。爱好看书，电影。座右铭，每一个不曾起舞的日子，都是对生命的辜负。本文来源：原创投稿 *爱可生...【详细内容】

2021-12-24　　爱可生　　　　Tags:MySQL 　点击:(7)　　评论:(0)　　加入收藏

分享MySQL记录锁、间隙锁、临键锁小案例演示，你学废了吗

生成间隙(gap)锁、临键(next-key)锁的前提条件是在 RR 隔离级别下。有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过，详细内容可以看这篇文章...【详细内容】

2021-12-14　　python数据分析　　　　Tags:MySQL记录锁　点击:(18)　　评论:(0)　　加入收藏

mysqlbinlog基本认识

binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了，它记录了所有的DDL和DML(除了数据查询语句)语句，以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二...【详细内容】

2021-12-14　　linux上的码农　　　　Tags:mysql 　点击:(13)　　评论:(0)　　加入收藏

百万级数据下的mysql深度解析

为查询优化你的查询大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一，而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候，这些查...【详细内容】

2021-12-09　　元宇宙iwemeta　　　　Tags:mysql 　点击:(15)　　评论:(0)　　加入收藏

Mysql数据库tinyint，int，bigint，char，varchar究竟用哪个？

测试的目的和原因，公司有很多程序员，每个程序员对数据库和表结构都有自己的理解。而且每个程序员的理解往往是以效率考虑。既然都是为了效率考虑，那么我就来测试一下究竟哪种使...【详细内容】

2021-12-08　　吴彬的分享　　　　Tags:Mysql数据库　点击:(14)　　评论:(0)　　加入收藏

Win下部署多个MySQL数据库实例

当你们考虑项目并发的时候，我在部署环境，当你们在纠结使用ArrayList还是LinkedArrayList的时候，我还是在部署环境。所以啊，技术不止境，我在部环境。今天这篇文章缕一下在同一台服...【详细内容】

2021-12-08　　秃头码哥　　　　Tags:MySQL数据库　点击:(17)　　评论:(0)　　加入收藏

MySQL中常用的15个查询子句

对于数据分析来说，MySQL使用最多的是查询，比如对数据进行排序、分组、去重、汇总及字符串匹配等，如果查询的数据涉及多个表，还需要要对表进行连接，本文就来说说MySQL中常用的查询...【详细内容】

2021-12-06　　笨鸟学数据分析　　　　Tags:MySQL 　点击:(21)　　评论:(0)　　加入收藏

15个MySQL常用基本SQL语句

在学习SQL语句之前，首先需要区分几个概念，我们常说的数据库是指数据库软件，例如MySQL、Oracle、SQL Server等，而本文提到的数据库是指数据库软件中的一个个用于存储数据的容器。...【详细内容】

2021-11-24　　笨鸟学数据分析　　　　Tags:SQL语句　点击:(23)　　评论:(0)　　加入收藏

用 MySQL 实现分布式锁，你听过吗？

概述以前参加过一个库存系统，由于其业务复杂性，搞了很多个应用来支撑。这样的话一份库存数据就有可能同时有多个应用来修改库存数据。比如说，有定时任务域xx.cron，和SystemA域...【详细内容】

2021-11-05　　Java云海　　　　Tags:分布式锁　点击:(31)　　评论:(0)　　加入收藏

MySQL高级SQL语句

MySQL的进阶查询一、按关键字排序使用ORDERBY语句来实现排序排序可针对一个或多个字段ASC:升序，默认排序方式【升序是从小到大】DESC:降序【降序是从大到小】ORDER BY的...【详细内容】

2021-11-05　　Java热点　　　　Tags:SQL语句　点击:(28)　　评论:(0)　　加入收藏

推荐资讯

聊聊如何自定义数据脱	河南人到底有多爱吃面
人称“犬中四煞”的4	离婚后，约定每月给孩子
“三皇五帝”分别是哪	印度低种姓群体如何翻
日本研发“飞行摩托”	2021年Steam最畅销游