mysql恢复脚本(利用master的备份搭建slave)

#!/bin/bash


####################################################################################################
#传参列表
#sh 100.sh 192.168.1.1 mysql mysqlbak-192.168.1.2-4003-2020-10-29 /home/backup 192.168.1.3 4003 192.168.1.2 4003

remote_backup_host=$1 #远程备份文件服务器IP
rsync_dir=$2          #远程服务器备份路径对应的rsync tag
r_filename=$3         #备份文件前缀，格式mysqlbak-数据库实例IP-数据库实例PORT-YYYY-MM-DD
r_bacupdir=$4         #恢复机器上传备份文件的路径
r_host=$5             #新建slave对应的主机IP
r_port=$6             #新建slave对应的实例port
r_masterhost=$7       #master实例的IP
r_masterport=$8       #master实例对应的port

####待完成的功能
#r_mysqlversion=$9
#r_type=${10}        
#r_gtid=${11}


####################################全局变量##########################################################
repl_user='slave_client'
repl_password='xxxxxx'

######################################################################################################

function check_parameters()
{
    if [[ $total_parameters -lt 8 ]];then
        echo "参数个数不匹配,请检查"
        echo "------------usage-----------------"
        echo "remote_backup_host=\$1 #远程备份文件服务器IP"
        echo "rsync_dir=\$2          #远程服务器备份路径对应的rsync tag"
        echo "r_filename=\$3         #备份文件前缀，格式mysqlbak-数据库实例IP-数据库实例PORT-YYYY-MM-DD"
        echo "r_bacupdir=\$4         #恢复机器上传备份文件的路径"
        echo "r_host=\$5             #新建slave对应的主机IP"
        echo "r_port=\$6             #新建slave对应的实例port"
        echo "r_masterhost=\$7       #master实例的IP"
        echo "r_masterport=\$8       #master实例对应的port"
        exit
    fi
}

#检查恢复的机器r_host上是否存在r_port对应的服务
function check_r_port()
{
    check_port=$(netstat -nltp | awk '{print $4}' | awk -F':' '{if($NF=='$r_port')print $NF}' | wc -l)
    if [[ $check_port -ne 0 ]];then
        echo "$r_host上$r_port端口已被占用,请检查"
        exit
    else
        echo "$r_host上不存在$r_port端口"
    fi
}

#将备份文件传到待恢复的机器上
function download_backup_file()
{   
    echo "开始拷贝备份文件到$r_host主机$r_bacupdir目录下"
    /usr/bin/rsync -aP --bwlimit=150000 root@${remote_backup_host}::${rsync_dir}/${r_filename}.xbstream ${r_bacupdir} >/dev/null 2>&1
    if [[ $? -ne 0 ]];then
        echo "备份数据文件不存在或传输失败,请检查"
        exit 
    else
        echo "完成拷贝备份文件$r_filename.xbstream"
    fi
    
    echo "开始拷贝参数文件到$r_host主机$r_bacupdir目录下"
    /usr/bin/rsync -auv root@${remote_backup_host}::mysql/${r_filename}.cnf ${r_bacupdir} >/dev/null 2>&1
    if [[ $? -ne 0 ]];then
        echo "备份参数文件不存在或传输失败,请检查"
        exit 
    else
        echo "完成拷贝参数文件$r_filename.cnf"
    fi
}


#解压备份文件
function unpack()
{
    if [[ ! -d ${r_bacupdir}/${r_filename}/ ]];then
        mkdir -p ${r_bacupdir}/${r_filename}/
    else
        rm -fr ${r_bacupdir}/${r_filename}/*
    fi
    
    #解压xbstream文件
    /usr/bin/xbstream -C ${r_bacupdir}/${r_filename} -x -v  < ${r_bacupdir}/${r_filename}.xbstream >/dev/null 2>&1
    if [[ $? -ne 0 ]];then
        echo "xbstream file unpack faild"
    fi
    
    #解压qp文件，加压完成后清理qp文件，最终删除xbstream备份文件
    /usr/bin/innobackupex --decompress --parallel=4 ${r_bacupdir}/${r_filename} >/dev/null 2>&1
    if [[ $? -ne 0 ]];then
        echo "qp file unpack faild"
    else
        find ${r_bacupdir}/${r_filename} -name "*.qp" | xargs rm
        rm -f ${r_bacupdir}/${r_filename}.xbstream
    fi
}


#修改参数文件并拷贝到指定路径下,默认/etc/mysql/my${r_port}.cnf
function modify_cfg_file()
{
    #修改server-id,由IP末尾2分位和端口拼接组成
    suffix_ip=$(echo $r_host | awk -F'.' '{print $3$4}')
    server_id="$suffix_ip$r_port"
    sed -i "s#server-id.*#server-id='${server_id}'#g" $r_bacupdir/$r_filename.cnf
    
    #修改report_host,替换为r_host
    sed -i "s#report_host.*#report_host='${r_host}'#g" $r_bacupdir/$r_filename.cnf
    
    if [[ ! -d /etc/mysql ]];then
        mkdir -p /etc/mysql
        mv $r_bacupdir/$r_filename.cnf /etc/mysql/my${r_port}.cnf
    else
        mv $r_bacupdir/$r_filename.cnf /etc/mysql/my${r_port}.cnf
    fi
    
    if [[ $? -ne 0 ]];then
        echo "参数文件修改或者创建失败，请检查"
        exit
    else
        echo "参数文件修改和创建成功"
    fi
    
}


#apply-log
function exec_apply_log()
{
    /usr/bin/innobackupex  --apply-log --use-memory=4G ${r_bacupdir}/${r_filename}   >/dev/null 2>&1
    if [[ $? -ne 0 ]];then
        echo "执行apply-log失败,请检查"
        exit
    else
        echo "执行apply-log成功"
    fi
}

#创建目录
function exec_mkdir()
{
    echo "创建mysql相关目录结构"
    mkdir -pv /home/data/my${r_port}/{data,log,socket,tmp}
    touch /home/data/my${r_port}/log/error.log
}


#copy-back
function exec_copy_back()
{
    /usr/bin/innobackupex --defaults-file=/etc/mysql/my${r_port}.cnf --copy-back --parallel=4  ${r_bacupdir}/${r_filename} >/dev/null 2>&1
    if [[ $? -ne 0 ]];then
        echo "执行copy-back失败,请检查"
        exit
    else
        echo "执行copy-back成功"
    fi
}

#mysql相关目录授权
function re_grant()
{
    echo "mysql相关目录授权"
    chown -R mysql:mysql /home/data/my${r_port}/*
}

#启动实例并检查是否再1分钟内启动成功
function start_and_check_mysql()
{
    echo "开始启动$r_host:$r_port实例"
    mysqld_safe --defaults-file=/etc/mysql/my${r_port}.cnf --user=mysql >/dev/null 2>&1 &
    
    i=0
    while true
    do
        check_1=$(mysql -S /home/data/my${r_port}/socket/mysqld.sock 2>/dev/null -e "select 1;")
    
        if [[ ! -z $check_1 ]];then
            echo "$r_host:$r_port实例已启动成功"
            break
        else
            sleep 5
            let i=i+5
            if [[ $i -gt 60 ]];then
                echo "$r_host:$r_port实例启动超过1分钟，请检查"
                exit
            fi
        fi
    done
}

#change 语句拼接
function change_and_startslave()
{
    echo "生成gtid_purged_info信息"
    cat $r_bacupdir/$r_filename/xtrabackup_binlog_info | xargs -n1 | sed '1,2d' > gtid_purged_info
    
    gtid_purged_content=""
    while read line
    do
        gtid_purged_content=$gtid_purged_content$line
    done < gtid_purged_info
    
    echo "拼接启动slave前的初始操作并启动slave"
    [ -f change.sql ] && rm -rf change.sql
    
    echo "reset slave all;" >> change.sql
    echo "reset master;" >> change.sql
    echo "SET @@SESSION.SQL_LOG_BIN= 0;" >> change.sql
    echo "SET @@GLOBAL.GTID_PURGED="\'$gtid_purged_content\'";" >> change.sql
    echo "SET @@SESSION.SQL_LOG_BIN = 1;"  >> change.sql
    
    echo "CHANGE MASTER TO 
    MASTER_HOST="\'${r_masterhost}\'",
    MASTER_PORT=${r_masterport},
    MASTER_USER="\'${repl_user}\'",
    MASTER_PASSWORD="\'${repl_password}\'",
    MASTER_AUTO_POSITION = 1;" >> change.sql
    
    echo "start slave;" >> change.sql
    
    mysql -S /home/data/my${r_port}/socket/mysqld.sock 2>/dev/null < change.sql
}



function main()
{
    total_parameters=$#
    check_parameters
    check_r_port
    download_backup_file
    unpack
    modify_cfg_file
    exec_apply_log
    exec_mkdir
    exec_copy_back
    re_grant
    start_and_check_mysql
    change_and_startslave
}

main $@