xtrabackup脚本

xtrabackup是MySQL的一种物理备份工具,相对于mysqldump,备份和还原速度更快 , 我写了一份可以进行备份 + 还原的脚本

#!bin/bash
all_bak_path="/opt/my_bak/xbak" # 主备目录
add_bak_path="/opt/my_bak/add" # 增量备份目录
mysql_data_path="/data/mysql/data" # 数据库数据路径
mysql_name="mysqld" # 数据库名称
mysql_cnf="/etc/my.cnf" # MySQL 配置文件
userName="root" # 数据库用户名称
userPasswd="root" # 数据库用户密码
# 获取mysql.sock文件路径
# sock_path=$(find / -name mysql.sock) # 第一次获取时使用 
sock_path="/usr/local/mysql/mysql.sock"
mysql_port="3306"
innobackupex_options="
    --defaults-file=${mysql_cnf} \
    --socket=${sock_path} \
    --user=${userName} --password=${userPasswd} \
    --port=${mysql_port}" 

# 0.创建全量备份和增来那个备份路径
create_path(){
    if [ ! -d ${all_bak_path} ] ; then
        mkdir -p ${all_bak_path}
        echo "创建/opt/my_bak/xbak/目录完成"
    fi

    if [ ! -d ${add_bak_path} ] ; then
        mkdir -p ${add_bak_path}
        echo "创建/opt/my_bak/add/目录完成"
    fi
}


# 1.安装xtrabackup
ins_xtra(){
    ins_app="libev-4.03-3.el6.x86_64.rpm percona-xtrabackup-24-2.4.29-1.el7.x86_64.rpm"
    yum -y install $ins_app
    if [ $? -ne 0 ]; then
        echo "安装xtrabackup失败"
        exit 1
    fi
    echo "安装xtrabackup完成"

    data_dir=$(cat ${mysql_cnf} | grep datadir)
    if [ -z "$data_dir" ]; then
        cat >> ${mysql_cnf} <<EOF
datadir=${mysql_data_path}/ # 选择备份的数据库目录
EOF
        systemctl restart ${mysql_name}
        if [ $? -ne 0 ]; then
            echo "重启mysql失败"
            exit 1
        fi
        echo -e "修改${mysql_cnf}文件完成"
    fi
}

# 2.对数据库进行全量备份
all_backup(){
    innobackupex ${innobackupex_options} ${all_bak_path}
}

# 3.对数据进行增量备份
add_backup(){

    # 检测是否有全量备份
    if [ -z "$(find $all_bak_path -maxdepth 1 -type d)" ]; then
        echo "没有全量备份,需先全量备份"
        exit 1
    fi

    # 执行增量备份部分语句
    add_half="innobackupex ${innobackupex_options} \
            --incremental "${add_bak_path}" "
    #最新的全量备份
    new_all_backup_path=$(find ${all_bak_path}/*_* -maxdepth 0 -type d | sort -r | head -1) 
    
    #没有更新的话,上一次目录路径选择全量目录
    if [ -z "$(find $add_bak_path -mindepth 1 -maxdepth 1)" ]; then
        $add_half --incremental-basedir="${new_all_backup_path}"
        echo "增量执行完成,在全量备份后备份"
    else
        # 获取所有增量备份
        new_add_backup_path=($(find ${add_bak_path}/*_* -maxdepth 0 -type d | sort | cut -d ":" -f 1)) 
        # 获取全量备份文件名
        tmp_all_path=$(echo $new_all_backup_path | cut -d "/" -f 5) 
        # 将文件名格式化为数字格式
        all_time=$(echo "${tmp_all_path//[-_]}/" | cut -d "/" -f 1) 
        # 获取增量备份数量
        add_length=${#new_add_backup_path[@]} 

        # 获取全量备份以后得增量备份
        for i in ${new_add_backup_path[@]} ; do # 遍历
            tmp_add_path=$(echo $i | cut -d "/" -f 5) # 获取文件名
            add_time=$(echo "${tmp_add_path//[-_]}/" | cut -d "/" -f 1) # 将文件名格式化为数字格式
            if [ $all_time -le $add_time ] ; then # 判断增量备份名称是否相对于全量备份日期更新
                $add_half --incremental-basedir="${new_add_backup_path[add_length-1]}"
                echo -e "增量执行完成,在上一次增量备份后备份,上一次备份位置${new_add_backup_path[add_length-1]}"
                break
            else
                $add_half --incremental-basedir="${new_all_backup_path}"
                echo "增量执行完成,在全量备份后备份"
                break
            fi
        done
    fi
}

# 4.对全量备份进行合并
merge_backup(){
    #最新的全量备份
    new_all_backup_path=$(find ${all_bak_path}/*_* -maxdepth 0 -type d | sort -r | head -1) 
    # 倒序获取所有增量备份
    new_add_backup_path=($(find ${add_bak_path}/*_* -maxdepth 0 -type d | sort | cut -d ":" -f 1)) 
    # 获取全量备份文件名
    tmp_all_path=$(echo $new_all_backup_path | cut -d "/" -f 5) 
    # 将文件名格式化为数字格式
    all_time=$(echo "${tmp_all_path//[-_]}/" | cut -d "/" -f 1) 
    # 获取增量备份数量
    add_length=${#new_add_backup_path[@]} 
    # 执行合并部分语句
    apply_half="innobackupex --apply-log"

    echo "准备全量备份"
    ${apply_half} --redo-only $new_all_backup_path
    if [ $? -eq 0 ] ; then 
        echo -e "准备全量备份完成,位置为${new_all_backup_path}"
    else 
        echo "准备全量备份报错,位置为${new_all_backup_path}"
        exit 1
    fi

    # 将增量备份脚本从后向前进行合并
    if [ -n $new_add_backup_path ] ; then
        # 获取全量备份以后得增量备份
        for i in ${new_add_backup_path[@]} ; do # 遍历
            tmp_add_path=$(echo $i | cut -d "/" -f 5) # 获取文件名
            add_time=$(echo "${tmp_add_path//[-_]}/" | cut -d "/" -f 1) # 将文件名格式化为数字格式
            if [ $all_time -le $add_time ] ; then # 判断增量备份名称是否相对于全量备份日期更新
                if [ $i == ${new_add_backup_path[add_length - 1]} ] ; then
                    $apply_half $new_all_backup_path --incremental-dir=${new_add_backup_path[add_length - 1]}
                    if [ $? -eq 0 ] ; then 
                        echo -e "最后一次合并完成,位置为${i}"
                    else 
                        echo "最后一次合并报错,位置为${i}"
                        exit 1
                    fi
                else
                    $apply_half --redo-only $new_all_backup_path --incremental-dir=$i
                    if [ $? -eq 0 ] ; then 
                        echo -e "增量合并至全量完成,位置为${i}"
                    else 
                        echo "增量合并至全量报错,位置为${i}"
                        echo -e "语句为: $apply_half --redo-only $new_all_backup_path --incremental-dir=${i}"
                        exit 1
                    fi
                fi
            fi
        done
    else
        echo "没有增量备份"
    fi

    echo "最后一次准备全量备份"
    ${apply_half} $new_all_backup_path
    if [ $? -eq 0 ] ; then 
        echo -e "准备全量备份完成,位置为${new_all_backup_path}"
    else 
        echo "准备全量备份报错,位置为${new_all_backup_path}"
        exit 1
    fi
}

# 5.对数据库进行还原
db_restore(){    
    #最新的全量备份
    new_all_backup_path=$(find ${all_bak_path}/*_* -maxdepth 0 -type d | sort -r | head -1) 

    innobackupex --copy-back ${new_all_backup_path}
    echo "还原数据完成"

    chown -R mysql.mysql ${mysql_data_path}
    echo "修改mysql数据库权限完成"

    systemctl restart ${mysql_name}
    echo "重启数据库完成"

    echo "数据库连接测试: "
    mysql -u${userName} -p${userPasswd} -e "show databases"
    mysql -u${userName} -p${userPasswd} -e "select * from test.testlog"
}  

options=("全量备份" "增量备份" "还原数据" "安装xtrabackup" "点击任意键退出")
create_path
select i in ${options[@]} ;do
    case $i in 
        ${options[0]})
            all_backup
        ;;
        ${options[1]})
            add_backup
        ;;
        ${options[2]})
            merge_backup
            db_restore
        ;;
        ${options[3]})
            ins_xtra
        ;;
        *)
            echo "退出"
            break 2
        ;;
    esac
done



``