原文链接：本文详述MySQL 5.7 模拟update误操作后进行数据恢复的全过程，希望对大家有帮助 。
https://www.modb.pro/db/26098（复制链接至浏览器，即可查看）
背景介绍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-ROWSmysql-bin.000003 | grep -B 15 'ture'| more
推荐阅读
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- MySQL查看连接数
- Linux下的MySQL Proxy 读写分离该怎么操作？
- 数据更新删除与排序：横向对比 Python、PowerBI、Excel、MySQL
- 20000 字干货笔记，一天搞定 MySQL
- 把MySQL中的各种锁及其原理都画出来
- MySQL的用户权限管理的应用总结
- MySQL数据库运维的基本命令
- Mysql写入频繁，怎么破？这是我见过的最清晰的“神操作”
- MySQL数据库表结构快速导出成MarkDown文档
- MySQL数据库架构和同步复制流程
