欢迎来到 ：要要发发发发发发发发
特别分享： 寿比南山网 一个超赞的个人博客
特别分享： 亦鸣客栈 一个超赞的个人博客

AKS 中 MySQL/Mongodb 备份回滚

216次阅读
没有评论

MySQL

方法一

备份(注入脚本)

向mysql pod中注入备份脚本并执行备份

kubectl cp -c mysql backup-aksmysql-container.sh system-stream-pre/mysql-0:tmp/

kubectl exec -it mysql-0 -n system-stream-pre -c mysql -- bash /tmp/backup-aksmysql-container.sh

附上脚本内容

#!/bin/bash
#author Weldon
#USERNAME=$ENV_MYSQL_USER
#PASSWORD=$ENV_MYSQL_PASSWORD
#HOST="$ENV_MYSQL_HOST.system-stream-$BITBUCKET_DEPLOYMENT_ENVIRONMENT.svc.cluster.local"
#PORT=$ENV_MYSQL_PORT
#BACKDIR=/home/backup/system-stream-$BITBUCKET_DEPLOYMENT_ENVIRONMENT

#manul
USERNAME=root
PASSWORD=xxxxxxxx
#HOST="mysql-headless.system-stream-pre.svc.cluster.local"
HOST=localhost
PORT=3306
BACKDIR=/tmp/system-stream-pre

DATE=`date +%Y-%m-%d`
MYSQL=/opt/bitnami/mysql/bin/mysql
MYSQLDUMP=/opt/bitnami/mysql/bin/mysqldump

function bakAllTables(){
    MYSQLDUMP_LIST=$(${MYSQL} -u${USERNAME} -p${PASSWORD} -h${HOST} -P${PORT} -e "show databases"| grep -Evi "database|infor|perfor|mysql|sys")
    echo -e "--------------------$(date '+%Y-%m-%d %H:%M:%S') Begin to backup all tables--------------------"
    for DBNAME in ${MYSQLDUMP_LIST}
    do
        TABLE="$(${MYSQL} -u${USERNAME} -p${PASSWORD} -h${HOST} -P${PORT} -e "use $DBNAME;show tables;"|sed '1d')"
        MYDIR=${BACKDIR}/allTables/${DATE}/${DBNAME}
        for tname in $TABLE
            do
                [ ! -d $MYDIR ] && mkdir -p $MYDIR
                $MYSQLDUMP -u${USERNAME} -p${PASSWORD} -h${HOST} -P${PORT}  $DBNAME $tname | gzip > $MYDIR/${DBNAME}_${tname}_${DATE}.sql.gz
                echo -e "$(date '+%Y-%m-%d %H:%M:%S')  Table: \e[0;35m${tname}\e[0m for Database: \e[0;35m${DBNAME}\e[0m, has been backup successfully"
            done
    done
    echo -e "--------------------$(date '+%Y-%m-%d %H:%M:%S') All tables backup over--------------------"
}

function bakAllDatabases(){
    MYSQLDUMP_LIST=$(${MYSQL} -u${USERNAME} -p${PASSWORD} -h${HOST} -P${PORT} -e "show databases"| grep -Evi "database|infor|perfor|mysql|sys")
    echo -e "--------------------$(date '+%Y-%m-%d %H:%M:%S') Begin to backup all databases--------------------"
    MYDIR=${BACKDIR}/allDatabases/${DATE}
    [ ! -d $MYDIR ] && mkdir -p $MYDIR
    for DBNAME in ${MYSQLDUMP_LIST}
    do
        $MYSQLDUMP -u${USERNAME} -p${PASSWORD} -h${HOST} -P${PORT} --single-transaction --master-data=2 --set-gtid-purged=off -F --triggers  --routines --events $DBNAME  | gzip > $MYDIR/${DBNAME}_${DATE}.sql.gz
        echo -e "$(date '+%Y-%m-%d %H:%M:%S')  Database: \e[0;35m${DBNAME}\e[0m, has been backup successfully"
    done
    echo -e "--------------------$(date '+%Y-%m-%d %H:%M:%S') All databases backup over--------------------"
}

function main(){
    bakAllTables
    bakAllDatabases
    echo -e "--------------------$(date '+%Y-%m-%d %H:%M:%S') All done--------------------\n"
}

main

再将容器中的备份数据导出到本地

# 从mysql pod 中导出单个文件
kubectl cp -c mysql system-stream-pre/mysql-0:tmp/all-databases-20220613.sql /home/weldon/20220613-bak/mysql/all-databases-20220613.sql

# 从mysql pod 中批量（递归）导出某一目录下 (/tmp/system-stream-pre)所有文件
kubectl exec -n system-stream-pre mysql-0 -c mysql -- tar cf - tmp/system-stream-pre | tar xf - -C /home/weldon/20220613-bak/mysql

恢复（pre环境数据同步到dev环境）

递归导入本地/home/weldon/20220613-bak/mysql/tmp/system-stream-pre/allDatabases/2022-06-13/下所有文件

上一步使用tar cf命令将容器内/tmp/system-stream-pre 目录整体压缩打包后，解压到/home/weldon/20220613-bak/mysql 目录下，所以恢复时需要将/home/weldon/20220613-bak/mysql/tmp/system-stream-pre/allDatabases/2022-06-13/目录下的sql文件导入容器内

[root@control-plane allDatabases]# pwd
/home/weldon/20220613-bak/mysql/tmp/system-stream-pre/allDatabases

在namespace=system-stream-dev1 的mysql容器内创建 /tmp/system-stream-pre目录
并将2022-06-13/目录下的所有sql文件导入容器内
再手动录入数据

kubectl exec -it mysql-0 -n system-stream-dev1 -c mysql -- mkdir -p /tmp/system-stream-pre

tar cf - 2022-06-13/ | kubectl exec -i -n system-stream-dev1 mysql-0 -c mysql -- tar xf - -C tmp/system-stream-pre

gunzip /tmp/2022-06-13/*.gz

mysqldump -uroot -pbite --databases bitestream_server_nacos < /tmp/2022-06-13/bitestream_server_nacos_2022-06-13.sql

恢复（脚本）

附上脚本内容


#!/bin/bash
#author Weldon
#USERNAME=$ENV_MYSQL_USER
#PASSWORD=$ENV_MYSQL_PASSWORD
#HOST="$ENV_MYSQL_HOST.system-stream-$BITBUCKET_DEPLOYMENT_ENVIRONMENT.svc.cluster.local"
#PORT=$ENV_MYSQL_PORT
#BACKDIR=/home/backup/system-stream-$BITBUCKET_DEPLOYMENT_ENVIRONMENT
#manul
USERNAME=root
PASSWORD=$MYSQL_ROOT_PASSWORD
# MYSQL_ROOT_PASSWORD=fkbwUhFgnj
#HOST="mysql-headless.system-stream-pre.svc.cluster.local"
HOST=localhost
PORT=3306
BACKDIR=/tmp/system-stream-pre
MYSQL=/opt/bitnami/mysql/bin/mysql
MYSQLDUMP=/opt/bitnami/mysql/bin/mysqldump
function restoreAllTables(){
    if [[ ! -d ${BACKDIR}/allTables/${DATE} || `ls ${BACKDIR}/allTables/${DATE} | wc -l` -lt 1 ]]
    then
        echo "${BACKDIR}/allTables/${DATE} is not existed, please put backup datas first!"
        exit 1
    fi
    echo -e "--------------------$(date '+%Y-%m-%d %H:%M:%S') Begin to restore all tables--------------------"
    for DBNAME in $(ls ${BACKDIR}/allTables/${DATE})
    do
        for table_bak_file in $(ls ${BACKDIR}/allTables/${DATE}/$DBNAME)
        do
            if [[ $DBNAME = "bitestream_server_nacos" || $DBNAME = "bitestream_server_seata" ]]
            then
                #special
                tname=`echo $table_bak_file | cut -d "-" -f 2`
            else
                #conventional
                tname=`echo $table_bak_file | awk -F "-" '{print $4}'`
            fi
            gunzip < ${BACKDIR}/allTables/${DATE}/$DBNAME/$table_bak_file | ${MYSQL} -u${USERNAME} -p${PASSWORD} -h${HOST} -P${PORT} $DBNAME
            if [ $? -eq 0 ]
            then
                echo -e "$(date '+%Y-%m-%d %H:%M:%S')  Table: \e[0;35m${tname}\e[0m for Database: \e[0;35m${DBNAME}\e[0m, has been restore successfully"
            else
                echo -e "$(date '+%Y-%m-%d %H:%M:%S')  Table: \e[0;35m${tname}\e[0m for Database: \e[0;35m${DBNAME}\e[0m, restore failed with error"  
            fi
        done
    done
    echo -e "--------------------$(date '+%Y-%m-%d %H:%M:%S') All tables restore over--------------------"
}
function restoreAllDatabases(){
    if [[ ! -d ${BACKDIR}/allDatabases/${DATE} || `ls ${BACKDIR}/allDatabases/${DATE} | wc -l` -lt 1 ]]
    then
        echo "${BACKDIR}/allDatabases/${DATE} is not existed, please put backup datas first!"
        exit 1
    fi
    echo -e "--------------------$(date '+%Y-%m-%d %H:%M:%S') Begin to restore all databases--------------------"
    for db_bak_file in $(ls ${BACKDIR}/allDatabases/${DATE})
    do
        if [[ -n `echo $db_bak_file | grep "bitestream_server_nacos"` || -n `echo $db_bak_file | grep "bitestream_server_seata"` ]]
        then
            #special
            DBNAME=`echo $db_bak_file | cut -d "-" -f 1`
        else
            #conventional
            DBNAME=`echo $db_bak_file | awk -F "-" '{print $1"-"$2"-"$3}'`
        fi
        gunzip < ${BACKDIR}/allDatabases/${DATE}/$db_bak_file | ${MYSQL} -u${USERNAME} -p${PASSWORD} -h${HOST} -P${PORT} $DBNAME
        if [ $? -eq 0 ]
        then
            echo -e "$(date '+%Y-%m-%d %H:%M:%S')  Database: \e[0;35m${DBNAME}\e[0m, has been restore successfully"
        else
            echo -e "$(date '+%Y-%m-%d %H:%M:%S')  Database: \e[0;35m${DBNAME}\e[0m, restore failed with error"
        fi
    done
    echo -e "--------------------$(date '+%Y-%m-%d %H:%M:%S') All databases restore over--------------------"
}
function main(){
    bakAllTables
    bakAllDatabases
    echo -e "--------------------$(date '+%Y-%m-%d %H:%M:%S') All done--------------------\n"
}
function help () {
  echo "Accepted cli arguments are:"
  echo -e "\t[--help|-h]                        ->> prints this help"
  echo -e "\t[--database|-d YYYY-mm-dd]        ->> restore all database with backup datas in $BACKDIR/allDatabases/{YYYY-mm-dd} like \$DatabaseName-{YYYY-mm-dd}.sql.gz"
  echo -e "\t\te.g. $0 --database 2022-01-01 or $0 -d 2022-01-01"
  echo -e "\t[--table|-t YYYY-mm-dd]           ->> restore all database with backup datas in ${BACKDIR}/allTables/{YYYY-mm-dd}/\$DatabaseName like \$DatabaseName-\$TableName-{YYYY-mm-dd}.sql.gz"
  echo -e "\t\te.g. $0 --table 2022-01-01 or $0 -t 2022-01-01"
  exit 0
}
function notice(){
    echo -e "Please input parameter like --database|-d or --table|-t \nAlso can see usage --help|-h for detail"
    exit 1
}
function main(){
    while [[ $# -gt 0 ]]
    do
        case $1 in
            '--database'|-d)
                DATE="$2"
                restoreAllDatabases
                shift
                shift
                ;;
            '--table'|-t)
                DATE="$2"
                restoreAllTables
                shift
                shift
                ;;
            '--help'|-h)
                help
                ;;
            *)
                echo -e "$* is Unsupport parameters..."
                notice
                ;;
        esac
    done
}
if [[ $# -lt 1 ]]
then
    notice
elif [[ $# -eq 1 ]]
then
    case $1 in
        '--help'|-h)
                help
                ;;
        *)
            echo -e "$* is Unsupport parameters..."
            notice
            ;;
    esac
elif [[ $# -gt 2 ]]
then
    notice
fi
main "$@"

向pod中上传用于恢复的备份数据，应当在allDatabases和 allTables 上一级目录下操作

[root@control-plane tmp]# pwd
/home/weldon/20220613-bak/mysql/tmp
[root@control-plane tmp]# ll
total 0
drwxr-xr-x 4 1001 root 43 Jun 13 13:48 system-stream-pre
[root@control-plane system-stream-pre]# tar cf - system-stream-pre/ | kubectl exec -i -n weldon mysql-1655204082-0 -c mysql -- tar xf - -C tmp/
AKS 中 MySQL/Mongodb 备份回滚

获取mysql用户密码

kubectl get secret –namespace weldon mysql-1653878152 -o jsonpath=”{.data.mysql-root-password}” | base64 –decode

将mysql数据恢复脚本上传进mysql容器

kubectl cp -c mysql restore-aksmysql-container.sh system-stream-pre/mysql-0:tmp/

#个人测试环境
kubectl cp -c mysql restore-aksmysql-container.sh weldon/mysql-1655204082-0:tmp/

执行恢复脚本

kubectl exec -it mysql-0 -n system-stream-pre -c mysql -- bash /tmp/restore-aksmysql-container.sh -d 2022-06-13

#个人测试环境
kubectl exec -it mysql-1655204082-0 -n weldon -c mysql -- bash /tmp/restore-aksmysql-container.sh -d 2022-06-14

方法二

备份（单次命令）

交互式直接存储备份数据到本地

#pre环境
kubectl exec -it mysql-0 -n system-stream-pre -c mysql -- mysqldump -u root -pxxxxxxxx --all-databases > /home/weldon/20220613-bak/mysql/all-databases-20220613.sql

#dev1环境
kubectl exec -it mysql-0 -n system-stream-dev1 -c mysql -- mysqldump -u root -pxxxxxxxx --all-databases > /home/weldon/20220613-bak/mysql/all-databases-20220613-dev1.sql

恢复

递归导入本地/home/weldon/20220613-bak/mysql下所有文件

kubectl cp -c mysql /home/weldon/20220613-bak/mysql/all-databases-20220613.sql system-stream-dev1/mysql-0:tmp/all-databases-20220613.sql
#两种进入容器方式
- k9s 进入容器
- kubectl exec -it mysql-0 -n system-stream-dev1 -c mysql -- /bin/bash

容器内：

mysql -uroot -p
>$password

source tmp/all-databases-20220613.sql;

下面这种方式可能触发导入失败

kubectl exec -it mysql-0 -n system-stream-dev1 -c mysql — mysqldump -u root -pbite –all-databases < /home/weldon/20220613-bak/mysql/all-databases-20220613.sql

Mongodb

备份Mongodb pre数据

kubectl exec -it mongodb-0 -n system-stream-pre -c mongodb -- mongodump -u "root" -p "xxxxxxxx" --authenticationDatabase "admin" -d bitestream-pre -o /tmp

导出mongo pre备份数据到本地/home/weldon/20220613-bak/mongodb/

kubectl cp -c mongodb system-stream-pre/mongodb-0:tmp/bitestream-pre /home/weldon/20220613-bak/mongodb/

同步Mongodb pre数据到Mongodb dev1

备份mongo dev1数据

kubectl exec -it mongodb-0 -n system-stream-pre -c mongodb -- mongodump -u "root" -p "xxxxxxxx" --authenticationDatabase "admin" -d bitestream-dev1 -o /tmp/bitestream-dev1

/tmp/bitestream-dev1为容器内目录

Mongodb pre数据导入Mongodb dev1

kubectl exec -it mongodb-0 -n system-stream-pre -c mongodb -- mongorestore -u "root" -p "xxxxxxxx" --authenticationDatabase "admin" -d bitestream-dev1 --drop --dir /tmp/bitestream-pre

备份后恢复数据遇到的坑

使用mysql Administrator工具备份后，原表中的json字段内容被格式化成binary，导致恢复数据时插入失败

解决办法：

在使用工具备份的同时，再使用 mysql 容器中自带的mysqldump命令备份一遍
而且本次故障，最终也是通过使用mysqldump的数据成功恢复

正文完
AKS bash k8s
发表至： Mongodb MySQL
2024-09-23
 1
weldonwang
版权声明：本站原创文章，由 weldonwang 于2024-09-23发表，共计10185字。
转载说明：除特殊说明外本站文章皆由CC-4.0协议发布，转载请注明出处。
AKS 中 MySQL/Mongodb 备份回滚
MySQL 故障记录
评论（没有评论）

近期文章

近期评论

归档

分类目录