mariabackup做增量备份

1.编辑全量备份脚本/root/sh/mariabackup-full.sh，内容如下:

#!/bin/bash

user=root

userPWD='dreamtechIT2019'

mysqlPort=3306

host=10.47.92.186

mysqlHome=$mysqlHome

DATE=`date +%Y%m%d`

MailGroup="lihaichun@zhixueyun.com"

bakDir=$mysqlHome/backup_data/$DATE

rm -rf $bakDir

mkdir -p $bakDir

$mysqlHome/bin/mariabackup --defaults-file=$mysqlHome/my.cnf --backup --user=$user --password=$userPWD --port=$mysqlPort --host=$host  --target-dir=$bakDir

if [ $? -eq 0 ];then

    echo "ic2020-zhixueyun-com " "mariadb $DATE  full backup ok" >> /tmp/checksqlback.log

    /usr/local/bin/sendEmail -f admin@zhixueyun.com -t $MailGroup -s smtp.exmail.qq.com -u "ic2020.zhixueyun.com mysql fullbackup success" -m "ic2020.zhixueyun.com-mysql-完全备份成功" -xu admin@zhixueyun.com -xp ZHIxyun2017

else

    echo "ic2020-zhixueyun-com " "mariadb $DATE  full backup failed" >> /tmp/checksqlback.log

    /usr/local/bin/sendEmail -f admin@zhixueyun.com -t $MailGroup -s smtp.exmail.qq.com -u "ic2020.zhixueyun.com mysql fullbackup failed" -m "ic2020.zhixueyun.com-mysql-完全备份失败" -xu admin@zhixueyun.com -xp ZHIxyun2017

fi

find $mysqlHome/backup_data/ -name "*" -type d -mtime +15 -exec rm {} \;

 

2.编辑增量备份脚本/root/sh/mariabackup-daily.sh，内容如下：

#!/bin/bash

user=root

userPWD='dreamtechIT2019'

mysqlPort=3306

host=10.47.92.186

mysqlHome=/app/mariadb

DATE=`date +%Y%m%d`

yestoday=`date +%Y%m%d -d "-1day"`

MailGroup="lihaichun@zhixueyun.com"

bakDir=$mysqlHome/backup_data/$DATE

yestodaybakDir=$mysqlHome/backup_data/$yestoday

rm -rf $bakDir

mkdir -p $bakDir

if [ ! -d "$yestodaybakDir" ];then

    $mysqlHome/bin/mariabackup --defaults-file=$mysqlHome/my.cnf --backup --user=$user --password=$userPWD --port=$mysqlPort --host=$host  --target-dir=$bakDir

else

    $mysqlHome/bin/mariabackup --defaults-file=$mysqlHome/my.cnf --backup --user=$user --password=$userPWD  --port=$mysqlPort --host=$host --target-dir=$bakDir --incremental-basedir=$yestodaybakDir

fi

if [ $? -eq 0 ];then

    echo "ic2020-zhixueyun-com " "mariadb $DATE  incremental backup ok" >> /tmp/checksqlback.log

    /usr/local/bin/sendEmail -f admin@zhixueyun.com -t $MailGroup -s smtp.exmail.qq.com -u "ic2020.zhixueyun.com mysql incremental backup  success" -m "ic2020.zhixueyun.com-mysql-增量备份成功" -xu admin@zhixueyun.com -xp ZHIxyun2017

else

    echo "ic2020-zhixueyun-com " "mariadb $DATE  incremental backup failed" >> /tmp/checksqlback.log

    /usr/local/bin/sendEmail -f admin@zhixueyun.com -t $MailGroup -s smtp.exmail.qq.com -u "ic2020.zhixueyun.com mysql incremental backup  failed" -m "ic2020.zhixueyun.com-mysql-增量备份失败" -xu admin@zhixueyun.com -xp ZHIxyun2017

fi

find $mysqlHome/backup_data/ -name "*" -type d -mtime +15 -exec rm {} \;

 

3.设置定时任务crontab -l，内容如下，周一到周六增量备份，周日全量备份

0 1 * * 0       /root/sh/mariabackup-full.sh > /dev/null

0 1 * * 1-6   /root/sh/mariabackup-daily.sh > /dev/null

 

4. 增量备份数据恢复步骤

#准备全量备份的数据
/app/mariadb/bin/mariabackup --defaults-file=/app/mariadb/my.cnf --prepare --apply-log-only --target-dir=/app/mariadb/backup_data/最近一个周日的日期

#准备增量的数据
/app/mariadb/bin/mariabackup --defaults-file=/app/mariadb/my.cnf --prepare --apply-log-only --target-dir=/app/mariadb/backup_data/full --incremental-dir=/app/mariadb/backup_data/最近一个周一到周六的日期

#停止数据库，并删除旧的数据文件，注意备份/app/mariadb/data/目录下的binlog
/app/mariadb/support-files/mysql.server stop
rm -rf /app/mariadb/data/*

#开始恢复合并后的全部数据的数据库
/app/mariadb/bin/mariabackup --defaults-file=/app/mariadb/my.cnf --copy-back --datadir=/app/mariadb/data --target-dir=/app/mariadb/backup_data/最近一个周日的日期

#启动恢复后的数据库
chown -R mysql:mysql /app/mariadb/data
/app/mariadb/support-files/mysql.server start