阿里云mysql RDS线下自动恢复脚本

#!/bin/sh

source /etc/profile

DATADIR=/data/mysql


read -p "请选择恢复模式(1.网络下载；2.本地已经存储)输入编号:" flag
if  [ ! -n "$flag" ] ;then
    echo "你需要输入恢复模式编号: flag!"
    exit 1
else
    echo "你输入的恢复模式编号： $flag"
fi

if [ $flag == 1 ] ;then
    read -p "请需要输入备份url地址 :" ADDRESS
    if  [ ! -n "$ADDRESS" ] ;then
        echo "你需要输入备份url地址: ADDRESS!"
        exit 1
    else
        echo "你输入的备份url地址： $ADDRESS"
    fi
	
	read -p "请需要输入备份下载存放路径(例如:/data) :" bak_dir
    if  [ ! -n "$bak_dir" ] ;then
        echo "你需要输入备份下载存放路径: bak_dir!"
        exit 1
    else
        echo "你输入的备份下载存放路径： $bak_dir"
    fi
	
    wget -c "$ADDRESS" -O $bak_dir/mysqlrdsbackupset_qp.xb
    BACKUPSET=$bak_dir/mysqlrdsbackupset_qp.xb
	
elif [ $flag == 2 ] ;then
    read -p "请需要输入备份文件路径(例如:/data/hins18463816_data_20220523024003_qp.xb) :" BACKUPSET
    if  [ ! -n "$BACKUPSET" ] ;then
        echo "你需要输入备份文件路径: BACKUPSET!"
        exit 1
    else
        echo "你输入的备份文件路径为： $BACKUPSET"
    fi
else 
    echo "你输入的编号不正确"
fi

bak_dir=${BACKUPSET%/*}

TMP=${BACKUPSET%/*}/tmp

if [ ! -d $TMP ];then
    echo "临时路径不存在，自动创建.."
    mkdir -p $TMP
fi

rm -rf $TMP/*

echo "解压备份到临时目录$TMP"
cat $BACKUPSET|xbstream -x -v -C $TMP
xtrabackup --decompress --remove-original --target-dir=$TMP

echo "恢复解压好的备份文件"
service mysqld stop
rm -rf $DATADIR/*
xtrabackup --prepare --apply-log --target-dir=$TMP
xtrabackup --move-back --target-dir=$TMP
chown mysql.mysql -R $DATADIR
service mysqld start

mysql_status=`netstat -nl | awk 'NR>2{if ($4 ~ /.*:3306/) {print "Yes";exit 0}}'`  
 
if [ "$mysql_status" != "Yes" ];then  
    echo "MySQL 恢复失败."
    exit 1	
fi 

echo "MySQL数据库恢复成功！！！"

echo "开通高权限账号:" 
service mysqld stop
/usr/local/mysql/bin/mysqld_safe --skip-grant-tables --skip-networking &
sleep 20
mysql -e "UPDATE mysql.user SET authentication_string='' WHERE user='aliyun_root';"
service mysqld restart

#恢复binglog
echo "当前数据库事务节点为： "
log_name=`cut -f 1 /data/tmp/xtrabackup_binlog_info`
log_pos=`cut -f 2 /data/tmp/xtrabackup_binlog_info`
echo $log_name $log_pos

read -p "是否用binlog前滚日志恢复数据库：[y/n]:" num
case $num in
Y | y)
    read -p "请输入 $log_name 的下载url地址：" log_addr
    wget -c "$log_addr" -O $bak_dir/$log_name
    echo "mysqlbinlog $bak_dir/$log_name --skip-gtids --start-position $log_pos |mysql -ualiyun_root -h127.0.0.1"
    mysqlbinlog $bak_dir/$log_name --skip-gtids --start-position $log_pos |mysql -ualiyun_root -h127.0.0.1   
    echo 1${log_name:0-6}
    lognum="$(( 1${log_name:0-6} + 1 ))"
    next_log=mysql-bin.${lognum:0-6}
    echo $next_log
    read -p "是否继续用binlog前滚数据库，下一个日志$next_log：[y/n]:" fg
        
    while [ $fg = "y" ]
    do
        read -p "请输入 $next_log 的下载url地址：" log_addr
        wget -c "$log_addr" -O $bak_dir/$next_log
        echo "mysqlbinlog $bak_dir/$next_log --skip-gtids |mysql -ualiyun_root -h127.0.0.1"
        mysqlbinlog $bak_dir/$next_log --skip-gtids |mysql -ualiyun_root -h127.0.0.1
        lognum="$(( 1${next_log:0-6} + 1 ))"
        next_log=mysql-bin.${lognum:0-6}
        read -p "是否继续用binlog前滚数据库，下一个日志$next_log：[y/n]:" fg
        
    done
    echo "数据库恢复完成!"

;;
N | n)
    echo "数据库恢复完成!"
;;
*)
 echo "输入错误"
;;
esac

#删除临时目录
rm -rf $TMP

结果展示：