自己的主机做服务器网站如何备案,星外网站开发,天津个人做网站,多语言做网站在数据库日常维护中#xff0c;开发人员是最让人头痛的#xff0c;很多时候都会由于SQL语句写的有问题导致服务器出问题#xff0c;导致资源耗尽。最危险的操作就是在做DML操作的时候忘加where条件#xff0c;导致全表更新#xff0c;这是作为运维或者DBA的我们改如何处理… 在数据库日常维护中开发人员是最让人头痛的很多时候都会由于SQL语句写的有问题导致服务器出问题导致资源耗尽。最危险的操作就是在做DML操作的时候忘加where条件导致全表更新这是作为运维或者DBA的我们改如何处理呢下面我分别针对update和delete操作忘加where条件导致全表更新的处理方法。
一. update 忘加where条件误操作恢复数据(binglog格式必须是ROW)
1.创建测试用的数据表 mysql 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)- );
Query OK, 0 rows affected (0.31 sec)mysql 2.插入测试数据 mysql insert into t1 (name,sex,address)values(daiiy,m,guangzhou);
Query OK, 1 row affected (0.01 sec)mysql insert into t1 (name,sex,address)values(tom,f,shanghai);
Query OK, 1 row affected (0.00 sec)mysql insert into t1 (name,sex,address)values(liany,m,beijing);
Query OK, 1 row affected (0.00 sec)mysql insert into t1 (name,sex,address)values(lilu,m,zhuhai);
Query OK, 1 row affected (0.05 sec)mysql 3.现在需要将id等于2的用户的地址改为zhuhaiupdate时没有添加where条件 mysql select * from t1;
---------------------------
| id | name | sex | address |
---------------------------
| 1 | daiiy | m | guangzhou |
| 2 | tom | f | shanghai |
| 3 | liany | m | beijing |
| 4 | lilu | m | zhuhai |
---------------------------
4 rows in set (0.01 sec)mysql update t1 set addresszhuhai;
Query OK, 3 rows affected (0.09 sec)
Rows matched: 4 Changed: 3 Warnings: 0mysql select * from t1;
-------------------------
| id | name | sex | address |
-------------------------
| 1 | daiiy | m | zhuhai |
| 2 | tom | f | zhuhai |
| 3 | liany | m | zhuhai |
| 4 | lilu | m | zhuhai |
-------------------------
4 rows in set (0.00 sec)mysql 4.开始恢复在线上的话应该比较复杂要先进行锁表以免数据再次被污染。锁表查看正在写哪个二进制日志 mysql lock tables t1 read ;
Query OK, 0 rows affected (0.00 sec)mysql show master status;
------------------------------------------------------------
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
------------------------------------------------------------
| mysql-bin.000024 | 1852 | | |
------------------------------------------------------------
1 row in set (0.00 sec)mysql 5.分析二进制日志并且在其中找到相关记录在更新时是addresszhuhai,我们可以在日志中过滤出来。
[rootlocalhost mysql]# mysqlbinlog --no-defaults -v -v --base64-outputDECODE-ROWS mysql-bin.000024 | grep -B 15 zhuhai # at 1629
# at 1679
#140305 10:52:24 server id 1 end_log_pos 1679 Table_map: db01.t1 mapped to number 38
#140305 10:52:24 server id 1 end_log_pos 1825 Update_rows: table id 38 flags: STMT_END_F
### UPDATE db01.t1
### WHERE
### 11 /* INT meta0 nullable0 is_null0 */
### 2daiiy /* STRING(60) meta65084 nullable0 is_null0 */
### 32 /* ENUM(1 byte) meta63233 nullable0 is_null0 */
### 4guangzhou /* VARSTRING(90) meta90 nullable0 is_null0 */
### SET
### 11 /* INT meta0 nullable0 is_null0 */
### 2daiiy /* STRING(60) meta65084 nullable0 is_null0 */
### 32 /* ENUM(1 byte) meta63233 nullable0 is_null0 */
### 4zhuhai /* VARSTRING(90) meta90 nullable0 is_null0 */
### UPDATE db01.t1
### WHERE
### 12 /* INT meta0 nullable0 is_null0 */
### 2tom /* STRING(60) meta65084 nullable0 is_null0 */
### 31 /* ENUM(1 byte) meta63233 nullable0 is_null0 */
### 4shanghai /* VARSTRING(90) meta90 nullable0 is_null0 */
### SET
### 12 /* INT meta0 nullable0 is_null0 */
### 2tom /* STRING(60) meta65084 nullable0 is_null0 */
### 31 /* ENUM(1 byte) meta63233 nullable0 is_null0 */
### 4zhuhai /* VARSTRING(90) meta90 nullable0 is_null0 */
### UPDATE db01.t1
### WHERE
### 13 /* INT meta0 nullable0 is_null0 */
### 2liany /* STRING(60) meta65084 nullable0 is_null0 */
### 32 /* ENUM(1 byte) meta63233 nullable0 is_null0 */
### 4beijing /* VARSTRING(90) meta90 nullable0 is_null0 */
### SET
### 13 /* INT meta0 nullable0 is_null0 */
### 2liany /* STRING(60) meta65084 nullable0 is_null0 */
### 32 /* ENUM(1 byte) meta63233 nullable0 is_null0 */
### 4zhuhai /* VARSTRING(90) meta90 nullable0 is_null0 */ 可以看见里面记录了每一行的变化这也是binglog格式要一定是row才行的原因。其中1,2,3,4,分别对应表中id,name,sex,address字段。相信大家看到这里有点明白了吧对没错你猜到了我们将相关记录转换为sql语句重新导入数据库。
6.处理分析处理的二进制日志 [rootlocalhost mysql]# mysqlbinlog --no-defaults -v -v --base64-outputDECODE-ROWS mysql-bin.000024 | sed -n /# at 1679/,/COMMIT/p t1.txt
[rootlocalhost mysql]# cat t1.txt
# at 1679
#140305 10:52:24 server id 1 end_log_pos 1679 Table_map: db01.t1 mapped to number 38
#140305 10:52:24 server id 1 end_log_pos 1825 Update_rows: table id 38 flags: STMT_END_F
### UPDATE db01.t1
### WHERE
### 11 /* INT meta0 nullable0 is_null0 */
### 2daiiy /* STRING(60) meta65084 nullable0 is_null0 */
### 32 /* ENUM(1 byte) meta63233 nullable0 is_null0 */
### 4guangzhou /* VARSTRING(90) meta90 nullable0 is_null0 */
### SET
### 11 /* INT meta0 nullable0 is_null0 */
### 2daiiy /* STRING(60) meta65084 nullable0 is_null0 */
### 32 /* ENUM(1 byte) meta63233 nullable0 is_null0 */
### 4zhuhai /* VARSTRING(90) meta90 nullable0 is_null0 */
### UPDATE db01.t1
### WHERE
### 12 /* INT meta0 nullable0 is_null0 */
### 2tom /* STRING(60) meta65084 nullable0 is_null0 */
### 31 /* ENUM(1 byte) meta63233 nullable0 is_null0 */
### 4shanghai /* VARSTRING(90) meta90 nullable0 is_null0 */
### SET
### 12 /* INT meta0 nullable0 is_null0 */
### 2tom /* STRING(60) meta65084 nullable0 is_null0 */
### 31 /* ENUM(1 byte) meta63233 nullable0 is_null0 */
### 4zhuhai /* VARSTRING(90) meta90 nullable0 is_null0 */
### UPDATE db01.t1
### WHERE
### 13 /* INT meta0 nullable0 is_null0 */
### 2liany /* STRING(60) meta65084 nullable0 is_null0 */
### 32 /* ENUM(1 byte) meta63233 nullable0 is_null0 */
### 4beijing /* VARSTRING(90) meta90 nullable0 is_null0 */
### SET
### 13 /* INT meta0 nullable0 is_null0 */
### 2liany /* STRING(60) meta65084 nullable0 is_null0 */
### 32 /* ENUM(1 byte) meta63233 nullable0 is_null0 */
### 4zhuhai /* VARSTRING(90) meta90 nullable0 is_null0 */
# at 1825
#140305 10:52:24 server id 1 end_log_pos 1852 Xid 26
COMMIT/*!*/;
[rootlocalhost mysql]# [rootlocalhost mysql]# 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 功能将where 和set位置对调
命令剖析
/WHERE/ #包含WHERE:a; #创建一个labela
N; #追加下一个输入行到读取行的末尾,读入到模式空间
/SET/!ba; # 如果不是/SET/返回a也就是重复读一直读到/SET/之前buffer的内容是WHERE\n.......\nSET第1步: s #替换命令例如s/a/b 将a替换为b第2步:\([^\n]*\)\n\(.*\)\n\(.*\) \ #转义字符[^\n]* buffer中的where(.*\) #单符号(.)匹配除换行符以外的单个字符*同上[^\n]*\ #代表非换行符回车开头*表示匹配零或多个字符\n #换行第3步\3\n\2\n\1 \3 内存中的set,第三个括号中的内容\2 内存中原来where与set之间的内容第二个括号中的内容\1 内存中的where第一个括号中的内容功能这句做了两个事情1.把字符串### 替换成 空格 2.把/*往后的内容 替换成, s/### //g #将### 替换成空串\ #转义字符\/\*.* #匹配/*之后出换行符外所有内容功能这句把字符串包含7的行中的全部换成空格/7/ #匹配包含7的行s/,// #将,替换为空串g #全部替换
[rootlocalhost mysql]# cat recover.sql
UPDATE db01.t1
SET11 ,2daiiy ,32 ,4guangzhou ,
WHERE11 ;
UPDATE db01.t1
SET12 ,2tom ,31 ,4shanghai ,
WHERE12 ;
UPDATE db01.t1
SET13 ,2liany ,32 ,4beijing ,
WHERE13 ;
[rootlocalhost mysql]# 将文件中的1,2,3,4替换为t1表中idnamesexaddress字段并删除最后字段的,号 [rootlocalhost mysql]# sed -i s/1/id/g;s/2/name/g;s/3/sex/g;s/4/address/g recover.sql
[rootlocalhost mysql]# sed -i -r s/(address.*),/\1/g recover.sql
[rootlocalhost mysql]# cat recover.sql
UPDATE db01.t1
SETid1 ,namedaiiy ,sex2 ,addressguangzhou
WHEREid1 ;
UPDATE db01.t1
SETid2 ,nametom ,sex1 ,addressshanghai
WHEREid2 ;
UPDATE db01.t1
SETid3 ,nameliany ,sex2 ,addressbeijing
WHEREid3 ;
[rootlocalhost mysql]# 7.到这里日志就处理好了现在导入即可导入数据后解锁表 mysql source recover.sql;
Query OK, 1 row affected (0.12 sec)
Rows matched: 1 Changed: 1 Warnings: 0Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql select * from t1;
---------------------------
| id | name | sex | address |
---------------------------
| 1 | daiiy | m | guangzhou |
| 2 | tom | f | shanghai |
| 3 | liany | m | beijing |
| 4 | lilu | m | zhuhai |
---------------------------
4 rows in set (0.00 sec)mysql 可以看见数据已经完全恢复这种方法的优点是快速方便。 二. delete 忘加where条件误删除恢复(binglog格式必须是ROW)
其实这和update忘加条件差不多不过这处理更简单,这里就用上面那张表做测试吧
1.模拟误删除数据 mysql select * from t1;
---------------------------
| id | name | sex | address |
---------------------------
| 1 | daiiy | m | guangzhou |
| 2 | tom | f | shanghai |
| 3 | liany | m | beijing |
| 4 | lilu | m | zhuhai |
---------------------------
4 rows in set (0.00 sec)mysql delete from t1;
Query OK, 4 rows affected (0.03 sec)mysql select * from t1;
Empty set (0.00 sec)mysql 2.在binglog中去查找相关记录 [rootlocalhost mysql]# mysqlbinlog --no-defaults --base64-outputdecode-rows -v -v mysql-bin.000024 | sed -n /### DELETE FROM db01.t1/,/COMMIT/p delete.txt
[rootlocalhost mysql]# cat delete.txt
### DELETE FROM db01.t1
### WHERE
### 11 /* INT meta0 nullable0 is_null0 */
### 2daiiy /* STRING(60) meta65084 nullable0 is_null0 */
### 32 /* ENUM(1 byte) meta63233 nullable0 is_null0 */
### 4guangzhou /* VARSTRING(90) meta90 nullable0 is_null0 */
### DELETE FROM db01.t1
### WHERE
### 12 /* INT meta0 nullable0 is_null0 */
### 2tom /* STRING(60) meta65084 nullable0 is_null0 */
### 31 /* ENUM(1 byte) meta63233 nullable0 is_null0 */
### 4shanghai /* VARSTRING(90) meta90 nullable0 is_null0 */
### DELETE FROM db01.t1
### WHERE
### 13 /* INT meta0 nullable0 is_null0 */
### 2liany /* STRING(60) meta65084 nullable0 is_null0 */
### 32 /* ENUM(1 byte) meta63233 nullable0 is_null0 */
### 4beijing /* VARSTRING(90) meta90 nullable0 is_null0 */
### DELETE FROM db01.t1
### WHERE
### 14 /* INT meta0 nullable0 is_null0 */
### 2lilu /* STRING(60) meta65084 nullable0 is_null0 */
### 32 /* ENUM(1 byte) meta63233 nullable0 is_null0 */
### 4zhuhai /* VARSTRING(90) meta90 nullable0 is_null0 */
# at 2719
#140305 11:41:00 server id 1 end_log_pos 2746 Xid 78
COMMIT/*!*/;
[rootlocalhost mysql]# 3.将记录转换为SQL语句 [rootlocalhost mysql]# 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
[rootlocalhost mysql]# cat t1.sql
INSERT INTO db01.t1
SELECT1 ,daiiy ,2 ,guangzhou ;
INSERT INTO db01.t1
SELECT2 ,tom ,1 ,shanghai ;
INSERT INTO db01.t1
SELECT3 ,liany ,2 ,beijing ;
INSERT INTO db01.t1
SELECT4 ,lilu ,2 ,zhuhai ;
[rootlocalhost mysql]# 4.导入数据验证数据完整性 mysql source t1.sql;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql select * from t1;
ERROR 1046 (3D000): No database selected
mysql select * from db01.t1;
---------------------------
| id | name | sex | address |
---------------------------
| 1 | daiiy | m | guangzhou |
| 2 | tom | f | shanghai |
| 3 | liany | m | beijing |
| 4 | lilu | m | zhuhai |
---------------------------
4 rows in set (0.00 sec)mysql 到这里数据就完整回来了。将binglog格式设置为row有利有弊好处是记录了每一行的实际变化在主从复制时也不容易出问题。但是由于记录每行的变化会占用大量磁盘主从复制时带宽占用会有所消耗。到底是使用row还是mixed需要在实际工作中自己去衡量但从整体上来说binglog的格式设置为row都是不二的选择。
总结
所以在数据库操作的过程中我们需要格外小心当然开发那边我们需要做好权限的控制不过有一个参数可以解决我们的问题让我们不用担心类似的问题发生
在[mysql]段落开启这个参数
safe-updates
这样当我们在做DML操作时忘记加where条件时mysqld服务器是不会执行操作的 mysql select * from t1;
----------------------
| id | name |
----------------------
| 1 | yayun |
| 2 | atlas |
| 3 | mysql |
| 6 | good yayun heheh |
----------------------
4 rows in set (0.00 sec)mysql 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
mysql
https://blog.csdn.net/weixin_33674976/article/details/92863378
