原 【DB宝17】使用mysqldump+mysqlbinlog恢复误删除的数据库
Tags： 原创MySQL误操作恢复mysqldumpmysqlbinlog
👉 本文共约1093个字，系统预计阅读时间或需5分钟。
目录
[toc]
一、源库建表
源库建表并插入2条数据，操作过程；
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 | 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表示一致性备份。
1 2 3 4 5 | [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条数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | 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数据库
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | 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数据库
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | [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来增量恢复。
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
