MySQL数据库的备份与恢复（2）——使用mysqldump命令（热备）

在数据库的日常维护工作中经常需要对数据进行导出操作，mysqldump是MySQL自带的数据导出工具，具有非常强大的功能。mysqldump命令可以把整个数据库装载到一个单独的文本文件中。这个文本文件包含有所有重建数据库所需要的SQL命令。这个命令取得所有的模式（Schema）并且将其转换成DDL语法（CREATE语句，即数据库定义语句），取得所有的数据，并且从这些数据中创建INSERT语句。mysqldump将数据库中所有的设计倒转。

该命令的语法如下：

mysqldump [options] [db_name [tbl_name ...]] > file_name

说明： （1）导出的文本文件包含以下内容：创建数据库判断语句—>删除表—>创建表—>锁表—>禁用索引—>插入数据—>启用索引—>解锁表； （2）options指定操作的用户名、密码以及其他参数； （3）指定要导出的数据库和表。

一、导出所有的数据库

（1）使用–all-databases或-A 参数可以导出所有的数据库及数据，包括系统数据库。

###使用 --all-databases, -A 参数 [root@Mysql11 ~]# mysqldump -uroot -p -A > /tmp/all.sql Enter password: [root@Mysql11 ~]#

（2）使用–add-drop-database参数在每个数据库创建之前添加drop数据库语句

[root@Mysql11 ~]# mysqldump -uroot -p -A --add-drop-database > /tmp/all.sql Enter password:

（3）使用–add-drop-table参数在每个数据表创建之前添加drop数据表语句（该参数默认为打开状态，使用–skip-add-drop-table取消选项）

[root@Mysql11 ~]# mysqldump -uroot -p -A --skip-add-drop-database --skip-add-drop-table > /tmp/all.sql Enter password:

二、导出一个或多个数据库

使用–databases db1 db2 … 参数指定要导出的数据库名，比如导出wgx、hist两个数据库的所有数据：

[root@Mysql11 ~]# mysqldump -uroot -p --databases wgx hist > /tmp/d1d2.sql Enter password:

导出wgx数据库的所有数据：

[root@Mysql11 ~]# mysqldump -uroot -p --databases wgx > /tmp/wgx.sql Enter password:

查看wgx文件的内容如下：

[root@Mysql11 ~]# cat /tmp/wgx.sql -- MySQL dump 10.13 Distrib 5.7.27, for Linux (x86_64) -- -- Host: localhost Database: wgx -- ------------------------------------------------------ -- Server version 5.7.27 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Current Database: `wgx` -- ############### 创建数据库wgx ############################################################ CREATE DATABASE /*!32312 IF NOT EXISTS*/ `wgx` /*!40100 DEFAULT CHARACTER SET utf8 */; ############################################################################################ USE `wgx`; -- -- Table structure for table `department` -- ################ 创建表department ######################################################## DROP TABLE IF EXISTS `department`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `department` ( `dept_id` int(11) NOT NULL AUTO_INCREMENT, `name` char(20) DEFAULT NULL, PRIMARY KEY (`dept_id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; ############################################################################################ -- -- Dumping data for table `department` -- ################ 锁表 #################################################################### LOCK TABLES `department` WRITE; ############################################################################################ /*!40000 ALTER TABLE `department` DISABLE KEYS */; ################ 向表department中插入数据 ################################################# INSERT INTO `department` VALUES (1,'guanli'),(2,'jingji'),(3,'jidian'),(4,'jisuanji'); ############################################################################################ /*!40000 ALTER TABLE `department` ENABLE KEYS */; ################ 解锁 #################################################################### UNLOCK TABLES; ############################################################################################ -- -- Table structure for table `emp` -- DROP TABLE IF EXISTS `emp`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `emp` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` char(20) DEFAULT NULL, `age` int(11) DEFAULT NULL, `address` varchar(20) DEFAULT NULL, `phone` char(11) DEFAULT NULL, `dept_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `emp` -- LOCK TABLES `emp` WRITE; /*!40000 ALTER TABLE `emp` DISABLE KEYS */; INSERT INTO `emp` VALUES (1,'zhangsan',20,'Xinxiang','15578941258',1),(2,'tom',20,'Xinxiang','13778942222',1),(3,'jack',20,'Zhengzhou','13675871454',1),(4,'john',21,'Zhengzhou','13937681111',2),(5,'mark',22,'Aanyang','13055882233',2); /*!40000 ALTER TABLE `emp` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2020-07-01 22:31:19

三、导出某个数据库中指定的表

使用–database指定数据库，使用–tables参数指定表，语法如下：

[root@Mysql11 ~]# mysqldump -uroot -p --databases 数据库名 --tables table1 table2 > /tmp/wgx.sql

注意：导出某个数据库中指定的表，只能针对一个数据库进行导出，且导出的内容和导出整个数据库也不一样，导出指定表的导出文本中没有创建数据库的判断语句，只有删除表-创建表-导入数据。

（1）导出dept和stu表：

[root@Mysql11 ~]# mysqldump -uroot -p --databases hist --tables dept stu > /tmp/wgx.sql Enter password:

导出stu表：

[root@Mysql11 ~]# mysqldump -uroot -p --databases hist --tables stu > /tmp/stu.sql Enter password:

查看stu.sql文件的内容：

[root@Mysql11 ~]# cat /tmp/stu.sql -- MySQL dump 10.13 Distrib 5.7.27, for Linux (x86_64) -- -- Host: localhost Database: hist -- ------------------------------------------------------ -- Server version 5.7.27 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `stu` -- DROP TABLE IF EXISTS `stu`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `stu` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` char(20) DEFAULT NULL, `age` int(11) DEFAULT NULL, `address` varchar(20) DEFAULT NULL, `phone` char(11) DEFAULT NULL, `dept_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `stu` -- LOCK TABLES `stu` WRITE; /*!40000 ALTER TABLE `stu` DISABLE KEYS */; INSERT INTO `stu` VALUES (1,'zhangsan',20,'Xinxiang','15578941258',1),(2,'tom',20,'Xinxiang','13778942222',1),(3,'jack',20,'Zhengzhou','13675871454',1),(4,'john',21,'Zhengzhou','13937681111',2),(5,'mark',22,'Aanyang','13055882233',2); /*!40000 ALTER TABLE `stu` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2020-07-01 22:48:07

四、导出表中满足指定条件的记录

使用–where指定导出的条件，比如：导出stu表中dept_id为1的记录：

[root@Mysql11 ~]# mysqldump -uroot -p --databases hist --tables stu --where='dept_id=1 > /tmp/stu.sql Enter password: [root@Mysql11 ~]# [root@Mysql11 ~]# cat /tmp/stu.sql -- MySQL dump 10.13 Distrib 5.7.27, for Linux (x86_64) -- -- Host: localhost Database: hist -- ------------------------------------------------------ -- Server version 5.7.27 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `stu` -- DROP TABLE IF EXISTS `stu`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; #### 创建表 CREATE TABLE `stu` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` char(20) DEFAULT NULL, `age` int(11) DEFAULT NULL, `address` varchar(20) DEFAULT NULL, `phone` char(11) DEFAULT NULL, `dept_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `stu` -- -- WHERE: dept_id=1 #### 锁表 LOCK TABLES `stu` WRITE; /*!40000 ALTER TABLE `stu` DISABLE KEYS */; ### 插入数据 INSERT INTO `stu` VALUES (1,'zhangsan',20,'Xinxiang','15578941258',1),(2,'tom',20,'Xinxiang','13778942222',1),(3,'jack',20,'Zhengzhou','13675871454',1); /*!40000 ALTER TABLE `stu` ENABLE KEYS */; ### 解锁 UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2020-07-01 22:56:16

导出stu表中姓名为’jack’的学生数据：

[root@Mysql11 ~]# mysqldump -uroot -p --databases hist --tables stu --where="name='jack'" > /tmp/stu.sql Enter password: [root@Mysql11 ~]# [root@Mysql11 ~]# cat /tmp/stu.sql -- MySQL dump 10.13 Distrib 5.7.27, for Linux (x86_64) -- -- Host: localhost Database: hist -- ------------------------------------------------------ -- Server version 5.7.27 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `stu` -- DROP TABLE IF EXISTS `stu`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `stu` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` char(20) DEFAULT NULL, `age` int(11) DEFAULT NULL, `address` varchar(20) DEFAULT NULL, `phone` char(11) DEFAULT NULL, `dept_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `stu` -- -- WHERE: name='jack' LOCK TABLES `stu` WRITE; /*!40000 ALTER TABLE `stu` DISABLE KEYS */; INSERT INTO `stu` VALUES (3,'jack',20,'Zhengzhou','13675871454',1); /*!40000 ALTER TABLE `stu` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2020-07-01 22:58:07

五、只导出表结构而不导出数据

使用–no-data参数可以只导出表结构，如下：

[root@Mysql11 ~]# mysqldump -uroot -p --databases hist --tables stu --no-data > /tmp/stu.sql Enter password: [root@Mysql11 ~]# cat /tmp/stu.sql -- MySQL dump 10.13 Distrib 5.7.27, for Linux (x86_64) -- -- Host: localhost Database: hist -- ------------------------------------------------------ -- Server version 5.7.27 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `stu` -- DROP TABLE IF EXISTS `stu`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `stu` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` char(20) DEFAULT NULL, `age` int(11) DEFAULT NULL, `address` varchar(20) DEFAULT NULL, `phone` char(11) DEFAULT NULL, `dept_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2020-07-01 23:04:42

六、生成新的binlog文件

使用-F参数可以在导出数据之后生成一个新的binlog文件。

[root@Mysql11 ~]# mysqldump -uroot -p --databases wgx -F >/tmp/wgx.sql Enter password:

七、导出存储过程和自定义函数

使用–routines或-R参数可以同时导出数据库中的存储过程和自定义函数：

[root@Mysql11 ~]# mysqldump -uroot -p --databases wgx --routines > /tmp/wgx.sql Enter password:

八、保证导出的一致性状态

使用–single-transaction参数在导出数据之前提交一个BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于InnoDB存储引擎。该选项和–lock-tables 选项是互斥的，因为LOCK TABLES 会使任何挂起的事务隐含提交。

[root@Mysql11 ~]# mysqldump -uroot -p --databases wgx --single-transaction > /tmp/wgx.sql Enter password:

九、导入备份的数据

mysql -uroot -p < stu.sql