sql server 误操作后恢复_MySQL 5.7 update误操作后数据恢复详解

本文详述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_n