innobackupex实现mysql一键备份还原并创建slave同步

准备工作：

1、提前建好bak_dir

mkdir /home/mysql/backup_xt/

2、提前创建好同步用户

GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'slave_user'@'192.168.1.%' IDENTIFIED BY '123456';

3、提前准备好备份用户、可直接用root用户
4、还原之后的mysql用户和数据都和原数据库一致

脚本如下：

#!/bin/bash
#备份端SQL信息
bak_date=`date +%Y-%m-%d`
cnf_file="/home/mysql/bak/my.cnf" #MySQL配置文件
s_host="192.168.1.11"
s_port="3306"
my_user="root"
my_password="123456"
bak_dir="/home/mysql/backup_xt/"  #需提前创建好,两边都要
datadir="/home/mysql/mysqldata/" #MySQL数据目录datadir

#还原端服务器信息
ssh_host="192.168.1.12"
ssh_port="22"

#MySQL主服务器信息
master_host="192.168.1.11"   #同步master
master_user="slave_user"   #同步用户,需提前建好
master_password="123456"   #同步用户密码
master_port="3306"    #同步端口
server_id="2"   #主服务器server-id

check_rpm(){
	#添加xtrabackup更新仓库
    if [[ `rpm -qa epel-release | wc -l` -eq 0 ]] ;then
            yum install epel-release -y 2>>/tmp/bak_error.log
    fi
    #安装xtrabackup
    if [[ `rpm -qa percona-xtrabackup-24 | wc -l` -eq 0 ]] ;then
            yum install https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.21/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.21-1.el7.x86_64.rpm -y 2>>/tmp/bak_error.log
    fi
}

echo "1.备份端"
echo "2.还原端"
read -t 30 -p "请输入你的选择: 1或2 " digit

case "$digit" in
                "1")
						#检查软件是否安装
                        check_rpm
                        #备份数据库
                        if [ ! -d "${bak_dir}" ];then
                                mkdir -p ${bak_dir}
                        fi
                        echo "正在备份数据库，耗费时间较长，请耐心等待..."
                        innobackupex --defaults-file=${cnf_file} --host=${s_host} --port=${s_port} --user=${my_user} --password=${my_password} ${bak_dir} 2>>/tmp/bak_error.log
                        if [ $? -ne 0 ];then
                                echo "备份数据库失败，请检查数据库用户名密码是否配置正常"
                                exit
                        fi
                        cd ${bak_dir}
                        today_bak=`ls | grep ${bak_date}`
                        tar -zcf ${today_bak}.tar ${today_bak} 2>>/tmp/bak_error.log

                        #传输至还原端,如运行到此步失败，则需将上面备份数据库步骤注释以防止重复备份
                        echo "传输备份数据至目标服务器，请耐心等待..."
                        scp -P ${ssh_port} ${today_bak} ${ssh_host}:${bak_dir} 2>>/tmp/bak_error.log
                        if [ $? -ne 0 ];then
                                echo "传输至还原端失败，请检查目标服务器是否存在文件夹：${d_bak_dir}"
                                echo "注意：如运行到此步失败，则需将脚本内备份数据库步骤注释以防止重复备份"
                                exit
                        fi
                        scp -P ${ssh_port} ${cnf_file} ${ssh_host}:${bak_dir} 2>>/tmp/bak_error.log
                        echo "备份完成，请将脚本拷贝至目标服务器docker内执行"
                        ;;
                "2")
                        #还原端
                        #解压数据包
                        service mysql stop
                        cd ${bak_dir}
                        cp ${cnf_file} ${cnf_file}bak
                        #cp my.cnf ${cnf_file}
                        sed -i "s/server-id\ =\ ${server_id}/server-id\ =\ 1${server_id}/g" ${cnf_file}
                        tar -xf `ls | grep tar` 2>>/tmp/bak_error.log
                        today_bak=`ls | grep -v tar | grep -v sh | grep -v cnf` 2>>/tmp/bak_error.log
						#检查软件是否安装
                        check_rpm
                        #整理日志
                        rm -fr ${datadir}*
                        echo "正在整理mysql日志，请耐心等待..."
                        innobackupex --apply-log ${bak_dir}${today_bak} 2>>/tmp/bak_error.log
                        if [ $? -ne 0 ];then
                                echo "整理失败，请查看日志/tmp/bak_error.log"
                                exit
                        fi
                        echo "开始进行数据还原，请耐心等待..."
                        innobackupex --datadir=${datadir} --copy-back ${bak_dir}${today_bak} 2>>/tmp/bak_error.log
                        if [ $? -ne 0 ];then
                                echo "还原数据库失败，请查看日志/tmp/bak_error.log"
                                exit
                        fi

                        chown mysql.mysql -R ${datadir}
                        service mysql start 2>>/tmp/bak_error.log
                        if [ $? -ne 0 ];then
                                echo "mysql启动失败，请查看日志/tmp/bak_error.log"
                                exit
                        fi

                        #MySQL主库信息
                        bin_log=`cat ${datadir}xtrabackup_info  | grep binlog_pos | awk -F\' '{print $2}'`
                        position=`cat ${datadir}xtrabackup_info  | grep binlog_pos | awk -F\' '{print $4}'`
                        master_log_file=${bin_log}
                        master_log_pos=${position}

                        #开启MySQL主从同步
                        echo "正在配置主从同步..."
                        echo "stop slave;" | mysql -u${my_user} -p${my_password} 2>>/tmp/bak_error.log
            echo "change master to master_host='${master_host}',master_user='${master_user}',master_password='${master_password}',master_port=${master_port},master_log_file='${master_log_file}',master_log_pos=${master_log_pos};" | mysql -u${my_user} -p${my_password} 2>>/tmp/bak_error.log
            echo "start slave;" | mysql -u${my_user} -p${my_password} 2>>/tmp/bak_error.log
                        echo "同步配置完毕..."
                        ;;
                "*")
                        echo "无效选项，请输入1或2"
esac