delete和truncate/drop恢复数据的过程
2018-12-12 15:20 烟雨楼人 阅读(1042) 评论(0) 编辑 收藏 举报使用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)
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 |
| 77 | a | 77 |
| 88 | b | 88 |
| 99 | c | 99 |
+----+-----------+------+
9 rows in set (0.00 sec)
|
开始找回删除的数据：
| 1.找到当前binlog.(注意也可能不是当前binlog)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000009 | 12178 |
| mysql-bin.000010 | 177 |
| mysql-bin.000011 | 3356 |
| mysql-bin.000012 | 1245 |
+------------------+-----------+
4 rows in set (0.00 sec)
发现当前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
查看数据：
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 |
| 77 | a | 77 |
| 81 | 1,2,3,4,5 | 4 |
| 88 | b | 88 |
| 99 | c | 99 |
+----+-----------+------+
至此数据成功找回。
|
使用全备和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文件从而找回数据。
|
