MySQL常见错误 -- delete忘加where条件误操作恢复

开启binlog，且binlog-format=row

新建一个表

CREATE TABLE `student` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`name` varchar(10) NOT NULL DEFAULT '',   `class` int(10), `score` varchar(10) NOT NULL DEFAULT '', PRIMARY KEY (`id`));

插入测试数据

insert into student(name,class,score) values('a',1,56),('b',1,61),('c',2,78),('d',2,45),('e',3,76),('f',3,89),('g',4,43),('h',4,90);

模拟删除

mysql> delete from student;
Query OK, 8 rows affected (0.00 sec)
 
mysql> select * from student;
Empty set (0.00 sec)

看一下删除命令的binlog POSITION

mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS bin-log.000039 | grep -B 10 "DELETE"

# at 8515372
#160323 17:16:31 server id 1  end_log_pos 8515519 CRC32 0xb503c687     Delete_rows: table id 151 flags: STMT_END_F
### DELETE FROM `abc`.`student`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='a' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
###   @3=1 /* INT meta=0 nullable=1 is_null=0 */
###   @4='56' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */

导出binlog

mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS bin-log.000039 | sed -n '/# at 8515372/,/COMMIT/p' > ~/delete.txt

cat ~/delete.txt

# at 8515372
#160323 17:16:31 server id 1  end_log_pos 8515519 CRC32 0xb503c687     Delete_rows: table id 151 flags: STMT_END_F
### DELETE FROM `abc`.`student`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='a' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
###   @3=1 /* INT meta=0 nullable=1 is_null=0 */
###   @4='56' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
### DELETE FROM `abc`.`student`
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='b' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
###   @3=1 /* INT meta=0 nullable=1 is_null=0 */
###   @4='61' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
### DELETE FROM `abc`.`student`
### WHERE
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2='c' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
###   @3=2 /* INT meta=0 nullable=1 is_null=0 */
###   @4='78' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
### DELETE FROM `abc`.`student`
### WHERE
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2='d' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
###   @3=2 /* INT meta=0 nullable=1 is_null=0 */
###   @4='45' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
### DELETE FROM `abc`.`student`
### WHERE
###   @1=5 /* INT meta=0 nullable=0 is_null=0 */
###   @2='e' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
###   @3=3 /* INT meta=0 nullable=1 is_null=0 */
###   @4='76' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
### DELETE FROM `abc`.`student`
### WHERE
###   @1=6 /* INT meta=0 nullable=0 is_null=0 */
###   @2='f' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
###   @3=3 /* INT meta=0 nullable=1 is_null=0 */
###   @4='89' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
### DELETE FROM `abc`.`student`
### WHERE
###   @1=7 /* INT meta=0 nullable=0 is_null=0 */
###   @2='g' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
###   @3=4 /* INT meta=0 nullable=1 is_null=0 */
###   @4='43' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
### DELETE FROM `abc`.`student`
### WHERE
###   @1=8 /* INT meta=0 nullable=0 is_null=0 */
###   @2='h' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
###   @3=4 /* INT meta=0 nullable=1 is_null=0 */
###   @4='90' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
# at 8515519
#160323 17:16:31 server id 1  end_log_pos 8515550 CRC32 0xbe922c5c     Xid = 416140
COMMIT/*!*/;

输出sql语句

sed -n '/###/p' ~/delete.txt | 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' > ~/insert.sql

cat ~/insert.sql

INSERT INTO `abc`.`student`
SELECT
  1 ,
  'a' ,
  1 ,
  '56' ;
INSERT INTO `abc`.`student`
SELECT
  2 ,
  'b' ,
  1 ,
  '61' ;
INSERT INTO `abc`.`student`
SELECT
  3 ,
  'c' ,
  2 ,
  '78' ;
INSERT INTO `abc`.`student`
SELECT
  4 ,
  'd' ,
  2 ,
  '45' ;
INSERT INTO `abc`.`student`
SELECT
  5 ,
  'e' ,
  3 ,
  '76' ;
INSERT INTO `abc`.`student`
SELECT
  6 ,
  'f' ,
  3 ,
  '89' ;
INSERT INTO `abc`.`student`
SELECT
  7 ,
  'g' ,
  4 ,
  '43' ;
INSERT INTO `abc`.`student`
SELECT
  8 ,
  'h' ,
  4 ,
  '90' ;

导入SQL

mysql> source ~/insert.sql
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
 
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
 
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
 
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
 
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
 
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0
 
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
 
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+------+-------+-------+
| id | name | class | score |
+----+------+-------+-------+
|  1 | a    |     1 | 56    |
|  2 | b    |     1 | 61    |
|  3 | c    |     2 | 78    |
|  4 | d    |     2 | 45    |
|  5 | e    |     3 | 76    |
|  6 | f    |     3 | 89    |
|  7 | g    |     4 | 43    |
|  8 | h    |     4 | 90    |
+----+------+-------+-------+
8 rows in set (0.00 sec)