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

一：实验介绍

我们一般会在备份的时候指定--all-databases来备份所有数据库，利用该备份文件还原的时候默认会还原所有数据库(即使你指定了某一个数据库，如mysql -u root -p数据库名称

二：实验步骤

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

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选项及要恢复的数据库名称。