• 首页
  • css3教程
  • html5教程
  • jQuery手册
  • php7教程
  • MySQL手册
  • apache手册
  • 使用mysqldump进行备份

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

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

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

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

    • 没有--tabmysqldump将SQL语句写入标准输出。该输出包含CREATE用于创建转储对象(数据库,表,存储的例程等)的INSERT语句,以及用于将数据加载到表中的语句。可以将输出保存在文件中,并稍后使用mysql重新创建转储的对象来重新加载。选项可用于修改SQL语句的格式,并控制转储哪些对象。
    • 使用--tabmysqldump为每个转储的表产生两个输出文件。服务器以制表符分隔的文本形式写入一个文件,每表行一行。该文件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或时--databasesmysqldump在每个数据库的转储输出之前写入CREATE DATABASEUSE声明。这样可以确保在重新加载转储文件时,如果转储文件不存在,它将创建每个数据库,并将其设置为默认数据库,以便将数据库内容加载到它们所来自的同一数据库中。如果要使转储文件在重新创建每个数据库之前强制删除它们,请也使用该--add-drop-database选项。在这种情况下,mysqldumpDROP 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-purgedOFFCOMMENTED在不激活的情况下输出第二个转储文件SET @@GLOBAL.gtid_purged语句,或在重播转储文件之前手动删除该语句。


    重新加载SQL格式的备份

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

    shell>mysql < dump.sql
    

    或者,从mysql内部,使用source命令:

    mysql> source dump.sql
    

    如果该文件是不包含CREATE DATABASEUSE语句的单数据库转储,请首先创建数据库(如有必要):

    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 DATAmysql客户端中使用以下语句:

    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';