MySQL命令之mysqldump -- 数据库备份程序

命令介绍

mysqldump 是 MySQL 用于转存储数据库的客户端程序。它主要产生一系列的 SQL 语句，可以封装到文件，该文件包含所有重建您的数据库所需要的 SQL 命令语句，如 CREATE DATABASE，CREATE TABLE，INSERT 等等。可以用来实现轻量级的快速迁移或恢复数据库。是 MySQL 数据库实现逻辑备份的一种方式。

在日常维护工作当中经常会需要对数据进行导出操作，而 mysqldump 则是 MySQL 导出数据时经常用到的命令工具。

mysqldump 命令可用来转储数据库进行备份或将数据转移到另一个 SQL 服务器（不一定是 MySQL 服务器）。

注意：
1.复制整个数据库目录也可以备份数据库，也是最直接有效的方式，但是只适用于使用了 MyISAM 引擎的数据库，不适用于使用了 InnoDB 引擎的数据库。
2.如果你在服务器上进行备份，并且表均为 MyISAM 表，应考虑使用 mysqlhotcopy，因为可以更快地进行备份和恢复。

常用选项

请参阅《MySQL命令之mysqldump的选项详解》

参考示例

将指定数据表的数据导出为 SQL 脚本文件和文本文件

将数据库 test 中的数据表 student 导出，导出的文件存放在 /root/test 目录下：

[root@htlwk0001host ~]# mysqldump -u root -p -T /root/test test student;
Enter password: 
1
2
3

注：
1.选项 -u 指定登录数据库的用户名；选项 -p 输入登录密码；选项 -T 指定导出的数据文件的存放位置
2.执行上面的命令语句后，会得到两个文件：student.sql 和 student.txt

将指定的多个数据表的数据导出为 SQL 脚本文件和文本文件

[root@htlwk0001host ~]# mysqldump -uroot -pqpw123.com -T /root/test qydpw td_appraise_dimension td_area;
1

注：
1.命令默认将路径后面的 qydpw 解析为数据库名称，后面的词语解析为数据表的名称。
2.表名之间只能使用空格分隔。

将指定数据库导出到脚本文件中

导出文件如果没有指明路径，默认是存放在用户家目录下。

语法格式：

mysqldump -u用户名 -p密码 数据库名 > 导出的文件名
1

例如，将数据库 test 导出到文件 test.sql 中：

[root@htlwk0001host ~]# mysqldump -uroot -p123456 test > test.sql 
1

说明：
1.默认选项 -p 后面的是数据库名称
2.导出的脚本文件存放在当前工作目录下
3.脚本文件中包含建表语句和插入数据的insert语句，不含创建数据库的语句

将指定的多个数据库导出到脚本文件中

[root@htlwk0001host ~]# mysqldump -uroot -p123456 --databases db1 db2 > /tmp/test.sql 
1

将指定的表导出到脚本文件中

语法格式：

mysqldump -u 用户名 -p密码 数据库名 表名 > 导出的文件名
1

例如，将数据库 test 的表 student 导出到文件 student.sql 中：

[root@htlwk0001host ~]# mysqldump -uroot -p123456 test student > student.sql
1

注：
1.命令会自动识别处第一个词语 test 是数据库名称，后面跟着是表的名称。
2.如果要将表的数据导入到指定的文件中，切记，不能使用选项 -T 或者 --tab 指定文件存放的路径，因为指定了文件存放的路径，命令会自己另外生产两个文件（一个是 .sql 文件，一个是 .txt 文件）存放在指定的路径下，而你自己指定的文件也会生成，但是不会有任何数据。
3.生成的文件默认存放在当前工作目录下。

将指定数据库中的多个表的数据导出到指定的文件中

将数据库 db1 中的表 a1 和 a2 导出到文件 /tmp/db1.sql 中：

[root@htlwk0001host ~]# mysqldump -uroot -p123456 --databases db1 --tables a1 a2 > /tmp/db1.sql 
1

注：切记，如果要将数据导出到你自己指定的文件中，则不能使用选项 -T 或者 --tab 指定文件存放的路径

将数据表中满足特定条件的记录导出

例如，导出数据库 db1 中的表 a1 中的 id=1 的数据：

[root@htlwk0001host ~]# mysqldump -uroot -proot --databases db1 --tables a1 --where='id=1' > /tmp/a1.sql 
1

例如，导出数据库 db1 中的表 a1 中的 name='liaowenxiong' 的数据：

[root@htlwk0001host ~]# mysqldump -uroot -proot --no-create-info --databases db1 --tables a1 --where="name='liaowenxiong'"  > /tmp/a1.sql
1

导出数据之后生成一个新的 binlog 文件

有时候会希望导出数据之后生成一个新的 binlog 文件，只需要加上 -F 参数即可：

[root@htlwk0001host ~]# mysqldump -uroot -proot --databases db1 -F > /tmp/db1.sql 
1

导出指定数据库的所有表结构

语法格式：

mysqldump -u user_name -p -d --add-drop-table database_name > outfile_name.sql
1

说明：
1.选项 -d 或 --no-data 表示不导出行数据
2.选项 --add-drop-table 表示在每个 create table 语句之前增加一个 drop table 语句
3.导出的脚本文件中的内容都是创建表的语句，不含创建数据库的语句，不含插入数据的语句

例如，将数据库 test 的数据结构导出到文件 test.sql 文件中：

[root@htlwk0001host ~]# mysqldump -uroot -p1234567 -d --add-drop-table test > test.sql
1

导出的 test.sql 的内容如下所示：

-- MySQL dump 10.13  Distrib 5.7.31, for Linux (x86_64)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version	5.7.31

/*!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 utf8mb4 */;
/*!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 `dept_htlwk`
--

DROP TABLE IF EXISTS `dept_htlwk`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `dept_htlwk` (
  `deptno` varchar(50) NOT NULL COMMENT '部门代码',
  `dname` varchar(50) NOT NULL COMMENT '部门名称',
  `location` varchar(200) NOT NULL COMMENT '部门地址'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门表';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `emp_htlwk`
--

DROP TABLE IF EXISTS `emp_htlwk`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `emp_htlwk` (
  `empno` varchar(50) DEFAULT NULL COMMENT '职员代码',
  `ename` varchar(50) DEFAULT NULL COMMENT '职员姓名',
  `job` varchar(50) DEFAULT NULL COMMENT '职员岗位',
  `salary` decimal(7,2) DEFAULT NULL COMMENT '职员月薪',
  `bonus` decimal(7,2) DEFAULT NULL COMMENT '奖金',
  `hiredate` date DEFAULT NULL,
  `mgr` varchar(50) DEFAULT NULL COMMENT '上级代码',
  `deptno` varchar(50) DEFAULT NULL COMMENT '部门代码'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='职员表';
/*!40101 SET character_set_client = @saved_cs_client */;
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
38
39
40
41
42
43
44
45
46
47
48
49

导出指定数据表的数据，同时自定义导出数据的格式

[root@htlwk0001host ~]# mysqldump -u root -p -T /root/test test student --fields-terminated-by '\t' --fields-enclosed-by '"' --lines-terminated-by '\n';
1

说明：
--fields-terminated-by 'string' # 字段分隔符
--fields-enclosed-by 'char' # 字段引用符
--fields-optionally-enclosed-by 'char' # 字段引用符，只在 char、varchar、text、date 等字段类型上生效
--fields-escaped-by 'char' # 转义字符
--lines-terminated-by 'string' # 记录结束符，即换行符

我们看下使用自定义格式导出的文本文件的内容：

[root@htlwk0001host ~]# cat /root/test/student.txt
"00000000000000000001"	"liaowenxiong"	"18"	\N	\N	"2021-09-25 02:40:51"
"00000000000000000002"	"liudehua"	"28"	\N	\N	"2021-09-25 02:40:51"
"00000000000000000003"	"zhangxueyou"	"38"	\N	\N	"2021-09-25 02:40:51"
1
2
3
4

可以看到字段值全部加上了双引号。

将指定数据库的数据全部导出，并备份数据库的一致性状态，并在备份文件中记录二进制日志最后的位置

例如，将数据库 test 的数据全部导出：

[root@htlwk0001host ~]# mysqldump -h10.10.30.241 -uadmin -p123456 --single-transaction --master-data=2 --triggers --routines --events test -T /data/backup/
1

注：
1.会将每个数据表导出成两个文件：SQL 脚本文件和文本文件。

2.--single-transaction，会在导出数据之前写入一个 START TRANSACTION SQL 语句，它仅对事务表（如InnoDB）有用，因为它会在启动事务时转储数据库的一致状态，而不会阻塞任何应用程序。

3.--master-data=2，将二进制文件最后的位置记录到备份文件中

在备份文件中你会看到下面这条语句：

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=4544;
1

上面的语句表示你在备份数据的时候，二进制日志文件名称是 mysql-bin.000001，而且在备份的时刻，二进制日志文件的最后的位置是 4544，那么将来你要恢复数据，可以从 4544 位置之后开始恢复，因为之前的数据你已经备份成了 SQL 脚本文件，你只要执行这个脚本文件就可以恢复数据了，但是位置 4544 之后发生的数据变化，你就要从这个位置往后慢慢找了。

4.--triggers，导出触发器

5.--routines，导出存储过程和函数

6.--events，Include Event Scheduler events for the dumped databases in the output. This option requires the EVENT privileges for those databases.

备份指定数据库的数据，并在备份文件中记录二进制日志最后的位置，而且刷新日志文件

[root@htlwk0001host ~]# mysqldump -uroot -pqpw123.com --lock-all-tables --triggers --routines --events --flush-logs --master-data=2 test > /root/test/test.sql	
1

说明：
刷新日志文件，会生成一个新的日志文件，而新的日志文件并不是旧的日志文件的复制，我看过新旧日志文件的内容并不相同。

将一个 MySQL 服务器的数据库的数据复制到另外一个 MySQL 服务器的数据库中

语法格式：

[root@htlwk0001host ~]# mysqldump --host=host1 -uuser_name -p --opt source_db| mysql --host=host2 -uuser_name -p -C target_db
1

注：host2 主机上已经创建 target_db 数据库。

例如，将当前数据库服务器中的数据库 qydpw 的数据全部复制到 IP 地址为 114.28.38.109 的数据库服务器的数据库 test 中：

[root@htlwk0001host ~]# mysqldump -uroot -p123456 --opt qydpw | mysql --host=114.28.38.109 -uroot -p123456 -C test
[root@htlwk0001host ~]# mysqldump --host=192.168.80.137 -uroot -proot -C --databases test | mysql --host=192.168.80.133 -uroot -proot test
1
2

说明：
1.选项 -C 可以启用压缩传递。
2.如果没有使用 --opt，mysqldump 就会把整个结果集装载到内存中，然后导出。如果数据非常大就会导致导出失败。

导出所有的数据库到脚本文件中

该命令会导出包括系统数据库在内的所有数据库：

[root@htlwk0001host ~]# mysqldump -uroot -proot --all-databases > /tmp/all.sql 
1

压缩备份指定的数据库

[root@htlwk0001host ~]# mysqldump -uroot -p123456 -P3306 -q -Q --set-gtid-purged=OFF --default-character-set=utf8 --hex-blob --skip-lock-tables --databases test | gzip >/root/test/test.sql.gz

[root@htlwk0001host ~]# mysqldump -uroot -p123456 -P3306 -q -Q --set-gtid-purged=OFF --default-character-set=utf8 --hex-blob --skip-lock-tables --databases test 2>/root/test/test.err | gzip >/root/test/test.sql.gz
1
2
3

说明：其中的语句 2>/root/test/test.err 表示将标准错误内容重定向到文件 test.err 中，如文件存在内容则清空。2> 表示错误输出重定向。

将压缩的备份数据文件解压，并还原到当前的数据库服务器中

[root@htlwk0001host ~]# gunzip -c test.sql.gz | mysql -uroot -pqpw123.com -vvv -P3306 --default-character-set=utf8 1> test.log 2> test.err
1

说明：
1.1> test.log 执行过程信息标准输出重定向到文件 test.log 中
2.2> test.err 将标准错误内容重定向到文件 test.err 中，如文件存在内容则清空

压缩备份和还原数据

[root@htlwk0001host ~]# mysqldump -hhost_name -uuser_name -ppassword --databases db_name | gzip > backup-file.sql.gz
1

对应的还原动作为：

[root@htlwk0001host ~]# gunzip < backup-file.sql.gz | mysql -uuser_name -ppassword db_name
1

注意：如果数据库服务器中不存在 db_name，执行上面的还原语句会报错，把 db_name 去掉即可

结合 Linux 的 cron 命令实现定时备份数据

定时执行备份命令语句

比如需要在每天凌晨 1:30 备份某个主机上的所有数据库并压缩 dump 文件为 gz 格式，那么可在 /etc/crontab 配置文件中加入下面代码行：

30 1 * * * root mysqldump -u root -pPASSWORD --all-databases | gzip > /mnt/disk2/database_`date '+%m-%d-%Y'`.sql.gz
1

前面5个参数分别表示分钟、小时、日、月、年，星号表示任意。date '+%m-%d-%Y' 得到当前日期的 MM-DD-YYYY 格式。

定时执行备份脚本文件

编写 Shell 脚本备份 MySQL 数据库：

[root@htlwk0001host ~]# vi /backup/backup.sh
#!bin/bash
cd /backup
echo "You are in backup dir"
mv backup* /oldbackup
echo "Old dbs are moved to oldbackup folder"
File = backup-$Now.sql
mysqldump -u user -p password database-name > $File
echo "Your database backup successfully completed"
1
2
3
4
5
6
7
8
9

上面脚本文件保存为 backup.sh，并且系统中已经创建两个目录 /olcbackup 和 /backup。每次执行 backup.sh 时都会先将 /backup 目录下所有名称以 backup 开头的文件移到 /oldbackup 目录下。

为上述脚本制定执行计划如下：

[root@htlwk0001host ~]# crontab -e
30 1 * * * /backup.sh
1
2