mysql怎样利用对所有数据库进行备份的备份文件只恢复某一个数据库

一：实验介绍

我们一般会在备份的时候指定--all-databases来备份所有数据库，利用该备份文件还原的时候默认会还原所有数据库（即使你指定了某一个数据库，如mysql -u root -p 数据库名称 < 备份文件）。那么，怎样利用对所有数据库进行备份的备份文件只恢复某一个数据库呢？这时需要用到--one-database选项。

二：实验步骤

1：现有数据

mysql> use dan;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> select * from t;
+------+
| id   |
+------+
|    3 |
|    2 |
|    4 |
|    5 |
+------+
4 rows in set (0.00 sec)
mysql> use song;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> select * from t;
+------+
| id   |
+------+
|    6 |
+------+
1 row in set (0.00 sec)
 
--dan数据库的t表有2,3,4,5四条数据，song数据库的t表仅有一条数据6.

2：备份

[root@target_pc databasefile]# mysqldump -u root -p --all-databases > /backup/databasefile/201503311428.sql

3：准备测试数据

--删除song数据库的数据
mysql> delete from t ;
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from t;
Empty set (0.00 sec)
 
--往dan数据库添加数据
mysql> use dan;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> insert into t(id) values(6),(7);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
 
mysql> select * from t;
+------+
| id   |
+------+
|    3 |
|    2 |
|    4 |
|    5 |
|    6 |
|    7 |
+------+
6 rows in set (0.00 sec)
 

4：备份

--在恢复前先对现有数据库做个全备，以保证出现问题的时候，可以恢复到故障点。

[root@target_pc databasefile]# mysqldump -u root -p --all-databases > /backup/databasefile/201503311455.sql

Enter password: 

5：恢复

[root@target_pc databasefile]# mysql -u root -p song --one-database< /backup/databasefile/201503311428.sql 

Enter password: 

 

6：验证

mysql> use dan;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
 
mysql> select * from t;
+------+
| id   |
+------+
|    3 |
|    2 |
|    4 |
|    5 |
|    6 |
|    7 |
+------+
6 rows in set (0.00 sec)
 
mysql> use song;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> select * from t;
+------+
| id   |
+------+
|    6 |
+------+
1 row in set (0.00 sec)

--可以看到dan数据库的数据保留了6,7，说明 dan这个数据库没有被恢复，只恢复了song这个数据库。

7：总结

当要从对所有数据库进行备份的备份文件中只恢复某一个数据库时，需要加上--one-database选项及要恢复的数据库名称。