update、delete没有带where条件,误操作,如何恢复呢?
我现在有一张学生表,我要把小于60更新成不及格。
- mysql> 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.02 sec)
结果,忘带where条件了,
- mysql> update student set score='failure';
- Query OK, 8 rows affected (0.11 sec)
- Rows matched: 8 Changed: 8 Warnings: 0
-
- mysql> select * from student;
- +----+------+-------+---------+
- | id | name | class | score |
- +----+------+-------+---------+
- | 1 | a | 1 | failure |
- | 2 | b | 1 | failure |
- | 3 | c | 2 | failure |
- | 4 | d | 2 | failure |
- | 5 | e | 3 | failure |
- | 6 | f | 3 | failure |
- | 7 | g | 4 | failure |
- | 8 | h | 4 | failure |
- +----+------+-------+---------+
- 8 rows in set (0.01 sec)
把整张表的记录都给更新成不及格了。
传统的方法是:利用最近的全量备份+增量binlog备份,恢复到误操作之前的状态,那么随着表的记录增大,binlog的增多,恢复起来很费时费力。
现在通过一个简单的方法,可以恢复到误操作之前的状态。
我的binlog日志设置为binlog_format = ROW,如果是STATEMENT,这个方法是无效的。切记!!!
首先,创建一个普通权限的账号(切记不能是SUPER权限),例如:
- GRANT ALL PRIVILEGES ON yourDB.* TO 'admin_read_only'@'%' IDENTIFIED BY '123456';
- flush privileges;
把read_only打开,设置数据库只读,
- mysql> set global read_only = 1;
- Query OK, 0 rows affected (0.01 sec)
把刚才创建的admin_read_only账号给运维,让运维把前端程序(PHP/JSP/.NET等)的用户名改下,然后重启前端程序(PHP/JSP/.NET等),这样再连接进来的用户对数据库的访问只能读不能写,保证恢复的一致性。
通过binlog先找到那条语句
- [root@M1 data]# /usr/local/mysql/bin/mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS
- mysql-bin.000001 | grep -B 15 'failure'| more
- /*!*/;
- # at 192
- #121124 23:55:15 server id 25 end_log_pos 249 CRC32 0x83a12fbc Table_map: `test`.`student` mapped to number 76
- # at 249
- #121124 23:55:15 server id 25 end_log_pos 549 CRC32 0xcf7d2635 Update_rows: table id 76 flags: STMT_END_F
- ### UPDATE test.student
- ### WHERE
- ### @11=1 /* INT meta=0 nullable=0 is_null=0 */
- ### @2='a' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */
- ### @3=1 /* INT meta=0 nullable=1 is_null=0 */
- ### @4='56' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
- ### SET
- ### @11=1 /* INT meta=0 nullable=0 is_null=0 */
- ### @2='a' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */
- ### @3=1 /* INT meta=0 nullable=1 is_null=0 */
- ### @4='failure' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
- ### UPDATE test.student
- ### WHERE
- ### @1=2 /* INT meta=0 nullable=0 is_null=0 */
- ### @2='b' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */
- ### @3=1 /* INT meta=0 nullable=1 is_null=0 */
- ### @4='61' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
- ### SET
- ### @1=2 /* INT meta=0 nullable=0 is_null=0 */
- ### @2='b' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */
- ### @3=1 /* INT meta=0 nullable=1 is_null=0 */
- ### @4='failure' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
- --More--
然后把那条binlog给导出来
- [root@M1 data]# /usr/local/mysql/bin/mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS
- mysql-bin.000001 | sed -n '/# at 249/,/COMMIT/p' > /opt/1.txt
- [root@M1 data]#
- [root@M1 data]# more /opt/1.txt
- # at 249
- #121124 23:55:15 server id 25 end_log_pos 549 CRC32 0xcf7d2635 Update_rows: table id 76 flags: STMT_END_F
- ### UPDATE test.student
- ### WHERE
- ### @11=1 /* INT meta=0 nullable=0 is_null=0 */
- ### @2='a' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */
- ### @3=1 /* INT meta=0 nullable=1 is_null=0 */
- ### @4='56' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
- ### SET
- ### @11=1 /* INT meta=0 nullable=0 is_null=0 */
- ### @2='a' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */
- ### @3=1 /* INT meta=0 nullable=1 is_null=0 */
- ### @4='failure' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
- ### UPDATE test.student
- ### WHERE
- ### @1=2 /* INT meta=0 nullable=0 is_null=0 */
- ### @2='b' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */
- ### @3=1 /* INT meta=0 nullable=1 is_null=0 */
- ### @4='61' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
- ### SET
- ### @1=2 /* INT meta=0 nullable=0 is_null=0 */
- ### @2='b' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */
- ### @3=1 /* INT meta=0 nullable=1 is_null=0 */
- ### @4='failure' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
- ### UPDATE test.student
- ### WHERE
- ### @1=3 /* INT meta=0 nullable=0 is_null=0 */
- ### @2='c' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */
- ### @3=2 /* INT meta=0 nullable=1 is_null=0 */
- ### @4='78' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
- ### SET
- ### @1=3 /* INT meta=0 nullable=0 is_null=0 */
- ### @2='c' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */
- ### @3=2 /* INT meta=0 nullable=1 is_null=0 */
- ### @4='failure' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
- ### UPDATE test.student
- ### WHERE
- ### @1=4 /* INT meta=0 nullable=0 is_null=0 */
- ### @2='d' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */
- ### @3=2 /* INT meta=0 nullable=1 is_null=0 */
- ### @4='45' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
- ### SET
- ### @1=4 /* INT meta=0 nullable=0 is_null=0 */
- ### @2='d' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */
- ### @3=2 /* INT meta=0 nullable=1 is_null=0 */
- ### @4='failure' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
- ### UPDATE test.student
- ### WHERE
- ### @1=5 /* INT meta=0 nullable=0 is_null=0 */
- ### @2='e' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */
- ### @33=3 /* INT meta=0 nullable=1 is_null=0 */
- ### @4='76' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
- ### SET
- ### @1=5 /* INT meta=0 nullable=0 is_null=0 */
- ### @2='e' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */
- ### @33=3 /* INT meta=0 nullable=1 is_null=0 */
- ### @4='failure' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
- ### UPDATE test.student
- ### WHERE
- ### @1=6 /* INT meta=0 nullable=0 is_null=0 */
- ### @2='f' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */
- ### @33=3 /* INT meta=0 nullable=1 is_null=0 */
- ### @4='89' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
- ### SET
- ### @1=6 /* INT meta=0 nullable=0 is_null=0 */
- ### @2='f' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */
- ### @33=3 /* INT meta=0 nullable=1 is_null=0 */
- ### @4='failure' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
- ### UPDATE test.student
- ### WHERE
- ### @1=7 /* INT meta=0 nullable=0 is_null=0 */
- ### @2='g' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */
- ### @3=4 /* INT meta=0 nullable=1 is_null=0 */
- ### @4='43' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
- ### SET
- ### @1=7 /* INT meta=0 nullable=0 is_null=0 */
- ### @2='g' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */
- ### @3=4 /* INT meta=0 nullable=1 is_null=0 */
- ### @4='failure' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
- ### UPDATE test.student
- ### WHERE
- ### @1=8 /* INT meta=0 nullable=0 is_null=0 */
- ### @2='h' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */
- ### @3=4 /* INT meta=0 nullable=1 is_null=0 */
- ### @4='90' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
- ### SET
- ### @1=8 /* INT meta=0 nullable=0 is_null=0 */
- ### @2='h' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */
- ### @3=4 /* INT meta=0 nullable=1 is_null=0 */
- ### @4='failure' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
- # at 549
- #121124 23:55:15 server id 25 end_log_pos 580 CRC32 0x378c91b0 Xid = 531
- COMMIT/*!*/;
- [root@M1 data]#
其中,这些是误操作之前的数据
- ### @1=8 /* INT meta=0 nullable=0 is_null=0 */
- ### @2='h' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */
- ### @3=4 /* INT meta=0 nullable=1 is_null=0 */
- ### @4='90' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
这些是误操作之后的数据
- ### @1=8 /* INT meta=0 nullable=0 is_null=0 */
- ### @2='h' /* VARSTRING(18) meta=18 nullable=1 is_null=0 */
- ### @3=4 /* INT meta=0 nullable=1 is_null=0 */
- ### @4='failure' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
这里,@1/@2/@3/@4对应的表字段是id,name,class,score
现在,就要进行最后一步的恢复操作了,只需把这些binlog转换成SQL语句,然后将其导入进去。
- [root@M1 opt]# sed '/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}' 1.txt
- | 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
- [root@M1 opt]#
- [root@M1 opt]# cat recover.sql
- UPDATE test.student
- SET
- @11=1 ,
- @2='a' ,
- @3=1 ,
- @4='56' ,
- WHERE
- @11=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' ,
- WHERE
- @1=4 ;
- UPDATE test.student
- SET
- @1=5 ,
- @2='e' ,
- @33=3 ,
- @4='76' ,
- WHERE
- @1=5 ;
- UPDATE test.student
- SET
- @1=6 ,
- @2='f' ,
- @33=3 ,
- @4='89' ,
- WHERE
- @1=6 ;
- UPDATE test.student
- SET
- @1=7 ,
- @2='g' ,
- @3=4 ,
- @4='43' ,
- WHERE
- @1=7 ;
- UPDATE test.student
- SET
- @1=8 ,
- @2='h' ,
- @3=4 ,
- @4='90' ,
- WHERE
- @1=8 ;
- [root@M1 opt]#
再把@1/@2/@3/@4对应的表字段是id,name,class,score,替换掉
- [root@M1 opt]# sed -i 's/@1/id/g;s/@2/name/g;s/@3/class/g;s/@4/score/g' recover.sql
- [root@M1 opt]# sed -i -r 's/(score=.*),/\1/g' recover.sql
[root@M1 opt]#
[root@M1 opt]# 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 ;
[root@M1 opt]#
OK。最激动人心的一幕到来了,我们进行恢复:
- mysql> select * from student;
- +----+------+-------+---------+
- | id | name | class | score |
- +----+------+-------+---------+
- | 1 | a | 1 | failure |
- | 2 | b | 1 | failure |
- | 3 | c | 2 | failure |
- | 4 | d | 2 | failure |
- | 5 | e | 3 | failure |
- | 6 | f | 3 | failure |
- | 7 | g | 4 | failure |
- | 8 | h | 4 | failure |
- +----+------+-------+---------+
- 8 rows in set (0.02 sec)
-
- mysql> source /opt/recover.sql
- Query OK, 1 row affected (0.11 sec)
- Rows matched: 1 Changed: 1 Warnings: 0
-
- Query OK, 1 row affected (0.95 sec)
- Rows matched: 1 Changed: 1 Warnings: 0
-
- Query OK, 1 row affected (0.16 sec)
- Rows matched: 1 Changed: 1 Warnings: 0
-
- Query OK, 1 row affected (0.03 sec)
- Rows matched: 1 Changed: 1 Warnings: 0
-
- Query OK, 1 row affected (0.80 sec)
- Rows matched: 1 Changed: 1 Warnings: 0
-
- Query OK, 1 row affected (0.08 sec)
- Rows matched: 1 Changed: 1 Warnings: 0
-
- Query OK, 1 row affected (0.09 sec)
- Rows matched: 1 Changed: 1 Warnings: 0
-
- Query OK, 1 row affected (0.07 sec)
- Rows matched: 1 Changed: 1 Warnings: 0
-
- mysql> 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.02 sec)
-
- mysql>
大功告成!
你也快动手试试吧!