MySQL 使用mysqldump命令进行数据库备份和恢复

mysqldump命令备份指定数据库

mysqldump 在库被删除的情况下，无法直接从文件恢复，需要手动新建同名库，才能从文件恢复数据

备份指定库的指定表

mysqldump -uroot -p [database_name] [tablename] > bak_file.dump

示例

root@8f131dc037af:~# mysqldump -uroot -p menagerie pet > menagerie.pet.dump

备份指定库

//备份一个数据库
root@8f131dc037af:~# mysqldump -uroot -p --databases menagerie > menagerie.dump
//备份两个或多个数据库
root@8f131dc037af:~# mysqldump -uroot -p --databases menagerie todos > menagerie_todos.dump

备份所有库

root@8f131dc037af:~# mysqldump -uroot -p --all-databases > all.dump

从dump文件恢复数据

第一种方法：mysql -uroot -p [database_name] < bak_file.dump
第二种方法：mysql -uroot -p -e "source [bak_file.dump]" database_name

示例：

删除表

mysql> select * from pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | 0000-00-00 |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | 0000-00-00 |
| Buffy    | Harold | dog     | f    | 1989-05-13 | 0000-00-00 |
| Fang     | Benny  | dog     | m    | 1990-08-27 | 0000-00-00 |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | 0000-00-00 |
| Whistler | Gwen   | bird    |      | 1997-12-09 | 0000-00-00 |
| Slim     | Benny  | snake   | m    | 1996-04-29 | 0000-00-00 |
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL       |
+----------+--------+---------+------+------------+------------+
9 rows in set (0.00 sec)

mysql> drop table pet;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+---------------------+
| Tables_in_menagerie |
+---------------------+
| animals             |
| event               |
| person              |
| shirt               |
| shop                |
| t1                  |
+---------------------+
6 rows in set (0.00 sec)

执行恢复命令

可根据单独表备份文件恢复指定表，如果备份文件是整个库则不能单独恢复（我还没学会）

root@8f131dc037af:~# mysql -uroot -p menagerie < menagerie.pet.dump
Enter password:
root@8f131dc037af:~#

查看恢复效果

mysql> show tables;
+---------------------+
| Tables_in_menagerie |
+---------------------+
| animals             |
| event               |
| person              |
| pet                 |
| shirt               |
| shop                |
| t1                  |
+---------------------+
7 rows in set (0.00 sec)

mysql> select * from pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | 0000-00-00 |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | 0000-00-00 |
| Buffy    | Harold | dog     | f    | 1989-05-13 | 0000-00-00 |
| Fang     | Benny  | dog     | m    | 1990-08-27 | 0000-00-00 |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | 0000-00-00 |
| Whistler | Gwen   | bird    |      | 1997-12-09 | 0000-00-00 |
| Slim     | Benny  | snake   | m    | 1996-04-29 | 0000-00-00 |
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL       |
+----------+--------+---------+------+------------+------------+
9 rows in set (0.00 sec)