一、发现问题
平时使用mysqldump备份时有人喜欢用-A –B参数进行全备,这样备份的时候会简单一点,但是恢复的时候如果直接恢复会把所有库都会恢复,这不是我们想要的结果。
二、分析问题
恢复单库或是单表我们可以通过shell命令从全库备份的SQL文件中截取出我们想要的部分。另外针对单库的恢复MySQL也有一个参数来解决这个问题.
三、解决问题
1、通过MySQL自带的参数恢复单库。# 全备的数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| WL_TJ56_DICT |
| mysql |
| performance_schema |
| test |
| test01 |
| test02 |
+--------------------+
#执行全备
[root@TestServer01 ~]# mysqldump -uroot -p123456 -S /data/mysql/mysql_3306/mysql.sock -A -B --events > /opt/fullbackup.sql
Warning: Using a password on the command line interface can be insecure.
[root@TestServer01 ~]# ll /opt/fullbackup.sql
-rw-r--r--. 1 root root 651037 Dec 20 00:44 /opt/fullbackup.sql
#drop数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| WL_TJ56_DICT |
| mysql |
| performance_schema |
| test |
| test01 |
| test02 |
+--------------------+
7 rows in set (0.00 sec)
mysql> drop database test;
Query OK, 2 rows affected (0.07 sec)
mysql> drop database test01;
Query OK, 1 row affected (0.02 sec)
mysql> drop database test02;
Query OK, 1 row affected (0.02 sec)
mysql> drop database WL_TJ56_DICT;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)