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        |
+----+-------------+
mysql中delete语句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@localhost data]# more /
### 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 ~]# | 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'> /
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'结束的内容...
| 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;'

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。