使用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 DATABASE
or 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 NOTEXISTS db1; mysql>USE db1; mysql>source dump.sql
对于Windows PowerShell用户:由于保留了<<字符供以后在PowerShell中使用,因此需要一种替代方法,例如使用引号cmd.exe /c "mysql < dump.sql"
。
使用mysqldump转储定界文本格式的数据
本节介绍如何使用mysqldump创建带分隔符的转储文件。有关重新加载此类转储文件的信息,请参见“重新加载定界文本格式备份”。
如果使用该选项调用mysqldump,它将用作输出目录,并使用每个表的两个文件分别转储该目录中的表。表名是这些文件的基本名称。对于名为的表,文件名为和。该文件包含该表的语句。该文件包含表数据,每表行一行。--tab=dir_name
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,然后将其写入.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 t1FIELDS TERMINATED BY ','FIELDS ENCLOSED BY '"'LINES TERMINATED BY '\r\n';