关闭

mysql update忘加where条件的操作恢复(模拟oracle闪回)

640人阅读 评论(0) 收藏 举报
分类:


先准备数据:

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 |
+----+------+-------+-------+

被错误更新的数据已找回来了。

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:153300次
    • 积分:3311
    • 等级:
    • 排名:第10897名
    • 原创:184篇
    • 转载:2篇
    • 译文:1篇
    • 评论:26条
    最新评论