【DB宝17】使用mysqldump+mysqlbinlog恢复误删除的数据库

DB宝 2021-03-23

507

点击上方蓝字 “DB宝”，关注我吧

一、源库建表

源库建表并插入2条数据，操作过程；

 1C:\Users\lhrxxt>mysql -uroot -plhr -h192.168.1.35 -P3309
 2mysql: [Warning] Using a password on the command line interface can be insecure.
 3Welcome to the MySQL monitor.  Commands end with ; or \g.
 4Your MySQL connection id is 5
 5Server version: 5.7.29-log MySQL Community Server (GPL)
 6
 7Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
 8
 9Oracle is a registered trademark of Oracle Corporation and/or its
10affiliates. Other names may be trademarks of their respective
11owners.
12
13Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
14
15MySQL [(none)]> use lhrdb;
16Database changed
17MySQL [lhrdb]> CREATE TABLE `tb1` (
18    ->   `id` int(10) NOT NULL AUTO_INCREMENT,
19    ->   `name` char(10) CHARACTER SET latin1 DEFAULT NULL,
20    ->   PRIMARY KEY (`id`)
21    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
22Query OK, 0 rows affected (0.05 sec)
23
24MySQL [lhrdb]> show master logs;
25+----------------------+-----------+
26| Log_name             | File_size |
27+----------------------+-----------+
28| mysql5729-bin.000001 |      1135 |
29+----------------------+-----------+
301 row in set (0.00 sec)
31MySQL [lhrdb]> insert into tb1 (name)  value ('aa'),('bb');
32Query OK, 2 rows affected (0.01 sec)
33Records: 2  Duplicates: 0  Warnings: 0
34
35MySQL [lhrdb]> show master logs;
36+----------------------+-----------+
37| Log_name             | File_size |
38+----------------------+-----------+
39| mysql5729-bin.000001 |      1405 |
40+----------------------+-----------+
411 row in set (0.00 sec)

二、mysqldump全备

如果是PXB备份的话，会在xtrabackup_binlog_info文件中记录备份完成时的binlog文件和pos点的；如果是mysqldump备份，则需要带上--master-data=2这个参数才会记录备份开始时的binlog文件和pos点。
--single-transaction表示一致性备份。

1[root@docker35 ~]# mysqldump -uroot -plhr -h192.168.1.35 -P3309 --hex-blob --routines --events --triggers --master-data=2 --single-transaction  --databases lhrdb > lhrdb.sql
2mysqldump: [Warning] Using a password on the command line interface can be insecure.
3[root@docker35 ~]# grep -i "CHANGE MASTER" lhrdb.sql
4-- CHANGE MASTER TO MASTER_LOG_FILE='mysql5729-bin.000001', MASTER_LOG_POS=1405;
5[root@docker35 ~]#

这里可以看到，备份开始时的pos点是mysql5729-bin.000001文件的1405，备份好的文件是lhrdb.sql文件，该文件是文本文件，可直接查看。如果后续通过binlog来恢复数据库时，则需要从mysql5729-bin.000001文件的1405号开始恢复。

三、源库继续写入3条数据

 1MySQL [lhrdb]> show master logs;
 2+----------------------+-----------+
 3| Log_name             | File_size |
 4+----------------------+-----------+
 5| mysql5729-bin.000001 |      1405 |
 6+----------------------+-----------+
 71 row in set (0.00 sec)
 8
 9MySQL [lhrdb]>  insert into tb1 (name)  value ('cc'),('dd');
10Query OK, 2 rows affected (0.01 sec)
11Records: 2  Duplicates: 0  Warnings: 0
12
13MySQL [lhrdb]> show master logs;
14+----------------------+-----------+
15| Log_name             | File_size |
16+----------------------+-----------+
17| mysql5729-bin.000001 |      1675 |
18+----------------------+-----------+
191 row in set (0.00 sec)
20
21MySQL [lhrdb]> flush logs;
22Query OK, 0 rows affected (0.01 sec)
23
24MySQL [lhrdb]> insert into tb1 (name)  value ('ee');
25Query OK, 1 row affected (0.01 sec)
26
27MySQL [lhrdb]> show master logs;
28+----------------------+-----------+
29| Log_name             | File_size |
30+----------------------+-----------+
31| mysql5729-bin.000001 |      1726 |
32| mysql5729-bin.000002 |       416 |
33+----------------------+-----------+
342 rows in set (0.00 sec)

上面我们进行flush logs是为了模拟现在已经有多个binlog文件了，恢复时进行多个binlog一起恢复。此时数据库lhrdb的tb1表共有5条数据。

四、源库模拟误操作删除lhrdb数据库

 1MySQL [lhrdb]> show tables;
 2+-----------------+
 3| Tables_in_lhrdb |
 4+-----------------+
 5| aa              |
 6| ftb             |
 7| lhr_test_null   |
 8| t_jpg           |
 9| tb1             |
10| temp            |
11| test_blob       |
12| test_count_lhr  |
13| test_innodb     |
14| test_myisam     |
15| users           |
16+-----------------+
1711 rows in set (0.00 sec)
18
19MySQL [lhrdb]> drop database lhrdb;
20Query OK, 11 rows affected (0.13 sec)
21MySQL [(none)]>

五、利用备份恢复lhrdb数据库

 1[root@docker35 ~]# mysql -uroot -plhr -h192.168.1.35 -P3309 < lhrdb.sql    
 2mysql: [Warning] Using a password on the command line interface can be insecure.
 3
 4MySQL [(none)]> use lhrdb;
 5Database changed
 6MySQL [lhrdb]> show tables;
 7+-----------------+
 8| Tables_in_lhrdb |
 9+-----------------+
10| aa              |
11| ftb             |
12| lhr_test_null   |
13| t_jpg           |
14| tb1             |
15| temp            |
16| test_blob       |
17| test_count_lhr  |
18| test_innodb     |
19| test_myisam     |
20| users           |
21+-----------------+
2211 rows in set (0.00 sec)
23MySQL [lhrdb]> select * from tb1;
24+----+------+
25| id | name |
26+----+------+
27|  1 | aa   |
28|  2 | bb   |
29+----+------+
302 rows in set (0.00 sec)

可以看到，虽然数据库lhrdb已经恢复了，但是只能看到备份前的数据，tb1只有2条数据，还差3条数据。接下来使用mysqlbinlog来增量恢复。

六、mysqlbinlog恢复

建议将二进制日志文件mysql5729-bin.000001和mysql5729-bin.000002从原来位置拷贝到一个临时目录。

首先要找出误操作前的pos点，也就是drop database lhrdb前的pos点，该点是接下来需要应用binlog的结束点：

 1[root@docker35 ~]# mysqlbinlog -v --base64-output=DECODE-ROWS mysql5729-bin.000001 | grep -C 6  -i "drop database" 
 2[root@docker35 ~]# mysqlbinlog -v --base64-output=DECODE-ROWS mysql5729-bin.000002 | grep -C 6  -i "drop database" 
 3# at 416
 4#200730 17:07:11 server id 4294967295  end_log_pos 481 CRC32 0xee1ce9dc         Anonymous_GTID  last_committed=1        sequence_number=2       rbr_only=no
 5SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
 6# at 481
 7#200730 17:07:11 server id 4294967295  end_log_pos 576 CRC32 0x4547b3c7         Query   thread_id=5     exec_time=0     error_code=0
 8SET TIMESTAMP=1596100031/*!*/;
 9drop database lhrdb
10/*!*/;
11# at 576
12#200730 17:09:47 server id 4294967295  end_log_pos 641 CRC32 0x379e9062         Anonymous_GTID  last_committed=2        sequence_number=3       rbr_only=no
13SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
14# at 641
15#200730 17:09:47 server id 4294967295  end_log_pos 806 CRC32 0x679ea696         Query   thread_id=7     exec_time=0     error_code=0
16[root@docker35 ~]#

从上面的结果可以看出，误操作前的pos点是mysql5729-bin.000002文件的481。所以，我们通过binlog来进行数据恢复的开始点是文件mysql5729-bin.000001的1405号，结束点是mysql5729-bin.000002文件的481。

我们可以首先查看要恢复的数据是否正确：

 1[root@docker35 ~]# mysqlbinlog --start-position=1405 --stop-position=481 mysql5729-bin.000001 mysql5729-bin.000002 -v --base64-output=DECODE-ROWS | more 
 2/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
 3/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 4DELIMITER /*!*/;
 5# at 1405
 6#200730 17:05:25 server id 4294967295  end_log_pos 1470 CRC32 0xdd1d29fd        Anonymous_GTID  last_committed=5        sequence_number=6       rbr_only=yes
 7/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
 8SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
 9# at 1470
10#200730 17:05:25 server id 4294967295  end_log_pos 1543 CRC32 0x48982bf1        Query   thread_id=5     exec_time=0     error_code=0
11SET TIMESTAMP=1596099925/*!*/;
12SET @@session.pseudo_thread_id=5/*!*/;
13SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
14SET @@session.sql_mode=1436549152/*!*/;
15SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
16/*!\C gbk *//*!*/;
17SET @@session.character_set_client=28,@@session.collation_connection=28,@@session.collation_server=8/*!*/;
18SET @@session.lc_time_names=0/*!*/;
19SET @@session.collation_database=DEFAULT/*!*/;
20BEGIN
21/*!*/;
22# at 1543
23#200730 17:05:25 server id 4294967295  end_log_pos 1593 CRC32 0x0a1c1778        Table_map: `lhrdb`.`tb1` mapped to number 124
24# at 1593
25#200730 17:05:25 server id 4294967295  end_log_pos 1644 CRC32 0xad7a71e3        Write_rows: table id 124 flags: STMT_END_F
26### INSERT INTO `lhrdb`.`tb1`
27### SET
28###   @1=3
29###   @2='cc'
30### INSERT INTO `lhrdb`.`tb1`
31### SET
32###   @1=4
33###   @2='dd'
34# at 1644
35#200730 17:05:25 server id 4294967295  end_log_pos 1675 CRC32 0x5ca301af        Xid = 228
36COMMIT/*!*/;
37# at 1675
38#200730 17:05:38 server id 4294967295  end_log_pos 1726 CRC32 0x3796ddb9        Rotate to mysql5729-bin.000002  pos: 4
39# at 4
40#200730 17:05:38 server id 4294967295  end_log_pos 123 CRC32 0xbbeb711c         Start: binlog v 4, server v 5.7.29-log created 200730 17:05:38
41# Warning: this binlog is either in use or was not closed properly.
42# at 123
43#200730 17:05:38 server id 4294967295  end_log_pos 154 CRC32 0xa35e2962         Previous-GTIDs
44# [empty]
45# at 154
46#200730 17:05:43 server id 4294967295  end_log_pos 219 CRC32 0x35bac000         Anonymous_GTID  last_committed=0        sequence_number=1       rbr_only=yes
47/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
48SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
49# at 219
50#200730 17:05:43 server id 4294967295  end_log_pos 292 CRC32 0xb25085fd         Query   thread_id=5     exec_time=0     error_code=0
51SET TIMESTAMP=1596099943/*!*/;
52BEGIN
53/*!*/;
54# at 292
55#200730 17:05:43 server id 4294967295  end_log_pos 342 CRC32 0x709ca4b9         Table_map: `lhrdb`.`tb1` mapped to number 124
56# at 342
57#200730 17:05:43 server id 4294967295  end_log_pos 385 CRC32 0xb1a730e8         Write_rows: table id 124 flags: STMT_END_F
58### INSERT INTO `lhrdb`.`tb1`
59### SET
60###   @1=5
61###   @2='ee'
62# at 385
63#200730 17:05:43 server id 4294967295  end_log_pos 416 CRC32 0x1286885c         Xid = 231
64COMMIT/*!*/;
65# at 416
66#200730 17:07:11 server id 4294967295  end_log_pos 481 CRC32 0xee1ce9dc         Anonymous_GTID  last_committed=1        sequence_number=2       rbr_only=no
67SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
68BEGIN /*added by mysqlbinlog */ /*!*/;
69ROLLBACK /* added by mysqlbinlog */ /*!*/;
70SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
71DELIMITER ;
72# End of log file
73/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
74/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
75You have new mail in /var/spool/mail/root

可以看到有3条插入语句，那么接下来就可以放心大胆的执行恢复操作了：

1[root@docker35 ~]# mysqlbinlog --start-position=1405 --stop-position=481 mysql5729-bin.000001 mysql5729-bin.000002 -D | mysql -uroot -plhr -h192.168.1.35 -P3309 lhrdb
2mysql: [Warning] Using a password on the command line interface can be insecure.
3[root@docker35 ~]#

--start-position是备份后记录下的pos点， --stop-position是误操前的pos点，如果批多个binlog文件，那么start-position是第一个binlog文件的pos点，stop-position是最后一个binlog的pos点，如果不添加stop-position参数，那么会恢复到二进制文件的最后一个位置。
-D 表示禁止恢复过程产生日志。

数据已恢复，我们在数据库中查看tb1表是否是5条数据：

 1[root@docker35 ~]# mysql -uroot -plhr -h192.168.1.35 -P3309 lhrdb
 2mysql: [Warning] Using a password on the command line interface can be insecure.
 3Reading table information for completion of table and column names
 4You can turn off this feature to get a quicker startup with -A
 5
 6Welcome to the MySQL monitor.  Commands end with ; or \g.
 7Your MySQL connection id is 9
 8Server version: 5.7.29-log MySQL Community Server (GPL)
 9
10Copyright (c) 2009-2019 Percona LLC and/or its affiliates
11Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
12
13Oracle is a registered trademark of Oracle Corporation and/or its
14affiliates. Other names may be trademarks of their respective
15owners.
16
17Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
18
19mysql> select * from lhrdb.tb1;
20+----+------+
21| id | name |
22+----+------+
23|  1 | aa   |
24|  2 | bb   |
25|  3 | cc   |
26|  4 | dd   |
27|  5 | ee   |
28+----+------+
295 rows in set (0.00 sec)

可以看到，数据已经恢复到最新的状态了，共5条数据。

本文结束。

• 微信公众号：DB宝，作者：小麦苗
• 作者博客地址：http://blog.itpub.net/26736162/
• 作者微信：db_bao
• 作者QQ：646634621，QQ群：230161599、618766405
• 提供Oracle OCP、OCM、高可用（rac+dg+ogg）和MySQL DBA培训
• 版权所有，欢迎分享本文，转载请保留出处
• 若有侵权请联系小麦苗删除

★DB宝分享的IT资料：https://mp.weixin.qq.com/s/Iwsy-zkzwgs8nYkcMz29ag
★DB宝笔试面试详解：https://mp.weixin.qq.com/s/Vm5PqNcDcITkOr9cQg6T7w

长按下图识别二维码或微信扫描下图二维码来关注小麦苗的微信公众号：DB宝，学习最实用的数据库技术。

mysql

文章转载自DB宝，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。