在資料庫日常維護中,開發人員是最讓人頭痛的,很多時候都會由于SQL語句寫的有問題導緻伺服器出問題,導緻資源耗盡。最危險的操作就是在做DML操作的時候忘加where條件,導緻全表更新,這是作為運維或者DBA的我們改如何處理呢?下面我分别針對update和delete操作忘加where條件導緻全表更新的處理方法。
一. update 忘加where條件誤操作恢複資料(binglog格式必須是ROW)
1.建立測試用的資料表
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
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>
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
2.插入測試資料
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
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>
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
3.現在需要将id等于2的使用者的位址改為zhuhai,update時沒有添加where條件
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
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 address='zhuhai';
Query OK, 3 rows affected (0.09 sec)
Rows matched: 4 Changed: 3 Warnings: 0
mysql> 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>
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
4.開始恢複,線上上的話,應該比較複雜,要先進行鎖表,以免資料再次被污染。(鎖表,檢視正在寫哪個二進制日志)
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
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>
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
5.分析二進制日志,并且在其中找到相關記錄,在更新時是address='zhuhai',我們可以在日志中過濾出來。
[root@localhost mysql]# mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000024 | grep -B 15 'zhuhai'
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
# 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
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='daiiy' /* STRING(60) meta=65084 nullable=0 is_null=0 */
### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='guangzhou' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='daiiy' /* STRING(60) meta=65084 nullable=0 is_null=0 */
### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='zhuhai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### UPDATE db01.t1
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='tom' /* STRING(60) meta=65084 nullable=0 is_null=0 */
### @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='shanghai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### SET
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='tom' /* STRING(60) meta=65084 nullable=0 is_null=0 */
### @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='zhuhai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### UPDATE db01.t1
### WHERE
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='liany' /* STRING(60) meta=65084 nullable=0 is_null=0 */
### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='beijing' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### SET
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='liany' /* STRING(60) meta=65084 nullable=0 is_null=0 */
### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='zhuhai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
可以看見裡面記錄了每一行的變化,這也是binglog格式要一定是row才行的原因。其中@1,@2,@3,@4,分别對應表中id,name,sex,address字段。相信大家看到這裡有點明白了吧,對,沒錯,你猜到了,我們将相關記錄轉換為sql語句,重新導入資料庫。
6.處理分析處理的二進制日志
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
[root@localhost mysql]# mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000024 | sed -n '/# at 1679/,/COMMIT/p' > t1.txt
[root@localhost 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
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='daiiy' /* STRING(60) meta=65084 nullable=0 is_null=0 */
### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='guangzhou' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='daiiy' /* STRING(60) meta=65084 nullable=0 is_null=0 */
### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='zhuhai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### UPDATE db01.t1
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='tom' /* STRING(60) meta=65084 nullable=0 is_null=0 */
### @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='shanghai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### SET
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='tom' /* STRING(60) meta=65084 nullable=0 is_null=0 */
### @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='zhuhai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### UPDATE db01.t1
### WHERE
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='liany' /* STRING(60) meta=65084 nullable=0 is_null=0 */
### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='beijing' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### SET
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='liany' /* STRING(60) meta=65084 nullable=0 is_null=0 */
### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='zhuhai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
# at 1825
#140305 10:52:24 server id 1 end_log_pos 1852 Xid = 26
COMMIT/*!*/;
[root@localhost mysql]#
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
這裡sed有點複雜,需要童鞋們好好自己研究研究,這裡我就不多說了。
[root@localhost 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
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
[root@localhost mysql]# cat recover.sql
UPDATE db01.t1
SET
@1=1 ,
@2='daiiy' ,
@3=2 ,
@4='guangzhou' ,
WHERE
@1=1 ;
UPDATE db01.t1
SET
@1=2 ,
@2='tom' ,
@3=1 ,
@4='shanghai' ,
WHERE
@1=2 ;
UPDATE db01.t1
SET
@1=3 ,
@2='liany' ,
@3=2 ,
@4='beijing' ,
WHERE
@1=3 ;
[root@localhost mysql]#
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
将檔案中的@1,@2,@3,@4替換為t1表中id,name,sex,address字段,并删除最後字段的","号
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
[root@localhost mysql]# sed -i 's/@1/id/g;s/@2/name/g;s/@3/sex/g;s/@4/address/g' recover.sql
[root@localhost mysql]# sed -i -r 's/(address=.*),/\1/g' recover.sql
[root@localhost mysql]# cat recover.sql
UPDATE db01.t1
SET
id=1 ,
name='daiiy' ,
sex=2 ,
address='guangzhou'
WHERE
id=1 ;
UPDATE db01.t1
SET
id=2 ,
name='tom' ,
sex=1 ,
address='shanghai'
WHERE
id=2 ;
UPDATE db01.t1
SET
id=3 ,
name='liany' ,
sex=2 ,
address='beijing'
WHERE
id=3 ;
[root@localhost mysql]#
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
7.到這裡日志就處理好了,現在導入即可(導入資料後,解鎖表);
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
mysql> source recover.sql;
Query OK, 1 row affected (0.12 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
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>
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
可以看見資料已經完全恢複,這種方法的優點是快速,友善。
二. delete 忘加where條件誤删除恢複(binglog格式必須是ROW)
其實這和update忘加條件差不多,不過這處理更簡單,這裡就用上面那張表做測試吧
1.模拟誤删除資料
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
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>
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
2.在binglog中去查找相關記錄
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
[root@localhost mysql]# mysqlbinlog --no-defaults --base64-output=decode-rows -v -v mysql-bin.000024 | sed -n '/### DELETE FROM db01.t1/,/COMMIT/p' > delete.txt
[root@localhost mysql]# cat delete.txt
### DELETE FROM db01.t1
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='daiiy' /* STRING(60) meta=65084 nullable=0 is_null=0 */
### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='guangzhou' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### DELETE FROM db01.t1
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='tom' /* STRING(60) meta=65084 nullable=0 is_null=0 */
### @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='shanghai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### DELETE FROM db01.t1
### WHERE
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='liany' /* STRING(60) meta=65084 nullable=0 is_null=0 */
### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='beijing' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### DELETE FROM db01.t1
### WHERE
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2='lilu' /* STRING(60) meta=65084 nullable=0 is_null=0 */
### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='zhuhai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
# at 2719
#140305 11:41:00 server id 1 end_log_pos 2746 Xid = 78
COMMIT/*!*/;
[root@localhost mysql]#
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
3.将記錄轉換為SQL語句
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
[root@localhost 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
[root@localhost mysql]# cat t1.sql
INSERT INTO db01.t1
SELECT
1 ,
'daiiy' ,
2 ,
'guangzhou' ;
INSERT INTO db01.t1
SELECT
2 ,
'tom' ,
1 ,
'shanghai' ;
INSERT INTO db01.t1
SELECT
3 ,
'liany' ,
2 ,
'beijing' ;
INSERT INTO db01.t1
SELECT
4 ,
'lilu' ,
2 ,
'zhuhai' ;
[root@localhost mysql]#
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
4.導入資料,驗證資料完整性
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
mysql> source t1.sql;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> 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>
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
到這裡資料就完整回來了。将binglog格式設定為row有利有弊,好處是記錄了每一行的實際變化,在主從複制時也不容易出問題。但是由于記錄每行的變化,會占用大量磁盤,主從複制時帶寬占用會有所消耗。到底是使用row還是mixed,需要在實際工作中自己去衡量,但從整體上來說,binglog的格式設定為row,都是不二的選擇。
總結:
是以在資料庫操作的過程中我們需要格外小心,當然開發那邊我們需要做好權限的控制,不過有一個參數可以解決我們的問題,讓我們不用擔心類似的問題發生:
在[mysql]段落開啟這個參數:
safe-updates
這樣當我們在做DML操作時忘記加where條件時,mysqld伺服器是不會執行操作的:
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
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://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
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>
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
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>
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
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 address='zhuhai';
Query OK, 3 rows affected (0.09 sec)
Rows matched: 4 Changed: 3 Warnings: 0
mysql> 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>
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
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>
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
[root@localhost mysql]# mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000024 | grep -B 15 'zhuhai'
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
# 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
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='daiiy' /* STRING(60) meta=65084 nullable=0 is_null=0 */
### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='guangzhou' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='daiiy' /* STRING(60) meta=65084 nullable=0 is_null=0 */
### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='zhuhai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### UPDATE db01.t1
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='tom' /* STRING(60) meta=65084 nullable=0 is_null=0 */
### @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='shanghai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### SET
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='tom' /* STRING(60) meta=65084 nullable=0 is_null=0 */
### @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='zhuhai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### UPDATE db01.t1
### WHERE
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='liany' /* STRING(60) meta=65084 nullable=0 is_null=0 */
### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='beijing' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### SET
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='liany' /* STRING(60) meta=65084 nullable=0 is_null=0 */
### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='zhuhai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
[root@localhost mysql]# mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000024 | sed -n '/# at 1679/,/COMMIT/p' > t1.txt
[root@localhost 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
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='daiiy' /* STRING(60) meta=65084 nullable=0 is_null=0 */
### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='guangzhou' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='daiiy' /* STRING(60) meta=65084 nullable=0 is_null=0 */
### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='zhuhai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### UPDATE db01.t1
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='tom' /* STRING(60) meta=65084 nullable=0 is_null=0 */
### @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='shanghai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### SET
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='tom' /* STRING(60) meta=65084 nullable=0 is_null=0 */
### @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='zhuhai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### UPDATE db01.t1
### WHERE
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='liany' /* STRING(60) meta=65084 nullable=0 is_null=0 */
### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='beijing' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### SET
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='liany' /* STRING(60) meta=65084 nullable=0 is_null=0 */
### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='zhuhai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
# at 1825
#140305 10:52:24 server id 1 end_log_pos 1852 Xid = 26
COMMIT/*!*/;
[root@localhost mysql]#
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
[root@localhost 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
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
[root@localhost mysql]# cat recover.sql
UPDATE db01.t1
SET
@1=1 ,
@2='daiiy' ,
@3=2 ,
@4='guangzhou' ,
WHERE
@1=1 ;
UPDATE db01.t1
SET
@1=2 ,
@2='tom' ,
@3=1 ,
@4='shanghai' ,
WHERE
@1=2 ;
UPDATE db01.t1
SET
@1=3 ,
@2='liany' ,
@3=2 ,
@4='beijing' ,
WHERE
@1=3 ;
[root@localhost mysql]#
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
[root@localhost mysql]# sed -i 's/@1/id/g;s/@2/name/g;s/@3/sex/g;s/@4/address/g' recover.sql
[root@localhost mysql]# sed -i -r 's/(address=.*),/\1/g' recover.sql
[root@localhost mysql]# cat recover.sql
UPDATE db01.t1
SET
id=1 ,
name='daiiy' ,
sex=2 ,
address='guangzhou'
WHERE
id=1 ;
UPDATE db01.t1
SET
id=2 ,
name='tom' ,
sex=1 ,
address='shanghai'
WHERE
id=2 ;
UPDATE db01.t1
SET
id=3 ,
name='liany' ,
sex=2 ,
address='beijing'
WHERE
id=3 ;
[root@localhost mysql]#
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
mysql> source recover.sql;
Query OK, 1 row affected (0.12 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
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>
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
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>
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
[root@localhost mysql]# mysqlbinlog --no-defaults --base64-output=decode-rows -v -v mysql-bin.000024 | sed -n '/### DELETE FROM db01.t1/,/COMMIT/p' > delete.txt
[root@localhost mysql]# cat delete.txt
### DELETE FROM db01.t1
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='daiiy' /* STRING(60) meta=65084 nullable=0 is_null=0 */
### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='guangzhou' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### DELETE FROM db01.t1
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='tom' /* STRING(60) meta=65084 nullable=0 is_null=0 */
### @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='shanghai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### DELETE FROM db01.t1
### WHERE
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2='liany' /* STRING(60) meta=65084 nullable=0 is_null=0 */
### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='beijing' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
### DELETE FROM db01.t1
### WHERE
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2='lilu' /* STRING(60) meta=65084 nullable=0 is_null=0 */
### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
### @4='zhuhai' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
# at 2719
#140305 11:41:00 server id 1 end_log_pos 2746 Xid = 78
COMMIT/*!*/;
[root@localhost mysql]#
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
[root@localhost 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
[root@localhost mysql]# cat t1.sql
INSERT INTO db01.t1
SELECT
1 ,
'daiiy' ,
2 ,
'guangzhou' ;
INSERT INTO db01.t1
SELECT
2 ,
'tom' ,
1 ,
'shanghai' ;
INSERT INTO db01.t1
SELECT
3 ,
'liany' ,
2 ,
'beijing' ;
INSERT INTO db01.t1
SELECT
4 ,
'lilu' ,
2 ,
'zhuhai' ;
[root@localhost mysql]#
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
mysql> source t1.sql;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> 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>
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
safe-updates
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiYWan5SZk92Y5B3bj9CXzV2Zh1WavwVbvNmLzd2bsJmbj5ibv1WbvN2Lc9CX6MHc0RHaiojIsJye.gif)
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>