MySQL备份与恢复

文章目录

当前数据库结构

db01 (主)

db02 (从)

备份策略

  • 每天一次全量备份
  • 每小时一次增量备份
  • 通过热备工具(不锁表,不影响线上应用)备份主库数据
  • 备份数据保存到db01,备份完成scp到db02
  • 保留一个月的增量及全量备份
时间 备份类型
00:01 全量备份
01:01 增量备份(当天首次)
02:01~23:01 增量备份

随着数据量的增加,全量备份可以做成每周一次,每2~8小时一次增量备份

备份目录

db{01,02}:/data/backup/{full,incremental}/

backup/
├── full
│   ├── 2015-08-16
│   └── 2015-08-16.log # 全量备份日志
└── incremental
├── 2015-08-16_14 # 每小时增量备份
├── 2015-08-16_14.log # 每小时增量备份日志
├── 2015-08-16_15
├── 2015-08-16_15.log
├── 2015-08-16_16
├── 2015-08-16_16.log
├── 2015-08-16_17
├── 2015-08-16_17.log
├── 2015-08-16_18
├── 2015-08-16_18.log
├── 2015-08-16_19
└── 2015-08-16_19.log

备份计划任务

# Info   : 数据库备份
# Author : zhouyq
# CTime : 2015-08-16
# 全量备份
1 0 * * * /bin/bash /root/bin/bakdb.sh full
# 第一次增量备份
1 1 * * * /bin/bash /root/bin/bakdb.sh incremental first
# 其他时间段增量备份
1 2-23 * * * /bin/bash /root/bin/bakdb.sh incremental

备份脚本

/root/bin/bakdb.sh



fullPath="/data/backup/full"
incrPath="/data/backup/incremental"
bakdate=`date +'%F'`
bakhour=`date +'%H'`

oneHourAgo=`date -d '1 hours ago' +'%F_%H'`

BakBin="/usr/bin/innobackupex --no-timestamp --user=root --socket /data/db/tmp/mysql.sock --defaults-file=/usr/local/mysql/my.cnf --sleep 100"

# backup function
function (){

baktype=$1
logfile=$2
incrpath=$3
bakpath=$4

if [ "$baktype" == "full" ];then
$BakBin $bakpath > $logfile 2>&1
elif [ "$baktype" == "incremental" ];then
$BakBin --incremental $incrpath --incremental-basedir $bakpath > $logfile 2>&1
fi
}

# ============= Main =============

if [ "$1" == "full" ];then
# 全量备份
hotbackup "full" "${fullPath}/${bakdate}.log" "none" "$fullPath/$bakdate"
/usr/bin/scp -P 9922 -rp ${fullPath}/${bakdate}* db02:${fullPath}

elif [ "$1" == "incremental" ];then
# 判断是否为第一次增量备份,只有第一次增量备份目录指向全量备份
# 第二次开始增量备份的上一次目录指向第一次增量目录即可
if [ "$2" == "first" ];then
hotbackup "incremental" "${incrPath}/${bakdate}_${bakhour}.log" "$incrPath/${bakdate}_${bakhour}" "$fullPath/$bakdate"
/usr/bin/scp -P 9922 -rp ${incrPath}/${bakdate}_${bakhour}* db02:${incrPath}
else
hotbackup "incremental" "${incrPath}/${bakdate}_${bakhour}.log" "$incrPath/${bakdate}_${bakhour}" "$incrPath/${oneHourAgo}"
/usr/bin/scp -P 9922 -rp ${incrPath}/${bakdate}_${bakhour}* db02:${incrPath}
fi
fi

恢复

全量备份恢复

innobackupex --user=root --defaults-file=/usr/local/mysql/my.cnf --apply-log /data/backup/full/2015-08-16

innobackupex --user=root --defaults-file=/usr/local/mysql/my.cnf --move-back /data/backup/full/2015-08-16

增量备份恢复

innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root --apply-log --redo-only /data/backup/full/2015-08-16 

innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root --apply-log --redo-only /data/backup/full/2015-08-16 --incremental-dir=/data/backup/incremental/2015-08-16_14

innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=root --apply-log --redo-only /data/backup/full/2015-08-16 --incremental-dir=/data/backup/incremental/2015-08-16_15