UPDATE或者DELETE忘加WHERE条件的恢复

原创
2017/07/17 18:27
阅读数 27

1、建立测试表

mysql> create table test( 
    -> id int not null auto_increment, 
    -> name char(20) not null, 
    -> sex char(4) not null, 
    -> score varchar(10) not null, 
    -> primary key(id) 
    -> ); 
Query OK, 0 rows affected (0.11 sec)

2、插入数据

mysql> insert into test(name,sex,score) values('张三','男',86); 
Query OK, 1 row affected (0.01 sec) 
…… 
mysql> select * from test; 
+----+--------+-----+-------+ 
| id | name   | sex | score | 
+----+--------+-----+-------+ 
|  1 | 张三   | 男  | 86    | 
|  2 | 李四   | 男  | 88    | 
|  3 | 王五   | 男  | 90    | 
|  4 | 麻六   | 男  | 92    | 
|  5 | 小芳   | 女  | 94    | 
|  6 | 小红   | 女  | 100   | 
+----+--------+-----+-------+ 
6 rows in set (0.00 sec)

mysql> update test set sex='tom'; 
Query OK, 6 rows affected (0.00 sec) 
Rows matched: 6  Changed: 6  Warnings: 0 
   
mysql> select * from test; 
+----+--------+-----+-------+ 
| id | name   | sex | score | 
+----+--------+-----+-------+ 
|  1 | 张三   | tom | 86    | 
|  2 | 李四   | tom | 88    | 
|  3 | 王五   | tom | 90    | 
|  4 | 麻六   | tom | 92    | 
|  5 | 小芳   | tom | 94    | 
|  6 | 小红   | tom | 100   | 
+----+--------+-----+-------+ 
6 rows in set (0.00 sec)

3、开始恢复,生产环境锁表,避免数据被再次污染

mysql> lock tables test read; 
Query OK, 0 rows affected (0.00 sec) 
mysql> insert into test(name,sex,score) values('小芳','女',94); 
ERROR 1099 (HY000): Table 'test' was locked with a READ lock and can't be updated 
mysql> show master status; 
+------------------+----------+--------------+------------------+ 
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | 
+------------------+----------+--------------+------------------+ 
| mysql-bin.000001 |     1816 |              |                  | 
+------------------+----------+--------------+------------------+ 
1 row in set (0.00 sec)

4、分析二进制日志

[root@xiaoya data]# mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000001 |grep -B 15 '中' 
BEGIN 
/*!*/; 
# at 1513 
# at 1569 
#160713 13:52:06 server id 1  end_log_pos 1569   Table_map: `students`.`test` mapped to number 33 
#160713 13:52:06 server id 1  end_log_pos 1789   Update_rows: table id 33 flags: STMT_END_F 
### UPDATE `students`.`test` 
### WHERE 
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */ 
###   @2='张三' /* STRING(60) meta=65084 nullable=0 is_null=0 */ 
###   @3='男' /* STRING(12) meta=65036 nullable=0 is_null=0 */ 
###   @4='86' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ 
### SET 
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */ 
###   @2='张三' /* STRING(60) meta=65084 nullable=0 is_null=0 */ 
###   @3='中' /* STRING(12) meta=65036 nullable=0 is_null=0 */ 
###   @4='86' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ 
### UPDATE `students`.`test` 
### WHERE 
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */ 
###   @2='李四' /* STRING(60) meta=65084 nullable=0 is_null=0 */ 
###   @3='男' /* STRING(12) meta=65036 nullable=0 is_null=0 */ 
###   @4='88' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ 
### SET 
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */ 
###   @2='李四' /* STRING(60) meta=65084 nullable=0 is_null=0 */ 
###   @3='中' /* STRING(12) meta=65036 nullable=0 is_null=0 */ 
###   @4='88' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ 
### UPDATE `students`.`test` 
### WHERE 
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */ 
###   @2='王五' /* STRING(60) meta=65084 nullable=0 is_null=0 */ 
###   @3='男' /* STRING(12) meta=65036 nullable=0 is_null=0 */ 
###   @4='90' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ 
### SET 
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */ 
###   @2='王五' /* STRING(60) meta=65084 nullable=0 is_null=0 */ 
###   @3='中' /* STRING(12) meta=65036 nullable=0 is_null=0 */ 
###   @4='90' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ 
### UPDATE `students`.`test` 
### WHERE 
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */ 
###   @2='小红' /* STRING(60) meta=65084 nullable=0 is_null=0 */ 
###   @3='女' /* STRING(12) meta=65036 nullable=0 is_null=0 */ 
###   @4='92' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ 
### SET 
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */ 
###   @2='小红' /* STRING(60) meta=65084 nullable=0 is_null=0 */ 
###   @3='中' /* STRING(12) meta=65036 nullable=0 is_null=0 */ 
###   @4='92' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ 
### UPDATE `students`.`test` 
### WHERE 
###   @1=5 /* INT meta=0 nullable=0 is_null=0 */ 
###   @2='小芳' /* STRING(60) meta=65084 nullable=0 is_null=0 */ 
###   @3='女' /* STRING(12) meta=65036 nullable=0 is_null=0 */ 
###   @4='96' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ 
### SET 
###   @1=5 /* INT meta=0 nullable=0 is_null=0 */ 
###   @2='小芳' /* STRING(60) meta=65084 nullable=0 is_null=0 */ 
###   @3='中' /* STRING(12) meta=65036 nullable=0 is_null=0 */ 

Binlog记录了每一行的变化情况,这里binlog格式必须是row,我们要做的就是把binlog转化成sql重新插入。

6、分析、处理二进制日志

[root@xiaoya data]# mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.000001 |sed -n '/# at 1569/,/# at 1789/p' >mysql-test.txt 
[root@xiaoya data]# cat mysql-test.txt 
# at 1569 
#160713 13:52:06 server id 1  end_log_pos 1569   Table_map: `students`.`test` mapped to number 33 
#160713 13:52:06 server id 1  end_log_pos 1789   Update_rows: table id 33 flags: STMT_END_F 
### UPDATE `students`.`test` 
### WHERE 
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */ 
###   @2='张三' /* STRING(60) meta=65084 nullable=0 is_null=0 */ 
###   @3='男' /* STRING(12) meta=65036 nullable=0 is_null=0 */ 
###   @4='86' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ 
### SET 
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */ 
###   @2='张三' /* STRING(60) meta=65084 nullable=0 is_null=0 */ 
###   @3='中' /* STRING(12) meta=65036 nullable=0 is_null=0 */ 
###   @4='86' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ 
### UPDATE `students`.`test` 
### WHERE 
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */ 
###   @2='李四' /* STRING(60) meta=65084 nullable=0 is_null=0 */ 
###   @3='男' /* STRING(12) meta=65036 nullable=0 is_null=0 */ 
###   @4='88' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ 
### SET 
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */ 
###   @2='李四' /* STRING(60) meta=65084 nullable=0 is_null=0 */ 
###   @3='中' /* STRING(12) meta=65036 nullable=0 is_null=0 */ 
###   @4='88' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ 
### UPDATE `students`.`test` 
### WHERE 
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */ 
###   @2='王五' /* STRING(60) meta=65084 nullable=0 is_null=0 */ 
###   @3='男' /* STRING(12) meta=65036 nullable=0 is_null=0 */ 
###   @4='90' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ 
### SET 
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */ 
###   @2='王五' /* STRING(60) meta=65084 nullable=0 is_null=0 */ 
###   @3='中' /* STRING(12) meta=65036 nullable=0 is_null=0 */ 
###   @4='90' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ 
### UPDATE `students`.`test` 
### WHERE 
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */ 
###   @2='小红' /* STRING(60) meta=65084 nullable=0 is_null=0 */ 
###   @3='女' /* STRING(12) meta=65036 nullable=0 is_null=0 */ 
###   @4='92' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ 
### SET 
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */ 
###   @2='小红' /* STRING(60) meta=65084 nullable=0 is_null=0 */ 
###   @3='中' /* STRING(12) meta=65036 nullable=0 is_null=0 */ 
###   @4='92' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ 
### UPDATE `students`.`test` 
### WHERE 
###   @1=5 /* INT meta=0 nullable=0 is_null=0 */ 
###   @2='小芳' /* STRING(60) meta=65084 nullable=0 is_null=0 */ 
###   @3='女' /* STRING(12) meta=65036 nullable=0 is_null=0 */ 
###   @4='96' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ 
### SET 
###   @1=5 /* INT meta=0 nullable=0 is_null=0 */ 
###   @2='小芳' /* STRING(60) meta=65084 nullable=0 is_null=0 */ 
###   @3='中' /* STRING(12) meta=65036 nullable=0 is_null=0 */ 
###   @4='96' /* VARSTRING(30) meta=30 nullable=0 is_null=0 */ 
# at 1789

下面有一个神奇的sed

[root@xiaoya data]# sed '/WHERE/{:a;N;/SET/!ba;s/\([^\n]*\)\n\(.*\)\n\(.*\)/\3\n\2\n\1/}' mysql-test.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' 
UPDATE `students`.`test` 
SET 
  @1=1 , 
  @2='张三' , 
  @3='男' , 
  @4='86' , 
WHERE 
  @1=1 ; 
UPDATE `students`.`test` 
SET 
  @1=2 , 
  @2='李四' , 
  @3='男' , 
  @4='88' , 
WHERE 
  @1=2 ; 
UPDATE `students`.`test` 
SET 
  @1=3 , 
  @2='王五' , 
  @3='男' , 
  @4='90' , 
WHERE 
  @1=3 ; 
UPDATE `students`.`test` 
SET 
  @1=4 , 
  @2='小红' , 
  @3='女' , 
  @4='92' , 
WHERE 
  @1=4 ; 
UPDATE `students`.`test` 
SET 
  @1=5 , 
  @2='小芳' , 
  @3='女' , 
  @4='96' , 
WHERE 
  @1=5 ;

下面这个就相对简单了

[root@xiaoya data]# sed -i 's/@1/id/g;s/@2/name/g;s/@3/sex/g;s/@4/score/g' recover.sql 
[root@xiaoya data]# sed -i -r 's/(score.*),/\1/g' recover.sql 
[root@xiaoya data]# cat recover.sql 
UPDATE `students`.`test` 
SET 
  id=1 , 
  name='张三' , 
  sex='男' , 
  score='86'
WHERE 
  id=1 ; 
UPDATE `students`.`test` 
SET 
  id=2 , 
  name='李四' , 
  sex='男' , 
  score='88'
WHERE 
  id=2 ; 
UPDATE `students`.`test` 
SET 
  id=3 , 
  name='王五' , 
  sex='男' , 
  score='90'
WHERE 
  id=3 ; 
UPDATE `students`.`test` 
SET 
  id=4 , 
  name='小红' , 
  sex='女' , 
  score='92'
WHERE 
  id=4 ; 
UPDATE `students`.`test` 
SET 
  id=5 , 
  name='小芳' , 
  sex='女' , 
  score='96'
WHERE 
  id=5 ;

6、日志处理到此ok,导入数据看一下

mysql> unlock tables; 
Query OK, 0 rows affected (0.00 sec) 
   
mysql> source /usr/local/mysql/data/recover.sql 
Query OK, 1 row affected (0.00 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.00 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.00 sec) 
Rows matched: 1  Changed: 1  Warnings: 0 
   
mysql> select * from test; 
+----+--------+-----+-------+ 
| id | name   | sex | score | 
+----+--------+-----+-------+ 
|  1 | 张三   | 男  | 86    | 
|  2 | 李四   | 男  | 88    | 
|  3 | 王五   | 男  | 90    | 
|  4 | 小红   | 女  | 92    | 
|  5 | 小芳   | 女  | 96    | 
+----+--------+-----+-------+ 
5 rows in set (0.00 sec)

到这里数据就完整回来了。将binglog格式设置为row有利有弊,好处是记录了每一行的实际变化,在主从复制时也不容易出问题。但是由于记录每行的变化,会占用大量磁盘,主从复制时带宽占用会有所消耗。到底是使用row还是mixed,需要在实际工作中自己去衡量,但从整体上来说,binglog的格式设置为row,都是不二的选择。

 

 

总结:

1、delete误删除和这个原理是一样的(把binlog在转换成sql重新插入)

2、解决问题的最好方式是预防问题的发生

方案一:定义别名

[root@xiaoya ~]# alias mysql='mysql -U'

[root@xiaoya ~]# echo "alias mysql='mysql -U'" >>/etc/profile
[root@xiaoya ~]# source /etc/profile

指定登陆-U 后,在mysql里,执行update和delete操作,如果没有指定where或limit,则程序拒绝执行。

方案二:在[mysql]段落开启这个参数:

safe-updates

这样当我们在做DML操作时忘记加where条件时,mysqld服务器是不会执行操作的

 

参考:http://www.cnblogs.com/gomysql/p/3582058.html 

为了方便大家交流,本人开通了微信公众号,和QQ群291519319。喜欢技术的一起来交流吧

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部