作者公众号，欢迎一起交流。

mysqldump命令是MySQL自带的客户端程序，可用于对数据库进行备份，由于是逻辑备份，故可以看到备份的内容是有一系列的SQL语句组成，并可根据实际情况对备份内容进行修改。当然，使用mysqldump进行数据库备份，需要对应的权限，比如备份表，至少需要对表的select权限，备份视图，需要对视图的show view权限，同样的，将备份重新加载进数据库，也需要相应的权限，本篇将对mysqldump命令进行一个详细的介绍。

1 语法结构

使用mysqldump进行数据库备份有三种方法，默认时将导出的SQL语句输出到屏幕，可将其保存到文件中，用法分别是：

Usage: mysqldump [OPTIONS] database [tables]OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]OR mysqldump [OPTIONS] --all-databases [OPTIONS]

1.1 mysqldump [OPTIONS] database [tables]

用于导出某个数据库或表，在导出的脚本中，没有create database和use语句，在重新导入时，可指定数据库，若没有数据库，则需要提前进行创建。

1.2 mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]

用于导出特定的数据库，--databses选项指定后面所导出的数据库列表，以空格分割，在每个数据库导出之前，在导出的脚本中会写create database和use语句，用于数据库的创建和指定要操作的库，在导入时如果数据库不存在，则会进行创建。

1.3 mysqldump [OPTIONS] --all-databases [OPTIONS]

用于导出所有数据库，默认不会导出performance_schema、sys，也不会导出information_schema，若需要导出时，需指定--databases选项。

2 选项说明

mysqldump的选项有很多，可使用mysqldump --help进行查看，选项有两种格式，一种是长格式，一种是短格式，这里将对选项进行说明。

2.1 帮助选项

使用mysqldump时，可通过以下选项展示帮助信息：

-?, --help：展示帮助信息
-V, --version：展示版本信息

2.2 数据库连接选项

要dump数据，需要先连接到数据库，那么对应的选项分别如下：

--bind-address=name：当服务器有多个网卡时，使用该选项绑定要连接MySQL服务器的网卡
-C，--compress：压缩客户端和服务器间发送的信息，MySQL 8.0.18后废弃该选项
--compress-algorithms=name：MySQL 8.0.18新增，指定压缩的算法，默认是uncompressed，压缩算法包括：zstd、ziib、uncompressed
--default-auth=name：客户端使用的默认认证插件
--enable-cleartext-plugin：激活/禁用mysql_clear_password认证插件
-h，--host=name：指定连接的主机名或IP，默认主机是localhost
--login-path=#：在.mylogin.cnf登录路径文件读取选项，创建或修改登录文件，需使用mysql_config_editor工具
-p，--password[=name]：指定连接数据库的密码
--plugin-dir=name：指定客户端查看的目录
-P，--port=#：使用TCP/IP连接时指定数据库端口
--skip-mysql-schema：MySQL 8.0.27引入，在还原dump文件时，不drop mysql schema，默认情况下，drop mysql schema
-S，--socket=name：若连接localhost，需指定该选项，指定使用Unix socket进行连接
-u，--user=name：指定连接数据库的用户名
--zstd-compression-level=#：使用zstd压缩算法时，指定压缩级别，默认是3，可设置1-22之间的某一个值
--ssl*：这里暂不介绍

2.3 选项文件选项

在使用mysqldump时，可指定选项文件来控制使用哪些选项：

--defaults-extra-file=#：在读取全局选项文件后，读取该选项文件
--defaults-file=#：只读取给定的选项文件
--defaults-group-suffix=#：不仅读取常规选项组，还读取以字串为前缀的常规名字命名的组
--no-defaults：指定该选项时，不会读取任何选项文件
--print-defaults：读取程序名称以及选项文件中的所有选项

2.4 DDL相关选项

使用mysqldump时，和DDL相关的选项如下：

--add-drop-database：导出时在每个create语句前增加drop database语句
--add-drop-table：默认ON，导出时在每个create语句前增加drop table语句
--add-drop-trigger：导出时在每个create语句前增加drop trigger语句
-Y，--all-tablespaces：导出所有的表空间，仅用于NDB Cluster表
-n，--no-create-db：指定--all-databases或--databases时抑制create database语句输出
-t，--no-create-info：导出时不输出表创建信息，即只导出数据，不导出表定义
-y，--no-tablespaces：导出时不输出所有的create logfile group和create tablespace语句
--replace：导出时使用replace into代替insert into

2.5 数据库过滤选项

使用mysqldump时，可通过选项对导出的对象进行过滤：

-A，--all-databases：导出所有数据库下的所有表
-B，--databases：导出指定的数据库
-E，--events：导出events
--ignore-error=name：指定导出遇到错误时忽略的错误号码
--ignore-table=name：导出时排除导出的表，若排除多个表，则需指定多次，每次指定一个表，例如：--ignore-table=db.table
-d，--no-data：导出时不导出数据行，即只导出表定义，不导出数据
-R，--routines：导出存储过程和函数
--tables：覆盖--databases或-B选项，导出表
--triggers：导出触发器，默认和表一起导出
-w，--where=name：导出满足条件的数据行，需用双引号引起条件

2.6 复制选项

使用mysqldump时，可创建空的实例，或者用于创建复制环境：

--apply-replica-statements：MySQL 8.0.26引入，和--dump-replica一起，导出时在语句之前增加stop replica语句，并在最后增加start replica语句
--delete-source-logs：MySQL 8.0.26引入，备份之前刷新日志，相当于执行flush logs，执行dump操作之后通过发送perge binary logs删除binlog，该选项自动激活--source-data选项
--dump-replica[=#]：导出时输出中包含binlog文件名和位置，指定1，则直接以change replication source to输出，指定2，以备注的形式输出
--include-source-host-port：和--dump-replica一起导出时在change replication source to后增加master_host=<host>,master_port=<port>
--source-data[=#]：导出时输出中包含binlog文件名和位置，指定1，则直接以change replication source to输出，指定2，以备注的形式输出
--set-gtid-purged[=name]：输出中增加SET @@GLOBAL.GTID_PURGED，默认值是AUTO，该选项包含的值分别是ON、COMMENTED、OFF和AUTO

2.7 格式选项

使用mysqldump时，可通过以下选项对输出内容进行格式化：

--compact：控制输出更少的信息，该选项启用--skip-add-drop-table、--skip-add-locks、--skip-comments、--skip-disable-keys和--skip-set-character选项
--compatible=name：兼容其它数据库或旧版本的MySQL服务器，唯一允许的值是ANSI
-c，--complete-insert：导出时使用完整的insert语句
-a，--create-options：默认ON，导出时包括create table语句中所有的表选项
--fields-terminated-by=name、--fields-enclosed-by=name、--fields-optionally-enclosed、--fields-escaped-by=name：使用--tab选项时指定，和使用load data时相关的fields有同样的作用
--hex-blob：以16进制打印binary字符串（binary、var binary、blob）
--lines-terminated-by=name：导出时行分隔符，使用--tab选项时指定
-Q，--quote-names：默认ON，导出时使用·来括住表和列名，可使用--skip-quote-names禁用
-r, --result-file=name：直接输出到指定的文件，用于Windows对换行的处理
-T，--tab=name：创建tab分割的文本文件，表定义和数据分开存放，分别创建tb1_name.sql和tb1_name.txt文件
--tz-utc：默认ON，该选项可使timestamp列dump和加载进不同时区的服务器
-X，--xml：以xml格式导出数据库

2.8 性能选项

使用mysqldump时，可通过以下选项对恢复时的性能进行控制：

--column-statistics：在输出中添加analyze table语句，用于加载时搜集统计信息
-K，--disable-keys：默认ON，该选项只对MyISAM表的非唯一索引有效
-e，--extended-insert：默认ON，导出时使用包含多个VALUES列表的多行insert语句
--insert-ignore：导出时使用insert ignore插入行
--max-allowed-packet=#：默认24M，最大1G，用于在客户端/服务器通信的最大缓冲区
--net-buffer-length=#：客户端/服务器通信的初始缓冲区大小
-M, --network-timeout：默认ON
--opt：默认ON，相当于--add-drop-table、--add-locks、--create-options、--quick、--extended-insert、--lock-tables、--set-character和--disable-keys，可使用--skip-opt禁用
-q, --quick：用于dump大表，用于逐行检索数据，而不是检索整个行集并缓存到内存再dump

2.9 事务选项

使用mysqldump时，可通过如下选项做事务相关的设置：

--add-locks：默认ON，导出时对每个表使用lock tables和unlocktables包着，加载时可更快
-F, --flush-logs：在开始dump之前刷新MySQL服务器日志文件，如果使用--databases或--all-databases选项，则每dump一个数据库都会刷新一次日志；如果只想刷新一次，可以将该选项与--lock-all-tables、--source-data或--master-data或--single-transaction一起使用
--flush-privileges：dump mysql数据库后在输出文件中增加flush privileges语句
-x，--lock-all-tables：通过在整个dump期间获取全局读锁来实现锁定所有数据库中的所有表，该选项自动关闭--single-transaction和--lock-tables
-l，--lock-tables：默认ON，对于每个被dump的数据库，在dump前锁定所有的表，对于InnoDB表，使用--single-transaction更优
--no-autocommit：将每个dump的表的insert语句封装在set autocommit=0和commit语句之间
--order-by-primary：导出时以主键或唯一键排序，主要用于导出MyISAM表至InnoDB表，但比较耗时
--single-transaction：该选项将事务隔离级别设置为repeatable read，并在dump之前向服务器发送一条start transaction语句。只对事务表（InnoDB）有用，因为它会在发出start transaction时dump数据库的一致状态，而不会阻塞任何应用程序；--single-transaction和--lock-tables选项是互斥的，因为lock tables会导致任何挂起的事务被隐式提交；要dump大表，可以将选项--single-transaction和--quick结合使用

2.10 国际化选项

使用mysqldump时，可通过以下选项展示字符集设置：

--character-sets-dir=name：字符集安装的目录
--default-character-set=name：设置默认字符集，若不指定，则使用utf8
-N, --no-set-names：关闭--set-charset设置
--set-charset：默认ON，输出中增加set names default_character_set语句

2.11 调式选项

使用mysqldump时，可通过选项在dump文件中输出调试信息：

--allow-keywords：允许使用关键字创建列
-i, --comments：默认ON，在导出的dump文件中增加诸如程序版本、服务器版本和主机信息
--dump-date：默认ON，在导出的dump文件最后打印日期
-f, --force：导出过程中忽略错误
--log-error=name：默认不记录警告和错误信息，导出过程中可以向指定的文件输出警告和错误信息
-v, --verbose：Verbose模式，打印更多的信息

3 dump示例

1）导出某一个数据库，不指定--database选项，可以看出导出的脚本不包含create database和use语句，这样可以将dump的数据库导入到其它数据库中

[root@node1 ~]# mysqldump -h192.168.56.201 -ufordba -pxxxxx sakila --set-gtid-purged=off > db01.sql

2）导出某一个数据库，指定--database选项，可以看出导出的脚本包含create database和use语句，若导出多个数据库，则在--database后以空格分割列出多个数据库

[root@node1 ~]# mysqldump -h192.168.56.201 -ufordba -pxxxxx --databases sakila --set-gtid-purged=off > db02.sql

3）导出所有数据库，使用--all-databases，或使用--databases列出多个数据库，输出同上

[root@node1 ~]# mysqldump -h192.168.56.201 -ufordba -pxxxxx --all-databases --set-gtid-purged=off > db03.sql

4）导出某一张表，将其表结构和数据分开

[root@node1 tmp]# mysqldump -h 192.168.56.201 -ufordba -pxxxxx sakila actor -T /tmp --single-transaction --set-gtid-purged=off[root@node1 tmp]# ll actor.*-rw-r--r--. 1 root root 1614 Dec 26 09:19 actor.sql-rw-r-----. 1 mysql mysql 7399 Dec 26 09:19 actor.txt

5）导出整库或者大表时，指定--quick，可提升导出的速度，若导出存储过程、函数、event时，可指定--routines、--events和--triggers选项

[root@node1 ~]# mysqldump -h 192.168.56.201 -ufordba -pxxxxx sakila --routines --events --triggers --single-transaction --quick --set-gtid-purged=off > db04.sql

6）若搭建复制环境，可通过指定--source-data等选项在导出的脚本中增加change replication语句，将dump的脚本用于搭建从库

[root@node1 ~]# mysqldump -h 192.168.56.201 -ufordba -pxxxxx --all-databases --routines --events --triggers --single-transaction --quick --set-gtid-purged=off --source-data=2 > db05.sql

7）基于从库搭建新的从库，可在从库dump数据库，然后在新从库上执行dump的脚本

[root@node2 ~]# mysqldump -h192.168.56.202 -ufordba -pxxxxx --all-databases --dump-replica=2 --include-source-host-port --single-transaction --set-gtid-purged=off > db01.sql