准备工作:

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

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

case "$digit" in
                "1")
                        #添加xtrabackup更新仓库
                        if [[ `rpm -qa epel-release | wc -l` -eq 0 ]] ;then
                                yum install epel-release -y 2>>/tmp/bak_error.log
                        fi
                        if [[ `rpm -qa percona-release | wc -l` -eq 0 ]] ;then
                                yum install http://www.percona.com/downloads/percona-release/redhat/0.1-6/percona-release-0.1-6.noarch.rpm -y 2>>/tmp/bak_error.log
                        fi
                        #安装xtrabackup
                        if [[ `rpm -qa percona-xtrabackup-24 | wc -l` -eq 0 ]] ;then
                                yum install percona-xtrabackup-24 -y 2>>/tmp/bak_error.log
                        fi
                        #备份数据库
                        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 -cf ${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}
                        mv /usr/local/mysql/my.cnf /usr/local/mysql/my.cnf.bak
                        cp my.cnf /usr/local/mysql/my.cnf
                        sed -i "s/server-id\ =\ ${server_id}/server-id\ =\ 1${server_id}/g" /usr/local/mysql/my.cnf
                        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
                        if [[ `rpm -qa epel-release | wc -l` -eq 0 ]] ;then
                                yum install epel-release -y 2>>/tmp/bak_error.log
                        fi
                        if [[ `rpm -qa percona-release | wc -l` -eq 0 ]] ;then
                                yum install http://www.percona.com/downloads/percona-release/redhat/0.1-6/percona-release-0.1-6.noarch.rpm -y 2>>/tmp/bak_error.log
                        fi
                        #安装xtrabackup
                        if [[ `rpm -qa percona-xtrabackup-24 | wc -l` -eq 0 ]] ;then
                                yum install percona-xtrabackup-24 -y 2>>/tmp/bak_error.log
                        fi
                        #整理日志
                        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;" | /usr/local/mysql/bin/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};" | /usr/local/mysql/bin/mysql -u${my_user} -p${my_password} 2>>/tmp/bak_error.log
            echo "start slave;" | /usr/local/mysql/bin/mysql -u${my_user} -p${my_password} 2>>/tmp/bak_error.log
                        echo "同步配置完毕..."
                        ;;
                "*")
                        echo "无效选项,请输入1或2"
esac