MySQL 之数据备份与恢复
使用 innobackupex 工具演示全量备份
使用 innobackupex 工具制作数据备份并执行恢复,本实验共两台主机,node1为故障主机将被node2替换,将node1上的数据做全量备份,恢复至node2
在node1上制作全量备份并将备份的数据发送至node2
#在node1准备备份数据存储目录
mkdir -pv /data/backup
#在node1全量备份
innobackupex --user=root /data/backup
#在node2主机上测试恢复功能
mkdir -pv /data/backup
#在node1将生成的备份目录发送至恢复主机
scp -rp /data/backup/2017-07-14_22-42-53 root@172.16.50.16:/data/backup/
#在node2的备份数据目录下执行
cd /data/backup/2017-07-14_22-42-53
innobackupex --copy-back ./
#查看恢复的数据
cd /var/lib/mysql/;ls
修改node2上恢复的数据的属主和属组
注意:数据恢复后,所有文件和目录的属主和属组都是制作备份的用户的属主和属组,需要修改为mysql
chown -R mysql.mysql /var/lib/mysql/*
在node2上启动服务
==注意:数据恢复后的第一时间需要做的就是——对恢复后经过检验无误的数据立刻进行全量备份==
- 在node2上制作全量备份
innobackupex -uroot /data/backup
全量 + 增量备份 及 数据恢复(+时间点恢复)
增量备份是基于上一次备份(全量或增量)制作的数据备份,MyISAM存储引擎不支持增量备份仅支持全量备份;InnoDB支持全量和增量备份
在上述实验的基础上,假设数据已经发生变化
#依赖已经做好的全量备份,制作增量备份
innobackupex -u root --incremental /data/backup --incremental-basedir /data/backup/2017-07-14_22-42-53
再次修改数据,制作第二次增量备份
#基于第一次增量备份,制作第二次增量备份
innobackupex -uroot --incremental /data/backup --incremental-basedir /data/backup/2017-07-14_23-48-14/
数据再次发生变化,但未来得及制作增量备份,此时发生故障,数据库服务崩溃
- 查询最近一次增量备份的二进制日志中的最后执行语句的位置数字
less 2017-07-15_00-00-20/xtrabackup_binlog_info
#结果如下,POS为1268
master-log.000005 1268
- 查看数据崩溃服务的主机的二进制日志
cd /var/lib/mysql
#截取1268之后的所有未备份的语句保存至某个 .sql 文件中,用于做时间点恢复
mysqlbinlog master-log.000005 -j 1268 > /data/backup/binlog.sql
- 将所有备份的数据(包括全量和增量还有截取的二进制日志文件)发送至用于恢复的主机
scp -rp /data/backup/* root@172.16.50.16:/data/backup/
恢复数据 (利用全量备份 + 第一次增量备份 + 第二次增量备份 + 二进制文件时间点恢复)
实验所需备份文件及目录名字整理
全量备份的目录名:2017-07-14_22-42-53
数据发生变化后的第一次增量备份目录名:2017-07-14_23-48-14
数据发生变化后的第二次增量备份目录名:2017-07-15_00-00-20
从最后一次备份的语句开始截取的二进制文件名:binlog.sql
数据恢复整体顺序:将全量备份与第一次增量备份合并后得到新的备份 ==> 将得到的新的备份与第二次增量备份合并得到新的备份 ==> 利用二进制文件时间点恢复
对制作备份过程中可能导致的未完成的中间事务,仅在所有备份(全量和所有增量)合并完成后才执行回滚操作(undo),在各个备份文件合并过程中仅执行提交操作(redo)。
- 对全量备份本身执行PREPARE准备工作,对于未完成的中间事务此时仅提交、不回滚
innobackupex --apply-log --redo-only /data/backup/2017-07-14_22-42-53
- 将准备好的全量备份与第一次增量备份合并,,对于未完成的中间事务此时仅提交、不回滚
innobackupex --apply-log --redo-only /data/backup/2017-07-14_22-42-53 --incremental-dir=/data/backup/2017-07-14_23-48-14/
- 将得到的新的备份与第二次增量备份合并,对于未完成的中间事务此时仅提交、不回滚
innobackupex --apply-log --redo-only /data/backup/2017-07-14_22-42-53 --incremental-dir=/data/backup/2017-07-15_00-00-20/
- 所有备份文件合并完成后,对中间事务执行提交、并执行回滚,得到一份完整的备份数据
innobackupex --apply-log /data/backup/2017-07-14_22-42-53/
- 将完整的备份数据和截短的二进制文件发送至用于恢复数据的主机
scp -rp 2017-07-14_22-42-53/ binlog.sql root@172.16.50.16:/data/backup
- 恢复备份的数据并修改恢复的数据的属主和属组后启动服务
innobackupex -uroot --copy-back /data/backup/2017-07-14_22-42-53/
chown -R mysql.mysql /var/lib/mysql/*
systemctl start mariadb
- 基于截取的二进制文件进行时间点恢复,将未来得及保存在备份中的修改恢复
mysql < /data/backup/binlog.sql
至此,数据完全恢复至崩溃一刻的状态
- 对完全恢复的数据应该第一时间进行一次全量备份并妥善保存
注意:尽量不要将备份数据和源数据存储在同一主机或同一设备上,可以加密后放至云存储。