数据库备份与恢复
当前数据库结构
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
|
备份计划任务
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
#!/bin/bash
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"
function hotbackup(){
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
}
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
|
