MariaDB Linux使用shell脚本进行MariaDB全量备份/还原，增量备份/还原，差异备份/还原

公司用到mysql和mariadb，Percona Xtrabackup无法备份10.3.0以上的，查了下资料，发现MariaDB自带备份还原方法，写了个脚本，做个记录

参数和Percona Xtrabackup8.0的差不多，稍稍微调整

#!/bin/bash

backupDir=/home/backup/mariadb/all  #全量备份文件夹
incrementalBackup=/home/backup/mariadb/incrementalBackup_mariadb #增量备份文件夹
differenceBackup=/home/backup/mariadb/differenceBackup_mariadb
mysqlDir=/var/lib/mysql #docker容器mariadb数据文件夹
mysqlDir2=/home/mariadb #宿主机mariadb数据文件夹


mysqldk=/home/backup/mariadb/mariadb #宿主机mariadb还原数据文件夹
user=root #数据库帐号
password=rootmlf #数据库密码
port=33309 #数据库端口
host=192.168.1.72 #数据库IP
docker_name=mlfmariadb #docker容器名称
defaultsFile=/etc/mysql/my.cnf #数据库配置文件，用于不在/var/lib/mysql 下的数据库
rmData=/home/backup/mariadb/rmData_mariadb #备份数据库目录，防止还原失败数据被删除

#开始时间
timer_start=`date "+%Y-%m-%d %H:%M:%S"`
if [ $1 = 'mysqldump' ];then
    if [ $2 = 'all' ];then
        if [ ! -d ${backupDir} ];then
            mkdir -p ${backupDir}
        else
            /usr/bin/rm -rf ${rmData}/*
        fi
        /usr/bin/rm -rf ${backupDir}/*    #删除全量,增量目录,差异目录
        /usr/bin/rm -rf ${incrementalBackup}
        /usr/bin/rm -rf ${differenceBackup}
        docker exec ${docker_name} mariabackup --defaults-file=${defaultsFile} --backup --datadir=${mysqlDir} --target-dir=${backupDir} --user=${user} --password=${password} --port=${port} --host=${host} --no-server-version-check
    elif [ $2 = 'add' ];then
        if [ ! -d ${incrementalBackup} ];then #增量备份文件夹不存在，就创建增量备份文件夹，并执行基于全量备份的增量备份
            mkdir -p ${incrementalBackup}
            docker exec ${docker_name} mariabackup --defaults-file=${defaultsFile} --backup --datadir=${mysqlDir} --target-dir=${incrementalBackup}/`date "+%Y%m%d%H%M%S"` --user=${user} --password=${password} --port=${port} --host=${host} --incremental-basedir=${backupDir} --no-server-version-check
        else #增量备份文件夹存在，执行基于最新一次的增量备份的增量备份
            filename=`ls -l ${incrementalBackup}| tail -n 1 | awk '{print $9}'` #取最新文件夹名称
            docker exec ${docker_name} mariabackup --defaults-file=${defaultsFile} --backup --datadir=${mysqlDir} --target-dir=${incrementalBackup}/`date "+%Y%m%d%H%M%S"` --user=${user} --password=${password} --port=${port} --host=${host} --incremental-basedir=${incrementalBackup}/${filename} --no-server-version-check
        fi
    elif [ $2 = 'nolike' ];then
        if [ ! -d ${differenceBackup} ];then #增量备份文件夹不存在，就创建增量备份文件夹，并执行基于全量备份的增量备份
            mkdir -p ${differenceBackup}
        fi
        docker exec ${docker_name} mariabackup --defaults-file=${defaultsFile} --backup --datadir=${mysqlDir} --target-dir=${differenceBackup}/`date "+%Y%m%d%H%M%S"` --user=${user} --password=${password} --port=${port} --host=${host} --incremental-basedir=${backupDir} --no-server-version-check    
    else
        echo  -e "参数错误\n参数2：all为全量备份，add为增量备份"
    fi

elif [ $1 = 'mysql' ];then
    if [ $2 = 'add' -o  $2 = 'nolike' -o  $2 = 'all' ];then
        if [ ! -d ${rmData} ];then
            mkdir -p ${rmData}  #创建临时存储目录
        fi
        if [ -d ${backupDir} ];then
            echo "正在备份mysql目录到："${rmData}
            # docker stop ${docker_name}
            docker cp  ${docker_name}:${mysqlDir}/ ${rmData}
            # rm -rf ${mysqlDir}/* #删除mysql目录
            # docker start ${docker_name}
            #docker stop ${docker_name} #关闭mysql
        else
            echo "全量备份文件夹不存在，退出,请检查"${backupDir}
            exit 0
        fi
    fi
    if [ $2 = 'add' ];then
        if [ ! -d ${incrementalBackup} -a -d ${backupDir} -a `ls ${backupDir} |wc -l` -ge 1 ];then
            echo "增量备份文件夹不存在，只执行全量还原"
            docker exec ${docker_name} mariabackup --prepare --target-dir=${backupDir} #预备数据
        elif [ -d ${incrementalBackup} -a `ls ${incrementalBackup} |wc -l` -ge 1 ];then
            echo "增量备份文件夹存在，并有数据，执行全量+增量还原"
            docker exec ${docker_name} mariabackup --prepare --apply-log-only --target-dir=${backupDir}
            filename=`ls -l ${incrementalBackup}| tail -n 1 | awk '{print $9}'` #取最新文件夹名称
            for i in `ls ${incrementalBackup}`;
            do
                if [ ${i} != ${filename} ];then
                    docker exec ${docker_name} mariabackup --prepare  --target-dir=${backupDir} --incremental-dir=${incrementalBackup}/${i}  #预备数据，把增量备份还原回全量备份文件夹
                else
                    docker exec ${docker_name} mariabackup --prepare --target-dir=${backupDir} --incremental-dir=${incrementalBackup}/${filename}  #预备数据,最后一次还原
                fi
            done
        elif [ -d ${incrementalBackup} -a `ls ${incrementalBackup} |wc -l` -eq 0 -a -d ${backupDir} -a `ls ${backupDir} |wc -l` -ge 1 ];then 
            echo "增量还原失败，增量备份文件夹内没有文件，只执行全量还原"
            /user/bin/rm -rf ${incrementalBackup}
            docker exec ${docker_name} mariabackup --prepare --target-dir=${backupDir} #预备数据
        else
            echo "增量还原失败！可能是没有全量备份文件夹,请检查"${backupDir}
            exit 0
        fi
    elif [ $2 = 'nolike' ];then
        if [ ! -d ${differenceBackup} -a -d ${backupDir} -a `ls ${backupDir} |wc -l` -ge 1 ];then
            echo "差异备份文件夹不存在，只执行全量还原"
            docker exec ${docker_name} mariabackup --prepare --target-dir=${backupDir} #预备数据
        elif [ -d ${differenceBackup} -a `ls ${differenceBackup} |wc -l` -ge 1 ];then
            echo "差异备份文件夹存在，并有数据，执行全量+差异还原"
            filename=`ls -l ${differenceBackup}| tail -n 1 | awk '{print $9}'` #取最新文件夹名称
            docker exec ${docker_name} mariabackup --prepare  --target-dir=${backupDir}
            docker exec ${docker_name} mariabackup --prepare --target-dir=${backupDir} --incremental-dir=${differenceBackup}/${filename}
        elif [ -d ${differenceBackup} -a `ls ${differenceBackup} |wc -l` -eq 0 -a -d ${backupDir} -a `ls ${backupDir} |wc -l` -ge 1 ];then 
            echo "差异还原失败，差异备份文件夹内没有文件，只执行全量还原"
            /user/bin/rm -rf ${differenceBackup}
            docker exec ${docker_name} mariabackup --prepare --target-dir=${backupDir} #预备数据
        else
            echo "差异还原失败！可能是没有全量备份文件夹,请检查"${backupDir}
            exit 0
        fi
    elif [ $2 = 'all' ];then
        if [ -d ${backupDir} -a `ls ${backupDir} |wc -l` -ge 1 ];then
            docker exec ${docker_name} mariabackup --prepare --target-dir=${backupDir} #预备数据
        else
            echo "可能不存在全量备份文件夹，或者文件夹里没有文件,请检查"${backupDir}
            exit 0
        fi
    else
        echo  -e "参数错误\n参数2：add为增量备份还原，nolike为差异备份还原，all为全量还原"
        exit 0
    fi
    docker exec ${docker_name} rm -rf ${mysqldk}/
    docker exec ${docker_name} mkdir -p ${mysqldk}/
    docker exec ${docker_name} mariabackup --copy-back --datadir=${mysqldk} --target-dir=${backupDir} #执行还原
    if [ $2 = 'add' ];then
        /usr/bin/rm -rf ${incrementalBackup} #还原完删除增量备份文件夹
    elif [ $2 = 'nolike' ];then
        /usr/bin/rm -rf ${differenceBackup}/${filename} #还原完删除差异备份文件夹
    fi
    docker stop ${docker_name}
    echo "停止mariadb,拷贝数据文件"
    \cp  -rf ${mysqldk}/* ${mysqlDir2}
    echo "启动mariadb,修改目录权限"
    docker start ${docker_name} #启动mariadb
    docker exec ${docker_name} chown -R mysql:mysql /var/lib/mysql/ #调整mysql目录权限
    echo "重启mariadb"
    docker restart ${docker_name} #启动mariadb
    echo "已经启动mariadb"
else
    echo  -e "参数错误\n参数1：mysqldump为备份，mysql为还原"
fi
#结束时间
timer_end=`date "+%Y-%m-%d %H:%M:%S"`
#计算时间差
duration=`echo $(($(date +%s -d "${timer_end}") - $(date +%s -d "${timer_start}"))) | awk '{t=split("60 s 60 m 24 h 999 d",a);for(n=1;n<t;n+=2){if($1==0)break;s=$1%a[n]a[n+1]s;$1=int($1/a[n])}print s}'`
echo "开始： $timer_start"
echo "结束： $timer_end"
echo "耗时： $duration"