delete和truncate/drop恢复数据的过程

使用myflash工具恢复delete操作数据，myflash工具注意事项：

该工具注意事项

1.binlog格式必须为row,且binlog_row_image=full

2.仅支持5.6与5.7

3.只能回滚DML（增、删、改）

binlog格式确定为row：

mysql> show variables like '%binlog_format%';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.00 sec)

delete后恢复数据的过程：

查看表的数据：

mysql> select * from lbg;

+----+-----------+------+
| id | name | ha |
+----+-----------+------+
| 1 | 1,2,3,4,5 | 1 |
| 2 | 1,2,3,4,5 | 1 |
| 3 | 1,2,3,4,5 | 2 |
| 4 | 1,2,3,4,5 | 2 |
| 5 | 1,2,3,4,5 | 3 |
| 9 | 1,2,3,4,5 | 4 |
| 10 | 1,2,3,4,5 | 4 |
| 81 | 1,2,3,4,5 | 4 |
+----+-----------+------+

delete两条数据：

mysql> delete from lbg where id >=10;

Query OK, 2 rows affected (0.01 sec)

再查看：

mysql> select * from lbg;
+----+-----------+------+
| id | name | ha |
+----+-----------+------+
| 1 | 1,2,3,4,5 | 1 |
| 2 | 1,2,3,4,5 | 1 |
| 3 | 1,2,3,4,5 | 2 |
| 4 | 1,2,3,4,5 | 2 |
| 5 | 1,2,3,4,5 | 3 |
| 9 | 1,2,3,4,5 | 4 |
+----+-----------+------+

模拟数据库依旧在插入数据：

mysql> insert into lbg values (77,'a',77);

Query OK, 1 row affected (0.01 sec)

mysql> insert into lbg values (88,'b',88);
Query OK, 1 row affected (0.01 sec)

mysql> insert into lbg values (99,'c',99);
Query OK, 1 row affected (0.00 sec)

开始找回删除的数据：

1.找到当前binlog.(注意也可能不是当前binlog)

发现当前binlog是000012.

查找删除数据时的pos点：

[root@master mysql3306]# mysqlbinlog mysql-bin.000012 -vv >/tmp/lbg.binlog

[root@master mysql3306]# vim /tmp/lbg.binlog
找到这段：
#181108 13:28:58 server id 88083306 end_log_pos 416 CRC32 0x8e01100e Delete_rows: table id 108 flags: STMT_END_F

BEGIN
;
# at 292
#181108 13:28:58 server id 88083306 end_log_pos 343 CRC32 0xed8c903f Table_map: `test1`.`lbg` mapped to number 108
# at 343
#181108 13:28:58 server id 88083306 end_log_pos 416 CRC32 0x8e01100e Delete_rows: table id 108 flags: STMT_END_F

BINLOG '
msnjWxNqC0AFMwAAAFcBAAAAAGwAAAAAAAEABXRlc3QxAANsYmcAAwMPAwJQAAY/kIzt
msnjWyBqC0AFSQAAAKABAAAAAGwAAAAAAAEAAgAD//gKAAAACTEsMiwzLDQsNQQAAAD4UQAAAAkx
LDIsMyw0LDUEAAAADhABjg==
';
### DELETE FROM `test1`.`lbg`
### WHERE
### @1=10
### @2='1,2,3,4,5'
### @3=4

### DELETE FROM `test1`.`lbg`
### WHERE
### @1=81
### @2='1,2,3,4,5'
### @3=4
# at 416
#181108 13:28:58 server id 88083306 end_log_pos 447 CRC32 0x06d673a3 Xid = 11
COMMIT;

# at 447
#181108 13:30:39 server id 88083306 end_log_pos 512 CRC32 0x4a005c6d Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes

可知开始和截止pos是292和447.（分别是BEGIN和COMMIT后一行的数据）

使用myflash反写sql：

[root@master ~]# /soft/MyFlash-master/binary/flashback --start-position=292 --stop-position=447 --sqlTypes='DELETE' --binlogFileNames=/home/mysql3306/mysql3306/mysql-bin.000012

注意使用myflash的文件不能是之前导出的tmp/lbg.binlog，而是源binlog文件或者拷贝出来的binlog文件。

查看myflash产生的文件：

在当前目录会产生二进制文件 binlog_output_base.flashback：

[root@master ~]# ls
anaconda-ks.cfg binlog_output_base.flashback c.py e.py g.py initial-setup-ks.cfg j.py
a.py b.py d.py f.py h.py i.py test.py

查看该二进制文件：
[root@master ~]# mysqlbinlog binlog_output_base.flashback -vv >/tmp/myflash.binlog
[root@master ~]# vim /tmp/myflash.binlog
发现有insert数据了：

BINLOG '
msnjWxNqC0AFMwAAAK4AAAAAAGwAAAAAAAEABXRlc3QxAANsYmcAAwMPAwJQAAY/kIzt
msnjWx5qC0AFSQAAAPcAAAAAAGwAAAAAAAEAAgAD//gKAAAACTEsMiwzLDQsNQQAAAD4UQAAAAkx
LDIsMyw0LDUEAAAADhABjg==
';
### INSERT INTO `test1`.`lbg`
### SET
### @1=10
### @2='1,2,3,4,5'
### @3=4

恢复数据：

mysql> source /tmp/myflash.binlog

查看数据：

至此数据成功找回。

使用全备和binlog恢复truncate和drop操作的数据：
基础表数据：

mysql> select * from lbg;

+----+------+------+
| id | name | ha |
+----+------+------+
| 1 | a | 1 |
| 2 | b | 2 |
| 3 | c | 3 |
+----+------+------+

mysql> select * from test;
+----+------+------+
| id | name | ha |
+----+------+------+
| 11 | aa | 11 |
| 22 | bb | 22 |
| 33 | cc | 33 |
+----+------+------+

全备操作：

[root@master mysql3306]# mysqldump -uroot -proot -S /tmp/mysql3306.sock --master-data=2 --single-transaction -A > /tmp/all.sql

模拟数据继续插入：

mysql> insert into lbg values (4,'d',4);

Query OK, 1 row affected (0.00 sec)

mysql> insert into lbg values (5,'e',5);
Query OK, 1 row affected (0.01 sec)

mysql> insert into test values (44,'dd',44);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values (55,'ee',55);
Query OK, 1 row affected (0.29 sec)

查询数据：

mysql> select * from lbg;
+----+------+------+
| id | name | ha |
+----+------+------+
| 1 | a | 1 |
| 2 | b | 2 |
| 3 | c | 3 |
| 4 | d | 4 |
| 5 | e | 5 |
+----+------+------+

mysql> select * from test;
+----+------+------+
| id | name | ha |
+----+------+------+
| 11 | aa | 11 |
| 22 | bb | 22 |
| 33 | cc | 33 |
| 44 | dd | 44 |
| 55 | ee | 55 |
+----+------+------+

开始truncate表lbg,drop表test:

mysql> truncate table lbg;

Query OK, 0 rows affected (0.31 sec)

mysql> drop table test;
Query OK, 0 rows affected (0.30 sec)

继续其他操作：

mysql> insert into lbg values (111,'aaa',111);

Query OK, 1 row affected (0.10 sec)

mysql> insert into lbg values (222,'bbb',222);
Query OK, 1 row affected (0.01 sec)

发现误删数据和表开始找回：
将全备和binlog拷贝到测试库：

[root@master mysql3306]# scp /home/mysql3306/mysql3306/mysql-bin.000012 root@192.168.88.9:/tmp

[root@master mysql3306]# scp /tmp/all.sql root@192.168.88.9:/tmp

在测试库应用全备：

mysql> source /tmp/all.sql

此时数据如下：

mysql> select * from lbg;
+----+------+------+
| id | name | ha |
+----+------+------+
| 1 | a | 1 |
| 2 | b | 2 |
| 3 | c | 3 |
+----+------+------+
3 rows in set (0.00 sec)

mysql> select * from test;
+----+------+------+
| id | name | ha |
+----+------+------+
| 11 | aa | 11 |
| 22 | bb | 22 |
| 33 | cc | 33 |
+----+------+------+
3 rows in set (0.00 sec)

通过备份文件里的pos点确定应用binlog开始的pos点：

[root@lbg2 ~]# vim /tmp/all.sql

在/tmp/all.sql里找到如下一行：
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000012', MASTER_LOG_POS=4601;
可以确定pos点是4601

在binlog里找到truncate和drop时的pos点：

[root@lbg2 ~]# mysqlbinlog /tmp/mysql-bin.000012 -vv > /tmp/lbg.binlog

[root@lbg2 ~]# vim /tmp/lbg.binlog
摘要内容如下：

COMMIT;
# at 6203
#181108 13:28:58 server id 88083306 end_log_pos 6268 CRC32 0xe6be1409 Anonymous_GTID last_committed=23 sequence_number=24 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS';

# at 6268
#181108 13:28:58 server id 88083306 end_log_pos 6354 CRC32 0x685261c5 Query thread_id=6 exec_time=7113 error_code=0

SET TIMESTAMP=1541654938;
truncate table lbg
;
# at 6354
#181108 13:28:58 server id 88083306 end_log_pos 6419 CRC32 0xfc171fb0 Anonymous_GTID last_committed=24 sequence_number=25 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS';
# at 6419
#181108 13:28:58 server id 88083306 end_log_pos 6538 CRC32 0x901f7138 Query thread_id=6 exec_time=7131 error_code=0

SET TIMESTAMP=1541654938;
DROP TABLE `test`
;
# at 6538

确定truncate时的binlog的pos点大致为6203。

根据pos点使用binlog恢复数据：

[root@lbg2 ~]# mysqlbinlog /tmp/mysql-bin.000012 --start-position=4601 --stop-position=6203 -vv > /tmp/mysql.binlog

mysql> source /tmp/mysql.binlog

mysql> select * from lbg;
+----+------+------+
| id | name | ha |
+----+------+------+
| 1 | a | 1 |
| 2 | b | 2 |
| 3 | c | 3 |
| 4 | d | 4 |
| 5 | e | 5 |
+----+------+------+
5 rows in set (0.00 sec)

mysql> select * from test;
+----+------+------+
| id | name | ha |
+----+------+------+
| 11 | aa | 11 |
| 22 | bb | 22 |
| 33 | cc | 33 |
| 44 | dd | 44 |
| 55 | ee | 55 |
+----+------+------+

至此被删除的数据已恢复，最后可选择数据导出再导入到正式库里。

另外还可使用全备加binlog追回delete操作误删的数据：

1.先应用全备，并找到全备的pos点。

2.在binlog中删除掉delete那段的乱码数据。
3.根据全备的pos点应用已在第2步修改的binlog文件从而找回数据。

delete和truncate/drop恢复数据的过程

delete和truncate/drop恢复数据的过程的相关教程结束。

相关推荐

【pandas小技巧】--数据转置

flink-cdc同步mysql数据到elasticsearch

使用 Easysearch 还原 Elasticsearch 快照数据

pytest数据参数化和数据驱动yaml的简单使用

Hadoop 中利用 mapreduce 读写 mysql 数据

jdbc数据连接池dbcp要导入的jar包

Visual Basic 6 API压缩数据

【爬虫+数据清洗+可视化】用Python分析“淄博烧烤“的评论数据