参考官方文档：

https://dev.mysql.com/doc/refman/5.7/en/using-mysqldump.html

本节介绍如何使用mysqldump生成转储文件，以及如何重新加载转储文件。 转储文件可以以多种方式使用：

• 作为在数据丢失的情况下启用数据恢复的备份。
• 作为设置复制从站的数据源。
• 作为实验数据的来源：
• 在不更改原始数据的情况下制作可以使用的数据库的副本。
• 测试潜在的升级不兼容性。

mysqldump产生两种类型的输出，具体取决于是否给出--tab选项：

• 没有--tab，mysqldump将SQL语句写入标准输出。 此输出包含CREATE语句以创建转储对象（数据库，表，存储例程等）和INSERT语句以将数据加载到表中。 输出可以保存在文件中，稍后使用mysql重新创建转储的对象。 选项可用于修改SQL语句的格式，并控制转储哪些对象。
• 使用--tab，mysqldump为每个转储的表生成两个输出文件。 服务器将一个文件写为制表符分隔的文本，每行一行一行。 该文件在输出目录中被命名为tbl_name.txt。 服务器还将该表的CREATE TABLE语句发送给mysqldump，mysqldump将其作为名为tbl_name.sql的文件写入输出目录中。

7.4.1 使用mysqldump 以SQL 格式 dump数据

本节介绍如何使用mysqldump创建SQL格式的转储文件。

默认情况下，mysqldump将信息作为SQL语句写入标准输出。 您可以将输出保存在文件中：

shell> mysqldump [arguments] > file_name

要转储所有数据库，使用mysqldump 在加上--all-database选项：

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

要只转储特定的数据库，请在命令行上命名并使用--databases选项：

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

--databases选项会将命令行中的所有名称视为数据库名称。 如果没有这个选项，mysqldump会将第一个名称视为数据库名称，将其后面的名称视为表名称。

使用--all-databases或--databases，mysqldump会在每个数据库的转储输出之前写入CREATE DATABASE和USE语句。 这确保了在转储文件重新加载时，它会创建每个数据库（如果它不存在并使其成为默认数据库），以便将数据库内容加载到它们所来自的同一个数据库中。 如果要在重新创建之前强制删除每个数据库，也可以使用--add-drop-database选项。 在这种情况下，mysqldump在每个CREATE DATABASE语句前写入一个DROP DATABASE语句。在这种情况下，mysqldump在每个CREATE DATABASE语句前写入一个DROP DATABASE语句。

要转储单个数据库，请在命令行中将其命名：

shell> mysqldump --databases test > dump.sql

在单数据库的情况下，可以省略--databases选项：

shell> mysqldump test > dump.sql

前两个命令之间的区别是，如果没有--databases，转储输出不包含CREATE DATABASE或USE语句。 这有几个含义：

• 当您重新加载转储文件时，您必须指定一个默认数据库名称，以便服务器知道要重新加载哪个数据库。
• 对于重新加载，您可以指定一个与原始名称不同的数据库名称，这使您可以将数据重新加载到不同的数据库中。
• 如果要重新加载的数据库不存在，则必须先创建它。
• 由于输出不包含CREATE DATABASE语句，因此--add-drop-database选项不起作用。 如果使用它，它不会产生DROP DATABASE语句。

要仅转储数据库中的特定表，请在数据库名称后面的命令行中对其进行命名：

shell> mysqldump test t1 t3 t7 > dump.sql

7.4.2 重新加载SQL-格式备份

要重新加载由mysqldump编写的由SQL语句组成的转储文件，请将其用作mysql客户端的输入。 如果转储文件是由mysqldump使用--all-databases或--databases选项创建的，则它包含CREATE DATABASE和USE语句，并且不需要指定加载数据的默认数据库：

shell> mysql < dump.sql

或者，从MySQL内部使用源命令：

mysql> source dump.sql

如果该文件是不包含CREATE DATABASE和USE语句的单数据库转储，请首先创建数据库（如有必要）：

shell> mysqladmin create db1

然后在加载转储文件时指定数据库名称：

shell> mysql db1 < dump.sql

另外，从MySQL内部，创建数据库，选择它作为默认数据库，并加载转储文件：

mysql> CREATE DATABASE IF NOT EXISTS db1;

mysql> USE db1;

mysql> source dump.sql

注意：

对于Windows PowerShell用户：由于“<”字符保留供将来在PowerShell中使用，因此需要使用替代方法，例如使用引号cmd.exe / c“mysql <dump.sql”。

7.4.3 使用mysqldump以分隔文本格式转储数据

本节介绍如何使用mysqldump创建分隔文本转储文件

如果使用--tab=dir_name选项调用mysqldump，则它将dir_name用作输出目录，并使用每个表的两个文件在该目录中单独转储表。 表名是这些文件的基本名称。 对于名为t1的表，这些文件被命名为t1.sql和t1.txt。 .sql文件包含表的CREATE TABLE语句。 .txt文件包含表数据，每行表示表的一行。

以下命令将db1数据库的内容转储到/ tmp数据库中的文件：

shell> mysqldump --tab=/tmp db1

包含表数据的.txt文件由服务器编写，因此它们由用于运行服务器的系统帐户拥有。 服务器使用SELECT ... INTO OUTFILE写入文件，因此您必须具有FILE权限才能执行此操作，并且如果给定的.txt文件已存在，则会发生错误。

服务器将转储表的CREATE定义发送给mysqldump，mysqldump将它们写入.sql文件。 这些文件因此由执行mysqldump的用户拥有。

最好是--tab仅用于转储本地服务器。 如果将它用于远程服务器，则--tab目录必须存在于本地和远程主机上，并且.txt文件将由服务器写入远程目录（在服务器主机上），而.sql 文件将由mysqldump写入本地目录（在客户端主机上）。

对于mysqldump --tab，默认情况下服务器将表数据写入.txt文件，每行对应一行，列值之间为制表符，列值周围没有引号，行换行符为换行符。 （这些默认值与SELECT ... INTO OUTFILE相同。）

要使用不同的格式写入数据文件，mysqldump支持这些选项：

• --fields-terminated-by=str

用于分隔列值的字符串（默认值：tab）。

• --fields-enclosed-by=char

包含列值的字符（默认值：无字符）。

• --fields-optionally-enclosed-by=char

包含非数字列值的字符（默认值：无字符）。

• --fields-escaped-by=char

转义特殊字符的字符（默认：不转义）。

• --lines-terminated-by=str

行终止字符串（默认值：换行符）。

根据您为任何这些选项指定的值，可能需要在命令行上为您的命令解释器引用或转义适当的值。 或者，使用十六进制表示法指定值。 假设您希望mysqldump在双引号内引用列值。 为此，请将双引号指定为--fields-enclosed-by选项的值。 但是这个字符通常是专门的命令翻译者，并且必须特别对待。 例如，在Unix上，你可以引用这样的双引号：

--fields-enclosed-by='"'

在任何平台上，您都可以用十六进制指定值：

--fields-enclosed-by=0x22

一起使用几个数据格式化选项是很常见的。 例如，要使用逗号分隔值格式转储表格，并使用以回车换行符（\r\ n）结尾的行，请使用以下命令（在一行中输入）：

shell> mysqldump --tab=/tmp --fields-terminated-by=, --fields-enclosed-by='"' --lines-terminated-by=0x0d0a db1

如果您使用任何数据格式化选项来转储表数据，则在稍后重新加载数据文件时，您需要指定相同的格式，以确保正确解释文件内容。

7.4.4 重新加载分隔文本格式备份

对于使用mysqldump --tab生成的备份，每个表在输出目录中由包含表的CREATE TABLE语句的.sql文件和包含表数据的.txt文件表示。 要重新加载表格，请首先将位置更改为输出目录。 然后使用mysql处理.sql文件以创建一个空表并处理.txt文件以将数据加载到表中：

shell> mysql db1 < t1.sql

shell> mysqlimport db1 t1.txt

另一种使用mysqlimport加载数据文件，是使用mysql客户端中的LOAD DATA INFILE语句：

mysql> USE db1;

mysql> LOAD DATA INFILE 't1.txt' INTO TABLE t1;

如果您在初始转储表时使用了任何带有mysqldump的数据格式化选项，则必须使用mysqlimport或LOAD DATA INFILE中的相同选项来确保正确解释数据文件内容：

shell> mysqlimport --fields-terminated-by=, --fields-enclosed-by='"' --lines-terminated-by=0x0d0a db1 t1.txt

或：

mysql> USE db1;

mysql> LOAD DATA INFILE 't1.txt' INTO TABLE t1 FIELDS TERMINATED BY ',' FIELDS ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

7.4.5 mysqldump Tips

7.4.5.1 生成一个数据库的拷贝

shell> mysqldump db1 > dump.sql

shell> mysqladmin create db2

shell> mysql db2 < dump.sql

不要在mysqldump命令行上使用--databases，因为这会导致USE db1包含在转储文件中，这会覆盖在命令行中命名db2的效果。

7.4.5.2 从一个服务器拷贝数据库到另一个服务器

在服务器1：

shell> mysqldump --databases db1 > dump.sql

从服务器1拷贝dump 文件 到服务器2

在服务器2：

shell> mysql < dump.sql

将--databases与mysqldump命令行一起使用会导致转储文件包含CREATE DATABASE和USE语句，该语句创建数据库（如果它存在并使其成为重新加载的数据的默认数据库）。

或者，您可以在mysqldump命令中省略--databases。 然后，您需要在服务器2上创建数据库（如有必要），并在重新加载转储文件时将其指定为默认数据库。

在服务器1：

shell> mysqldump db1 > dump.sql

在服务器2：

shell> mysqladmin create db1

shell> mysql db1 < dump.sql

在这种情况下，您可以指定不同的数据库名称，因此，通过从mysqldump命令中删除--databases，可以从一个数据库转储数据并将其加载到另一个数据库中。

7.4.5.3 dump 时存储的程序

有几个选项控制mysqldump如何处理存储的程序（存储过程和函数，触发器和事件）：

• -- 事件：转储事件调度程序事件
• -- 例程：转储存储过程和函数
• - -触发器：转储表的触发器

--triggers选项是默认启用的，这样当表被转储时，会伴随着它们所具有的任何触发器。 其他选项默认是禁用的，必须明确指定才能转储相应的对象。 要明确禁用这些选项中的任何一个，请使用其跳过形式：--skip-events，--skip-routines或--skip-triggers。

7.4.5.4 分开dumping 表定义和内容

--no-data选项告诉mysqldump不转储表数据，导致转储文件仅包含用于创建表的语句。 相反，--no-create-info选项告诉mysqldump从输出中取消CREATE语句，以便转储文件只包含表数据。

例如，要为测试数据库单独转储表定义和数据，请使用以下命令：

shell> mysqldump --no-data test > dump-defs.sql

shell> mysqldump --no-create-info test > dump-data.sql

对于仅定义转储，添加 - --routines 和--events选项以包含存储的例程和事件定义：

shell> mysqldump --no-data --routines --events test > dump-defs.sql

7.4.5.5 使用 mysqldump 来测试升级不兼容性

在考虑进行MySQL升级时，谨慎地安装与当前生产版本分开的新版本。 然后，您可以从生产服务器转储数据库和数据库对象定义，并将它们加载到新服务器中，以验证它们是否得到正确处理。 （这对于测试降级也很有用。）

在生产服务器：

shell> mysqldump --all-databases --no-data --routines --events > dump-defs.sql

在被升级的服务器：

shell> mysql < dump-defs.sql

由于转储文件不包含表格数据，因此可以快速处理。 这使您能够发现潜在的不兼容性，而无需等待冗长的数据加载操作。 正在处理转储文件时查找警告或错误。

在验证了定义处理正确之后，转储数据并尝试将其加载到升级后的服务器中。

在生产服务器：

shell> mysqldump --all-databases --no-create-info > dump-data.sql

在被升级的服务器：

shell> mysql < dump-data.sql

现在检查表格内容并运行一些测试查询。