1、编写脚本，支持让用户自主选择，使用mysqldump还是xtraback全量备份。
2、配置Mysql主从同步
3、使用MHA实现Mysql高可用。

1.编写脚本，支持让用户自主选择，使用mysqldump还是xtraback全量备份

思路：
mysqldump的备份分为全库备份-A和-B部分库备份 这个写两个函数 
xtrabackup完全备份全库备份 写函数
定义彩色变量
while循环里面写，cat输入重定向打印彩色菜单提示符语句 read -p提示符，case语句菜单调用函数

image.png

复制可用，仅仅是全备份 没有考虑binlog备份。
下面注释的read -p的行 如果取消则变成交互式输入变量脚本

root@17  ~]# cat bk.sh
#!/bin/bash
. /etc/init.d/functions
. /etc/profile.d/path.sh

RED="echo -e \e[1;31m"
END="echo -e \e[0m"

TIME=`date +%F_%H-%M`
XBK=/backup/xbk
FULL_BK=/backup/full
#BIN_BK=/backup/binlog
BK_DIR=/backup
ETC="/mysql/3306/etc/my.cnf"
SOCK="/mysql/3306/sock/mysql.sock"
DB_LIST=$(mysql -e 'show databases'|grep -Ev 'information_schema|performance_schema|sys|Database')
USER=root
PASS=''
BK_USER=root
BK_IP=10.0.0.27
BK_PASS=123456
BK_PORT=22
#read -p "input the mysql_user: " USER
#read -p "input the mysql_passwd: " PASS
#read -p "input the bk_host user(like root): " BK_USER
#read -p "input the bk_host IP (like 10.0.0.8): " BK_IP
#read -p "input bk_host PASSWORD: " BK_PASS

test_connect_mysql(){
    if [ -z $PASS ];then
    mysql -u$USER -S $SOCK -e 'select @@hostname' &>/dev/null && action "connect mysql successful" || { action "connect to mysql failed,please check user,passd or host " false ;exit 3; }
    else
    mysql -u$USER -p${PASS} -S $SOCK -e 'select @@hostname' &>/dev/null && action "connect mysql successful" || { action "connect to mysql failed,please check user,passd or host " false ;exit 3; }

fi
}

dumpbk_B_mysql(){
[ -d ${FULL_BK} ] || mkdir -p ${FULL_BK}
#[ -d ${BIN_BK} ] || mkdir -p ${BIN_BK}


for DB in ${DB_LIST};do
    if [ $PASS ! = "" ];then
        mysqldump -u$USER -p${PASS} -S $SOCK -B $DB  -F -E -R --triggers --single-transaction  --master-data=2 -q | gzip > ${FULL_BK}/${DB}_dumpbk_${TIME}.sql.gz
    else
        mysqldump -u$USER -S $SOCK -B $DB  -F -E -R --triggers --single-transaction  --master-data=2 -q | gzip > ${FULL_BK}/${DB}_dumpbk_${TIME}.sql.gz
    fi
    [ $? -eq 0 ] && action "local ${DB} backup successful,see $FULL_BK" || { action "local backup failed" false;exit 3; }
done
}

dumpbk_A_mysql(){
[ -d ${FULL_BK} ] || mkdir -p ${FULL_BK}
#[ -d ${BIN_BK} ] || mkdir -p ${BIN_BK}

for DB in ${DB_LIST};do
    if [ $PASS ! = "" ];then
        mysqldump -u$USER -p${PASS} -S $SOCK -A -F --single-transaction --master-data=2 -q | gzip > ${FULL_BK}/ALL_dumpbk_${TIME}.sql.gz
    else
        mysqldump -u$USER -S $SOCK -A -F --single-transaction --master-data=2 -q | gzip > ${FULL_BK}/ALL_dumpbk_${TIME}.sql.gz
    fi
    [ $? -eq 0 ] && action "local backup successful,see backup dir $FULL_BK" ||{ action "local backup failed" false;exit 3; }
done
}


remote_bk (){

rpm -q sshpass &> /dev/null || yum -y install sshpass &>/dev/null

[ -f ~/.ssh/id_rsa ] || ssh-keygen -P "" -f /root/.ssh/id_rsa &>/dev/null

sshpass -p ${BK_PASS} ssh-copy-id -o StrictHostKeyChecking=no -i ~/.ssh/id_rsa.pub ${BK_USER}@${BK_IP} -p ${BK_PORT} &>/dev/null || { action "remote backup server message maybe incorrect" false;exit 3; }

sshpass -p ${BK_PASS} ssh -o StrictHostKeyChecking=no ${BK_USER}@${BK_IP} -p ${BK_PORT} mkdir -p ${BK_DIR}

rsync -auv -e "ssh -p $BK_PORT" ${BK_DIR}/* ${BK_USER}@${BK_IP}:${BK_DIR} &>/dev/null

[ $? -eq 0 ] && action "remote backup successful,see ${BK_USER}@${BK_IP}:${FULL_BK}" || { action "remote backup failed" false;exit3; }

}

xtrabackup_all (){
[ -d ${XBK} ] || mkdir -p ${XBK}
if [ $PASS ! = "" ];then
        innobackupex --defaults-file=${ETC} --socket=${SOCK} --user=${BK_USER} --password=${BK_PASS} ${XBK}
    else
        innobackupex --defaults-file=${ETC} --socket=${SOCK} --user=${BK_USER} ${XBK} &>/dev/null
    fi

[ $? -eq 0 ] && action "local backup successful,see backup dir $XBK" || { action "local backup failed" false;exit 3; }
}

while :;do
    $RED
    cat <<-EOF
    1)mysqldump全备份

    2)mysqldump分库备份

    3)xtrabackup全备份

    4)退出
    EOF
    $END
    read -p "please choose a backup method: " MENU
    case $MENU in
        1)
            test_connect_mysql
            dumpbk_A_mysql
            remote_bk
            ;;
        2)
            test_connect_mysql
            dumpbk_B_mysql
            remote_bk
            ;;
        3)
            test_connect_mysql
            xtrabackup_all
            remote_bk
            ;;
        4)
            exit 0
            ;;
        *)
            action "please input correct value 1|2|3" false
            exit 3
            ;;
    esac
done

2.配置Mysql主从同步 我这里用GTID复制

主库10.0.0.8 master
从库10.0.0.18 slave

image.png
主库配置

更改master配置文件

# replication
server_id=8
# binlog
# sql_log_bin=ON    #default ON
log_bin=/mysql/3306/binlog/mysql-bin
# sync_binlog=1
binlog_format=row   # default row
gtid_mode=on
enforce-gtid-consistency=true   #强制一致性

systemctl restart mysqld

创建复制用户并授权

mysql> create user repl@'10.0.0.%' identified by '123';
Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave on *.* to repl@'10.0.0.%';
Query OK, 0 rows affected (0.01 sec)

主库mysqldump全备份所有数据到从库slave1

# 如果是第一次配置主从，直接建立主从，不用此步骤
mysqldump -A --single-transaction --master-data=1 -F >/root/all.sql
scp /root/all.sql root@10.0.0.18:/root

从库配置
从库配置文件

# replication
server_id=18
# binlog
# sql_log_bin=ON    #default ON
log_bin=/mysql/3306/binlog/mysql-bin
read-only                   #从库加只读
# sync_binlog=1
binlog_format=row   # default row
gtid_mode=on
enforce-gtid-consistency=true   #强制一致性

systemctl restart mysqld

还原数据库到从库

# 同样如果第一次建立主从 不用此步骤
root@18  ~]# mysql -e 'set sql_log_bin=0;source /root/all.sql;set sql_log_bin=1'

写change master to主库信息到master.info 注意此处写masterIP

CHANGE MASTER TO
  MASTER_HOST='10.0.0.8',                                       
  MASTER_USER='repl',
  MASTER_PASSWORD='123',
  MASTER_PORT=3306,
  MASTER_AUTO_POSITION=1;

启动从库IO和SQL线程并查看状态

start slave;
show slave status\G;

3.实现MHA高可用

这个文章太长了 请看我的单独写的博客
配合binlog-server和VIP和邮件实现MHA高可用