k8s mysql主从

CHANGE MASTER TO MASTER_HOST='172.16.68.77',MASTER_PORT=31308,MASTER_USER='slave_db',MASTER_PASSWORD='ujmyhn@Sdn1',MASTER_LOG_FILE='master.000006',MASTER_LOG_POS=224951275;start slave;
View Code

 
转至元数据结尾
 

环境： ubuntu 16.04

k8s版本： 1.11.5

目标：mysql master挂机后切换到mysql slave上运行(以mysql-open为例)

需要root免密 sudo免密

 devtest-node112   mysql-open-master

 devtest-node121  mysql-open-slave

一、主从切换   （大约为1-2分钟左右切换完成）

1.检测mysql   master 运行情况（每分钟执行一次，失败5次而且slave工作正常的情况下进行切换）

mysql-master-check-new.sh Expand source
 

2.给老的标签打上SchedulingDisabled ，命令如下

kubectl cordon devtest-node112

3.去除mysql slave的slave设置，使用mysql客户端执行如下命令：

stop slave;reset master;reset slave all;

4.删除mysql slave的deploy

kubectl delete deploy mysql-open-deploy

kubectl delete po mysql-open-slave-deploy-xxxxx  --grace-period=0 --force

5.删除slave标签

kubectl label nodes devtest-node121  ToSlave-

6.给slave节点打上master的label

kubectl label nodes devtest-node121 ToMysql=mysql-open

7.删除原有的myql-open-master 的pod

kubectl delete po mysql-open-deploy-xxxxx  --grace-period=0 --force

完成主切换到slave节点上,如果密码和用户不一致请修正脚本内容

S_MYSQL_U="root"
S_MYSQL_P="123456"

汇总的脚本如下：

mastertoslave.sh Expand source
 

二、恢复slave节点  （恢复时间的长短，取决于数据库备份和scp的时间）

现在的情况是  devtest-node121 为主节点，devtest-node112 已经可以正常运行

1.备份devtest-node121 上的数据，为从节点完成初始化数据（使用percona-xtrabackup，由于使用的mysql 5.7的 ，xtrabackup请使用2.x的版本）

红色部分根据实际情况进行调整

innobackupex --user=USERNAME --password=PASSWORD --datadir=mysqldata_path -S mysqldata_path/mysql.sock /data/bak

2.复制备份的文件到需要创建slave的节点上这里是 devtest-node138 

scp -r /data/bak/2020-xxxx-xxxx-xxxx devtest-node112:/data/mysql-open-slave

chown -R 999:999 /data/mysql-open-slave

3.打上slave的label，并去除原来的master的label

kubectl label nodes devtest-node112  ToSlave=mysql-open ToMysql-

4.恢复调度的机制

kubectl uncordon devtest-node112

5.重新创建slave

kubectl apply -f path/mysql-open-slave-deploy.yaml

6.等pod起来后，使用mysql客户端执行如下命令,红色部分为根据实际情况选填

CHANGE MASTER TO MASTER_HOST='mysql-open-master',MASTER_USER='slaveuser',MASTER_PASSWORD='123456',MASTER_LOG_FILE='master.000001',MASTER_LOG_POS=154;start slave;

  

mysql-open-master-----》mysql-master的主机名或svc地址

slaveuser               -----》建立主从的用户名

123456                  -----》建立主从的密码

master.000001      -----》二进制文件的名称                   （cat /data/mysql-open-slave/xtrabackup_binlog_info，第一列）

154                       -----》二进制文件的执行的字符数     （cat /data/mysql-open-slave/xtrabackup_binlog_info，第二列）

7.查看slave的状态，使用mysql客户端执行

show slave statusG;

汇总恢复部分脚本1-6的步骤

mysql bak Collapse source
#!/bin/bash
#not for incremental
set -e
USERNAME="root"
PASSWORD="MThjN2Y3Zj"
if [ ! -f /usr/bin/xtrabackup ];then
        sudo  apt install -y percona-xtrabackup-24 --allow-unauthenticated
fi
#get path for mysql
DOCKERID=$(sudo docker ps|grep mysql|grep -v pause|awk '{print $1}')
if [ "${DOCKERID}" == "" -o $(echo ${DOCKERID}|sed 's? ? ?g'|wc -l) -ne 1 ];then
        echo "no mysql server or mysql server is not 1,please check"
        exit 0
fi
sudo docker inspect -f {{.State.Pid}} ${DOCKERID}
MPATH=$(sudo docker inspect $DOCKERID |grep :/var/lib/mysql|sed 's?"??g'|awk -F ":" '{print $1}'|awk '{print $1}')
mkdir -p /data/bak
sudo chmod -R 777 /data/bak
innobackupex --user=${USERNAME} --password=${PASSWORD} --datadir=${MPATH} -S ${MPATH}/mysql.sock /data/bak
i=$(sudo ls /data/bak/ -l|grep ^d|awk '{print $NF}'|grep ^20|tail -1)
innobackupex --apply-log /data/bak/"$i"
#KEEP=$(ls -l /data/bak|grep "^d"|sort -rk8|awk 'NR==1,NR==14{print $NF}'|sed "s?$?|?g")
#KEEPNEW=$(echo ${KEEP%?}|sed 's? ??g')
#DELD=$(ls /data/bak|grep -vE $KEEPNEW)
#cd /data/bak && rm -rf $DELD
echo 'finish'
echo 'finish'>/tmp/mysql-bak-process.txt
mysql slave restore Collapse source
#!/bin/bash
set -e
BASEDIR="../yaml"
ROOTPASSWORD="MThjN2Y3Zj"
alias ssh="ssh -o ConnectTimeout=10"
#if [ ! -f /tmp/mysql-restore.txt ];then
    kubectl get po -o wide --all-namespaces|grep -w mysql|grep Running|grep -v slave|awk '{print $1,$2,$(NF-1)}'>/tmp/mysql-restore.txt
#fi
ALLMYSQLNU=$(cat /tmp/mysql-restore.txt|wc -l)
for ((m=1;m<=ALLMYSQLNU;m++));do
#while read i;do
    i=$(cat /tmp/mysql-restore.txt|awk NR==$m'{print}')
    NS=$(echo $i|awk '{print $1}')
    MYSQLNAME=$(echo $i|awk '{print $2}'|awk -F "-deploy" '{print $1}')
    STATUSSLAVE=$(kubectl -n ${NS} get po|grep ${MYSQLNAME}-slave|wc -l)
    if [ ${STATUSSLAVE} -eq 0 ];then
        if [ $(echo ${MYSQLNAME}|grep terminal|wc -l) -eq 1 ];then
            MYSQLLABEL="mysql-terminal"
        else
            MYSQLLABEL="${MYSQLNAME}"
        fi
 
        OLDMASTERNODE=$(kubectl get nodes --show-labels|grep SchedulingDisabled|grep -w "${MYSQLLABEL}"|awk '{print $1}')
        #add slave label
        if [ "${OLDMASTERNODE}" != "" ];then
 
            #bakup old data
            MYSQL_DATA_BAK="$(ssh ${OLDMASTERNODE} sudo ls -d /data/mysql*)"
            ssh ${OLDMASTERNODE} "sudo mkdir -p /data/oldbak/`date +%F`"
            ssh ${OLDMASTERNODE} "sudo mv /data/mysql* /data/oldbak/`date +%F`/"
            #check docker and kubelet service
            if [ "$(ssh ${OLDMASTERNODE} sudo systemctl status docker|grep Active|awk '{print $2}')" != "active" ];then
                ssh ${OLDMASTERNODE} sudo  systemctl start docker
            fi
            if [ "$(ssh ${OLDMASTERNODE} sudo systemctl status kubelet|grep Active|awk '{print $2}')" != "active" ];then
                ssh ${OLDMASTERNODE} sudo  systemctl start kubelet
            fi
        else
            echo "no node can use,please check"
            exit 0
        fi
        #bakup current mysql master
        CMASTERNODE=$(kubectl -n ${NS} get po -o wide|grep ${MYSQLNAME}-deploy|grep Run|awk '{print $(NF-1)}')
        if [ "${CMASTERNODE}" != "" ];then
            #kubectl label nodes ${CMASTERNODE} ToSlave-
            scp mysql-bak.sh  ${CMASTERNODE}:/tmp/mysql-bak.sh
            ssh ${CMASTERNODE} 'sudo bash -x /tmp/mysql-bak.sh'
        else
            echo "no ${MYSQLNAME} master"
        fi
        #cp bakfile to OLDMASTERNODE
        BAKPATH=$(ssh ${CMASTERNODE} "sudo ls /data/bak/ -l|grep ^d|awk '{print $NF}'|grep ^20|tail -1")
        if [ "${BAKPATH}" != "" ];then
            ssh ${CMASTERNODE} sudo chmod 755 -R /data/bak
            ssh ${OLDMASTERNODE} sudo mkdir -p /data/${MYSQLNAME}-slave
            ssh ${OLDMASTERNODE} sudo chmod -R 777 /data/${MYSQLNAME}-slave
            ssh -t ${OLDMASTERNODE} scp -r ${CMASTERNODE}:/data/bak/${BAKPATH}/* /data/${MYSQLNAME}-slave
            ssh ${OLDMASTERNODE} sudo chown -R 999:999 /data/${MYSQLNAME}-slave
        fi
        #delete old master label
        kubectl label nodes ${OLDMASTERNODE} ToMysql-
        #add slave label
        kubectl label nodes ${OLDMASTERNODE} ToSlave=${MYSQLLABEL} --overwrite
         
        #restore Schedule
        kubectl uncordon ${OLDMASTERNODE}
 
        #delete taint
        #kubectl taint nodes ${OLDMASTERNODE} ToMysql:NoSchedule-
        #kubectl taint nodes ${OLDMASTERNODE} ToMysql:NoExecute-
 
        sleep 10
 
        #restore slave yaml
        kubectl apply -f ${BASEDIR}/${MYSQLNAME}-slave-deploy.yaml
        sleep 5
        kubectl -n ${NS} get po|grep -w ${MYSQLNAME}
        sleep 15
        BINNAME=$(ssh ${OLDMASTERNODE} cat /data/${MYSQLNAME}-slave/xtrabackup_binlog_info|awk '{print $1}')
        BINPOS=$(ssh ${OLDMASTERNODE} cat /data/${MYSQLNAME}-slave/xtrabackup_binlog_info|awk '{print $2}')
        NSLAVENAME=$(kubectl -n ${NS} get po -o wide|grep ${MYSQLNAME}-slave|awk '{print $1}')
        SLAVECMD="CHANGE MASTER TO MASTER_HOST='${MYSQLNAME}-master',MASTER_USER='slave_db',MASTER_PASSWORD='ujmyhn@Sdn1',MASTER_LOG_FILE='${BINNAME}',MASTER_LOG_POS=${BINPOS};start slave;"
        kubectl -n ${NS} exec -it ${NSLAVENAME} -- mysql -u"root" -p"$ROOTPASSWORD" -e "${SLAVECMD}"
        echo "${MYSQLNAME} restore finish"
         
    fi
#done</tmp/mysql-restore.txt
done
#bash mysql-status.sh
echo -e " "
echo "all finished"
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/hanwei666/p/14372285.html