MySQL之备份与恢复(八)

备份与恢复

还原逻辑备份

如果还原的是逻辑备份而不是物理备份，则与使用操作系统简单地复制文件到适当位置的方式不同，需要使用MySQL服务器本身来加载数据到表中。在加载导出文件之前，应该先花一点时间考虑文件有多大，需要多久加载完，以及在启动之前还需要做什么事情，例如通知用户或禁用掉部分应用。禁掉二进制日志也是个好主意，除非需要将还原操作复制到备库:服务器加载一个巨大的导出文件的代价很高，并且写二进制日志会增加更多的(可能没有必要的)开销。加载巨大的文件对于一些存储引擎也有影响。例如，在单个事务中加载100GB数据到InnoDB就不是个好想法，因为巨大的回滚段将会导致问题。应该以可控大小的块来加载，并且逐个提交事务。有两种类型的逻辑备份，所以相应地有两种类型的还原操作。

加载SQL文件

如果有一个SQL导出文件，它将包含可执行的SQL.需要做的就是运行这个文件。假设备份Sakila示例数据库和Schema到单个文件，下面是用来还原的常用命令。

mysql < sakila-backup.sql

也可以从mysql米精灵行客户端用SOURCE命令加载文件。这只是做相同事情的不同方法，不过该方法使得某些事情更简单。例如，如果你是MySQL管理用户，就可以关闭用客户端连接执行时的二进制记录，然后加载文件而不需要重启MySQL服务器。

mysql>SET SQL_LOG_BIN =0;
mysql>SOURCE sakila-backup.sql;
mysql>SET SQL_LOG_BIN=1;

需要注意的时，如果使用SOURCE,当定向文件到mysql时，默认秦广下，发生一个错误不会导致一批语句退出。如果备份做过压缩，那么不要分别解压缩和加载。应该在单个操作中完成解压缩和加载，这样做会快很多。

gunzip -c sakila-backup.sql.gz | mysql

如果想用SOURCE命令加载一个压缩文件，请见下面关于命名管道的讨论。如果只想恢复单个表(例如，actor表)，要怎么做呢？如果数据没有分行但有schema信息，那么还原数据并不难。

grep 'INSERT INTO `actor` sakila-backup.sql | mysql sakila'

或者，如果文件是压缩过的，那么命令如下:

gunzip -c sakila-backup.sql.gz | grep 'INSERT INTO `actor`' | mysql sakila

如果需要创建表并还原数据，而在单个文件中有整个数据库，则必须先编辑这个文件。这也是有一些人喜欢导出每个表到各自文件中的原因。大部分编辑器无法应付巨大的文件，尤其如果它们是压缩过的。另外，也不会想实际地编辑文件本身——只想抽取相关地行——因此可能必须做一些命令工作。使用grep来仅抽出给定表的INSERT语句较简单，就像我们在前面命令中做的那样，但得到CREATE TABLE语句比较难。下面是抽取所需段落的sed脚本。

sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `actor`/!d;q' sakila-backup.sql

我们得承认这条命令非常隐晦。如果必须以这种方式还原数据，那只能说明备份设计非常糟糕。如果有一点规划，可能就不会需要痛苦地区尝试弄清楚sed如何工作了。只需要备份每个表到各自地文件，或者可以更进异步，分别备份数据和Schema.

加载符号分隔文件

(符号分隔样式)

(sql文件)

如果是通过SELECT INTO OUTFILE导出的符号分隔文件，可以使用LOAD DATA INFILE通过相同的参数来加载。也可以用mysqlimport，这是LOAD DATA INFILE的一个包装。这种方式依赖命名约定决定从哪里加载一个文件的数据。我们希望你导出了Schema，而不仅是数据。如果是这样，那应该是一个SQL导出，就可以使用上一节中描述的技术来加载。使用LOAD DATA FILE有一个非常好的优化技巧。LOAD DATA INFILE必须直接从文本文件中读取，因此，如果是压缩文件很多人会在加载前先解压缩，这是非常慢的磁盘密集型操作。然而，在支持FIFO"命名管道"文件的系统如GNU/Linux上，对这种操作有个很好的方法。首先，创建一个命名管道并将解压缩数据流到它里面。

mkfifo /tmp/backup/default/sakila/payment.fifo
chmod 666 /tmp/backup/default/sakila/payment.fifo
gunzip -c /tmp/backup/default/sakila/payment.txt.gz > /tmp/backup/default/sakila/payment.fifo

注意到我们使用了一个大于号字符(>)来重定向解压缩输出到payment.fifo文件中——而不是在不同程序之间创建匿名管道的管道符号。管道会等待，直到其他程序打开它并从另外一段读取数据。简单一点说，MySQL服务器可以从管道中读取解压缩后的数据，就像其他文件一样。如果可能，不要忘记尽调二进制日志。

mysql>SET SQL_LOG_BIN = 0; -- Optional
          > LOAD DATA INFILE 'tmp/backup/defualt/sakila/payment/fifo'
          > INTO TABLE sakila.payment;

一旦MySQL加载完数据，gunzip就会退出，然后可以删除该命令管道。在MySQL命令行客户端使用SOURCE命令加载压缩的文件也可以使用此技术。Percona Toolkit中的pt-fifo-split程序还可以帮助分块加载大文件，而不是在单个大事务中操作，这样效率更高

你无法从这里到达那里

从DATETIME变为TIMESTAMP.以节约空间并使处理过程更快，表定义如下:

CREATE  TABLE tbl(
col1 timestamp NOT NULL,
col2 timestamp NOT NULL default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTMAP
......

这个表帝国一在MySQL5.0.40版本上导致了一个语法错误，而这是创建时的版本。可以执行导出，但无法加载。这很奇怪，诸如这样无法预料的错误也是测试备份重要的原因之一。你永远不会直到什么会阻止你还原数据

基于时间点的恢复

对MySQL做基于时间点的恢复常见的方法是还原最近一次全备份，然后从那个时间点开始重放二进制日志(有时较"前滚恢复")。只要有二进制日志，就可以恢复到任何希望的时间点。甚至可以不太费力地恢复单个数据库。主要的缺点是二进制日志重放可能会是一个很慢的过程。它大体上等同于复制。如果有一个备库，并且已经测量到SQL线程的利用率有多高，那么对重放二进制日志会有多快就会心里有数了。例如，，如果SQL线程约有50%被利用，则恢复一周二进制日志的工作可能在三到四天内完成。一个典型场景是对有害的语句的结果做回滚操作，例如DROP TABLE。让我们看一个简化的例子，看只有MyISAM表的情况下该如何做。假如是在半夜，备份任务在运行与下面所列相当的语句，复制数据库到同一服务器上的其他地方。

mysql> FLUSH TABLES WITH READ LOCK;
         > server1# cp -a /var/lib/mysql/sakila /backup/sakila;
mysql> FLUSH LOGS;
         > server1# mysql -e "SHOW MASTER STATUS" --vertical > /backup/master.info
mysql> UNLOCK TABLES;

然后，假设有人在晚些时间运行下列语句.

mysql>USE sakila;
mysql>DROP TABLE sakila.payment;

为了便于说明，我们先假设可以单独地恢复这个数据库(即此库中地表不涉及跨库查询)。再假设是直到后来出问题才意识到这个有问题地语句。目标是恢复数据库中除了有问题地语句之外所有发生地事务。也就是说，其他表已经做的所有修改都必须保持，包括有问题的语句运行之后的修改。这并不是很难做到。首先，停掉MySQL以阻止更多的修改，然后从备份中仅恢复sakila数据库。

server1# /etc/init.d/mysql stop
server1# mv /var/lib/mysql/sakila /var lib/mysql/sakila.tmp
server1# cp -a /backup/sakila /var/lib/mysql

再到运行的服务器的my.cnf中添加如下配置以禁止正常的连接

skip-networking
socket=/tmp/mysql_recover.sock

现在可以安全地启动服务器了。

server1# /etc/init.d/mysql start

下一个任务是从二进制日志中分出需要重放和忽略的语句。事发时，自半夜的备份依赖服务器只创建了一个二进制日志。我们可以用grep来检查二进制日志文件以找到问题语句

server1# mysqlbinlog --database=sakila /var/log/mysql/mysql-bin.000215 | grep -B3 -i 'drop table sakila.payment'

我们可以看到，想忽略的语句在日志文件中的某个位置，下一个语句的位置是多少。可以用下面的命令重放二进制日志直到某个位置，然后从某个位置继续

server1# mysqlbinlog --database=sakila /var/log/mysql/mysql-bin.000215 --stop-position=352 | mysql -uroot -p
server1# mysqlbinlog --database=sakila /var/log/mysql/mysql-bin.000215 --start-position=429 | mysql -uroot -p

接下来要做的是检测数据以确保没问题，然后关闭服务器并撤销对my.cnf的改变，最后重启服务器