binlog恢复误删除数据

演示

1、先查询表数据
(root@localhost) [test]> select * from t_stu;
+----+-----------+-----+
| id | name      | sex |
+----+-----------+-----+
|  1 | 张学友    |   1 |
|  2 | 刘德华    |   1 |
|  3 | 郭富城    |   1 |
|  4 | 蔡依林    |   2 |
|  5 | xxx       |   0 |
+----+-----------+-----+
5 rows in set (0.00 sec)

2、查看表结构  
(root@localhost) [test]> desc t_stu;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | NO   |     |         |                |
| sex   | tinyint(4)  | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

3、误删除数据
(root@localhost) [test]> delete from t_stu where id < 3;
Query OK, 2 rows affected (0.00 sec)

4、在查询数据，发现误删除
(root@localhost) [test]> select * from t_stu;
+----+-----------+-----+
| id | name      | sex |
+----+-----------+-----+
|  3 | 郭富城    |   1 |
|  4 | 蔡依林    |   2 |
|  5 | xxx       |   0 |
+----+-----------+-----+
3 rows in set (0.00 sec)

5、查看binllog日志
[root@rabbitmq_1 mysql]# mysqlbinlog -vvv --base64-output=decode-rows mysql-bin.000137
结合sed 命令 只打印匹配到的行

[root@rabbitmq_1 mysql]# mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000137 | sed -n '/### DELETE FROM `test`.`t_stu`/,/COMMIT/ p'
### DELETE FROM `test`.`t_stu`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
###   @2='张学友' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
###   @3=1 /* TINYINT meta=0 nullable=0 is_null=0 */
### DELETE FROM `test`.`t_stu`
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2='刘德华' /* VARSTRING(40) meta=40 nullable=0 is_null=0 */
###   @3=1 /* TINYINT meta=0 nullable=0 is_null=0 */
# at 1646
#231114  9:50:27 server id 1  end_log_pos 1677 CRC32 0x3f61dc3c         Xid = 74
COMMIT/*!*/;

6、把打印的内容输出到一个文件中
[root@rabbitmq_1 mysql]# mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000137 | sed -n '/### DELETE FROM `test`.`t_stu`/,/COMMIT/ p' > /tmp/a.txt

7、把TXT文件转化成标准的sql
[root@rabbitmq_1 tmp]# cat a.txt  | sed -n '/### /p'| sed 's/### //g;s/DELETE FROM/INSERT INTO/g;s/WHERE/(id,name,sex)VALUES(/g;s/\/\*.*/,/g;s/@[1-9]=//g' |sed '5s/\,/\);/g;10s/\,/\)/g' > b.sql

8、然后去数据路恢复数据
(root@localhost) [test]> source /tmp/b.sql;
Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)