博客
关于我
强烈建议你试试无所不能的chatGPT，快点击我
MySQL 5.7 update误操作后进行数据恢复
阅读量：3758 次
发布时间：2019-05-22

本文共 12215 字，大约阅读时间需要 40 分钟。

背景介绍

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

内容如下

```javaat 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语句：```java```javased '/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}' 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.sqlroot@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)恢复到了修改之前的记录，本次恢复测试完成。

转载地址：http://eiesn.baihongyu.com/

你可能感兴趣的文章
实验二 二维随机变量信息量的计算
查看>>
使用react脚手架创建react项目时发生错误
查看>>
关于setState是异步与同步的
查看>>
56. 合并区间---js解法
查看>>
5. 最长回文子串---js解法
查看>>
USACO 2007 Open Gold/acwing2240：餐饮 （拆点+最大流）‘三分图匹配’
查看>>
那些年你不知道的C++STL进制转换函数
查看>>
区间和并问题 思路加模板整理（校门外的树）
查看>>
C++中next_permutation函数的使用方法、原理及手动实现
查看>>
网络流常用小技巧之 拆点
查看>>
最大权闭合子图
查看>>
最小权点覆盖集 与 最大权独立集
查看>>
POJ 2125 Destroying The Graph && Acwing 2325. 有向图破坏（拆点+最小权点覆盖集）
查看>>
计算几何基础知识整理大全 代码模板与证明过程 （直线、向量、多边形、三维计算几何、凸包、半平面交、最小圆覆盖）
查看>>
计算几何之 判断两线段是否相交 代码模板与证明
查看>>
三维计算几何之三维凸包 增量法
查看>>
MySQL变量,存储过程,函数,流程控制详解(小白都能懂哦)
查看>>
9篇小白都能懂系列博客学完MySQL基础
查看>>
还在为Linux入门发愁？
查看>>
如何优雅而又不失内涵的在centos7下安装tree命令
查看>>
喝酒易醉，品茶养心，人生如梦，品茶悟道，何以解忧？唯有杜康！-- 愿君每日到此一游！
当前时间: 2024-09-15 22:02:09   当前IP: 3.144.121.247   联系邮箱:javaeecc@qq.com     Copyright © 2020 - 2022 baihongyu.com 京ICP备2021015314号-2
强烈建议你试试无所不能的CHAT-GPT，快点击我
强烈建议你试试无所不能的CHAT-GPT，快点击我