MySQL mysqldump备份数据库及恢复数据库（mysql命令）_mysqldump还原数据库

数据库的主要作用就是对数据进行保存和维护，所以备份数据是数据库管理中最常用的操作。为了防止数据库意外崩溃或硬件损伤而导致的数据丢失，数据库系统提供了备份和恢复策略。

保证数据安全的最重要的一个措施就是定期的对数据库进行备份。这样即使发生了意外，也会把损失降到最低。

数据库备份是指通过导出数据或者复制表文件的方式来制作数据库的副本。当数据库出现故障或遭到破坏时，将备份的数据库加载到系统，从而使数据库从错误状态恢复到备份时的正确状态。

MySQL 中提供了两种备份方式，即 mysqldump 命令以及 mysqlhotcopy 脚本。由于 mysqlhotcopy 只能用于 MyISAM 表，所以 MySQL 5.7 移除了 mysqlhotcopy 脚本。

本节主要介绍如何使用 mysqldump 命令备份数据库。

mysqldump 命令执行时，可以将数据库中的数据备份成一个文本文件。数据表的结构和数据将存储在生成的文本文件中。

备份一个数据库

使用 mysqldump 命令备份一个数据库的语法格式如下：

mysqldump -u username -p dbname [tbname ...]> filename.sql
1

对上述语法参数说明如下：

• username：表示用户名称；
• dbname：表示需要备份的数据库名称；
• tbname：表示数据库中需要备份的数据表，可以指定多个数据表。省略该参数时，会备份整个数据库；
• 右箭头“>”：用来告诉 mysqldump 将备份数据表的定义和数据写入备份文件；
• filename.sql：表示备份文件的名称，文件名前面可以加绝对路径。通常将数据库备份成一个后缀名为.sql的文件。

注意：mysqldump 命令备份的文件并非一定要求后缀名为.sql，备份成其他格式的文件也是可以的。例如，后缀名为.txt的文件。通常情况下，建议备份成后缀名为.sql 的文件。因为，后缀名为.sql的文件给人第一感觉就是与数据库有关的文件。

例 1

下面使用 root 用户备份 test 数据库下的 student 表。打开命令行（cmd）窗口，输入备份命令和密码，运行过程如下：

C:\Windows\system32>mysqldump -uroot -p test student><span style="color: rgba(0, 0, 0, 1)">C:\student.sql
Enter password: ****
1
2

注意：mysqldump 命令必须在 cmd 窗口下执行，不能登录到 MySQL 服务中执行。

输入密码后，MySQL 会对 test 数据库下的 student 数据表进行备份。之后就可以在指定路径下查看刚才备份过的文件了。student.sql 文件中的部分内容如下：

-- MySQL dump 10.13  Distrib 5.7.29, for Win64 (x86_64)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version 5.7.29-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
--此处删除了部分内容
--
-- Table structure for table `student`
--

DROP TABLE IF EXISTS `student`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `student` (
  `id` int(4) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `stuno` int(11) DEFAULT NULL,
  `age` int(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `student`
--

LOCK TABLES `student` WRITE;
/*!40000 ALTER TABLE `student` DISABLE KEYS */;
INSERT INTO `student` VALUES (1,'zhangsan',23,18),(2,'lisi',24,19),(3,'wangwu',25,18),(4,'zhaoliu',26,18);
/*!40000 ALTER TABLE `student` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
......
-- Dump completed on 2019-03-09 13:03:15
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

student.sql 文件开头记录了 MySQL 的版本、备份的主机名和数据库名。

文件中，以“–”开头的都是 SQL 语言的注释。以 “/!40101” 等形式开头的是与 MySQL 有关的注释。40101 是 MySQL 数据库的版本号，这里就表示 MySQL 4.1.1。如果恢复数据时，MySQL 的版本比 4.1.1 高，“/!40101”和“/”之间的内容被当作 SQL 命令来执行。如果比 4.1.1 低，“/!40101”和“/”之间的内容被当作注释。“/!”和“*/”中的内容在其它数据库中将被作为注释忽略，这可以提高数据库的可移植性。

DROP 语句、CREATE 语句和 INSERT 语句都是数据库恢复时使用的；“DROP TABLE IF EXISTS ‘student’ ”语句用来判断数据库中是否还有名为 student 的表，如果存在，就删除这个表；CREATE 语句用来创建 student 表；INSERT 语句用来恢复所有数据。文件的最后记录了备份的时间。

注意：上面 student.sql 文件中没有创建数据库的语句，因此，student.sql 文件中的所有表和记录必须恢复到一个已经存在的数据库中。恢复数据时，CREATE TABLE 语句会在数据库中创建表，然后执行 INSERT 语句向表中插入记录。

备份多个数据库

如果要使用 mysqldump 命令备份多个数据库，需要使用 –databases 参数。备份多个数据库的语法格式如下：

mysqldump -u username -P --databases dbname1 dbname2 ... > filename.sql
1

加上“–databases”参数后，必须指定至少一个数据库名称，多个数据库名称之间用空格隔开。

例 2

下面使用 root 用户备份 test 数据库和 mysql 数据库。命令如下:

mysqldump -u root -p --databases test mysql>C:\testandmysql.sql
1

执行完后，可以在C:\下面看到名为 testandmysql.sql 的文件，这个文件中存储着这两个数据库的信息。

备份所有数据库

mysqldump 命令备份所有数据库的语法格式如下：

mysqldump -u username -P --all-databases>filename.sql
1

使用“–all-databases”参数时，不需要指定数据库名称。

例 3

下面使用 root 用户备份所有数据库。命令如下：

mysqldump -u root -p --all-databases > C:\all.sql
1

执行完后，可以在 C:\下面看到名为 all.sql 的文件，这个文件中存储着所有数据库的信息。

MySQL恢复数据库（mysql命令）

当数据丢失或意外损坏时，可以通过恢复已经备份的数据来尽量减少数据的丢失和破坏造成的损失。本节主要介绍如何对备份的数据进行恢复操作。

备份文件中通常包含 CREATE 语句和 INSERT 语句。

在 MySQL 中，可以使用 mysql 命令来恢复备份的数据。mysql 命令可以执行备份文件中的 CREATE 语句和 INSERT 语句，也就是说，mysql 命令可以通过 CREATE 语句来创建数据库和表，通过 INSERT 语句来插入备份的数据。

mysql 命令语法格式如下：

mysql -u username -P [dbname] < filename.sql
1

其中：

• username 表示用户名称；
• dbname 表示数据库名称，该参数是可选参数。如果 filename.sql 文件为 mysqldump 命令创建的包含创建数据库语句的文件，则执行时不需要指定数据库名。如果指定的数据库名不存在将会报错；
• filename.sql 表示备份文件的名称。

注意：mysql 命令和 mysqldump 命令一样，都直接在命令行（cmd）窗口下执行。

例 1

下面使用 root 用户恢复所有数据库，命令如下：

mysql -u root -p < C:\all.sql
1

执行完后，MySQL 数据库就已经恢复了 all.sql 文件中的所有数据库。

注意：如果使用--all-databases参数备份了所有的数据库，那么恢复时不需要指定数据库。因为，其对应的 sql 文件中含有 CREATE DATABASE 语句，可以通过该语句创建数据库。创建数据库之后，可以执行 sql 文件中的 USE 语句选择数据库，然后在数据库中创建表并且插入记录。