数据库优化——备份与恢复

摘要

本文为数据库优化系列文章的第五篇文章 :《数据库优化——备份与恢复》

更多文章参见

数据库优化 :
http://blog.csdn.net/leyounger/article/details/70157087

备份与恢复

1. 备份/恢复策略

对于一个DBA来说，定制合理的备份策略是十分重要的：

//(1) 确定要备份的表的存储引擎是事务性还是非事务性，两种不同的存储引擎备份方式在处理数据一致性方面是不太一样的//(2) 确定使用全备份还是增量备份。// 全备份: //    优点:备份保持最新备份，恢复的时候花费更少的时间//    缺点:如果数据量大，将花费很多的时间，并对系统造成较长时间的压力// 增量备份://    优点:只需要备份每天的增量日志，备份时间少，对负载压力也小//    缺点:恢复的时候需要全备份加上次备份到故障前的所有日志，恢复时间会长些//(3) 可以采取复制的方法来做异地备份，但是复制不能代替备份，它对数据库的误操作也无能为力//(4) 要定期做备份，备份的周期要充分考虑系统可以承受的恢复时间。备份要在负载较小的时候进行。//(5) 确保MySQL打开log-bin选项，有了binlog，MySQL才可以在必要的时候做完整恢复，或基于时间点的恢复，或基于位置的恢复//(6) 要经常做备份恢复测试，确保备份是有效的，并且使可以恢复的

2. 逻辑备份和恢复

逻辑恢复的最大优点是，对于各种引擎都可以用同样的方法来备份

1. 备份

使用mysqldump工具来完成逻辑备份，有以下三种方法来调用

//(1) 备份指定的数据库，或者此数据库中某些表shell> mysqldump [options] db_name [tables]//(2) 备份指定的一个或多个数据库shell> mysqldump [options] ---database DB1 [DB2 DB3]//(3) 备份所有数据库shell> mysqldump [options] --all-database

如果没有指定数据库中的任何表，默认导出所有数据库中所有表。以下给出一些例子：

//(1) 备份所有数据库mysqldump -uroot -p --all-database > all.sql//(2) 备份数据库testmysqldump -uroot -p test > all.sql//(3) 备份数据库test下的表empmysqldump -uroot -p test emp > all.sql//(4) 备份数据库test下的表emp和deptmysqldump -uroot -p test emp dept > all.sql//(5) 备份数据库test下的所有表为逗号分隔的文本，备份到/tmpmysqldump -uroot -T /tmp test emp --fields-terminated-by ','

MyISAM引擎在备份的时候需要加上-l参数，表示将所有表加上读锁，备份期间，所有表将只能读而不能进行数据更新。

但是，对于InnoDB来说，可以采取更好的选项–single-transaction，此选项将使得InnoDB引擎获得一个快照，使得备份的数据能够保证一致性。

2. 完全恢复

//恢复的语法mysql -uroot -p dbname < bakfile//注意，将备份恢复后数据并不完整，还需要将备份后执行的日志进行重做mysqlbinlog binlog-file | mysql -u root -p ***//以下是一个完整的备份与恢复的例子//(1) 上午9点，备份数据库//  mysqldump -uroot -p -l -F test > test.tmp//  其中-l表示给所有表加读锁，-F表示生成一个新的日志文件//  当前数据如下 //  ————————————//  |id  |name |//  |1   |z1   |//  |2   |z2   |//  |3   |z3   |//  |4   |z4   |//  ————————————//(2) 9点半，然后，插入新的数据//  INSERT INFO emp VALUES(5,'z5');//  INSERT INFO emp VALUES(6,'z6');//(3) 10点，数据库突然故障，数据无法访问，需要恢复备份//  mysql -uroot -p test < test.dmp//  恢复后数据如下 //  ————————————//  |id  |name |//  |1   |z1   |//  |2   |z2   |//  |3   |z3   |//  |4   |z4   |//  ————————————//(4) 使用mysqlbinlog恢复自mysqldump备份以来的BINLOG//  mysqlbinlog localhost-bin.000015 | mysql -u root -p test//  完全恢复后的数据如下//  ————————————//  |id  |name |//  |1   |z1   |//  |2   |z2   |//  |3   |z3   |//  |4   |z4   |//  |5   |z5   |//  |6   |z6   |//  ————————————

3. 基于时间点恢复

由于误操作，比如误删除了一张表，这时使用完全恢复是没有用的，因为日志里留存误操作的语句，我们需要的是恢复到误操作之前的状态，然后跳过 误操作语句，再恢复后面执行的语句，完成我们的恢复。这种恢复叫做不完全恢复。

在MySQL中，不完成恢复分为基于时间点的恢复和基于位置的恢复

//以下是基于时间点的操作步骤//(1) 如果上午10点发生误操作，可以用以下语句用备份和BINLOG将数据恢复到故障前// mysqlbinlog --stop-date="2005-04-20 8:59:59" /var/log/mysql/bin.123456 | mysql -u root -pmypwd//(2) 跳过故障时的时间点，继续执行后面的BINLOG，完成恢复// mysqlbinlog --start-date="2005-04-20 10:01:00" /var/log/mysql/bin.123456 | mysql -u root -pmypwd

4. 基于位置恢复

和基于时间点的恢复类似，但是更精确，因为同一个时间点可能又很多条SQL语句同时执行。恢复的操作步骤如下。

//(1) 在shell下执行如下命令mysqlbinlog --start-date="2005-04-20 9:55:00"             --stop-date="2005-04-20 10:05:00"             /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql// 该命令在/tmp目录创建小的文本文件，编辑此文件，找到出错语句前后的位置号，例如前后位置号分别为368312和368215//(2) 恢复了一起的备份文件后，应从命令行输入下面的内容mysqlbinlog --stop-position="368312" /var/log/mysql/bin.123456 \            | mysql -u root -pmypwdmysqlbinlog --start-position="368315" /var/log/mysql/bin.123456 \            | mysql -u root -pmypwd// 上面的第一行将恢复到停止位置为止的所有事务。// 下一行将恢复从给定的起始位置直到二进制日志结束的所有事务。// 因为mysqlbinlog的输出包括每个SQL记录之前的SET TIMESTAMP语句，因此恢复的数据和相关MySQL日志将反应事务执行的原时间。

3. 物理备份和恢复

物理备份又分为冷备份 和 热备份两种，和逻辑备份相比，它的最大优点是备份和恢复的速度更快，因为物理备份的原理都是基于文件的cp

1. 冷备份

冷备份就是停掉数据库服务，cp数据文件的办法。这种方法对MyISAM和InnoDB都适合，但是一般很少使用，因为很多应用不允许长时间停机。

进行备份的操作如下：停掉MySQL服务，在操作系统级别备份MySQL的数据文件和日志文件到备份目录

进行恢复的操作如下：首先停掉MySQL服务，在操作系统级别恢复MySQL的数据文件；然后重启MySQL服务，使用mysqlbinlog工具恢复自备份以来的所有BINLOG

2. 热备份

MySQL中，对于不同的引擎热备份方法有所不同，下面主要介绍MyISAM和InnoDB的热备份方法

1. MyISAM引擎

MyISAM的热备份方法有很多，本质其实就是将要备份的表加读锁，然后再cp数据文件到备份目录。常用的两个方法：

//(1) 使用mysqlhotcopy工具shell> mysqlhotcopy db_name [/path/to/new_directory]//其他功能可以使用-help来查看帮助//(2) 手工锁表copy(在mysqlhotcopy使用不正常时)// 首先，给数据库所有表加读锁mysql> flush tables for read;// 然后，cp数据库文件

2. InnoDB引擎

ibbackup是innobase公司的一个热备份工具，专门针对InnoDB进行物理热备份，此工具是收费的。

具体怎么用，还是问问度娘吧 ：）

4. 表的导入导出

1. 导出

为了一些特定的目的，经常需要将表里的数据导出为某些符号分割的纯数据文本，而不是SQL语句。主要的应用包括：
(1) 用来作为Excel显示
(2) 单纯为了节省备份空间
(3) 为了快速地加载数据，LOAD DATA的加载速度比普通的SQL加载要快20倍以上

//(1) 方法1// 使用SELECT ... INTO OUTFILE 命令来导出数据，具体语法如下mysql> SELECT * FROM tablename INTO OUTFILE 'target_file' [option];

其中的[option]可以是以下选项

FIELDS TERMINATED BY 'string' //(字段分隔符，默认为制表符'\t')FIELDS [OPTIONALLY] ENCLOSED BY 'char' //(字段引用符，如果加OPTIONALLY选项，则只用在char、varchar和text等字段型字段上，默认不适用引用符)FIELDS ESCAPED BY 'char' (转义字符，默认为'\')LINES STARTING BY 'string' (每行前都加此字符串，默认为'')LINES TERMINATED BY 'string' (行结束符，默认为'\n')

举个栗子，将emp表中数据导出为数据文本，其中，字段分隔符为”,”，字段引用符为””“（双引号），记录结束符为回车符，具体实现如下：

mysql> SELECT * FROM emp INTO OUTFILE '/tmp/emp.txt'         FIELDS TERMINATED BY "," ENCLOSED BY '"';

如果不希望数值型字段加上引号，则使用OPTIONALLY

mysql> SELECT * FROM emp INTO OUTFILE '/tmp/emp.txt'         FIELDS TERMINATED BY ","         OPTIONALLY ENCLOSED BY '"';

注意：SELECT … INTO OUTFILE 产生的输出文件如果有重名文件，将不会创建成功，原文件不会被自动覆盖

//(2) 方法2 用mysqldump导出数据为文本mysqldump -u username -T target_dir dbname tablename [option][option]可以使用以下选项--fields-terminated-by=name--fields-enclosed-by=name--fields=optionally-enclosed-by=name--fields-escaped-by=name--lines-terminated-by=name

2. 导入

//(1) 使用LOAD DATA INFILEmysql> LOAD DATA [LOCAL] INFILE 'filename' INTO TABLE tablename [option][option]太多，读者请自行百度 ：）//(2) 使用mysqlimportshell> mysqlimport -u root -p*** [--LOCAL] dbname order_tab.txt [option][option] 与mysqldump基本相同