MySQL之备份与恢复(五)

备份与恢复

备份数据

符号分隔文件备份

可以使用SQL命令SELECT INTO OUTFILE以符号分隔文件格式创建数据的逻辑备份。(可以用mysqldump的 --tab选项导出到符号分隔文件中)。符号分隔文件包含以ASCII展示的原始数据，没有SQL、注释和列名。下面是一个导出为逗号分隔值(CVS)格式的例子。对于表个形式的数据来说这是一个很好的通用格式。

mysql> SHOW VARIABLES LIKE '%secure%';
mysql> SELECT * INTO OUTFILE '/var/lib/mysql-files//actor.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM sakila.actor;
1
2

导出时你可能会遇到报错，按照图中所示导入到mysql认为安全的目录下就行。比起SQL导出文件，符号分隔文件要更紧凑且更易于用命令行工具操作，这种方法最大的优点时备份和还原速度更快。可以和导出时使用一样的选项，用LOAD DATA INFILE方法加载数据到表中。

mysql> LOAD DATA INFILE '/var/lib/mysql-files/actor.txt' INTO TABLE sakila.actor FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
1
2

图中也可以看到，由于主键发生了冲突所以没有导入成功，但最起码说明这个导入数据的动作是有的。
下面这个非正式的测试演示了SQL文件和符号分隔文件在备份和还原上的速度差异。在测试中，我们对生产数据做了修改。导出的表看起来像下面这样:

CREATE TABLE load_test(
col1 date NOT NULL,
col2 int NOT NULL,
col3 smallint unsigned NOT NULL,
col4 mediumint NOT NULL,
col5 mediumint NOT NULL,
col6 mediumint NOT NULL,
col7 decimal(3,1) default NULL,
col8 varchar(10) NOT NULL default '',
col9 int NOT NULL,
PRIMARY KEY (col1, col2)
) ENGINE =InnoDB;
1
2
3
4
5
6
7
8
9
10
11
12

这张表有1500万行，占用近700MB的磁盘空格键。对比了两种备份和还原方法的性能。可以看到测试中还原时间有较大的差异。但是SELECT INTO OUTFILE方法也有一些限制。

• 1.只能备份到运行MySQL服务器的机器上的文件中(可以写一个自定义SELECT INTO OUTFILE程序，在读取SELECT结果的同时写到磁盘文件中，我们已经看到有些人采用这种方法)
• 2.运行MySQL的系统用户必须有文件目录的写权限，因为是由MySQL服务器来执行文件的写入，而不是运行SQL命令的用户
• 3.出于安全原因，不能覆盖已经存在的文件，不管文件权限如何
• 4.不能直接导出到压缩文件中
• 5.某些情况下很难进行正确的导出或导入，例如非标准的字符集

文件系统快照

文件系统快照是一种非常好的在线备份方法。支持快照的文件系统能够瞬间创建用来备份的内容一致的镜像。支持快照的文件系统和设备包括FreeBSD的文件系统、ZFS文件系统、GNU/Linux的逻辑卷管理(LVM)、以及许多的SAN系统和文件存储解决方案，例如NetApp存储。不要把快照和备份相混淆。创建快照是减少必须持有锁的时间的一个简单方法:释放锁后，必须复制文件到备份中。事实上，有些时候甚至可以创建InnoDB快照而不需要锁定。我们将要展示两种使用LVM来对InnoDB文件系统做备份的方法，可以选择最小化锁或零锁的方案。快照对于特别用途的备份是一个非常好的方法。一个例子是在升级过程中遇到有问题而回退的情况。可以在升级前创建一个镜像，这样如果升级有问题，只需要回滚到该镜像。可以对任何不确定和有风险的操作都这么做，例如对一个巨大的表做变更(需要多少时间是未知的)

LVM快照是如何工作的

LVM使用写时复制(copy-on-write)的技术来创建快照——例如，对整个卷的某个瞬间的逻辑副本，这与数据库中的MVCC有点像，不同的是它只保留一个老的数据版本。注意，我们说的不是物理副本。逻辑副本看起来好像包含了创建快照卷中所有的数据，但实际上一开始快照是不包含数据的。相比复制数据到快照中，LVM只是简单地标记创建快照的时间点，然后对该快照请求读数据时，实际上是从原始卷中读取的。因此初始的复制基本上是一个瞬间就能完成的操作，不管创建快照的卷有多大。当原始卷中某些数据有变化时，LVM在任何变更写入之前，会复制受影响的块到快照预留的区域中。LVM不保留数据的多个"老版本"，因此对原始卷中变更块的额外写入并不需要对快照做其他更多的工作。换句话说，对每个块只有第一次写入才会导致写时复制到预留的区域。
现在，在快照中请求这些块时，LVM会从复制块中而不是从原始卷中读取。所以，可以继续看到快照中相同时间点的数据而不需要阻塞任何原始卷。如图所示。快照会在/dev目录下创建一个新的逻辑卷，可以像挂载其他设备一样挂载它。理论上讲，这种技术可以对一个非常大的卷做快照，而只需要非常少的物理存储空间，但是，必须设置足够的空间，保证在快照打开时，能够保存所有期望在原始卷上更新的块。如果不预留足够的写时复制空间，当快照用完所有的空间后，设备就会变得不可用。这个影响就像拔出一个外部设备:任何从设备上读的备份工作都会因IO错误而失败