第 3 篇 高级应用 第 12 章 MySQL的备份和恢复

数据库的备份和恢复是信息管理系统中非常重要的内容，备份，就是把数据库复制到转储设备的过程。和其他数据库类似，MySQL的备份恢复也分为逻辑备份恢复和物理备份恢复。除了直接操作MySQL完成MySQL完成备份恢复外，日常开发管理中常常使用数据库客户端来完成相关操作，phpMyAdmin就是一款不错的MySQL客户端工具。

12.1 数据库的备份

对数据库管理员来说，了解数据库备份的重要性并合理定制出数据备份方案无疑是管理工作中的重中之重。数据备份是保护数据的最后手段，也是防止主动型信息攻击的最后一道防线。

12.1.1 数据库备份的意义

完整的备份包括自动化的数据管理与系统的全面恢复，因此，从这个意义上说，备份=拷贝+管理；数据库备份的意义如下：
1. 提供信息管理系统的风险修复能力，在数据库崩溃的时候能及时找到备份数据；
2. 在web2.0时代，没有用户数据的应用没有任何意义，数据库的备份是一种防患于未然的强力且最有效手段。
3. 使用数据库的备份和还原是数据应急方案中代价花费最小的，是企业数据保护的最后选择。
数据库管理员可以思考的因素：
1. 数据库要定期做备份，备份的周期应当根据应用数据系统可承受的恢复时间，而且定期备份的时间应当在系统负载最低的时候进行。对于重要的数据，要保证在极端情况下的损失都可以正常恢复。
2. 定期备份后，同样需要定期做恢复测试，了解备份的正确可靠性，确保备份是有意义的、可回复的。
3. 根据系统需要来确定是否采用增量备份，增量备份只需要备份每天的增量数据，备份花费的时间少，对系统负载的压力也小。缺点就是恢复的时候需要加载之前所有的备份数据，回复时间较长。
4. 确保MySQL打开了log-bin选项，MySQL在做完整恢复或者基于时间点恢复的时候都需要BINLOG。
5. 可以考虑异地备份。

12.1.2 逻辑备份和恢复

1. 逻辑备份
逻辑备份也可称为文件级备份，是将数据库中的数据备份为一个文本文件，而备份的大小取决于文件大小。并且该文本文件是可以移植到其他机器上的，甚至是不同硬件结果的机器。
（1）使用MYSQLDUMP命令生成INSERT语句备份
此方法类似Oracle的EXPDP/EXP工具，语法如下：
mysqldump [arguments] > file_name.sql
范例12-1
备份所有数据库：
mysqldump -uroot -proot –all-database>/tmp/alldb.sql
备份某些数据库：
mysqldump -uroot -proot –database sqoop hive>/tmp/sqoop_hibe.sql
备份某数据库中的表：
mysqldump -uroot -proot sqoop tb1>/tmp/sqoop_tb1.sql
查看备份内容：
more sqoop_tb1.sql
需要考虑如何保证数据备份的一致性？可通过以下两种方法做到：
- 同一时刻取出所有数据
对于事务支持的存储引擎，如Innodb或者BDB等，可以通过控制将整个备份过程在同一个事务中，使用“–single-transaction”选项，示例如下：
mysqldump –single-transaction test>test_back.sql
- 数据库中的数据处于静止状态
LOCK-TABLES每次锁定一个数据库的表，此参数默认为true。
LOCK-ALL-TABLES一次锁定所有的表，使用与dump的表分别处于各个不同的数据库中的情况。
（2）生成特定格式的纯文本文件备份
- 通过SELECT…TO OUTFILE FROM…命令
通过Query将特定数据以指定的方式输出到文本文件中，类似与Oracle中的SPOOL功能。参数说明如下：
FIELDS ESCAPED BY [‘name’]:在SQL语句中需要转义的字符；
FIELDS TERMINATED BY：设定每两个字段之前的分隔符；
FIELDS [OPTIONALLY] ENCLOSED BY ‘name’:包装，有OPTIONALLY数字类型不被包装，否则全包装；
LINES TERMINATED BY ‘name’：行分割符，即每记录结束时添加的字符。
- 通过MYSQLDUMP工具命令导出文本
用此方法可以生成一个文本数据和一个对应的数据库结构创建脚本
技巧：如果没有指定具体备份的数据表，则mysql默认会导出该数据库的的所有表。
2.逻辑备份的恢复
（1）INSERT语句文件的恢复
使用MySQL命令直接恢复
把sqoop库中的tb1表恢复到test库中，语句：
mysql -uroot -proot -D test 或者连接上MySQL在命令行中执行恢复
mysql -uroot -proot -D test
select database();
source /tmp/sqoop_tb1.sql;
或者./tmp/sqoop_tb1.sql
（2）纯文本文件的恢复
使用LOAD DATA INFILE命令
此命令是SELECT…TO OUTFILE FROM反操作，类似于Oracle的Sqlldr工具，语法：
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE ‘file_name.txt’
[REPLACE | IGNORE]
INTO TABLE tal_name
[FIELDS
[TERMINATED BY ‘string’]
[[OPTIONALLY] ENCLOSED BY ‘char’]
[ESCAPED BY ‘char’]
]
[LINES
[STARTING BY ‘string’]
[TERMINATED BY ‘string’]
]
[IGNORE number LINES]
[(col_name_or_user_var,…)]
[SET col_name=expr,..]]
使用mysqlimport工具恢复
此工具可用于恢复上面MYSQLDUMP生成TET和SQL两文件，所以要保证TXT文件对应的数据库中的表存在。
范例12-6
首先恢复表结构
mysql -uroot -proot -D test < /tmp/tb1.sql
恢复数据：
mysqlimport -uroot -proot test –fields-enclosed-by=\”–fields-terminated-by=,/tmp/tb1.txt

12.1.3 物理备份和恢复

物理备份和逻辑备份的最大优点是备份和恢复的速度都很快，这是因为物理备份的原理都是基于文件拷贝。物理备份比逻辑备份速度更快，物理备份分为以下两种：

1. 冷备份：这种方式是最直接的备份方式，就是首先停掉数据库服务，然后CP数据文件，恢复是停止Mysql，先进行操作系统级别恢复数据文件，然后重启MySQL服务，使用MYSQLBINLOG工具恢复自备份以来的所有BINLOG。这种方式虽然简单，而且对各个存储引擎都支持，但是有一个非常大的弊端就是需要关闭数据库服务。在当前的大多信息系统都是不允许长时间停机的。
2. 热备份：对于不同存储引擎方法也不同
   （1）MyISAM存储引擎，本质就是将要备份的表加读锁，然后在CP数据文件到备份目录。
   方法1：使用MYSQLHOTCOPY工具
   MYSQLHOTCOPY工具是MySQL自带的热备份工具，使用方法：
   mysqlhotcopy db_name [/path/to/new_directory]
   方法2：手工锁表COPY
   mysql>flush tables for read;
   然后CP数据文件到备份目录下
   （2）InnoDB存储引擎
   iBBACKUP工具可以热备份InnoDB存储引擎类数据哭，但它是收费的，此处不研究。
   此处推荐开源工具Xtrabackup。主要具备以下特点：免费开源、支持在线备份、备份速度快、占用磁盘空间小等，并且支持不同情况下的备份形式。Xtrabackup只能备份InnoDB和XtraDB两种数据表，而不能备份MyISAM数据表。而Innobackupex是一个封装了Xtrabackup的Perl脚本，所有同时备份处理InnoDB和XtraDB，但在处理MyISAM时需要加一个读锁。

12.1.1 各种备份与恢复的具体实现

1. 利用SELECT INTO OUTFILE实现数据的备份与还原
2. 利用mysqldump工具对数据进行备份和还原
3. 备份的策略：完全备份+增量备份+二进制日志
4. 策略：完全备份+二进制日志
5. 可以自动备份校验数据的可用性

12.2 通过phpMyAdmin实现备份和恢复

phpMyAdmin提供的数据库备份恢复是通过导入导出功能来实现的，支持导出成CSV、Excel、SQL等多种格式，SQL兼容性允许导出其他数据库语法的SQL语句，支持的数据库包括Oracle、DB2、SQL Server等，为数据在异构数据库之间的迁移提供了便利。

12.3 高手点拨

逻辑备份和物理备份在高效性、实时性和支持度方面的区别：

1. 高效性
   逻辑备份是基于文件级别的备份，由于每个文件都是由不同的逻辑块组成。每一个逻辑的文件块存储在连续的物理磁盘块上，但组成一个文件的不同逻辑块极有可能存储在分散的磁盘块上。逻辑备份在对非连续存储磁盘上的文件进行备份时需要额外的查找操作。这些额外的操作增加了磁盘的开销，降低了磁盘的吞吐率。所以，与物理备份相比，备份性能较差。逻辑备份模式下，文件即使一个很小的改变，也需将整个文件备份。这样如果一个文件很大的情况下，就会大幅度降低备份效率，增加磁盘开销和备份时间。
   物理备份是位与文件系统之下和硬件磁盘驱动之上。增加了一个软驱动，它忽略了文件和结构，处理过程简洁，因此在执行过程中所花费在搜索操作上的开销较少，备份的性能很高。物理备份避免了当文件出现一个小的改动的时候，就需要对整个文件做备份，只是会去做改动部分的备份，有效地提高了备份效率，节省了备份时间。
2. 实时性
   逻辑备份很难做到实时备份的，因为它的每次修改都是基于文件的，而文件的哪部分被修改，系统很难实时捕获到，所有备份的时候需要把整个文件读一遍再发到备机，实时效率不高。
   物理备份可以做到高效的实时备份，因为在每次主机往磁盘写数据的时候，都需要同时将数据写入到备机，这种写入操作都是基于磁盘扇区的，所以，很快就能被识别。只有在备机完成之后，才会返回给上层的应用系统来继续下一步工作。

3. 支持度
   逻辑备份是以单个文件为单位对数据进行复制，所以它受文件系统限制，仅能对部分支持的文件系统座备份，不支持RAW分区。
   物理备份是在文件系统之下对数据进行复制，所以它不受文件系统限制，可以支持各种文件系统包括RAW分区。
   制定一个好的备份策略：

   1. 备份的目的
      （1）做灾难恢复：对损坏的数据进行恢复和还原。
      （2）需求改变：因需求改变而需要把数据还原到改变以前。
      （3）测试：测试新功能是否可用。
   2. 备份需要考虑的问题
      （1）可以容忍丢失多长时间的数据。
      （2）恢复数据要在多长时间内完成。
      （3）恢复的时候是否需要持续提供服务。
      （4）恢复的对象，是整个库，多个表，还是单个库，单个表。
   3. 备份的类型
      （1）根据是否需要数据库离线。
      冷备：需要关MySQL服务，读写请求均不允许状态下进行。
      温备：服务在线，但仅支持读请求，不允许写请求。
      热备：备份的同时，业务不受影响。
      注：这种类型的备份，取决于业务的需求，而不是备份的工具。
      MyISAM不支持热备，InnoDB支持热备，但是需要专门的工具。
      （2）根据要备份的数据集合的范围。
      完全备份：备份全部字符集。
      增量备份：上次完全备份或增量备份以来改变了的数据，不能单独使用，要借助完全备份，备份的频率取决于数据的更新频率。
      差异备份：上次完全备份以来改变了的数据。
      建议的恢复策略：
      完全+增量+二进制日志
      完全+差异+二进制日志
      （3）根据备份数据或文件
      物理备份：备份和恢复都比较简单，能够跨MySQL的版本，恢复速度快，属于文件系统级别的。建议：不要假设备份一定可用，要测试：mysql——check tables
      检测表是否可用逻辑备份：备份表中的数据和代码：恢复简单，备份的结果为ASCII文件，可以编辑，与存储引擎有关，可以通过网络备份和恢复。缺点：备份或恢复都需要MySQL服务器进程参与，备份结果占据更多的空间，浮点数可能会丢失，精度还原之后缩影需要重建。
   4. 备份的对象
      （1）数据
      （2）配置文件
      （3）代码：存储过程、函数、触发器
      （4）OS相关的配置文件
      （5）复制相关的配置
      （6）二进制日志。