mysql中delete误删除如何利用binlog回滚-编程学习网

这篇文章主要介绍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
###   @2='5002301999X'
### DELETE FROM `test`.`tet3`
### WHERE
###   @1=5
###   @2='0000000X'
### DELETE FROM `test`.`tet3`
### WHERE
###   @1=6
###   @2='oi80'
### DELETE FROM `test`.`tet3`
### WHERE
###   @1=7
###   @2='887'
### DELETE FROM `test`.`tet3`
### WHERE
###   @1=8
###   @2='887'
### DELETE FROM `test`.`tet3`
### WHERE
###   @1=10
###   @2='jju'
# 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.