mysql语句delete如何回滚_mysql中delete误删除,利用binlog回滚

mysql> select * from tet3;

+----+-------------+

| id | dd          |

+----+-------------+

|  1 | XX          |

|  2 | YY          |

|  3 | aaa         |

|  4 | 5002301999X |

|  5 | 0000000X    |

|  6 | oi80        |

|  7 | 887         |

|  8 | 887         |

| 10 | jju         |

+----+-------------+

9 rows in set (0.03 sec)

mysql> delete from tet3 where id>3;

Query OK, 6 rows affected (0.03 sec)

mysql> select * from tet3;

+----+------+

| id | dd   |

+----+------+

|  1 | XX   |

|  2 | YY   |

|  3 | aaa  |

+----+------+

3 rows in set (0.00 sec)

[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v -v db-bin.000016| sed -n '/### DELETE FROM `test`.`tet3`/,/COMMIT/p'> /root/delete.txt

[root@localhost data]# more /root/delete.txt

### DELETE FROM `test`.`tet3`

### WHERE

###   @1=4 /* INT meta=0 nullable=0 is_null=0 */

###   @2='5002301999X' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */

### DELETE FROM `test`.`tet3`

### WHERE

###   @1=5 /* INT meta=0 nullable=0 is_null=0 */

###   @2='0000000X' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */

### DELETE FROM `test`.`tet3`

### WHERE

###   @1=6 /* INT meta=0 nullable=0 is_null=0 */

###   @2='oi80' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */

### DELETE FROM `test`.`tet3`

### WHERE

###   @1=7 /* INT meta=0 nullable=0 is_null=0 */

###   @2='887' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */

### DELETE FROM `test`.`tet3`

### WHERE

###   @1=8 /* INT meta=0 nullable=0 is_null=0 */

###   @2='887' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */

### DELETE FROM `test`.`tet3`

### WHERE

###   @1=10 /* INT meta=0 nullable=0 is_null=0 */

###   @2='jju' /* VARSTRING(20) meta=20 nullable=1 is_null=0 */

# at 3640

#150426 23:17:36 server id 199  end_log_pos 3671 CRC32 0xb946f7f5       Xid = 164

COMMIT/*!*/;

[root@localhost ~]# cat delete.txt | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;' |sed -r 's/(@2.*),/\1;/g' | sed 's/@[1-9]=//g' >insert.sql

[root@localhost ~]#

[root@localhost ~]#

[root@localhost ~]# more insert.sql

INSERT INTO `test`.`tet3`

SELECT

4 ,

'5002301999X' ;

INSERT INTO `test`.`tet3`

SELECT

5 ,

'0000000X' ;

INSERT INTO `test`.`tet3`

SELECT

6 ,

'oi80' ;

INSERT INTO `test`.`tet3`

SELECT

7 ,

'887' ;

INSERT INTO `test`.`tet3`

SELECT

8 ,

'887' ;

INSERT INTO `test`.`tet3`

SELECT

10 ,

'jju' ;

以上就是我们需要的回滚sql了...执行就行了..

命令解释:

mysqlbinlog --no-defaults --base64-output=decode-rows -v -v db-bin.000016| sed -n '/### DELETE FROM `test`.`tet3`/,/COMMIT/p'> /root/delete.txt

mysqlbinlog --no-defaults --base64-output=decode-rows -v -v db-bin.000016

这属于mysqlbinlog命令参数...

--no-defaults  阻止mysqlbinlog工具从任何配置文件读取参数(保证密码安全)

--base64-output=decode-rows  显示出row模式带来的sql变更

-v -v  采用二进制日志文件方式查看

sed -n '/### DELETE FROM `test`.`tet3`/,/COMMIT/p'

打印从'### DELETE FROm `test`.`tet3`'开始到'COMMIT'结束的内容...

cat delete.txt | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;' |sed -r 's/(@2.*),/\1;/g' | sed 's/@[1-9]=//g' >insert.sql

sed -n '/###/p'

打印'###'开头的行

sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;'

分开解读: s/### //g;s/\/\*.*/,/g;  这部分是把'### ' 和/*..*/去除掉;

s/DELETE FROM/INSERT INTO/g;   这部分是吧delete from 换成insert into;

s/WHERE/SELECT/g;  这部分是吧where换成select;

|sed -r 's/(@2.*),/\1;/g'

-r是正则表达式,意思是在@2开头的一行末尾加一个分号.

sed 's/@[1-9]=//g'

这个就简单了..就是将@1-@9的去除.当然本例中只有@1和@2.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20892230/viewspace-2127458/，如需转载，请注明出处，否则将追究法律责任。