MySQL 误操作后数据恢复(update,delete忘加where条件)
在数据库日常维护中,开发人员是最让人头痛的,很多时候都会由于SQL语句写的有问题导致服务器出问题,导致资源耗尽。最危险的操作就是在做DML操作的时候忘加where条件,导致全表更新,这是作为运维或者DBA的我们改如何处理呢?下面我分别针对update和delete操作忘加where条件导致全表更新的处理方法。
一. update 忘加where条件误操作恢复数据(binglog格式必须是ROW)
1.创建测试用的数据表
create table t1 (
id int unsigned not null auto_increment,
name char(20) not null,
sex enum('f','m') not null default 'm',
address varchar(30) not null,
primary key(id)
);
2.插入测试数据
insert into t1 (name,sex,address)values('daiiy','m','guangzhou');
insert into t1 (name,sex,address)values('tom','f','shanghai');
insert into t1 (name,sex,address)values('liany','m','beijing');
insert into t1 (name,sex,address)values('lilu','m','zhuhai');
3.现在需要将id等于2的用户的地址改为zhuhai,update时没有添加where条件
select * from t1;
update t1 set address='爱迪达';
select * from t1;
4.开始恢复,在线上的话,应该比较复杂,要先进行锁表,以免数据再次被污染。(锁表,查看正在写哪个二进制日志)
lock tables t1 read ;
show master status;
5.分析二进制日志,并且在其中找到相关记录,在更新时是address='zhuhai',我们可以在日志中过滤出来。
mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000024 | grep -B 15 '爱迪达'
/*!*/;
# at 3361718
# at 3361769
#200921 11:33:54 server id 1 end_log_pos 3361769 Table_map: `walle`.`t1` mapped to number 6599890
#200921 11:33:54 server id 1 end_log_pos 3361963 Update_rows: table id 6599890 flags: STMT_END_F
### UPDATE `walle`.`t1`
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='daiiy' /* STRING(80) meta=65104 nullable=0 is_null=0 */
### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='guangzhou' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='daiiy' /* STRING(80) meta=65104 nullable=0 is_null=0 */
### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='爱迪达' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
### UPDATE `walle`.`t1`
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='tom' /* STRING(80) meta=65104 nullable=0 is_null=0 */
### @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='shanghai' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
### SET
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='tom' /* STRING(80) meta=65104 nullable=0 is_null=0 */
### @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='爱迪达' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
### UPDATE `walle`.`t1`
### WHERE
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='liany' /* STRING(80) meta=65104 nullable=0 is_null=0 */
### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='beijing' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
### SET
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='liany' /* STRING(80) meta=65104 nullable=0 is_null=0 */
### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='爱迪达' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
### UPDATE `walle`.`t1`
### WHERE
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2='lilu' /* STRING(80) meta=65104 nullable=0 is_null=0 */
### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='zhuhai' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
### SET
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2='lilu' /* STRING(80) meta=65104 nullable=0 is_null=0 */
### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='爱迪达' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
[root@idc-zq-kvm-dev-004 zqrd-rw]#
里面记录了每一行的变化,这也是binglog格式要一定是row才行的原因。其中@1,@2,@3,@4,分别对应表中id,name,sex,address字段。相信大家看到这里有点明白了吧,对,没错,你猜到了,我们将相关记录转换为sql语句,重新导入数据库。
6.处理分析处理的二进制日志
mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000024 | sed -n '/# at 3361769/,/COMMIT/p' > t1.txt
cat t1.txt
# at 3361769
#200921 11:33:54 server id 1 end_log_pos 3361769 Table_map: `walle`.`t1` mapped to number 6599890
#200921 11:33:54 server id 1 end_log_pos 3361963 Update_rows: table id 6599890 flags: STMT_END_F
### UPDATE `walle`.`t1`
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='daiiy' /* STRING(80) meta=65104 nullable=0 is_null=0 */
### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='guangzhou' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='daiiy' /* STRING(80) meta=65104 nullable=0 is_null=0 */
### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='爱迪达' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
### UPDATE `walle`.`t1`
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='tom' /* STRING(80) meta=65104 nullable=0 is_null=0 */
### @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='shanghai' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
### SET
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='tom' /* STRING(80) meta=65104 nullable=0 is_null=0 */
### @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='爱迪达' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
### UPDATE `walle`.`t1`
### WHERE
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='liany' /* STRING(80) meta=65104 nullable=0 is_null=0 */
### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='beijing' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
### SET
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='liany' /* STRING(80) meta=65104 nullable=0 is_null=0 */
### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='爱迪达' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
### UPDATE `walle`.`t1`
### WHERE
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2='lilu' /* STRING(80) meta=65104 nullable=0 is_null=0 */
### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='zhuhai' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
### SET
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2='lilu' /* STRING(80) meta=65104 nullable=0 is_null=0 */
### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='爱迪达' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */
# at 3361963
#200921 11:33:54 server id 1 end_log_pos 3361990 Xid = 238769211
COMMIT/*!*/;
这里sed有点复杂,需要童鞋们好好自己研究研究,这里我就不多说了。
sed '/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}' t1.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
cat recover.sql
UPDATE `walle`.`t1`
SET
@1=1 ,
@2='daiiy' ,
@3=2 ,
@4='guangzhou' ,
WHERE
@1=1 ;
UPDATE `walle`.`t1`
SET
@1=2 ,
@2='tom' ,
@3=1 ,
@4='shanghai' ,
WHERE
@1=2 ;
UPDATE `walle`.`t1`
SET
@1=3 ,
@2='liany' ,
@3=2 ,
@4='beijing' ,
WHERE
@1=3 ;
UPDATE `walle`.`t1`
SET
@1=4 ,
@2='lilu' ,
@3=2 ,
@4='zhuhai' ,
WHERE
@1=4 ;
将文件中的@1,@2,@3,@4替换为t1表中id,name,sex,address字段,并删除最后字段的","号
sed -i 's/@1/id/g;s/@2/name/g;s/@3/sex/g;s/@4/address/g' recover.sql
sed -i -r 's/(address=.*),/\1/g' recover.sql
cat recover.sql
7.到这里日志就处理好了,现在导入即可(导入数据后,解锁表);
source recover.sql;
select * from t1;
可以看见数据已经完全恢复,这种方法的优点是快速,方便。
二. delete 忘加where条件误删除恢复(binglog格式必须是ROW)
其实这和update忘加条件差不多,不过这处理更简单,这里就用上面那张表做测试吧
1.模拟误删除数据
select * from t1;
delete from t1;
2.在binglog中去查找相关记录
mysqlbinlog --no-defaults --base64-output=decode-rows -v -v mysql-bin.000024 | sed -n '/### DELETE FROM db01.t1/,/COMMIT/p' > delete.txt
cat delete.txt
3.将记录转换为SQL语句
cat delete.txt | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;' | sed -r 's/(@4.*),/\1;/g' | sed 's/@[1-9]=//g' > t1.sql
cat t1.sql
4.导入数据,验证数据完整性
source t1.sql;
select * from t1;
到这里数据就完整回来了。将binglog格式设置为row有利有弊,好处是记录了每一行的实际变化,在主从复制时也不容易出问题。但是由于记录每行的变化,会占用大量磁盘,主从复制时带宽占用会有所消耗。到底是使用row还是mixed,需要在实际工作中自己去衡量,但从整体上来说,binglog的格式设置为row,都是不二的选择。
总结:
所以在数据库操作的过程中我们需要格外小心,当然开发那边我们需要做好权限的控制,不过有一个参数可以解决我们的问题,让我们不用担心类似的问题发生:
在[mysql]段落开启这个参数:
safe-updates
这样当我们在做DML操作时忘记加where条件时,mysqld服务器是不会执行操作的:
select * from t1;
+----+------------------+
| id | name |
+----+------------------+
| 1 | yayun |
| 2 | atlas |
| 3 | mysql |
| 6 | good yayun heheh |
+----+------------------+
4 rows in set (0.00 sec)
delete from t1;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column