使用mysqldump进行备份

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

• 作为备份，以便在数据丢失的情况下恢复数据。
• 作为设置复制从站的数据源。

• 作为实验数据的来源：

  • 制作可在不更改原始数据的情况下使用的数据库副本。
  • 测试潜在的升级不兼容性。

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

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

使用mysqldump转储SQL格式的数据

本节介绍如何使用mysqldump创建SQL格式的转储文件。有关重新加载此类转储文件的信息，

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

shell>mysqldump [ rguments] > file_name

要转储所有数据库，请使用以下选项调用mysqldump--all-databases：

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在DROP DATABASE每个CREATE DATABASE语句之前写一个语句。

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

shell>mysqldump --databases test > dump.sql

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

shell>mysqldump test > dump.sql

前面两个命令之间的区别在于，不带--databases，转储输出不包含CREATE DATABASEor USE语句。这有几个含义：

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

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

shell>mysqldump test t1 t3 t7 > dump.sql

默认情况下，如果在创建转储文件（gtid_mode=ON）的服务器上使用了GTID ，则mysqldumpSET @@GLOBAL.gtid_purged在输出中包含一条语句，以将GTID从gtid_executed源服务器的gtid_purged集合添加到目标服务器的集合。如果仅转储特定的数据库或表，则需要注意的是，mysqldump包含的值将包含所有事务的GTID。gtid_executed在源服务器上进行设置，即使是那些更改了数据库的禁止部分或服务器上其他数据库（未包含在部分转储中）也是如此。如果仅在目标服务器上重播一个部分转储文件，则额外的GTID不会对该服务器的未来操作造成任何问题。但是，如果在包含相同GTID的目标服务器上重播第二个转储文件（例如，来自同一源服务器的另一个部分转储），SET @@GLOBAL.gtid_purged则第二个转储文件中的任何语句都会失败。为避免此问题，请将mysqldump选项设置--set-gtid-purged为OFF或COMMENTED在不激活的情况下输出第二个转储文件SET @@GLOBAL.gtid_purged语句，或在重播转储文件之前手动删除该语句。

重新加载SQL格式的备份

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

shell>mysql < dump.sql

或者，从mysql内部，使用source命令：

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"。

使用mysqldump转储定界文本格式的数据

本节介绍如何使用mysqldump创建带分隔符的转储文件。有关重新加载此类转储文件的信息，请参见“重新加载定界文本格式备份”。

如果使用该选项调用mysqldump，它将用作输出目录，并使用每个表的两个文件分别转储该目录中的表。表名是这些文件的基本名称。对于名为的表，文件名为和。该文件包含该表的语句。该文件包含表数据，每表行一行。--tab=dir_namedir_namet1t1.sqlt1.txt.sqlCREATE TABLE.txt

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

shell> mysqldump --tab=/tmp db1

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

服务器将CREATE转储表的定义发送到mysqldump，然后将其写入.sql文件。因此，这些文件归执行mysqldump的用户所有。

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

对于mysqldump --tab，服务器默认将表数据写入.txt文件，每行一行，在列值之间使用制表符，在列值之间不带引号，并使用换行符作为行终止符。（这些默认值与相同SELECT ... INTO OUTFILE。）

为了使数据文件可以使用其他格式写入，mysqldump支持以下选项：

• --fields-terminated-by=str

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

• --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=0x0d0 db1

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

重新加载定界文本格式的备份

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

shell>mysql db1 < t1.sql
shell>mysqlimport db1 t1.txt

使用mysqlimport加载数据文件的另一种方法是LOAD DATA在mysql客户端中使用以下语句：

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

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

shell>mysqlimport --fields-terminated-by=,
--fields-enclosed-by='"' --lines-terminated-by=0x0d0 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';