mysql update忘加where条件的操作恢复(模拟oracle闪回)
来源：互联网 发布：巨杉数据库 spark 编辑：程序博客网 时间：2024/06/09 14:12
先准备数据：
CREATE TABLE `student` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL DEFAULT '',
`class` varchar(10) NOT NULL DEFAULT '',
`score` int(3) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
(product)root@localhost [test]> select * from student;
+----+------+-------+-------+
| id | name | class | score |
+----+------+-------+-------+
| 1 | a | 1 | 56 |
| 2 | b | 1 | 61 |
| 3 | c | 2 | 78 |
| 4 | d | 2 | 45 |
| 5 | e | 3 | 76 |
| 6 | f | 3 | 89 |
| 7 | g | 4 | 43 |
| 8 | h | 4 | 90 |
+----+------+-------+-------+
8 rows in set (0.00 sec)
在执行操作时，忘记带了where条件了，如下：
(product)root@localhost [test]> update student set score=0;
Query OK, 8 rows affected (0.00 sec)
Rows matched: 8 Changed: 8 Warnings: 0
(product)root@localhost [test]> select * from student;
+----+------+-------+-------+
| id | name | class | score |
+----+------+-------+-------+
| 1 | a | 1 | 0 |
| 2 | b | 1 | 0 |
| 3 | c | 2 | 0 |
| 4 | d | 2 | 0 |
| 5 | e | 3 | 0 |
| 6 | f | 3 | 0 |
| 7 | g | 4 | 0 |
| 8 | h | 4 | 0 |
+----+------+-------+-------+
8 rows in set (0.00 sec)
结果，整个表的记录都更新成0（0表示不记格);
下面开始恢复：
先建一个普通权限的帐号(不能是super权限):
grant all privileges on test.* to 'readonly'@'%' identified by '123456';
把read only打开，设置数据库只读，如下：
set global read_only=1;
把readonly帐号给运维前端人员，让他们将前端应用中的用名改下，并重启应用，现在对数据库都是只读访问了。
通过binlog先找到那条语句：
-bash-4.1$ mysqlbinlog --no-defaults -v -v --base64-output=decode-rows mysql-bin.000143|grep -B 15'@4=0'|more
# update student set score=0
# at 15119746
#160828 6:50:00 server id 65303306 end_log_pos 15119803 CRC32 0x991b4161 Table_map: `test`.`student` mapped to number 111
# at 15119803
#160828 6:50:00 server id 65303306 end_log_pos 15120047 CRC32 0x36d5c3e2 Update_rows: table id 111 flags: STMT_END_F
### UPDATE `test`.`student`
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='a' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @3='1' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @4=56 /* INT meta=0 nullable=0 is_null=0 */
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='a' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @3='1' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @4=0 /* INT meta=0 nullable=0 is_null=0 */
### UPDATE `test`.`student`
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='b' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @3='1' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @4=61 /* INT meta=0 nullable=0 is_null=0 */
### SET
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='b' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @3='1' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @4=0 /* INT meta=0 nullable=0 is_null=0 */
### UPDATE `test`.`student`
### WHERE
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='c' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @3='2' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @4=78 /* INT meta=0 nullable=0 is_null=0 */
### SET
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='c' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @3='2' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @4=0 /* INT meta=0 nullable=0 is_null=0 */
### UPDATE `test`.`student`
### WHERE
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2='d' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @3='2' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @4=45 /* INT meta=0 nullable=0 is_null=0 */
### SET
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2='d' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @3='2' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @4=0 /* INT meta=0 nullable=0 is_null=0 */
### UPDATE `test`.`student`
### WHERE
### @1=5 /* INT meta=0 nullable=0 is_null=0 */
### @2='e' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @3='3' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @4=76 /* INT meta=0 nullable=0 is_null=0 */
### SET
### @1=5 /* INT meta=0 nullable=0 is_null=0 */
### @2='e' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @3='3' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @4=0 /* INT meta=0 nullable=0 is_null=0 */
### UPDATE `test`.`student`
### WHERE
### @1=6 /* INT meta=0 nullable=0 is_null=0 */
### @2='f' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @3='3' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @4=89 /* INT meta=0 nullable=0 is_null=0 */
### SET
### @1=6 /* INT meta=0 nullable=0 is_null=0 */
### @2='f' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @3='3' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @4=0 /* INT meta=0 nullable=0 is_null=0 */
### UPDATE `test`.`student`
### WHERE
### @1=7 /* INT meta=0 nullable=0 is_null=0 */
### @2='g' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @3='4' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @4=43 /* INT meta=0 nullable=0 is_null=0 */
### SET
### @1=7 /* INT meta=0 nullable=0 is_null=0 */
### @2='g' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @3='4' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @4=0 /* INT meta=0 nullable=0 is_null=0 */
### UPDATE `test`.`student`
### WHERE
### @1=8 /* INT meta=0 nullable=0 is_null=0 */
### @2='h' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @3='4' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @4=90 /* INT meta=0 nullable=0 is_null=0 */
### SET
### @1=8 /* INT meta=0 nullable=0 is_null=0 */
### @2='h' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @3='4' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @4=0 /* INT meta=0 nullable=0 is_null=0 */
把binlog导出来：
mysqlbinlog --no-defaults -v -v --base64-output=decode-rows mysql-bin.000143|sed -n '/# at 15119803/,/COMMIT/p' >/data/mysql/mysql3306/logs/1.sql
-bash-4.1$ more /data/mysql/mysql3306/logs/1.sql
# at 15119803
#160828 6:50:00 server id 65303306 end_log_pos 15120047 CRC32 0x36d5c3e2 Update_rows: table id 111 flags: STMT_END_F
### UPDATE `test`.`student`
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='a' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @3='1' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @4=56 /* INT meta=0 nullable=0 is_null=0 */
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='a' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @3='1' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @4=0 /* INT meta=0 nullable=0 is_null=0 */
### UPDATE `test`.`student`
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='b' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @3='1' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @4=61 /* INT meta=0 nullable=0 is_null=0 */
### SET
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='b' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @3='1' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @4=0 /* INT meta=0 nullable=0 is_null=0 */
### UPDATE `test`.`student`
### WHERE
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='c' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @3='2' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @4=78 /* INT meta=0 nullable=0 is_null=0 */
### SET
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='c' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @3='2' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @4=0 /* INT meta=0 nullable=0 is_null=0 */
### UPDATE `test`.`student`
### WHERE
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2='d' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @3='2' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @4=45 /* INT meta=0 nullable=0 is_null=0 */
### SET
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2='d' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @3='2' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @4=0 /* INT meta=0 nullable=0 is_null=0 */
### UPDATE `test`.`student`
### WHERE
### @1=5 /* INT meta=0 nullable=0 is_null=0 */
### @2='e' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @3='3' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @4=76 /* INT meta=0 nullable=0 is_null=0 */
### SET
### @1=5 /* INT meta=0 nullable=0 is_null=0 */
### @2='e' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @3='3' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @4=0 /* INT meta=0 nullable=0 is_null=0 */
### UPDATE `test`.`student`
### WHERE
### @1=6 /* INT meta=0 nullable=0 is_null=0 */
### @2='f' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @3='3' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @4=89 /* INT meta=0 nullable=0 is_null=0 */
### SET
### @1=6 /* INT meta=0 nullable=0 is_null=0 */
### @2='f' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @3='3' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @4=0 /* INT meta=0 nullable=0 is_null=0 */
### UPDATE `test`.`student`
### WHERE
### @1=7 /* INT meta=0 nullable=0 is_null=0 */
### @2='g' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @3='4' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @4=43 /* INT meta=0 nullable=0 is_null=0 */
### SET
### @1=7 /* INT meta=0 nullable=0 is_null=0 */
### @2='g' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @3='4' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @4=0 /* INT meta=0 nullable=0 is_null=0 */
### UPDATE `test`.`student`
### WHERE
### @1=8 /* INT meta=0 nullable=0 is_null=0 */
### @2='h' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @3='4' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @4=90 /* INT meta=0 nullable=0 is_null=0 */
### SET
### @1=8 /* INT meta=0 nullable=0 is_null=0 */
### @2='h' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @3='4' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @4=0 /* INT meta=0 nullable=0 is_null=0 */
# at 15120047
#160828 6:50:00 server id 65303306 end_log_pos 15120078 CRC32 0x2d1407ee Xid = 26350746
COMMIT/*!*/;
这些是误操作前的数据：
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='a' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @3='1' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @4=56 /* INT meta=0 nullable=0 is_null=0 */
这些是误操作后的数据：
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='a' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @3='1' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */
### @4=0 /* INT meta=0 nullable=0 is_null=0 */
这里 @1、@2、@3、@4对应表的字段分别是id、name、class、score，下面进一步做恢复操作。
cd /data/mysql/mysql3306/logs
sed '/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}' 1.sql | sed -r '/WHERE/{:a;N;/@4/!ba;s/### @2.*//g}'|sed 's/### //g;s/\/\*.*/,/g'
| sed '/WHERE/{:a;N;/@1/!ba;s/,/;/g};s/#.*//g;s/COMMIT,//g' | sed '/^$/d' > recover.sql
-bash-4.1$ more recover.sql
UPDATE `test`.`student`
SET
@1=1 ,
@2='a' ,
@3='1' ,
@4=56 ,
WHERE
@1=1 ;
UPDATE `test`.`student`
SET
@1=2 ,
@2='b' ,
@3='1' ,
@4=61 ,
WHERE
@1=2 ;
UPDATE `test`.`student`
SET
@1=3 ,
@2='c' ,
@3='2' ,
@4=78 ,
WHERE
@1=3 ;
UPDATE `test`.`student`
SET
@1=4 ,
@2='d' ,
@3='2' ,
@4=45 ,
.........
sed '/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}' 1.sql | sed -r '/WHERE/{:a;N;/@4/!ba;s/### @2.*//g}'|sed 's/### //g;s/\/\*.*/,/g'
| sed '/WHERE/{:a;N;/@1/!ba;s/,/;/g};s/#.*//g;s/COMMIT,//g' | sed '/^$/d'命令分段说明一下作用：
sed '/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}' 1.sql ---是将WHERE和SET互相对调
sed -r '/WHERE/{:a;N;/@4/!ba;s/### @2.*//g}'---去掉where条件后的@2到@4行，只留@1那行
sed '/WHERE/{:a;N;/@1/!ba;s/,/;/g};s/#.*//g;s/COMMIT,//g' ---将字段后的,号用;号替换，将#号开头的行用空替换
sed '/^$/d' ---删除空行
再把@1、@2、@3、@4对应表的字段分别用id、name、class、score替换：
sed -i 's/@1/id/g;s/@2/name/g;s/@3/class/g;s/@4/score/g' recover.sql
再将最后一个字段后的,号去掉：
sed -i -r 's/(score=.*),/\1/g' recover.sql
-bash-4.1$ cat recover.sql
UPDATE `test`.`student`
SET
id=1 ,
name='a' ,
class='1' ,
score=56
WHERE
id=1 ;
UPDATE `test`.`student`
SET
id=2 ,
name='b' ,
class='1' ,
score=61
WHERE
id=2 ;
UPDATE `test`.`student`
SET
id=3 ,
name='c' ,
class='2' ,
score=78
WHERE
id=3 ;
UPDATE `test`.`student`
SET
id=4 ,
name='d' ,
class='2' ,
score=45
WHERE
id=4 ;
UPDATE `test`.`student`
SET
id=5 ,
name='e' ,
class='3' ,
score=76
WHERE
id=5 ;
UPDATE `test`.`student`
SET
id=6 ,
name='f' ,
class='3' ,
score=89
WHERE
id=6 ;
UPDATE `test`.`student`
SET
id=7 ,
name='g' ,
class='4' ,
score=43
WHERE
id=7 ;
UPDATE `test`.`student`
SET
id=8 ,
name='h' ,
class='4' ,
score=90
WHERE
id=8 ;
下面进行恢复操作：
(product)root@localhost [test]> select * from student;
+----+------+-------+-------+
| id | name | class | score |
+----+------+-------+-------+
| 1 | a | 1 | 0 |
| 2 | b | 1 | 0 |
| 3 | c | 2 | 0 |
| 4 | d | 2 | 0 |
| 5 | e | 3 | 0 |
| 6 | f | 3 | 0 |
| 7 | g | 4 | 0 |
| 8 | h | 4 | 0 |
+----+------+-------+-------+
8 rows in set (0.00 sec)
(product)root@localhost [(none)]> use test
Database changed
(product)root@localhost [test]> source recover.sql
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
(product)root@localhost [test]> select * from student;
+----+------+-------+-------+
| id | name | class | score |
+----+------+-------+-------+
| 1 | a | 1 | 56 |
| 2 | b | 1 | 61 |
| 3 | c | 2 | 78 |
| 4 | d | 2 | 45 |
| 5 | e | 3 | 76 |
| 6 | f | 3 | 89 |
| 7 | g | 4 | 43 |
| 8 | h | 4 | 90 |
+----+------+-------+-------+
被错误更新的数据已找回来了。
- mysql update忘加where条件的操作恢复(模拟oracle闪回)
- mysql delete错误使用where条件的操作恢复(模拟oracle闪回)
- MySQL 误操作 （update,delete忘加where条件）
- MySQL恢复之update忘加where条件误操作后数据恢复（提前条件binlog为row行格式）
- mysql恢复之delete 忘加where条件误删除恢复(binglog格式必须是ROW)
- Oracle exp导出加where指定条件
- Oracle insert 插入数据 加 where 条件
- mysql where 条件中加 if 判断
- 封装MySQL操作时Where条件语句的组织
- 在MySQL中阻止UPDATE, DELETE 语句的执行，在没有添加WHERE条件
- 数据库一个表更新时没有加where 语句，把数据库恢复到执行update前的过程如下
- UPDATE语句中的WHERE条件
- Oracle update......where......子句
- oracle where 后面的条件中|| 是什么意思
- exp 导出加where 条件
- MySQL是如何优化WHERE条件的
- MySQL中where条件的执行分析
- 条件加在where 和on后面的特殊比较
- 占位
- 占位
- 快速上线—— 开发、运维和敏捷迭代（社区活动总结）
- Leetcode 162. Find Peak Element
- [oc实战练习一]2倍图与3倍图
- mysql update忘加where条件的操作恢复(模拟oracle闪回)
- mysql delete错误使用where条件的操作恢复(模拟oracle闪回)
- atom 快捷键
- PHP 汉字转拼音工具
- 深入理解Yii2.0（1） 属性
- 正则表达式引擎测试笔记
- 第3,6,7,10章读书笔记
- 下一个较大元素
- 操作系统时间改变之后,OGG Veridata应该进行的处理步骤
