MySQL 之数据备份与恢复

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/

数据再次发生变化，但未来得及制作增量备份，此时发生故障，数据库服务崩溃

1. 查询最近一次增量备份的二进制日志中的最后执行语句的位置数字

less 2017-07-15_00-00-20/xtrabackup_binlog_info 
#结果如下，POS为1268
master-log.000005       1268

1. 查看数据崩溃服务的主机的二进制日志

cd /var/lib/mysql
#截取1268之后的所有未备份的语句保存至某个 .sql 文件中，用于做时间点恢复
mysqlbinlog master-log.000005 -j 1268 > /data/backup/binlog.sql

1. 将所有备份的数据（包括全量和增量还有截取的二进制日志文件）发送至用于恢复的主机

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)。

1. 对全量备份本身执行PREPARE准备工作，对于未完成的中间事务此时仅提交、不回滚

innobackupex --apply-log --redo-only /data/backup/2017-07-14_22-42-53

1. 将准备好的全量备份与第一次增量备份合并，，对于未完成的中间事务此时仅提交、不回滚

innobackupex --apply-log --redo-only /data/backup/2017-07-14_22-42-53 --incremental-dir=/data/backup/2017-07-14_23-48-14/

1. 将得到的新的备份与第二次增量备份合并，对于未完成的中间事务此时仅提交、不回滚

innobackupex --apply-log --redo-only /data/backup/2017-07-14_22-42-53 --incremental-dir=/data/backup/2017-07-15_00-00-20/

1. 所有备份文件合并完成后，对中间事务执行提交、并执行回滚，得到一份完整的备份数据

innobackupex --apply-log /data/backup/2017-07-14_22-42-53/

1. 将完整的备份数据和截短的二进制文件发送至用于恢复数据的主机

scp -rp 2017-07-14_22-42-53/ binlog.sql root@172.16.50.16:/data/backup

1. 恢复备份的数据并修改恢复的数据的属主和属组后启动服务

innobackupex -uroot --copy-back /data/backup/2017-07-14_22-42-53/
chown -R mysql.mysql /var/lib/mysql/*
systemctl start mariadb

1. 基于截取的二进制文件进行时间点恢复，将未来得及保存在备份中的修改恢复

mysql < /data/backup/binlog.sql

• 至此，数据完全恢复至崩溃一刻的状态

  1. 对完全恢复的数据应该第一时间进行一次全量备份并妥善保存

注意：尽量不要将备份数据和源数据存储在同一主机或同一设备上，可以加密后放至云存储。