Mysql增量备份与恢复

mysql增量备份和恢复的原理与实现方法

 

文档目的：我写这篇文档的目的是为了提高MYSQL数据的备份与恢复的效率，文档内提及的技术基本都是围绕全程不锁表的情况下对INNODB表库进行整库备份、增量备份（无法适用于MYISAM表库）与恢复方法，从而可以达到在不影响业务的情况下进行备份维护。

 

1． 实现原理

每天使用mysqldump做一次全库备份，每小时增量备份BIN文件。

策略：每天24小时

||---------------------||---------------------||---------------------||---------------------||---------------------||

00:00:00            01:00:00        02:00:00        03:00:00       04:00:00    依次类推

全库备份          每小时的binlog增量备份  ……

 

2． 实现方法

每天凌晨00:00:00使用mysqldump做一次全库备份，方法如下：

mysqldump –h -u root –p --single-transaction --skip-triggers --flush-logs --delete-master-logs --master-data=1 --database   > /root/temp/mysqlbak20100112140000.sql

 

参数说明

--single-transaction 该选项从服务器转储数据之前发出一个BEGIN SQL语句。它只适用于事务表，例如InnoDB和BDB，因为然后它将在发出BEGIN而没有阻塞任何应用程序时转储一致的数据库状态。注意：此参数是与“--lock-tables”参数抵触的，二者只能选一。

--skip-triggers 不在本次备份触发器，触发器采用单独备份方式操作。

 

--flush-logs 转储日志，从而产生一个新的BIN日志。

 

--delete-master-logs 删除之前所有BIN日志，保留当前正在用的BIN日志，不推荐使用该

参数。

 

--master-data=1 该选项将二进制日志的位置和文件名写入到输出中。该选项要求有RELOAD权限，并且必须启用二进制日志。如果该选项值等于1，位置和文件名被写入CHANGE MASTER语句形式的转储输出，如果你使用该SQL转储主服务器以设置从服务器，从服务器从主服务器二进制日志的正确位置开始。如果选项值等于2，CHANGE MASTER语句被写成SQL注释。如果value被省略，这是默认动作。此处我们使用值1。

 

 

 

技术特点：

（1）       全程不锁表操作，但可保证数据一致性。

（2）       重新转储一个新的bin-log日志，并清除备份点之前的所有binlog日志文件，保证主服务器的bin日志不会占用太多磁盘空间。

 

 

（3）       如果有主从技术，位置和文件名被写入CHANGE MASTER语句形式的转储输出，数据先从mysqldump全库备份文件中恢复后，从机可以自动读取全库备份点后的所有BINLOG日志，如果没有该参数，需要手动执行change master。

 

注意：

（1）       该全库备份方式，只能在一条MYSQLDUMP语句中实现一个备份点上的所有库数据备份，而且无法同时实现结构与数据分离备份；另外，备份点之前的所有BIN日志文件会被自动删除。

（2）       需要写脚本来处理每次增量BINLOG文件的备份与保存，一般建议使用RSYNC方式远程同步备份。为保证数据一致性，备份前一定要使用flush logs命令来转储新的BIN日志。

（3）       跳过备份触发器（单独备份）

 

3． 模拟场景

 

A机与B机是主从关系，A主B从。A负责写B负责读，同时参与业务。

 

事件1

B机由于数据文件损坏导致数据无法被读取，需要以最快速度恢复数据。

 

恢复方法：

（1）       先执行“ stop slave;”，再进入数据库后DROP掉库。

（2）       删除数据目录下的所有relay-bin文件，relay-bin.index文件，master.info文件，relay-log.info文件

（3）       将每天凌晨00:00:00的mysqldump全库备份复制到B机，从中恢复数据，再执行“reset slave;start slave;”，MYSQL会自动同步整库备份点后的所有数据。

 

分析：

常规的恢复方法是从主服务器上完全再同步一次数据，需要花费的时间比较长，不仅会大量增加主服务器的IO消耗，还需要主机上有第一次启动以来的所有BINLOG日志。如果只有A和B机做主从，这么做不仅会给主机带来很大压力，而且恢复速度慢。但使用事件1的方法恢复可以很快恢复数据，也不会给主机造成太大压力。

 

事件2

A机上的数据因误操作而被误删除整库，从机此时也会被同时执行相同操作。数据只能通过某个备份点来恢复数据。

 

恢复方法：

（1） 使用每天凌晨00:00:00的mysqldump全库备份恢复数据。

进入mysql数据库后执行 “source /root/temp/mysqlbak20100112140000.sql”

 

 

 

 

 

（2） 再使用mysqlbinlog 工具从整库备份点后到误操作时间点之间的binlog日志文件中进行恢复。

mysqlbinlog –no-defaults /root/temp/BIN日志文件 |mysql –uroot –p

注意：该操作如果不是ROOT权限，一定要有RELOAD权限。

 

 

（4）       手动change master

查找到最后一个bin日志里的最后一次pos位置，比如是mysqlha-bin.000019日志最后一个POS位置是372。

Mysqlbinlog –no-defaults /root/temp/mysqlha-bin.000019

 

（5）   进入MYSQL后执行

Change master to master_host=’’,master_user=’’,master_password=’’,master_logfile=’mysqlha-bin.000019’,master_log_pos=372;

 

（5）       然后用事件1的方法恢复从机B的数据。

 

分析：

损失可以将减至最小，损失程度取决于binlog日志文件的备份间隔周期。如果半小时备份一次binlog日志，最大损失在30分钟左右，一般小于30分钟。