MySQL数据库增量备份及恢复方案

 

u  前言

       操作系统崩溃、电源故障、文件系统崩溃和硬件故障等异常状况都可能导致我们正在使用的数据库出现故障而产生数据库中数据不一致的情况。为了保证数据库使用安全，必须定期备份数据库；数据库备份可以分为：完全备份、日志备份、增量备份和文件备份。对于一个大型数据库，频繁执行完全备份可能会需要太多的时间，而且完全备份经常会多次备份一些没有更新过的数据，会造成资源浪费。现在最常用的数据库备份策略是在完全备份的基础上进行较频繁的增量备份。例如，我们可以在数据库使用较少的时段每周进行一次完全备份，然后每天进行一次增量备份，备份下这段时间中可能修改数据库内容的操作，以便在发生文件系统故障、硬件问题等问题导致数据库发生灾难性崩溃的时候利用备份数据进行数据库的恢复。

 

u  技术分析

MySQL数据库对上述几种导致数据库崩溃的故障都给出了很好的解决办法以保证数据的一致性。

对于操作系统崩溃和电源故障导致的MySQL数据库崩溃，使用MySQL提供的内置方法，在大多数情况下都可以非常有效的恢复：如果我们使用的是MyISAM数据库，可以使用REPAIR TABLE 或者myisamchk –r 对可能损毁的数据库表进行修补；如果我们使用的是InnoDB，则InnoDB会自动找到挂起的提交了的或未提交的事务列表，并自动回滚未提交的事务和刷新已提交的事务。

如果我们使用前面的方面未能正确恢复被损害的数据，或者对于文件系统崩溃和硬件故障导致的数据库严重毁坏的情况，我们就需要从其他安全备份恢复。这种恢复方法基于前面提到的完全备份和增量备份。

在MySQL中，我们可以使用mysqldump方法将数据库导出为一个.sql文件，这个方法可以将我们正在使用的整个数据库或者多个数据库完全拷备到一个文件中从而实现对数据库的完全备份。我们再把这个文件保存到其他安全的地方，就可以在需要的时候使用该完全备份进行数据库恢复。

虽然MySQL没有提供直接的增量备份方法，但我们可以通过MySQL提供的二进制日志（binary logs）间接实现增量备份。二进制日志保存了所有更新或者可能更新数据库的操作（例如，当一个没有匹配行的delete操作也会被记录在二进制日志中），二进制日志在启动MySQL服务器后开始记录，并在文件达到max_binlog_size或者接收到flush logs命令后重新创建新的日志文件进行记录。基于二进制日志以上特性，我们使用二进制日志进行增量备份是非常方便和有效的。我们只需定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些日志保存到安全的地方就完成了一个时间段的增量备份。

在定期完全备份和及时增量备份的策略中进行数据库备份需要两步：首先通过执行完全备份的.sql文件恢复数据库，然后用mysqlbinlog恢复上次完全备份至数据库崩溃时的二进制日志文件序列恢复此时间段内对数据库的更新。

一般来说，我们采用的完全+增量备份的频率为每周进行一次完全备份，每日进行增量备份。根据实际应用的不同，可以适当调整。

 

u  实现步骤

通过上一节的分析，我们可以总结出MySQL的完全+增量备份策略主要包括以下几个步骤：

Ø  首先，我们需要开启MySQL服务器的二进制日志功能，其实现方法有很多种，最常用的是在MySQL的配置文件的mysqld项中加入log-bin=[filepath]项；也可以使用mysqld –log-bin=[filepath]重新启动MySQL服务器。

Ø  其次，使用mysqldump对数据库进行完全备份，它可以实现对数据据的联机，非阻塞的热备份，不会影响其他进程对数据库的读写操作。（参考指令：mysqldump -uroot --password=123 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > d:/mysql5.0/full_%date:~0,10%_1500_PM.sql）

Ø  第三，使用flush logs指令刷新创建新的二进制日志。可以通过mysqladmin flush logs或者flush logs语句实现。最方便有效的方法是将它做成批处理文件，然后让操作系统定期执行。

Ø  使用mysql < *.sql进行完全备份的恢复。

Ø  使用mysqlbinlog logs-bin.[0-9]* | mysql进行增量备份的恢复。一般的，假设我们周日下午三点进行了完全备份并生成备份文件full_backup_20100415_3_PM.sql，周一周二中午一点进行了两次增量备份，分别生成增量备份文件inc_backup_1_PM.0007和inc_backup_1_PM.0008，周三上午10：00数据库发生崩溃，需要进行数据库恢复，此时正在记录的二进制日志为inc_backup_1_PM.0009，我们还需要恢复其中记录的操作。具体的恢复操作为：

mysql < full_backup_20100415_3_PM.sql

mysqlbinlog inc_backup_1_PM.0007 inc_backup_1_PM.0008 inc_backup_1_PM.0009

u  具体应用(结合T8项目)

根据上述分析，我们在T8项目使用MySQL增量备份，也需要三个步骤：

Ø  首选修改MySQL启动配置文件my.cnf，在其中的mysqld项中增加log-bin选项，重新启动MySQL 服务器以开启二进制日志功能，在生成文件/usr/data/mysql/ t8server-bin.index和t8server-bin..000001。如图所示：

 

 

Ø  编写完全备份可执行文件脚本：

 

Ø  编写增量备份可执行文件脚本：

 

Ø  执行full_backup_20100417_1_pm，生成/usr/data/mysql/full_backup_20100417_1_pm. sql文件。

Ø  执行inc_backup_1_pm，生成/usr/data/mysql/t8server-bin..000002。

其中full_backup_20100417_1_pm. sql和t8server-bin..000002分别是完全备份和增量备份文件。

要进行备份文件的恢复，只需执行命令：

 

u  总结

MySQL提供了很方便的完全+增量备份实现方法，我们只需调用系统内置的方法或者作出一些细微的配置就可以对MySQL数据库进行备份和恢复。对于MyISAM数据库和InnoDB数据库，都可以通过mysqldump实现数据库的完全逻辑备份，通过启动二进制日志（binary logs），可以记录一个时间段内对数据库的所有可能更新的操作，从而通过flush logs创建新的日志而实现增量备份。

MySQL也提供了非常方便的恢复方法，由于mysqldump的输出结果为可执行的.sql文件，我们可以直接运行该文件实现对完全备份的恢复。然后通过mysqlbinlog命令恢复崩溃之前的二进制日志序列，就可以将数据库恢复至崩溃前的状态。