4.5.4 mysqldump —数据库备份程序
mysqldumpClient 端 Util 执行logical backups,生成一组 SQL 语句,可以执行这些语句来重现原始数据库对象定义和 table 数据。它转储一个或多个 MySQL 数据库以进行备份或转移到另一台 SQL 服务器。 mysqldump命令还可以生成 CSV,其他定界文本或 XML 格式的输出。
mysqldump至少对转储 table 具有SELECT特权,对转储视图至少具有SHOW VIEW特权,对于转储触发器具有TRIGGER特权,如果不使用--single-transaction选项则需要LOCK TABLES,并且(从 MySQL 5.7.31 起)至少需要PROCESS。如选项说明中所述,某些选项可能需要其他特权。
要重新加载转储文件,您必须具有执行该文件所包含的语句所需的特权,例如由这些语句创建的对象的相应CREATE
特权。
mysqldump输出可以包含ALTER DATABASE条用于更改数据库排序规则的语句。在转储存储的程序以保留其字符编码时,可以使用这些字符。要重新加载包含此类语句的转储文件,需要对受影响的数据库具有ALTER
特权。
Note
在 Windows 上使用 PowerShell 通过输出重定向创建的转储将创建具有 UTF-16 编码的文件:
shell> mysqldump [options] > dump.sql
但是,不允许将 UTF-16 作为连接字符集(请参见不允许的 Client 字符集),因此转储文件将无法正确加载。要变通解决此问题,请使用--result-file
选项,该选项以 ASCII 格式创建输出:
shell> mysqldump [options] --result-file=dump.sql
性能和可伸缩性注意事项
mysqldump
的优势包括在还原之前查看甚至编辑输出的便利性和灵 Active。您可以克隆数据库以进行开发和 DBA 工作,也可以对现有数据库进行一些改动以进行测试。它并非旨在作为用于备份大量数据的快速或可扩展解决方案。对于大数据量,即使备份步骤花费了合理的时间,恢复数据也可能会非常缓慢,因为重播 SQL 语句会涉及用于插入,创建索引等的磁盘 I/O。
对于大规模备份和还原,更适合使用physical备份来复制原始格式的数据文件,该格式可以快速还原:
- 如果您的 table 主要是
InnoDB
table,或者混合使用InnoDB
和MyISAM
table,请考虑使用 MySQL Enterprise Backup 产品的 mysqlbackup 命令。 (作为 Enterprise 订阅的一部分提供.)它为InnoDB
备份提供了最佳性能,同时中断最少。它还可以从MyISAM
和其他存储引擎备份 table;并且它提供了许多方便的选项来适应不同的备份方案。参见第 29.2 节“ MySQL 企业备份概述”。
mysqldump可以逐行检索和转储 table 内容,也可以从 table 中检索全部内容并在转储之前将其缓冲在内存中。如果要转储大 table,内存中的缓冲可能会成为问题。要逐行转储 table,请使用--quick选项(或--opt,启用--quick)。默认情况下启用--opt选项(因此也启用--quick),因此要启用内存缓冲,请使用--skip-quick。
如果您使用的是最新版本的mysqldump来生成转储以重新加载到非常旧的 MySQL 服务器中,请使用--skip-opt选项而不是--opt或--extended-insert选项。
有关mysqldump的其他信息,请参见第 7.4 节“使用 mysqldump 进行备份”。
Invocation Syntax
通常,共有三种使用mysqldump的方式-为了转储一组一个或多个 table,一组一个或多个完整数据库或整个 MySQL 服务器-如下所示:
shell> mysqldump [options] db_name [tbl_name ...]
shell> mysqldump [options] --databases db_name ...
shell> mysqldump [options] --all-databases
要转储整个数据库,请不要在* db_name
*之后命名任何 table,也不要使用--databases或--all-databases选项。
要查看您的mysqldump版本支持的选项列 table,请发出命令mysqldump --help。
选项语法-按字母 Sequences 汇总
mysqldump支持以下选项,可以在命令行或选项文件的[mysqldump]
和[client]
组中指定这些选项。有关 MySQL 程序使用的选项文件的信息,请参见第 4.2.2.2 节“使用选项文件”。
table4.15 mysqldump 选项
Option Name | Description | Introduced | Deprecated |
---|---|---|---|
--add-drop-database | 在每个 CREATE DATABASE 语句之前添加 DROP DATABASE 语句 | ||
--add-drop-table | 在每个 CREATE TABLE 语句之前添加 DROP TABLE 语句 | ||
--add-drop-trigger | 在每个 CREATE TRIGGER 语句之前添加 DROP TRIGGER 语句 | ||
--add-locks | 用 LOCK TABLES 和 UNLOCK TABLES 语句包围每个 table 转储 | ||
--all-databases | 转储所有数据库中的所有 table | ||
--allow-keywords | 允许创建作为关键字的列名 | ||
--apply-slave-statements | 在 CHANGE MASTER 语句之前包含 STOP SLAVE,在输出结束时包含 START SLAVE | ||
--bind-address | 使用指定的网络接口连接到 MySQL Server | ||
--character-sets-dir | 字符集的安装目录 | ||
--comments | 添加 Comments 到转储文件 | ||
--compact | 产生更紧凑的输出 | ||
--compatible | 产生与其他数据库系统或更旧的 MySQL 服务器更兼容的输出 | ||
--complete-insert | 使用包含列名称的完整 INSERT 语句 | ||
--compress | 压缩 Client 端和服务器之间发送的所有信息 | ||
--create-options | 在 CREATE TABLE 语句中包括所有特定于 MySQL 的 table 选项 | ||
--databases | 将所有名称参数解释为数据库名称 | ||
--debug | 编写调试日志 | ||
--debug-check | 程序退出时打印调试信息 | ||
--debug-info | 程序退出时打印调试信息,内存和 CPU 统计信息 | ||
--default-auth | 身份验证插件使用 | ||
--default-character-set | 指定默认字符集 | ||
--defaults-extra-file | 除常规选项文件外,还读取命名的选项文件 | ||
--defaults-file | 只读命名的选项文件 | ||
--defaults-group-suffix | 选项组后缀值 | ||
--delete-master-logs | 在主复制服务器上,执行转储操作后删除二进制日志 | ||
--disable-keys | 对于每个 table,在 INSERT 语句周围加上用于禁用和启用键的语句 | ||
--dump-date | 如果给出--comments,则将转储日期包括为“转储完成于”Comments | ||
--dump-slave | 包含 CHANGE MASTER 语句,该语句列出了从属主机的二进制日志坐标 | ||
--enable-cleartext-plugin | 启用明文身份验证插件 | 5.7.10 | |
--events | 从转储的数据库中转储事件 | ||
--extended-insert | 使用多行 INSERT 语法 | ||
--fields-enclosed-by | 该选项与--tab 选项一起使用,其含义与 LOAD DATA 的相应子句相同 | ||
--fields-escaped-by | 该选项与--tab 选项一起使用,其含义与 LOAD DATA 的相应子句相同 | ||
--fields-optionally-enclosed-by | 该选项与--tab 选项一起使用,其含义与 LOAD DATA 的相应子句相同 | ||
--fields-terminated-by | 该选项与--tab 选项一起使用,其含义与 LOAD DATA 的相应子句相同 | ||
--flush-logs | 开始转储之前刷新 MySQL 服务器日志文件 | ||
--flush-privileges | 转储 mysql 数据库后发出 FLUSH PRIVILEGES 语句 | ||
--force | 即使在 table 转储期间发生 SQL 错误,也要 continue | ||
--get-server-public-key | 从服务器请求 RSA 公钥 | 5.7.23 | |
--help | 显示帮助信息并退出 | ||
--hex-blob | 使用十六进制 table 示法转储二进制列 | ||
--host | MySQL 服务器所在的主机 | ||
--ignore-error | 忽略指定的错误 | ||
--ignore-table | 不要转储给定的 table | ||
--include-master-host-port | 在由--dump-slave 生成的 CHANGE MASTER 语句中包括 MASTER_HOST/MASTER_PORT 选项 | ||
--insert-ignore | 编写 INSERT IGNORE 而不是 INSERT 语句 | ||
--lines-terminated-by | 该选项与--tab 选项一起使用,其含义与 LOAD DATA 的相应子句相同 | ||
--lock-all-tables | 锁定所有数据库中的所有 table | ||
--lock-tables | 转储之前锁定所有 table | ||
--log-error | 将警告和错误附加到命名文件 | ||
--login-path | 从.mylogin.cnf 中读取登录路径选项 | ||
--master-data | 将二进制日志文件的名称和位置写入输出 | ||
--max-allowed-packet | 发送到服务器或从服务器接收的最大数据包长度 | ||
--net-buffer-length | TCP/IP 和套接字通信的缓冲区大小 | ||
--no-autocommit | 将每个转储 table 的 INSERT 语句包含在 SET autocommit = 0 和 COMMIT 语句内 | ||
--no-create-db | 不要写 CREATE DATABASE 语句 | ||
--no-create-info | 不要编写重新创建每个转储 table 的 CREATE TABLE 语句 | ||
--no-data | 不要转储 table 内容 | ||
--no-defaults | 不读取选项文件 | ||
--no-set-names | 与--skip-set-charset 相同 | ||
--no-tablespaces | 不要在输出中写入任何 CREATE LOGFILE GROUP 或 CREATE TABLESPACE 语句 | ||
--opt | --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset 的简写 | ||
--order-by-primary | 转储按主键或第一个唯一索引排序的每个 table 的行 | ||
--password | 连接服务器时使用的密码 | ||
--pipe | 使用命名管道连接到服务器(仅 Windows) | ||
--plugin-dir | 安装插件的目录 | ||
--port | 用于连接的 TCP/IP 端口号 | ||
--print-defaults | 打印默认选项 | ||
--protocol | 使用的传输协议 | ||
--quick | 一次从服务器检索 table 的行 | ||
--quote-names | 反引号字符内的引号标识符 | ||
--replace | 编写 REPLACE 语句而不是 INSERT 语句 | ||
--result-file | 直接输出到给定文件 | ||
--routines | 从转储的数据库中转储存储的例程(过程和函数) | ||
--secure-auth | 不要以旧(4.1 之前)格式向服务器发送密码 | Yes | |
--server-public-key-path | 包含 RSA 公钥的文件的路径名 | 5.7.23 | |
--set-charset | 将 SET NAMES default_character_set 添加到输出 | ||
--set-gtid-purged | 是否将 SET @@ GLOBAL.GTID_PURGED 添加到输出 | ||
--shared-memory-base-name | 共享内存连接的共享内存名称(仅 Windows) | ||
--single-transaction | 从服务器转储数据之前发出 BEGIN SQL 语句 | ||
--skip-add-drop-table | 不要在每个 CREATE TABLE 语句之前添加 DROP TABLE 语句 | ||
--skip-add-locks | 不添加锁 | ||
--skip-comments | 不要添加 Comments 到转储文件 | ||
--skip-compact | 不要产生更紧凑的输出 | ||
--skip-disable-keys | 不要禁用按键 | ||
--skip-extended-insert | 关闭扩展插入 | ||
--skip-opt | 关闭--opt 设置的选项 | ||
--skip-quick | 不要一次从服务器检索 table 的行 | ||
--skip-quote-names | 不引用标识符 | ||
--skip-set-charset | 不要写 SET NAMES 语句 | ||
--skip-triggers | 不要转储触发器 | ||
--skip-tz-utc | 关闭 tz-utc | ||
--socket | Unix 套接字文件或 Windows 命名管道使用 | ||
--ssl | 启用连接加密 | ||
--ssl-ca | 包含受信任的 SSL 证书颁发机构列 table 的文件 | ||
--ssl-capath | 包含受信任的 SSL 证书颁发机构证书文件的目录 | ||
--ssl-cert | 包含 X.509 证书的文件 | ||
--ssl-cipher | 连接加密的允许密码 | ||
--ssl-crl | 包含证书吊销列 table 的文件 | ||
--ssl-crlpath | 包含证书吊销列 table 文件的目录 | ||
--ssl-key | 包含 X.509 密钥的文件 | ||
--ssl-mode | 与服务器连接的所需安全状态 | 5.7.11 | |
--ssl-verify-server-cert | 根据服务器证书的通用名身份验证主机名 | ||
--tab | 产生制 table 符分隔的数据文件 | ||
--tables | 覆盖-数据库或-B 选项 | ||
--tls-version | 允许的 TLS 协议进行加密连接 | 5.7.10 | |
--triggers | 每个转储 table 的转储触发器 | ||
--tz-utc | 添加 SET TIME_ZONE ='00:00'来转储文件 | ||
--user | 连接服务器时使用的 MySQL 用户名 | ||
--verbose | Verbose mode | ||
--version | 显示版本信息并退出 | ||
--where | 仅转储给定 WHERE 条件选择的行 | ||
--xml | 产生 XML 输出 |
Connection Options
mysqldump命令登录到 MySQL 服务器以提取信息。以下选项指定如何连接到同一台计算机或远程系统上的 MySQL 服务器。
在具有多个网络接口的计算机上,使用此选项选择用于连接到 MySQL 服务器的接口。
尽可能压缩 Client 端和服务器之间发送的所有信息。参见第 4.2.6 节“连接压缩控制”。
有关使用哪个 Client 端身份验证插件的提示。参见第 6.2.13 节“可插入身份验证”。
启用mysql_clear_password
明文身份验证插件。 (请参阅第 6.4.1.6 节“Client 端明文可插入身份验证”。)
MySQL 5.7.10 中添加了此选项。
从服务器请求基于 RSA 密钥对的密码交换所需的公共密钥。此选项适用于使用caching_sha2_password
身份验证插件进行身份验证的 Client 端。对于该插件,除非请求,否则服务器不会发送公钥。对于未使用该插件进行身份验证的帐户,将忽略此选项。如果不使用基于 RSA 的密码交换,也将被忽略,例如 Client 端使用安全连接连接到服务器时。
如果给出了--server-public-key-path=file_name并指定了有效的公共密钥文件,则它优先于--get-server-public-key。
有关caching_sha2_password
插件的信息,请参见第 6.4.1.4 节“缓存 SHA-2 可插入身份验证”。
--get-server-public-key选项已添加到 MySQL 5.7.23 中。
从给定主机上的 MySQL 服务器中转储数据。默认主机为localhost
。
从.mylogin.cnf
登录路径文件中的命名登录路径中读取选项。 “登录路径”是一个选项组,其中包含一些选项,这些选项指定要连接到哪个 MySQL 服务器以及要作为身份验证的帐户。要创建或修改登录路径文件,请使用mysql_config_editorUtil。参见第 4.6.6 节“ mysql_config_editor-MySQL 配置 Util”。
有关此选项和其他选项文件选项的更多信息,请参见第 4.2.2.3 节“影响选项文件处理的命令行选项”。
用于连接服务器的 MySQL 帐户的密码。密码值是可选的。如果未给出,mysqldump提示 Importing 一个。如果提供,则--password=或-p
与后面的密码之间必须没有空格。如果未指定密码选项,则默认为不发送密码。
在命令行上指定密码应该被认为是不安全的。为避免在命令行上 Importing 密码,请使用选项文件。参见第 6.1.2.1 节,“最终用户密码安全准则”。
要明确指定没有密码并且mysqldump不应提示 Importing 密码,请使用--skip-password选项。
在 Windows 上,使用命名管道连接到服务器。仅当服务器在启用了named_pipe系统变量的情况下启动以支持命名管道连接时,此选项才适用。此外,构建连接的用户必须是named_pipe_full_access_group系统变量指定的 Windows 组的成员。
在其中查找插件的目录。如果--default-auth选项用于指定身份验证插件,但mysqldump找不到它,请指定此选项。参见第 6.2.13 节“可插入身份验证”。
对于 TCP/IP 连接,使用的端口号。
用于连接到服务器的传输协议。当其他连接参数通常导致使用您想要的协议以外的协议时,此功能很有用。有关允许值的详细信息,请参见第 4.2.5 节“连接传输协议”。
不要以旧(4.1 之前)格式向服务器发送密码。这样可以防止除使用较新密码格式的服务器以外的其他连接。
从 MySQL 5.7.5 开始,此选项已被弃用,并将在以后的 MySQL 版本中删除。它始终处于启用状态,尝试禁用它(--skip-secure-auth,--secure-auth=0)会产生错误。在 MySQL 5.7.5 之前,此选项默认为启用,但可以禁用。
Note
使用 4.1 之前的哈希方法的密码比使用本地密码哈希方法的密码安全性较低,应避免使用。不建议使用 4.1 之前的密码,并在 MySQL 5.7.5 中删除了对它们的支持。有关帐户升级的说明,请参阅第 6.4.1.3 节“迁移到 4.1 版之前的密码哈希和 mysql_old_password 插件”。
PEM 格式的文件的路径名,其中包含服务器用于基于 RSA 密钥对的密码交换所需的公用密钥的 Client 端副本。此选项适用于使用sha256_password
或caching_sha2_password
身份验证插件进行身份验证的 Client 端。对于未通过这些插件之一进行身份验证的帐户,将忽略此选项。如果不使用基于 RSA 的密码交换,也将被忽略,例如 Client 端使用安全连接连接到服务器时。
如果给出了--server-public-key-path=file_name并指定了有效的公共密钥文件,则它优先于--get-server-public-key。
对于sha256_password
,仅当使用 OpenSSL 构建 MySQL 时,此选项才适用。
有关sha256_password
和caching_sha2_password
插件的信息,请参见第 6.4.1.5 节“ SHA-256 可插拔身份验证”和第 6.4.1.4 节“缓存 SHA-2 可插入身份验证”。
--server-public-key-path选项已添加到 MySQL 5.7.23 中。
对于到localhost
的连接(要使用的 Unix 套接字文件),或者在 Windows 上,要使用的命名管道的名称。
在 Windows 上,仅当在启用了named_pipe系统变量以支持命名管道连接的情况下启动服务器时,此选项才适用。此外,构建连接的用户必须是named_pipe_full_access_group系统变量指定的 Windows 组的成员。
以--ssl
开头的选项指定是否使用 SSL 连接到服务器,并指示在何处查找 SSL 密钥和证书。参见加密连接的命令选项。
加密连接允许的 TLS 协议。该值是一个或多个逗号分隔的协议名称的列 table。可以为此选项命名的协议取决于用于编译 MySQL 的 SSL 库。有关详细信息,请参见第 6.3.2 节“加密的连接 TLS 协议和密码”。
MySQL 5.7.10 中添加了此选项。
用于连接到服务器的 MySQL 帐户的用户名。
Option-File Options
这些选项用于控制要读取的选项文件。
在全局选项文件之后但在用户选项文件之前(在 Unix 上)读取此选项文件。如果该文件不存在或无法访问,则发生错误。 * file_name
*如果是相对路径名而不是完整路径名,则相对于当前目录进行解释。
有关此选项和其他选项文件选项的更多信息,请参见第 4.2.2.3 节“影响选项文件处理的命令行选项”。
仅使用给定的选项文件。如果该文件不存在或无法访问,则发生错误。 * file_name
*如果是相对路径名而不是完整路径名,则相对于当前目录进行解释。
exception:即使使用--defaults-file,Client 端程序也读取.mylogin.cnf
。
有关此选项和其他选项文件选项的更多信息,请参见第 4.2.2.3 节“影响选项文件处理的命令行选项”。
不仅阅读常规选项组,还阅读具有常规名称和后缀* str
*的组。例如,mysqldump通常读取[client]
和[mysqldump]
组。如果提供了--defaults-group-suffix=_other选项,则mysqldump也会读取[client_other]
和[mysqldump_other]
组。
有关此选项和其他选项文件选项的更多信息,请参见第 4.2.2.3 节“影响选项文件处理的命令行选项”。
不要读取任何选项文件。如果由于从选项文件中读取未知选项而导致程序启动失败,则可以使用--no-defaults来防止读取它们。
exception 是在所有情况下都读取.mylogin.cnf
文件(如果存在)。这样即使在使用--no-defaults的情况下,也可以通过比命令行更安全的方式指定密码。 (.mylogin.cnf
由mysql_config_editorUtil 创建。请参见第 4.6.6 节“ mysql_config_editor-MySQL 配置 Util”。)
有关此选项和其他选项文件选项的更多信息,请参见第 4.2.2.3 节“影响选项文件处理的命令行选项”。
打印程序名称及其从选项文件中获取的所有选项。
有关此选项和其他选项文件选项的更多信息,请参见第 4.2.2.3 节“影响选项文件处理的命令行选项”。
DDL Options
mysqldump的使用场景包括设置一个全新的 MySQL 实例(包括数据库 table),以及用现有数据库和 table 替换现有实例中的数据。通过以下选项,您可以通过对转储文件中的各种 DDL 语句进行编码,来指定还原转储时要拆除和设置的内容。
在每个CREATE DATABASE语句之前写一个DROP DATABASE语句。此选项通常与--all-databases或--databases选项结合使用,因为除非指定了其中一个选项,否则不会写入CREATE DATABASE语句。
在每个CREATE TABLE语句之前写一个DROP TABLE语句。
在每个CREATE TRIGGER语句之前写一个DROP TRIGGER语句。
向 table 转储添加创建NDBtable 使用的任何 table 空间所需的所有 SQL 语句。否则,此信息未包含在mysqldump的输出中。该选项当前仅与 NDB 群集 table 相关,MySQL 5.7 不支持该 table。
如果指定了--databases或--all-databases选项,则禁止输出中包含的CREATE DATABASE语句。
不要写创建每个转储 table 的CREATE TABLE语句。
Note
此选项不排除从mysqldump输出创建日志文件组或 table 空间的语句;但是,您可以为此使用--no-tablespaces选项。
此选项禁止输出mysqldump中的所有创建日志文件组和CREATE TABLESPACE语句。
Debug Options
以下选项可打印调试信息,将调试信息编码在转储文件中,或者使转储操作 continue 进行,而不考虑潜在的问题。
允许创建作为关键字的列名。这通过在每个列名前面加上 table 名来起作用。
在转储文件中写入其他信息,例如程序版本,服务器版本和主机。默认情况下启用此选项。要取消显示此附加信息,请使用--skip-comments。
编写调试日志。典型的* debug_options
*字符串是d:t:o,file_name
。默认值为d:t:o,/tmp/mysqldump.trace
。
仅当使用WITH_DEBUG构建 MySQL 时,此选项才可用。使用此选项不是构建 Oracle 提供的 MySQL 发行版二进制文件。
程序退出时,打印一些调试信息。
仅当使用WITH_DEBUG构建 MySQL 时,此选项才可用。使用此选项不是构建 Oracle 提供的 MySQL 发行版二进制文件。
程序退出时,打印调试信息以及内存和 CPU 使用情况统计信息。
仅当使用WITH_DEBUG构建 MySQL 时,此选项才可用。使用此选项不是构建 Oracle 提供的 MySQL 发行版二进制文件。
如果提供了--comments选项,则mysqldump在以下格式的转储末尾生成 Comments:
-- Dump completed on DATE
但是,即使数据相同,该日期也会导致在不同时间获取的转储文件看起来不同。 --dump-date和--skip-dump-date控制是否将日期添加到 Comment 中。默认值为--dump-date(在 Comments 中包括日期)。 --skip-dump-date禁止日期打印。
忽略所有错误;即使在 table 转储期间发生 SQL 错误,也要 continue 操作。
此选项的一种用法是,即使mysqldump遇到由于定义引用已被删除的 table 而变得无效的视图,也使它们 continue 执行。如果没有--force
,则mysqldump退出并显示错误消息。使用--force
时,mysqldump将输出错误消息,但也会将包含视图定义的 SQLComments 写入转储输出并 continue 执行。
如果还提供--ignore-error选项以忽略特定错误,则--force优先。
通过将警告和错误附加到命名文件来记录它们。默认为不记录日志。
请参阅--comments选项的说明。
详细模式。打印有关程序功能的更多信息。
Help Options
以下选项显示有关mysqldump命令本身的信息。
显示帮助消息并退出。
显示版本信息并退出。
Internationalization Options
以下选项更改了mysqldump命令以本国语言设置 table 示字符数据的方式。
字符集的安装目录。参见第 10.15 节“字符集配置”。
使用* charset_name
*作为默认字符集。参见第 10.15 节“字符集配置”。如果未指定字符集,则mysqldump使用utf8
。
关闭--set-charset设置,与指定--skip-set-charset
相同。
将SET NAMES default_character_set写入输出。默认情况下启用此选项。要取消显示SET NAMES语句,请使用--skip-set-charset。
Replication Options
mysqldump命令通常用于在复制配置中的从属服务器上创建空实例或包含数据的实例。以下选项适用于在复制主服务器和从属服务器上转储和还原数据。
对于使用--dump-slave选项生成的从属转储,请在更改为主语句之前添加STOP SLAVE语句,并在输出末尾添加START SLAVE语句。
在主复制服务器上,执行转储操作后,通过向服务器发送清除二进制日志语句来删除二进制日志。此选项自动启用--master-data。
此选项与--master-data相似,不同之处在于它用于转储复制从服务器以生成转储文件,该转储文件可用于将另一台服务器设置为与转储服务器具有相同主服务器的从服务器。它使转储输出包含更改为主语句,该语句指示转储的从属主机的二进制日志坐标(文件名和位置)。 更改为主语句从显示从站状态输出读取Relay_Master_Log_File
和Exec_Master_Log_Pos
的值,并将它们分别用于MASTER_LOG_FILE
和MASTER_LOG_POS
。这些是从属服务器应开始复制的主服务器坐标。
Note
已执行的中继日志中的事务 Sequences 不一致会导致使用错误的位置。有关更多信息,请参见第 16.4.1.32 节,“复制和事务不一致”。
--dump-slave
导致使用主节点的坐标,而不是转储服务器的坐标,就像--master-data选项一样。另外,指定此选项会使--master-data
选项被覆盖(如果使用),并被有效地忽略。
Warning
如果要应用转储的服务器使用gtid_mode=ON和MASTER_AUTOPOSITION=1
,则不应使用此选项。
选项值的处理方式与--master-data相同(不设置值或设置 1 导致将CHANGE MASTER TO
语句写入转储,设置 2 导致将语句写入但用 SQLComments 括起来),其作用与--master-data
in 相同。启用或禁用其他选项以及如何处理锁定的术语。
此选项使mysqldump在转储之前停止从 SQL 线程,然后在转储之后重新启动。
结合--dump-slave
,也可以使用--apply-slave-statements和--include-master-host-port选项。
对于使用--dump-slave选项生成的从属转储中的更改为主语句,请添加MASTER_HOST
和MASTER_PORT
选项作为从属主服务器的主机名和 TCP/IP 端口号。
使用此选项转储主复制服务器以生成转储文件,该转储文件可用于将另一台服务器设置为主服务器的从属服务器。它使转储输出包含更改为主语句,该语句指示转储服务器的二进制日志坐标(文件名和位置)。这些是将转储文件加载到从属服务器后,从属服务器应从其开始复制的主服务器坐标。
如果选项值为 2,则更改为主语句被写为 SQLComments,因此仅提供信息;重新加载转储文件时,它无效。如果选项值为 1,则该语句不作为 Comments 写入,并在重新加载转储文件时生效。如果未指定选项值,则默认值为 1.
此选项需要RELOAD特权,并且必须启用二进制日志。
--master-data
选项自动关闭--lock-tables。除非也指定了--single-transaction,否则它也会打开--lock-all-tables,在这种情况下,转储开始时仅在短时间内获取了全局读锁定(请参见--single-transaction的说明)。在所有情况下,对日志的任何操作都会在转储的确切 Moment 发生。
也可以通过使用--dump-slave选项转储主服务器的现有从属服务器来设置从属服务器,该选项将覆盖--master-data
并导致如果同时使用这两个选项,则忽略它。
通过指示是否向输出添加SET @@GLOBAL.gtid_purged语句,此选项可以控制写入转储文件的全局事务 ID(GTID)信息。此选项还可能导致将语句写入输出,从而在重新加载转储文件时禁用二进制日志记录。
下 table 显示了允许的选项值。默认值为AUTO
。
Value | Meaning |
---|---|
OFF | 在输出中不添加SET 语句。 |
ON | 在输出中添加SET 语句。如果未在服务器上启用 GTID,则会发生错误。 |
AUTO | 如果在服务器上启用了 GTID,则在输出中添加SET 语句。 |
来自使用基于 GTID 的复制的服务器的部分转储需要指定--set-gtid-purged={ON|OFF}选项。如果打算仅使用转储服务器中的某些数据来部署新的复制从属,请使用ON
。如果要通过在拓扑中复制 table 来修复 table,请使用OFF
。如果要在不相交的复制拓扑之间复制 table 并将保留该 table,请使用OFF
。
重新加载转储文件时,--set-gtid-purged
选项对二进制日志记录有以下影响:
-
--set-gtid-purged=OFF
:SET @@SESSION.SQL_LOG_BIN=0;
未添加到输出中。-
--set-gtid-purged=ON
:SET @@SESSION.SQL_LOG_BIN=0;
已添加到输出中。 -
如果要备份的服务器上启用了 GTID,则
--set-gtid-purged=AUTO
:SET @@SESSION.SQL_LOG_BIN=0;
将添加到输出中(即AUTO
评估为ON
)。
-
Note
如果转储文件包含系统 table,则不建议在服务器(gtid_mode=ON)上启用 GTID 时加载转储文件。 mysqldump为使用非事务 MyISAM 存储引擎的系统 table 发出 DML 指令,并且在启用 GTID 时不允许使用此组合。还应注意,将一个启用了 GTID 的服务器中的转储文件加载到另一个启用了 GTID 的服务器中,会导致生成不同的事务标识符。
Format Options
以下选项指定如何 table 示整个转储文件或转储文件中的某些类型的数据。它们还控制是否将某些可选信息写入转储文件。
产生更紧凑的输出。此选项启用--skip-add-drop-table,--skip-add-locks,--skip-comments,--skip-disable-keys和--skip-set-charset选项。
产生与其他数据库系统或更旧的 MySQL 服务器更兼容的输出。 * name
*的值可以是ansi
,mysql323
,mysql40
,postgresql
,oracle
,mssql
,db2
,maxdb
,no_key_options
,no_table_options
或no_field_options
。要使用多个值,请用逗号分隔。这些值与设置服务器 SQL 模式的相应选项的含义相同。参见第 5.1.10 节“服务器 SQL 模式”。
此选项不保证与其他服务器的兼容性。它仅启用当前可用于使转储输出更兼容的那些 SQL 模式值。例如,--compatible=oracle
不会将数据类型 Map 到 Oracle 类型,也不会使用 OracleComments 语法。
使用包含列名的完整INSERT语句。
在CREATE TABLE语句中包括所有特定于 MySQL 的 table 选项。
-
--fields-terminated-by=...
,--fields-enclosed-by=...
,--fields-optionally-enclosed-by=...
,--fields-escaped-by=...
这些选项与--tab选项一起使用,并且与LOAD DATA的相应FIELDS
子句具有相同的含义。参见第 13.2.6 节“ LOAD DATA 语句”。
使用十六进制 table 示法转储二进制列(例如'abc'
变为0x616263
)。与二进制字符集一起使用时,受影响的数据类型为BINARY,VARBINARY,BLOB类型,BIT,所有空间数据类型以及其他非二进制数据类型。
此选项与--tab选项一起使用,其含义与LOAD DATA的相应LINES
子句相同。参见第 13.2.6 节“ LOAD DATA 语句”。
引号内的引号标识符(例如数据库,table 和列名)。如果启用了ANSI_QUOTES SQL 模式,则标识符用"
个字符引起来。默认情况下启用此选项。可以使用--skip-quote-names
禁用它,但是应该在诸如--compatible之类的任何选项之后赋予该选项,该选项可以启用--quote-names。
直接输出到命名文件。即使生成转储时发生错误,也将创建结果文件并覆盖其先前的内容。
在 Windows 上应使用此选项,以防止将换行符\n
字符转换为\r\n
回车/换行符序列。
产生制 table 符分隔的文本格式数据文件。对于每个转储的 table,mysqldump创建一个tbl_name.sql
文件,该文件包含创建 table 的CREATE TABLE语句,服务器写入一个tbl_name.txt
文件,该文件包含其数据。选项值是在其中写入文件的目录。
Note
默认情况下,.txt
数据文件在列值之间使用制 table 符和每行末尾的换行符进行格式化。可以使用--fields-xxx
和--lines-terminated-by选项明确指定格式。
列值将转换为--default-character-set选项指定的字符集。
此选项使TIMESTAMP列可以在不同时区的服务器之间转储和重新加载。 mysqldump将其连接时区设置为 UTC,并将SET TIME_ZONE='+00:00'
添加到转储文件中。如果没有此选项,则TIMESTAMP列将在源服务器和目标服务器本地的时区中转储并重新加载,如果服务器位于不同的时区中,这可能导致值更改。 --tz-utc
还可以防止由于夏时制而导致的更改。 --tz-utc
默认启用。要禁用它,请使用--skip-tz-utc
。
将转储输出写成格式正确的 XML。
NULL
,'NULL'
和空值 :对于名为* column_name
*的列,在此选项生成的输出中,NULL
值,空字符串和字符串值'NULL'
相互区分,如下所示。
Value: | XML Representation: |
---|---|
NULL (未知值) | <field name="column_name" xsi:nil="true" /> |
'' (空字符串) | <field name="column_name"></field> |
'NULL' (字符串值) | <field name="column_name">NULL</field> |
使用--xml选项运行时,mysqlClient 端的输出也遵循上述规则。 (请参见第 4.5.1.1 节“ mysqlClient 端选项”。)
mysqldump的 XML 输出包括 XML 名称空间,如下所示:
shell> mysqldump --xml -u root world City
<?xml version="1.0"?>
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database name="world">
<table_structure name="City">
<field Field="ID" Type="int(11)" Null="NO" Key="PRI" Extra="auto_increment" />
<field Field="Name" Type="char(35)" Null="NO" Key="" Default="" Extra="" />
<field Field="CountryCode" Type="char(3)" Null="NO" Key="" Default="" Extra="" />
<field Field="District" Type="char(20)" Null="NO" Key="" Default="" Extra="" />
<field Field="Population" Type="int(11)" Null="NO" Key="" Default="0" Extra="" />
<key Table="City" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="ID"
Collation="A" Cardinality="4079" Null="" Index_type="BTREE" Comment="" />
<options Name="City" Engine="MyISAM" Version="10" Row_format="Fixed" Rows="4079"
Avg_row_length="67" Data_length="273293" Max_data_length="18858823439613951"
Index_length="43008" Data_free="0" Auto_increment="4080"
Create_time="2007-03-31 01:47:01" Update_time="2007-03-31 01:47:02"
Collation="latin1_swedish_ci" Create_options="" Comment="" />
</table_structure>
<table_data name="City">
<row>
<field name="ID">1</field>
<field name="Name">Kabul</field>
<field name="CountryCode">AFG</field>
<field name="District">Kabol</field>
<field name="Population">1780000</field>
</row>
...
<row>
<field name="ID">4079</field>
<field name="Name">Rafah</field>
<field name="CountryCode">PSE</field>
<field name="District">Rafah</field>
<field name="Population">92020</field>
</row>
</table_data>
</database>
</mysqldump>
Filtering Options
以下选项控制将哪些类型的模式对象写入转储文件:按类别(例如触发器或事件);按类别。例如,按名称选择要转储的数据库和 table;甚至使用WHERE
子句从 table 数据中过滤行。
转储所有数据库中的所有 table。这与使用--databases选项并在命令行上命名所有数据库相同。
转储几个数据库。通常,mysqldump将命令行上的名字参数视为数据库名称,并将其后的名字视为 table 名称。使用此选项,它将所有名称参数视为数据库名称。每个新数据库之前的输出中都包含CREATE DATABASE和USE语句。
此选项可用于转储INFORMATION_SCHEMA
和performance_schema
数据库,即使使用--all-databases选项也通常不会转储这些数据库。 (也可以使用--skip-lock-tables选项。)
在输出中包括转储数据库的事件计划程序事件。此选项需要这些数据库的EVENT特权。
使用--events
生成的输出包含CREATE EVENT条语句来创建事件。但是,这些语句不包括事件创建和修改时间戳记之类的属性,因此,当重新加载事件时,将使用等于重新加载时间的时间戳记来创建事件。
如果您需要使用原始时间戳记属性创建事件,请不要使用--events
。相反,请使用对mysql
数据库具有适当特权的 MySQL 帐户直接转储并重新加载mysql.event
table 的内容。
忽略指定的错误。该选项值是一个逗号分隔的错误号列 table,指定了在mysqldump执行期间要忽略的错误。如果还提供--force选项以忽略所有错误,则--force优先。
不要转储给定的 table,必须同时使用数据库名和 table 名指定该 table。要忽略多个 table,请多次使用此选项。此选项也可用于忽略视图。
不要写任何 table 行信息(即,不要转储 table 内容)。如果只想转储 table 的CREATE TABLE语句(例如,通过加载转储文件来创建 table 的空副本),这将很有用。
在输出中包括用于转储数据库的存储例程(过程和函数)。此选项要求mysql.proc
table 具有SELECT特权。
使用--routines
生成的输出包含CREATE PROCEDURE和CREATE FUNCTION语句以创建例程。但是,这些语句不包括例程创建和修改时间戳之类的属性,因此,在重新加载例程时,将使用等于重新加载时间的时间戳来创建例程。
如果您需要使用原始时间戳属性创建例程,请不要使用--routines
。相反,请使用对mysql
数据库具有适当特权的 MySQL 帐户直接转储并重新加载mysql.proc
table 的内容。
覆盖--databases或-B
选项。 mysqldump将选项后的所有名称参数都视为 table 名称。
在输出中包括每个转储 table 的触发器。默认情况下启用此选项。使用--skip-triggers
禁用它。
为了能够转储 table 的触发器,您必须具有该 table 的TRIGGER特权。
允许多个触发器。 mysqldump以激活 Sequences 转储触发器,以便在重新加载转储文件时,将以相同的激活 Sequences 创建触发器。但是,如果mysqldump转储文件包含具有相同触发事件和动作时间的 table 的多个触发器,则尝试将转储文件加载到不支持多个触发器的较旧服务器中时会发生错误。 (有关变通方法,请参见第 2.12.3 节“降级说明”;您可以将触发器转换为与旧服务器兼容。)
仅转储由给定的WHERE
条件选择的行。如果条件中包含空格或其他命令解释程序特有的字符,则必须在条件周围加引号。
Examples:
--where="user='jimf'"
-w"userid>1"
-w"userid<1"
Performance Options
以下选项与性能(尤其是还原操作)最相关。对于大型数据集,还原操作(处理转储文件中的INSERT
语句)是最耗时的部分。当紧急需要快速恢复数据时,请提前计划和测试此阶段的性能。对于以小时为单位的还原时间,您可能更喜欢备用的备份和还原解决方案,例如MySQL 企业备份table 示InnoDB
以及混合使用的数据库。
transactional options也会影响性能,主要是针对转储操作。
对于每个 table,将INSERT语句用/*!40000 ALTER TABLE tbl_name DISABLE KEYS */;
和/*!40000 ALTER TABLE tbl_name ENABLE KEYS */;
语句括起来。这将加快加载转储文件的速度,因为在插入所有行之后都会创建索引。此选项仅对MyISAM
table 的非唯一索引有效。
使用包含多个VALUES
列 table 的多行语法编写INSERT语句。这样可以生成较小的转储文件,并在重新加载文件时加快插入速度。
写INSERT IGNORE条语句而不是INSERT条语句。
用于 Client 端/服务器通信的缓冲区的最大大小。默认值为 24MB,最大为 1GB。
用于 Client 端/服务器通信的缓冲区的初始大小。当创建多行INSERT语句时(与--extended-insert或--opt选项一起使用),mysqldump创建的行最多可达--net-buffer-length个字节。如果增加此变量,请确保 MySQL 服务器net_buffer_length系统变量的值至少为该值。
默认情况下启用的此选项是--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset的组合的简写形式。它提供了快速的转储操作并生成了一个转储文件,可以将其快速重新加载到 MySQL 服务器中。
由于--opt
选项默认情况下处于启用状态,因此您只需指定其相反选项--skip-opt即可关闭一些默认设置。有关有选择地启用或禁用受--opt
影响的选项的子集的信息,请参见mysqldump 选项组的讨论。
此选项对于转储大型 table 很有用。它强制mysqldump一次从服务器中检索 table 的行,而不是检索整个行集并将其缓冲在内存中,然后再将其写出。
请参阅--opt选项的说明。
Transactional Options
以下选项权衡了转储操作的性能和导出数据的可靠性和一致性。
用LOCK TABLES和UNLOCK TABLES语句包围每个 table 转储。重新装入转储文件时,这可以加快插入速度。参见第 8.2.4.1 节“优化 INSERT 语句”。
开始转储之前,请刷新 MySQL 服务器日志文件。此选项需要RELOAD特权。如果将此选项与--all-databases选项结合使用,则对于每个转储数据库,都会清除日志。使用--lock-all-tables,--master-data或--single-transaction时是一个 exception:在这种情况下,日志仅刷新一次,这与所有 table 都被带读取锁的平桌子锁定的 Moment 相对应。如果希望转储和日志刷新完全在同一时间发生,则应将--flush-logs
与--lock-all-tables,--master-data或--single-transaction一起使用。
转储mysql
数据库后,向转储输出中添加FLUSH PRIVILEGES语句。每当转储包含mysql
数据库和依赖于mysql
数据库中数据的任何其他数据库以进行正确还原时,都应使用此选项。
Note
要从旧版本升级到 MySQL 5.7 或更高版本,请勿使用--flush-privileges
。有关这种情况下的升级说明,请参阅第 2.11.3 节“ MySQL 5.7 中的更改”。
锁定所有数据库中的所有 table。这是通过在整个转储期间获取全局读取锁定来实现的。此选项自动关闭--single-transaction和--lock-tables。
对于每个转储的数据库,在转储它们之前,锁定要转储的所有 table。这些 table 用READ LOCAL
锁定,以允许在MyISAM
table 的情况下并发插入。对于InnoDB
之类的事务 table,--single-transaction比--lock-tables
更好,因为它根本不需要锁定 table。
因为--lock-tables
分别锁定每个数据库的 table,所以此选项不能保证转储文件中的 table 在数据库之间在逻辑上是一致的。不同数据库中的 table 可能以完全不同的状态转储。
某些选项(例如--opt)会自动启用--lock-tables
。如果要覆盖此选项,请在选项列 table 末尾使用--skip-lock-tables
。
将每个转储 table 的INSERT语句包含在SET autocommit = 0
和COMMIT语句内。
转储每个 table 的行,这些行按其主键或按其第一个唯一索引排序(如果存在这样的索引)。在转储要加载到InnoDB
table 中的MyISAM
table 时,这很有用,但会使转储操作花费更长的时间。
在 Windows 上,共享内存名称,用于使用共享内存与本地服务器构建的连接。默认值为MYSQL
。共享内存名称区分大小写。
仅当在启用了shared_memory系统变量以支持共享内存连接的情况下启动服务器时,此选项才适用。
此选项将事务隔离模式设置为REPEATABLE READ,并在转储数据之前向服务器发送START TRANSACTION SQL 语句。它仅对诸如InnoDB
之类的事务 table 很有用,因为这样,它将在发出START TRANSACTION时转储数据库的一致状态,而不会阻塞任何应用程序。
使用此选项时,应记住只有InnoDB
个 table 以一致状态转储。例如,使用此选项时转储的任何MyISAM
或MEMORY
table 可能仍会更改状态。
在处理--single-transaction转储时,为了确保有效的转储文件(正确的 table 内容和二进制日志坐标),没有其他连接应使用以下语句:ALTER TABLE,CREATE TABLE,DROP TABLE,RENAME TABLE,TRUNCATE TABLE。一致的读取并非与这些语句隔离开,因此在要转储的 table 上使用它们会导致mysqldump执行的SELECT检索 table 内容以获得错误的内容或失败。
--single-transaction
选项和--lock-tables选项是互斥的,因为LOCK TABLES导致任何未决事务隐式提交。
要转储大 table,请将--single-transaction
选项与--quick选项结合使用。
Option Groups
-
--opt选项打开了一些设置,这些设置可以一起执行快速转储操作。所有这些设置默认情况下都是启用的,因为
--opt
默认情况下处于启用状态。因此,很少指定--opt
。相反,您可以通过指定--skip-opt
将这些设置作为一组关闭,可以通过稍后在命令行上指定关联的选项来选择性地重新启用某些设置。 -
--compact选项关闭了一些设置,这些设置控制是否在输出中显示可选的语句和 Comments。同样,您可以将此选项与其他选项一起使用,以重新启用某些设置,或者使用
--skip-compact
table 单将所有设置打开。
当您有选择地启用或禁用组选项的效果时,Sequences 很重要,因为选项是从头到尾处理的。例如,--disable-keys --lock-tables --skip-opt不会达到预期的效果;它本身与--skip-opt相同。
Examples
要备份整个数据库:
shell> mysqldump db_name > backup-file.sql
要将转储文件重新加载到服务器中:
shell> mysql db_name < backup-file.sql
重新加载转储文件的另一种方法:
shell> mysql -e "source /path-to-backup/backup-file.sql" db_name
mysqldump通过将数据从一个 MySQL 服务器复制到另一个 MySQL 服务器来填充数据库也非常有用:
shell> mysqldump --opt db_name | mysql --host=remote_host -C db_name
您可以使用一个命令转储多个数据库:
shell> mysqldump --databases db_name1 [db_name2 ...] > my_databases.sql
要转储所有数据库,请使用--all-databases选项:
shell> mysqldump --all-databases > all_databases.sql
对于InnoDB
个 table,mysqldump提供了一种进行在线备份的方法:
shell> mysqldump --all-databases --master-data --single-transaction > all_databases.sql
此备份在转储开始时获取所有 table 的全局读取锁定(使用带读取锁的平桌子)。一旦获取了此锁,便会读取二进制日志坐标并释放该锁。如果发出FLUSH语句时正在运行长时间的更新语句,则 MySQL 服务器可能会停滞,直到这些语句完成。之后,转储将变为无锁状态,并且不会干扰对 table 的读取和写入。如果 MySQL 服务器收到的更新语句很短(就执行时间而言),则即使有很多更新,初始锁定期也不应该引起注意。
对于时间点恢复(也称为“前滚”),当您需要还原旧的备份并重放自该备份以来发生的更改时,旋转二进制日志(请参见第 5.4.4 节“二进制日志”)或至少知道转储所对应的二进制日志坐标:
shell> mysqldump --all-databases --master-data=2 > all_databases.sql
Or:
shell> mysqldump --all-databases --flush-logs --master-data=2
> all_databases.sql
--master-data和--single-transaction选项可以同时使用,如果 table 是使用InnoDB
存储引擎存储的,则提供了一种方便的方法来使在线备份适合于在时间点恢复之前使用。
有关进行备份的更多信息,请参见第 7.2 节“数据库备份方法”和第 7.3 节“示例备份和恢复策略”。
-
要选择--opt的效果(某些功能除外),请对每个功能使用
--skip
选项。要禁用扩展插入和内存缓冲,请使用--opt --skip-extended-insert --skip-quick。 (实际上,--skip-extended-insert --skip-quick就足够了,因为--opt默认情况下处于启用状态。) -
要为除索引禁用和 table 锁定之外的所有功能反转--opt,请使用--skip-opt --disable-keys --lock-tables。
Restrictions
mysqldump默认不会转储INFORMATION_SCHEMA
,performance_schema
或sys
模式。要转储其中任何一个,请在命令行上明确命名它们。您也可以使用--databases选项为它们命名。对于INFORMATION_SCHEMA
和performance_schema
,还请使用--skip-lock-tables选项。
mysqldump不转储 NDB 群集ndbinfo信息数据库。
mysqldump不转储InnoDB
CREATE TABLESPACE语句。
不建议从使用mysqldump进行的转储还原到启用了 GTID 的 MySQL 5.6.9 或更早版本的服务器。参见第 16.1.3.6 节,“使用 GTID 复制的限制”。
mysqldump包含用于为mysql
数据库的转储重新创建general_log
和slow_query_log
table 的语句。日志 table 的内容不转储。
如果由于权限不足而在备份视图时遇到问题,请参阅第 23.9 节“视图限制”以获取解决方法。