在数据库日常维护中，开发人员是最让人头痛的，很多时候都会由于SQL语句写的有问题导致服务器出问题，导致资源耗尽。最危险的操作就是在做DML操作的时候忘加where条件，导致全表更新，这是作为运维或者DBA的我们改如何处理呢？下面我分别针对update和delete操作忘加where条件导致全表更新的处理方法。
误操作之前数据库是binglog格式必须是ROW状态
一. update 忘加where条件误操作恢复数据(binglog格式必须是ROW)
1.创建测试用的数据表
mysql> create table testdb.student_new(id int,name varchar(20),address varchar(20));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into testdb.student_new values(1,'anzhen','wuhan'),(2,'limi','beijing'),(3,'xiaohong','shenzhen');
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from testdb.student_new;
+------+----------+----------+
| id | name | address |
+------+----------+----------+
| 1 | anzhen | wuhan |
| 2 | limi | beijing |
| 3 | xiaohong | shenzhen |
+------+----------+----------+
3 rows in set (0.00 sec)
2:误操作，在需要将id等于2的用户的地址改为zhuhai，update时没有添加where条件
mysql> update testdb.student_new set address='nanning';
Query OK, 3 rows affected (0.07 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from testdb.student_new;
+------+----------+---------+
| id | name | address |
+------+----------+---------+
| 1 | anzhen | nanning |
| 2 | limi | nanning |
| 3 | xiaohong | nanning |
+------+----------+---------+
3 rows in set (0.00 sec)
4.开始恢复，在线上的话，应该比较复杂，要先进行锁表，以免数据再次被污染。（锁表，查看正在写哪个二进制日志）
mysql> lock tables testdb.student_new read; Query OK, 0 rows affected (0.00 sec)
查看当前的二进制日志，如果在误操作之后没有flush logs的话，那么当前的二进制日志文件包括误操作的数据。如果有刷新二进制日志，往前面的二进制日志找误操作数据。 mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql_bin.000401 | 4459 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
mysql> flush logs;
（5）新开一个session。找出误操作关于的关键字 address='nanning'，同时/usr/local/mysql/bin/mysqlbinlog为全路径。
[root@localhost104 mysql]# cp /usr/local/mysql/data/mysql_bin.000401 /tmp/mysql_bin.000401;
这样把二进制日志复制出来，可以不影响生产的文件和数据库服务。安全起见。
找出误操作数据：
[root@localhost104 mysql]# /usr/local/mysql/bin/mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS /tmp/mysql_bin.000401 | grep -B 15 'nanning'SET TIMESTAMP=1500787611/*!*/;
BEGIN
/*!*/;
# at 4211
#170723 13:26:51 server id 10 end_log_pos 4273 CRC32 0xcd5d99f0 Table_map: `testdb`.`student_new` mapped to number 225
# at 4273
#170723 13:26:51 server id 10 end_log_pos 4428 CRC32 0x7a38195a Update_rows: table id 225 flags: STMT_END_F
### UPDATE `testdb`.`student_new`
### WHERE
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
### @2='anzhen' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @3='wuhan' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### SET
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
### @2='anzhen' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @3='nanning' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### UPDATE `testdb`.`student_new`
### WHERE
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
### @2='limi' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @3='beijing' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### SET
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
### @2='limi' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @3='nanning' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### UPDATE `testdb`.`student_new`
### WHERE
### @1=3 /* INT meta=0 nullable=1 is_null=0 */
### @2='xiaohong' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @3='shenzhen' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### SET
### @1=3 /* INT meta=0 nullable=1 is_null=0 */
### @2='xiaohong' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @3='nanning' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
可以看见里面记录了每一行的变化，这也是binglog格式要一定是row才行的原因。其中@1,@2,@3,分别对应表中id,name,address字段。相信大家看到这里有点明白了吧，对，没错，你猜到了，我们将相关记录转换为sql语句，重新导入数据库。
记住# at 4211事务的位置，用于搜索。
sed -n '/# at 4211/,/COMMIT/p' 表示打印以# at 4211开始，以COMMIT结束的文件
[root@localhost104 mysql]# /usr/local/mysql/bin/mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS /tmp/mysql_bin.000401 | sed -n '/# at 4211/,/COMMIT/p' > /tmp/student_new.txt
[root@localhost104 mysql]# cat /tmp/student_new.txt
# at 4211
#170723 13:26:51 server id 10 end_log_pos 4273 CRC32 0xcd5d99f0 Table_map: `testdb`.`student_new` mapped to number 225
# at 4273
#170723 13:26:51 server id 10 end_log_pos 4428 CRC32 0x7a38195a Update_rows: table id 225 flags: STMT_END_F
### UPDATE `testdb`.`student_new`
### WHERE
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
### @2='anzhen' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @3='wuhan' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### SET
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
### @2='anzhen' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @3='nanning' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### UPDATE `testdb`.`student_new`
### WHERE
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
### @2='limi' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @3='beijing' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### SET
### @1=2 /* INT meta=0 nullable=1 is_null=0 */
### @2='limi' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @3='nanning' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### UPDATE `testdb`.`student_new`
### WHERE
### @1=3 /* INT meta=0 nullable=1 is_null=0 */
### @2='xiaohong' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @3='shenzhen' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### SET
### @1=3 /* INT meta=0 nullable=1 is_null=0 */
### @2='xiaohong' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
### @3='nanning' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */
# at 4428
#170723 13:26:51 server id 10 end_log_pos 4459 CRC32 0x205dbc4a Xid = 177
COMMIT/*!*/;
数据库列数只有3列，但是下面的命令要写成@3如这样/@3/!ba; 下面语句就是把误操作的逆操作。
[root@localhost104 mysql]# sed '/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}' /tmp/student_new.txt | sed -r '/WHERE/{:a;N;/@3/!ba;s/### @2.*//g}' | sed 's/### //g;s/\/\*.*/,/g' | sed '/WHERE/{:a;N;/@1/!ba;s/,/;/g};s/#.*//g;s/COMMIT,//g' | sed '/^$/d' > /tmp/student_new.sql
[root@localhost104 mysql]# cat /tmp/student_new.sql
UPDATE `testdb`.`student_new`
SET
@1=1 ,
@2='anzhen' ,
@3='wuhan' ,
WHERE
@1=1 ;
UPDATE `testdb`.`student_new`
SET
@1=2 ,
@2='limi' ,
@3='beijing' ,
WHERE
@1=2 ;
UPDATE `testdb`.`student_new`
SET
@1=3 ,
@2='xiaohong' ,
@3='shenzhen' ,
WHERE
@1=3 ;
格式不正确，要调格式：把@1，@2，@3换成列名称。顺序不能换。按照数据库来排列。
[root@localhost104 mysql]# sed -i 's/@1/id/g;s/@2/name/g;s/@3/address/g' /tmp/student_new.sql
[root@localhost104 mysql]#
[root@localhost104 mysql]# cat /tmp/student_new.sql
UPDATE `testdb`.`student_new`
SET
id=1 ,
name='anzhen' ,
address='wuhan' ,
WHERE
id=1 ;
UPDATE `testdb`.`student_new`
SET
id=2 ,
name='limi' ,
address='beijing' ,
WHERE
id=2 ;
UPDATE `testdb`.`student_new`
SET
id=3 ,
name='xiaohong' ,
address='shenzhen' ,
WHERE
id=3 ;
删除address列的，号
[root@localhost104 mysql]# sed -i -r 's/(address=.*),/\1/g' /tmp/student_new.sql
[root@localhost104 mysql]#
[root@localhost104 mysql]# cat /tmp/student_new.sql
UPDATE `testdb`.`student_new`
SET
id=1 ,
name='anzhen' ,
address='wuhan'
WHERE
id=1 ;
UPDATE `testdb`.`student_new`
SET
id=2 ,
name='limi' ,
address='beijing'
WHERE
id=2 ;
UPDATE `testdb`.`student_new`
SET
id=3 ,
name='xiaohong' ,
address='shenzhen'
WHERE
id=3 ;
在恢复前要解锁。才可以更新！
mysql> unlock tables;source /tmp/student_new.sql;
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.01 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
mysql>
mysql> select * from `testdb`.`student_new`;
+------+----------+----------+
| id | name | address |
+------+----------+----------+
| 1 | anzhen | wuhan |
| 2 | limi | beijing |
| 3 | xiaohong | shenzhen |
+------+----------+----------+
3 rows in set (0.00 sec)
可以看见数据已经完全恢复，这种方法的优点是快速，方便。
357
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
