【MySQL学生手册】mysqldump数据恢复

本文地址：https://www.askmac.cn/archives/mysql-data-recovery.html

 

11.8 数据恢复

 

进行数据恢复操作的前提是你需要一份备份，作为恢复所需的一部分。它可以是你数据库某个时间点所做的snapshot快照（当进行备份时）。不过，对于一个活动的服务端，数据会在最近的备份之后就会发生改变。因此恢复操作所需的另一部分材料则是服务端之后所做的数据变更记录 —— 也就是，binary log。因此一个恢复操作涉及到使用备份来恢复数据库并且之后重新执行在binary log中所包含的备份之后的数据修改操作。

 

通常恢复操作有以下步骤：

1. 首先对数据存放目录进行拷贝，这是为了防止在恢复时出错而以防万一。
2. 使用备份文件恢复数据库。如果你已经制作有一个二进制备份，那么这个步骤涉及到关闭服务端并使用这些备份来替换丢失或损坏的文件。
3. 重新执行在备份后记录在binary log中的数据修改操作。

 

11.8.1 导入mysqldump输出

 

可以使用mysqldump工具来导出数据并生成SQL格式的dump文件，之后通过mysql客户端工具来执行此文件进行导入操作。例如，你可以使用如下语句来生成一份world数据库下Country表的dump文件：

 

shell> mysqldump world Country > dump.sql

 

之后的数据库导入，则使用mysql：

 

shell> mysql world < dump.sql

 

当mysqldump的输出文件中没有指定数据库时，你在使用mysql客户端工具命令时就有必要指定到某个需要操作的库。如果在mysqldump执行中带有 –database 或 –all-databases项时，所生成的dump文件则已包含有相应的 USE db_name语句。

 

mysqldump在使用时不仅仅可用于恢复表和数据库，同时和mysql一起使用也可起到类似”拷贝”的用途。mysql可以直接从管道支进行读取，因此两个命令可以组合成一条命令来将库中的表拷贝到另一个库中。例如，将world数据库中Country表拷贝到test库中，使用以下命令：

 

shell> mysqldump world Country | mysql test

 

管道技术也可以将数据库或表通过网络拷贝到另一个远端库中。如，远端主机为other.host.com：

 

shell> mysqldump world Country | mysql -h other.host.com world

 

如果dump文件中包含有很长的INSERT语句，那么这可能会超出默认到通信缓冲（communication buffer: 1M）。你可以对mysqldump和mysql通过使用 --max-allowed-packet项来增大缓冲大小。这个命令项可以设置一个单位为byte的值或者值后加上K，M或G来指明大小。例如，--max-allowed-packet=32M定义了大小为32MB。服务端也需要在运行时使用 --max-allowed-packet值来增大其自身的通信缓冲，使其足够大。

当你使用mysqldump时使用了 --tab项，那么它会生成一个以tab制表符分隔的数据文件。对于这种dump文件，重新导入时则需要注意使用相应的方法。假设你的dump文件对应导出表world.City使用/tmp目录在作为输出目录：

 

 

shell> mysqldump --tab=/tmp world City

 

 

输出将分两部分，一部分为一个包含有City表CREATE TABLE语句的City.sql文件，另一部分为一个City.txt文件包含了相应的表数据。当使用这些文件进行表导入时，首先切换当前路径到导出的目录，然后使用mysql来处理.sql文件，之后只用mysqlimport来导入.txt中的表数据。

 

shell> cd /tmp
shell> mysql world < City.sql
shell> mysqlimport world City.txt

 

如果你在使用 --tab项时还结合其它格式控制项如 --fields-terminated-by 和 --fields-enclosed-by一起使用，那么在使用mysqlimport进行导入时，就需要使用对应相同的格式控制项来使得命令程序知道如何翻译数据文件以进行导入。

 

 

11.8.2 使用MySQL Workbench导入dump文件

 

MySQL Workbench可以用于导入由其本身或mysqldump所生成的SQL格式的dump文件。它也可以在导入前对dump文件进行分析来查看哪些表会被恢复，然后在相应窗口中呈现。你可以选择在操作前排除某些表或视图，这对于你仅需要恢复dump文件中部分数据时很有用（例如，仅从一个全库dump文件中恢复某些表）。

 

11.8.3 使用LOAD DATA INFILE进行导入

 

LOAD DATA INFILE可作为除了INSERT语句外的另一种可用方式来为表增加新记录。INSERT语句是在语句中直接定义了要插入的记录值。LOAD DATA INFILE则从特定的数据文件中来读取值。

最简单的LOAD DATA INFILE语句形式仅指定数据文件名和所要导入数据的目标表即可。

 

LOAD DATA 'file_name' INTO TABLE table_name;

 

 

其中文件名以引号括起。在Windows上，路径分隔符为【\】，但MySQL会将此符号作为字符串中的逃逸字符处理。为了解决这个问题，请在Windows路径中使用【/】或【\\】作为分隔符。在导入文件 C:\mydata\data.txt时，以以下语句定义导入操作：

 

 

LOAD DATA INFILE 'C:/mydata/data.txt' INTO TABLE t;
LOAD DATA INFILE 'C:\\mydata\data.txt' INTO TABLE t;

 

 

LOAD DATA INFILE语法如下：

 

MySQL假设（除非被特别告知）文件被放置在服务端主机上，且文件具有默认格式（列以tab制表符分隔，每行以\n做中止符起新行），且每行包含有表中每列值。不过LOAD DATA INFILE可以提供语法来控制数据导入操作中的每个方面：

• 指定要导入的文件
• 跳过数据文件行
• 导入指定表列
• 跳过或转换列值
• 对于重复行记录的控制
• 数据文件格式定义
• NULL值导入

 

对以tab或逗号进行列值分隔的数据文件的导入

导入数据文件，在使用LOAD DATA INFILE命令时，你必须了解：

• 列值分隔符
• 行分隔符
• 列值使用的哪种符号括起来（如引号等）？
• 列名是否在文件中
• 在导入前，文件头上是否需要跳过某些行？
• 文件所依赖的文件系统
• 是否有访问文件权限
• 列顺序
• 文件中抽取的列数量和表是否匹配？

假设一个名为/tmp/data.txt的文件包含有以逗号分隔的CSV格式数据，列值以双引号包围并且行以回车符中止。使用LOAD DATA INFILE语句导入表：

 

 

LOAD DATA INFILE '/tmp/data.txt' INTO TABLE t
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\n';

 

定义文件位置

LOAD DATA INFILE可以读取坐落在服务端主机或客户端主机位置的文件：

默认，MySQL认为文件在服务端主机上。MySQL服务端可以直接读取文件。

如果语句使用LOAD DATA LOCAL INFILE而不是LOAD DATA INFILE，那么所读取的文件是从客户端主机（语句发出的主机）读取。这种情况下，客户端程序会读取数据文件并通过网络将内容发送给服务端。

 

跳过或转换列值

在导入操作时，可以选择跳过某些数据文件中的列，或在插入表前转换所读取的列值。这些特性可以通过在插入列时定义用户变量和使用SET语法的方式进行操作。

 

在定义时，可以将一个数据列输入到一个用户变量而不是将其直接放入表列中。当你分配列给一个用户变量，但不对此变量做任何处理时。效果就相当于忽略此列，此列值就不会被插入表中。或者，通过设置SET语法，你可以使用表达式来在值插入前进行转换。

 

例如，你有一个名为/tmp/people2.txt的文件，其导出的表内容结构和subscriber表类似，且需要将这些数据倒入这张表中，已知文件内容中有四列值：ID number, fisrt name, last name和address列（其中仅address列值可对匹配subscriber表的address列）。文件内容需要经过部分列值转换才能真正导入到subscriber表。首先，ID列值和subscriber表不匹配需要被无视。其二，first name和last name需要使用空格进行字符串合并。最终转换导入的语句可以这样写：

 

LOAD DATA INFILE '/tmp/people2.txt' INTO TABLE subscriber(@skip, @first, @last, address)
 SET name=CONCAT(@first, ' ', @last);

 

LOAD DATA INFILE对重复记录的处理

LOAD DATA INFILE可以使你在使用INSERT或REPLACE语句新增记录时，可控制对具有唯一键值表的新重复记录的处理。你可以允许报错发生，选择无视新记录，或使用新记录替换旧记录。LOAD DATA INFILE通过两个修饰性关键字来支持对重复记录问题的处理。不过，数据文件在服务端主机还是客户端主机上，进行导入时对重复记录的处理行为会稍有不同，这里你需要注意到。

 

当导入的文件在服务端主机时，LOAD DATA INFILE会以此行为处理重复键值的记录：

• 默认，一行具有重复键的导入记录会导致报错而之后数据文件中余下的记录不会被导入。之前的记录则被导入了表中。
• 如果你在文件名后加上了IGNORE关键字，那么键值重复记录会被无视不导入，也不会有报错抛出。LOAD DATA INFILE会处理整个文件，并将没有重复键值问题的记录都导入进表中。
• 如果你在文件名后加上REPLACE关键字。新的重复键值记录会将表中具有相同唯一键值的记录进行替换更新。LOAD DATA INFILE会处理整个文件并将所有记录写入表中。

IGNORE和REPLACE这两个关键字是互斥的，你在使用时只能选其一，不能两个一起使用。

 

对于存放在客户端主机中的数据文件，唯一键重复问题的处理基本相似，不过默认是会忽略含有重复键值的记录，而不是进行报错中止操作处理。就是说，默认使用的IGNORE修饰符功能。其原因是C/S协议不允许从客户端主机传输数据文件到服务端的过程在启动后被打断，因此并没有方便的方法在操作中间中断操作。

 

执行LOAD DATA INFILE后返回的信息

当LOAD DATA INFILE执行时，它会持续追踪被处理的记录数和数据转换的发生数。然后在最后以以下字符串格式返回给客户端（其中的返回值会依照每次操作的情况不同而不同）：

 

 

Records: 174  Deleted: 0  Skipped: 3  Warnings: 14

 

统计的信息具有以下含义：

• Records：指的是从数据文件中读取的记录数。这并不是指被加入到表中的记录数。
• Deleted：指的是表中由于具有相同唯一键值而被替换的记录数。如果你在语句中使用REPLACE关键字，那么这里统计的值可能为非0值。
• Skipped：指的是由于具有重复唯一键值而被无视不导入的记录数。如果你在语句中使用IGNORE关键字，那么这里统计的值可能为非0值。
• Warnings：指的是输入文件中发现的问题数。这可能由有多种原因产生，如丢失所需的数据值或数据转换问题（例如，由于目标列为数值型，而将一个空字符串转换为0）。由于警告可能对记录中的每个列值产生警告，因此其统计数有可能高于输入的记录数。你使用试用SHOW WARNINGS语句在导入文件后查看相应告警信息。

注意：即便你正在进行的操作正处于事务中，且你的目标表是一张InnoDB表，LOAD DATA INFILE语句执行失败后，已经导入的记录不会被回滚。

 

使用mysqlimport进行导入

你也可以使用mysqlimport客户端程序来将数据文件导入到表中。它实际上是对LOAD DATA INFILE语句提供的命令行接口工具。mysqlimport会查看在其命令行执行时指定的命令项，之后它会连接服务端，对于每个在命令行中提供的输入文件名，发出一句LOAD DATA INFILE语句来将对应文件数据导入相应的表中。

 

由于mysqlimport以这种方式运行，因此为了更有效地使用，你应该很熟悉LOAD DATA INFILE语句。

 

从命令行调用mysqlimport：

 

shell> mysqlimport options db_name input_file ...

 

db_name指定了需要导入表所在的数据库名，input_file则指定了需要导入的数据文件。你可以根据你的需要在数据库名后加上多个需要导入的文件名。

 

mysqlimport使用每个文件的文件名来确认对应文件数据所要导入的表。程序在剥离了文件名尾缀扩展名后得到表名。例如，mysqlimport通过City.txt或City.dat文件名输入来知道导入的目标表名为City。在找到对应表名后，mysqlimport发出LOAD DATA INFILE语句来将文件数据导入到表中。

 

使用mysqlimport时被导入的表必须已经存在，且每个输入文件中必须仅含有数据值。mysqlimport不能对包含SQL语句的dump文件做处理。

 

可以在调用mysqlimport时加上 --help项来查看完整的可用命令项说明。

 

默认下，mysqlimport的输入文件被假定其所包含的每行是以换行符中止，且没行列值间以tab制表符分隔。和LOAD DATA INFILE语句的所需文件的默认格式相同。

 

下面列出一些通常使用的命令项：

 

命令项	定义
--lines-terminated-by=string	指定每行末尾中止符，默认为【\n】
--fields-terminated-by=string	指定每行列数据之间的分隔符，默认为【\t】制表符
--fields-enclosed-by=char	如果列值使用类似引号这样的符号括起来的话，这里指定括起的符号，默认为双引号【”】
--ignore 或 --replace	对于存在重复键值的记录的处理，类似于LOAD DATA INFILE中IGNORE和REPLACE的使用
--local	允许使用放置于客户端主机位置的数据文件

 

 

这些命令项使你能够灵活应对包含各种格式的数据文件。

下面以对world数据库City表使用City.txt文件导入数据为例：

 

 

shell> mysqlimport --lines-terminated-by="\r\n" world City.txt
shell> mysqlimport --fields-terminated-by=, --lines-terminated-by="\r" world City.txt
shell> mysqlimport --fields-enclosed-by='"' world City.txt

 

11.8.4 Binary Log内容处理

在你恢复了二进制备份文件或重新导入你的文本备份文件后，你应该重新处理那些记录在服务端binary日志文件中的备份时间点之后的数据变更，这样才算是完成恢复操作。为了做这个，你需要判断哪些日志是在你做完备份后写入的。然后使用mysqlbinlog工具程序将这些其中的日志内容转成SQL语句文本，之后使用mysql来执行这些结果语句。

 

如果在备份之前和之后，每个日志文件都有被完整写入，那么我们在处理binary log时是最简单的。加入你在备份前的binary log编号为1到49。备份后数据修改写入的日志，其日志编号从50到52。如果binary log文件取名以bin为前缀取名，日志处理命令如下：

 

shell> mysqlbinlog bin.000050 bin000051 bin.000052 | mysql

 

所有需要处理的二进制日志文件应该在一个mysqlbinlog命令中被处理。如果你使用多行命令对文件进行按个处理，那么这里可能会存在内部文件中语句依赖无法满足的问题。

 

如果一个binary log文件是写到一半时，进行数据库备份操作。那么在备份后，你仅应抽取此日志中的部分，再加上之后的所有日志文件来进行恢复处理。为了处理部分文件抽取，mysqlbinlog支持你使用命令项来指定时间或日志抓取的起始位置：

 

• --start-datetime项定义了日志记录抓取的起始日期时间，命令项参数值为DATETIME格式值。
• --start-position项可被用于指定开始抓取的起始日志位置。
• --stop-datetime和 --stop-position项被用于指定日志停止抓取的时间点和日志位置。

 

例如，抓取编号从50至52的日志内容，且从2016-05-20 17:43:20时记录的事件开始抓取：

 

shell> mysqlbinlog --start-datetime="2016-05-20 17:43:20" bin.000050 bin.000051 bin.000052 | mysql

 

如果你不确定你需要开始抓取的对应时间点或日志位置，那么可以使用mysqlbinlog来读取并显示日志内容，从而进行比对查看。在查看时，可以加上分页程序命令以方便查看：

 

shell> mysqlbinlog file_name | more