第十二周作业
2021-01-23 本文已影响0人
念念OPS
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
主库配置
更改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高可用