本节描述如何使用 mysqldump 创建 SQL 格式的导出文件。有关重新加载此类导出文件的信息，请参见 第 9.4.2 节，“重新加载 SQL 格式备份”。This section describes how to use mysqldump to create SQL-format dump files. For information about reloading such dump files, see Section 9.4.2, “Reloading SQL-Format Backups”.

默认情况下，mysqldump 会将信息作为 SQL 语句写入标准输出。您可以将输出保存到文件中：By default, mysqldump writes information as SQL statements to the standard output. You can save the output in a file:

$> mysqldump [arguments] > file_name

要导出所有数据库，请使用 mysqldump 并加上 --all-databases 选项：To dump all databases, invoke mysqldump with the --all-databases option:

$> mysqldump --all-databases > dump.sql

要仅导出特定数据库，请在命令行中指定它们，并使用 --databases 选项：To dump only specific databases, name them on the command line and use the --databases option:

$> mysqldump --databases db1 db2 db3 > dump.sql

--databases 选项会使命令行中列出的所有名称都被视为数据库名称。如果不使用此选项，mysqldump 会将第一个名称视为数据库名称，后续名称视为表名。The --databases option causes all names on the command line to be treated as database names. Without this option, mysqldump treats the first name as a database name and those following as table names.

使用 --all-databases 或 --databases 时，mysqldump 在导出每个数据库的数据之前，会写入 CREATE DATABASE 和 USE 语句。这确保在重新加载转储文件时，如果数据库不存在，则创建每个数据库，并将其设为默认数据库，从而将数据库内容加载回原来的数据库。如果你希望转储文件在重新创建数据库之前强制删除每个数据库，还可以使用 --add-drop-database 选项。在这种情况下，mysqldump 会在每个 CREATE DATABASE 语句之前写入一个 DROP DATABASE 语句。With --all-databases or --databases, mysqldump writes CREATE DATABASE and USE statements prior to the dump output for each database. This ensures that when the dump file is reloaded, it creates each database if it does not exist and makes it the default database so database contents are loaded into the same database from which they came. If you want to cause the dump file to force a drop of each database before recreating it, use the --add-drop-database option as well. In this case, mysqldump writes a DROP DATABASE statement preceding each CREATE DATABASE statement.

要导出单个数据库，请在命令行中指定其名称：To dump a single database, name it on the command line:

$> mysqldump --databases test > dump.sql

在单数据库情况下，可以省略 --databases 选项：In the single-database case, it is permissible to omit the --databases option:

$> mysqldump test > dump.sql

前面两个命令的区别在于，如果没有 --databases，转储输出中不包含 CREATE DATABASE 或 USE 语句。这有几个影响：The difference between the two preceding commands is that without --databases, the dump output contains no CREATE DATABASE or USE statements. This has several implications:

要仅导出数据库中的特定表，请在命令行中在数据库名称之后列出它们：To dump only specific tables from a database, name them on the command line following the database name:

$> mysqldump test t1 t3 t7 > dump.sql

默认情况下，如果创建转储文件的服务器正在使用 GTID（gtid_mode=ON），mysqldump 在输出中包含一个 SET @@GLOBAL.gtid_purged 语句，用于添加来自gtid_executed 在源服务器上设置为目标服务器上的 gtid_purged。如果你只转储特定的数据库或表，需要注意的是，mysqldump 包含源服务器上设置的 SET @@GLOBAL.gtid_purged即使是那些更改了被抑制数据库部分或未包含在部分转储中的其他数据库。如果你只在目标服务器上重放一个部分转储文件，额外的 GTID 不会对该服务器的未来运行造成任何问题。然而，如果你在目标服务器上重放包含相同 GTID 的第二个转储文件（例如同一源服务器的另一个部分转储），第二个转储文件中的任何 SET @@GLOBAL.gtid_purged 语句都会失败。为避免此问题，可以将 mysqldump 选项 --set-gtid-purged 为 OFF 或 gtid_executed输出第二个转储文件，且没有激活 COMMENTED 语句，或者在重放转储文件前手动删除该语句。By default, if GTIDs are in use on the server where you create the dump file (gtid_mode=ON), mysqldump includes a SET @@GLOBAL.gtid_purged statement in the output to add the GTIDs from the gtid_executed set on the source server to the gtid_purged set on the target server. If you are dumping only specific databases or tables, it is important to note that the value that is included by mysqldump includes the GTIDs of all transactions in the gtid_executed set on the source server, even those that changed suppressed parts of the database, or other databases on the server that were not included in the partial dump. If you only replay one partial dump file on the target server, the extra GTIDs do not cause any problems with the future operation of that server. However, if you replay a second dump file on the target server that contains the same GTIDs (for example, another partial dump from the same source server), any SET @@GLOBAL.gtid_purged statement in the second dump file fails. To avoid this issue, either set the mysqldump option --set-gtid-purged to OFF or COMMENTED to output the second dump file without an active SET @@GLOBAL.gtid_purged statement, or remove the statement manually before replaying the dump file.