MYSQL数据库的备份与恢复

备份与恢复在任何数据库里面都是非常重要的内容，特别是随着数据量的增加，备份问题会越来越突出，因此，好的备份方法与备份策略是非常重要的。结合我所做的项目本身，下面主要就windows平台下MySQL(5.0)数据库的常用备份与恢复做一些总结。

 

 

1       相关准备工作

      MySQL数据库的备份与恢复与数据库日志有着紧密联系，特别是数据库的二进制日志（BINLOG），更是对于灾难恢复、增量备份起着重极其重要的作用。因此，开启和熟悉相关日志是十分必要的。

1、  二进制日志（BINLOG）。二进制日志记录了所有的DDL（数据定义语言）语句和DML（数据操作语言）语句，但是不包括数据查询语句。也就是说所有的数据库更改都被记录  在了二进制日志中（因此该文件可能会非常庞大）。设置方法为：在my.ini文件中[mysqld]选项后面添加如下语句：

log-bin=filename

例如：log-bin="D:/LOGBIN/data_logbin.log"

filename为存放二进制文件的路径，强烈建议与MySQL的数据目录分开存放。

 

 2、 错误日志。错误日志是MySQL中最重要的日志之一，它记录了MySQL启动和停止时，以及服务器在运行过程中发生的任何严重错误的相关信息。当数据库出现任何故障导致无法正常使用时，可以首先查看此日志。该日志黙认是开启的，但也可以指定，方法为在my.ini文件中[mysqld]选项后面添加如下语句：

     log-error=filename

     例如：log-error="D:/ERRORBIN/data_error.log"

     filename为存放错误文件的路径，建议也与MySQL的数据目录分开存放。

   

 

 

2       备份/恢复策略

MySQL数据库中的备份可以分为逻辑备份与物理备份，逻辑备份是将数据库中的数据备份为一个文本文件（即我们通常见到的SQL脚本文件），而物理备份则是基于对数据文件的处理（比如对于InnoDB的数据库表，其数据文件即为.ibd文件）。

2.1    逻辑备份与恢复

2.1.1   手工备份

逻辑备份使用mysqldump工具来完成备份。其使用方法为：

mysqldump [主机] [用户名] [密码] [数据库]>[备份路径]

例如：

mysqldump -h192.168.1.7 -uroot –phello data_test>d:/backup.sql

或者写成：

mysqldump --single-transaction -h192.168.1.7 -uroot phello data_test>d:/backup.sql

或者写成：

mysqldump --single-transaction -h192.168.1.7 -uroot -phello –F data_test>d:/backup.sql

第二种写法对于InnoDB存储引擎的表来说，是一个很好的办法，它可以使mysqldump得到数据库的一个快照，使得备份的数据能够保证一致。否则，备份时，用户就只能对表有读的权限了。第三种写法更好，它可以令数据库生成一个新的二进制日志文件，而该文件只记录了自备份开始以来所有的数据库操作，这样更方便。

2.1.2   定时备份

首先写一个批处理脚本，例如该脚本内容为：

mysqldump --single-transaction -h192.168.1.7 -uroot -phello data_test>d:/backup.sql

       然后将该脚本保存为bat格式文件，再在windows的“任务计划”里面设定一个定时执行该脚本的任务，比如说每周一的晚12:00执行该脚本（这个时间点最好设在用户量可能最少的时间），这样就能定时备份相应的数据库了。当然这种备份只是一种简单的全备份，建议不要大量使用，可以适时的使用。

 

2.1.3   增量备份

上述备份方法都属于全备份，这种备份优点是备份保持最新备份，恢复的时候可以花费更少的时间，但缺点是如果数据量大，将会花费很多的时间，并对系统造 成较长时间的压力。增量备份则恰好相反，只需备份每天的增量日志，备份时间少，对负载的压力也少，缺点是恢复的时候需要全备份加上上次的全备份，恢复时间长些。俱体方法如下：

首先将二进制日志文件复制到备份目录，这个因为是对文件进行CP操作，所以非常快。

然后，利用mydqlbinlog工具将该日志文件中对应当天的SQL语句抽出来形成SQL脚本文件，比如说要得到2009年6月22日的数据：

mysqlbinlog --start-date=”2009-06-22 00:00:00”--stop-date=”2009-06-22 24:00:00” D:/LOGBIN/data_logbin.0000002>d:/logback20090622.sql

          最后将备份目录下的二进制日志文件删除即可（想保留也无所谓，只不过占空间）。

2.1.4   恢复

用mysqldump的完全恢复很简单，如下所示例子：

1)        上午9点开始备份数据库(可以是手工，也可以是定时备份)：

mysqldump --single-transaction -h192.168.1.7 -uroot -phello data_test>d:/backup.sql

2)        9点半备份完毕

3)        到了10点，数据库故障，数据无法访问，需要恢复备份

4)        使用mysqlbinlog得到9点到10点间的日志数据，如下所示：

mysqlbinlog –start-date=”2009-06-22 10:00:00”—stop-date=”2009-06-22 10:30:00” D:/LOGBIN/data_logbin.0000002>d:/logback.sql

5)        还原之前备份的数据：

mysql –uroot –psinojp database<d:/backup.sql

6)        还原从日志文件中得到的相关时间段的数据：

mysql –uroot –psinojp database<d:/logback.sql

7)        完成恢复

以上恢复时用到了二进制文件LOGBIN，且是基于时间点恢复LOGBIN里的数据，其实，更精确一点，我们还可以根据位置来恢复二进制文件里的数据。因为由于误操作，比如误删除了一张表，这时使用基于时间点的恢复是有误差的，因为日志里面还存在误操作的语句，我们需要的是恢复到误操作以前的状态，然后跳过误操作语句，再恢复后面执行的语句，完成我们的恢复。这样的要求下，我们就要用到基于位置的恢复了。如下所示：

mysqlbinlog –stop-position=”1000” d:/logbin.0000005>d:/time1.sql

mysqlbinlog –start-position=”1050” d:/logbin.0000005>d:/time2.sql

这样一来，位置1000到1050之间的语句咱就不执行了（这个要求自己去查看日志文件，确认误操作语句的序号）。

 

2.2    物理备份与恢复

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

2.2.1   冷备份

冷备份其实就是停掉数据库服务，CP数据文件的方法。这种方法一般很少用，因为很多应用是不允许长时间停机的。方法如下：

备份：首先停掉MySQL服务，在操作系统级别备份数据文件和日志文件到备份目录。

恢复：首先停掉MySQL服务，在操作系统级别恢复数据文件（直接将备份的数据文件复制到数据目录即可），然后重启服务，再使用mysqlbinlog工具恢复自备份以来的所有BINLOG。

2.2.2   热备份

热备份一般由专门的备份工具来完成，优点是速度快，生成的备份文件小，对用户的影响也小，缺点是目前还没有免费的热备份工具。现在较流行的是ibbackup这款热备份工具，可以免费试用一个月，效果不错。

2.2.3   其它方式

实际上，除了上述备份方法外，如果数据库系统进行了主从复制设置，则也可以使用一个从服务器执行备份，这样就不会干扰主服务器，使得在备份过程中主服务器可以继续处理更新。但这种备份方式存在一定的数据差异，所以不提倡大力使用。

同时，可以将全备份与增量备份结合起来使用。但要注意的是，要经常做备份恢复测试，确保备份是有效的，并且是可以恢复的。

 

 

    

3       备份数据的物理存放

由于备份的数据库文件通常会非常大（T级），并且从安全角度来考虑，都应该将备份数据与原数据进行隔离。可以采用专门的备份服务器，定时将数据备份到该备份服务器上来，并且定期将之前备份数据刻录成光盘，以防万一。