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

AiDBA宝典

发布于 2020-08-14 11:42:48

8610

发布于 2020-08-14 11:42:48

一、源库建表
二、mysqldump全备
三、源库继续写入3条数据
四、源库模拟误操作删除lhrdb数据库
五、利用备份恢复lhrdb数据库
六、mysqlbinlog恢复

一、源库建表

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

C:\Users\lhrxxt>mysql -uroot -plhr -h192.168.1.35 -P3309
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.29-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> use lhrdb;
Database changed
MySQL [lhrdb]> CREATE TABLE `tb1` (
    ->   `id` int(10) NOT NULL AUTO_INCREMENT,
    ->   `name` char(10) CHARACTER SET latin1 DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.05 sec)

MySQL [lhrdb]> show master logs;
+----------------------+-----------+
| Log_name             | File_size |
+----------------------+-----------+
| mysql5729-bin.000001 |      1135 |
+----------------------+-----------+
1 row in set (0.00 sec)
MySQL [lhrdb]> insert into tb1 (name)  value ('aa'),('bb');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

MySQL [lhrdb]> show master logs;
+----------------------+-----------+
| Log_name             | File_size |
+----------------------+-----------+
| mysql5729-bin.000001 |      1405 |
+----------------------+-----------+
1 row in set (0.00 sec)

二、mysqldump全备

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

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

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

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

MySQL [lhrdb]> show master logs;
+----------------------+-----------+
| Log_name             | File_size |
+----------------------+-----------+
| mysql5729-bin.000001 |      1405 |
+----------------------+-----------+
1 row in set (0.00 sec)

MySQL [lhrdb]>  insert into tb1 (name)  value ('cc'),('dd');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

MySQL [lhrdb]> show master logs;
+----------------------+-----------+
| Log_name             | File_size |
+----------------------+-----------+
| mysql5729-bin.000001 |      1675 |
+----------------------+-----------+
1 row in set (0.00 sec)

MySQL [lhrdb]> flush logs;
Query OK, 0 rows affected (0.01 sec)

MySQL [lhrdb]> insert into tb1 (name)  value ('ee');
Query OK, 1 row affected (0.01 sec)

MySQL [lhrdb]> show master logs;
+----------------------+-----------+
| Log_name             | File_size |
+----------------------+-----------+
| mysql5729-bin.000001 |      1726 |
| mysql5729-bin.000002 |       416 |
+----------------------+-----------+
2 rows in set (0.00 sec)

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

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

MySQL [lhrdb]> show tables;
+-----------------+
| Tables_in_lhrdb |
+-----------------+
| aa              |
| ftb             |
| lhr_test_null   |
| t_jpg           |
| tb1             |
| temp            |
| test_blob       |
| test_count_lhr  |
| test_innodb     |
| test_myisam     |
| users           |
+-----------------+
11 rows in set (0.00 sec)

MySQL [lhrdb]> drop database lhrdb;
Query OK, 11 rows affected (0.13 sec)
MySQL [(none)]>

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

[root@docker35 ~]# mysql -uroot -plhr -h192.168.1.35 -P3309 < lhrdb.sql    
mysql: [Warning] Using a password on the command line interface can be insecure.

MySQL [(none)]> use lhrdb;
Database changed
MySQL [lhrdb]> show tables;
+-----------------+
| Tables_in_lhrdb |
+-----------------+
| aa              |
| ftb             |
| lhr_test_null   |
| t_jpg           |
| tb1             |
| temp            |
| test_blob       |
| test_count_lhr  |
| test_innodb     |
| test_myisam     |
| users           |
+-----------------+
11 rows in set (0.00 sec)
MySQL [lhrdb]> select * from tb1;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
+----+------+
2 rows in set (0.00 sec)

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

六、mysqlbinlog恢复

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

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

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

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

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

[root@docker35 ~]# mysqlbinlog --start-position=1405 --stop-position=481 mysql5729-bin.000001 mysql5729-bin.000002 -v --base64-output=DECODE-ROWS | more 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 1405
#200730 17:05:25 server id 4294967295  end_log_pos 1470 CRC32 0xdd1d29fd        Anonymous_GTID  last_committed=5        sequence_number=6       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1470
#200730 17:05:25 server id 4294967295  end_log_pos 1543 CRC32 0x48982bf1        Query   thread_id=5     exec_time=0     error_code=0
SET TIMESTAMP=1596099925/*!*/;
SET @@session.pseudo_thread_id=5/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C gbk *//*!*/;
SET @@session.character_set_client=28,@@session.collation_connection=28,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 1543
#200730 17:05:25 server id 4294967295  end_log_pos 1593 CRC32 0x0a1c1778        Table_map: `lhrdb`.`tb1` mapped to number 124
# at 1593
#200730 17:05:25 server id 4294967295  end_log_pos 1644 CRC32 0xad7a71e3        Write_rows: table id 124 flags: STMT_END_F
### INSERT INTO `lhrdb`.`tb1`
### SET
###   @1=3
###   @2='cc'
### INSERT INTO `lhrdb`.`tb1`
### SET
###   @1=4
###   @2='dd'
# at 1644
#200730 17:05:25 server id 4294967295  end_log_pos 1675 CRC32 0x5ca301af        Xid = 228
COMMIT/*!*/;
# at 1675
#200730 17:05:38 server id 4294967295  end_log_pos 1726 CRC32 0x3796ddb9        Rotate to mysql5729-bin.000002  pos: 4
# at 4
#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
# Warning: this binlog is either in use or was not closed properly.
# at 123
#200730 17:05:38 server id 4294967295  end_log_pos 154 CRC32 0xa35e2962         Previous-GTIDs
# [empty]
# at 154
#200730 17:05:43 server id 4294967295  end_log_pos 219 CRC32 0x35bac000         Anonymous_GTID  last_committed=0        sequence_number=1       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#200730 17:05:43 server id 4294967295  end_log_pos 292 CRC32 0xb25085fd         Query   thread_id=5     exec_time=0     error_code=0
SET TIMESTAMP=1596099943/*!*/;
BEGIN
/*!*/;
# at 292
#200730 17:05:43 server id 4294967295  end_log_pos 342 CRC32 0x709ca4b9         Table_map: `lhrdb`.`tb1` mapped to number 124
# at 342
#200730 17:05:43 server id 4294967295  end_log_pos 385 CRC32 0xb1a730e8         Write_rows: table id 124 flags: STMT_END_F
### INSERT INTO `lhrdb`.`tb1`
### SET
###   @1=5
###   @2='ee'
# at 385
#200730 17:05:43 server id 4294967295  end_log_pos 416 CRC32 0x1286885c         Xid = 231
COMMIT/*!*/;
# at 416
#200730 17:07:11 server id 4294967295  end_log_pos 481 CRC32 0xee1ce9dc         Anonymous_GTID  last_committed=1        sequence_number=2       rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
BEGIN /*added by mysqlbinlog */ /*!*/;
ROLLBACK /* added by mysqlbinlog */ /*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
You have new mail in /var/spool/mail/root

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

[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
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@docker35 ~]#

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

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

[root@docker35 ~]# mysql -uroot -plhr -h192.168.1.35 -P3309 lhrdb
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.29-log MySQL Community Server (GPL)

Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from lhrdb.tb1;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
|  4 | dd   |
|  5 | ee   |
+----+------+
5 rows in set (0.00 sec)

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

本文结束。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2020-08-13，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库

sql

本文分享自 DB宝微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

数据库

sql

登录后参与评论

0 条评论

热度