使用 k8s 的 Job 或 CronJob 备份 MySQL 数据库

2023-10-18

字数统计: 1.2k字 | 阅读时长≈ 6分

使用 k8s 的 Job 备份 MySQL 数据库

由于阿里云 RDS MYSQL 数据库没有开放外网连接权限，然后生产环境又没有安装 MYSQL 数据库，缺少 mysqldump 命令，所有决定使用 kubernetes 起一个 job 执行备份操作。

k8s 的 Job 负责批量处理短暂的一次性任务 (short lived one-off tasks)，即仅执行一次的任务，它保证批处理任务的一个或多个Pod成功结束。这里刚好可以用来备份 MYSQL 数据库

创建 job 文件 mysqldump-job.yaml，内容如下

apiVersion: batch/v1
kind: Job
metadata:
  name: mysql-backup
spec:
  template:
    spec:
      affinity:
        nodeAffinity:
          requiredDuringSchedulingIgnoredDuringExecution:
            nodeSelectorTerms:
            - matchExpressions:
              - key: kubernetes.io/hostname
                operator: In
                values:
                - k8s-prod-master01
      tolerations:
      - key: "groups"
        operator: "Equal"
        effect: "NoExecute"
        value: "vbaas"
      - key: "role"
        operator: "Equal"
        effect: "NoExecute"
        value: "master"
      containers:
        - name: mysqldump
          image: nacos/nacos-mysql-master:latest
          imagePullPolicy: "IfNotPresent"
          command: 
          - "/bin/sh"
          - "-c"
          - "/data/mysql/shell/mysqldump.sh"
          volumeMounts:
            - mountPath: "/etc/localtime"
              name: "localtime"
            - mountPath: "/data/mysql/shell"
              name: mysql-backup-scripts
            - mountPath: "/data/mysql/backup"
              name: mysql-backup-data
            - mountPath: "/etc/resolv.conf"
              name: resolv
      volumes:
        - name: "mysql-backup-scripts"
          hostPath:
            path: "/data/mysql/shell"
        - name: "mysql-backup-data"
          hostPath:
            path: "/data/mysql/backup"
        - hostPath:
            path: "/usr/share/zoneinfo/Asia/Shanghai"
          name: "localtime"
        - name: resolv
          hostPath:
            path: /etc/resolv.conf 
      restartPolicy: Never
  backoffLimit: 2

创建数据备份存放目录以及脚本目录
1
mkdir -p /data/mysql/{backup,shell}

创建 mysqldump.sh 脚本文件，内容如下

#!/bin/bash

# 保存备份个数
number=3

# 备份保存路径
backup_dir=/data/mysql/backup

# 日期
datetime=`date +%Y%m%d`

# 备份工具
mysqldump=/usr/bin/mysqldump

# 用户名
username='root'

# 密码
password='xawdadasfadasdqadad'

# 主机地址
host="vonebaas-prod.mysql.zhangbei.rds.aliyuncs.com"

# 将要备份的数据库
database_name="nacos \
vbaas_c_platform_prod \
vbaas_c_custom_core_prod \
vbaas_c_open_core_prod \
vchain_order_prod \
vbaas_s_platform_prod \
vbaas_s_platform_node_prod \
vbaas_c_platform_explorer_prod \
vchain_base_msg_prod \
xxl_job_vbaas4_prod \
vbaas_c_ops_prod \
vbaas_s_platform_explorer_prod \
vchain_core_gateway_prod \
vchain_core_xxl_prod \
vbaas_c_cross_prod \
chainmaker_ca_prod \
chainmaker_explorer_prod"

# 备份文件名称
backupFileName="allDatabases-${datetime}.sql"

# 备份所有数据库
#$tool -u $username -p$password -hmysql-master -P3306 --databases $database_name > $backup_dir/$database_name-$dd.sql
${mysqldump} -u ${username} \
  -p${password} \
  -h${host} \
  -P3306 \
  --triggers \
  --routines \
  --events \
  --single-transaction \
  --databases ${database_name} \
  --ignore-table=vchain_core_xxl_prod.xxl_job_log \
  --ignore-table=xxl_job_vbaas4_prod.xxl_job_log > $backup_dir/${backupFileName}

#写创建备份日志
echo "create $backup_dir/${backupFileName}" >> $backup_dir/log.txt

#找出需要删除的备份
delfile=`ls -l -crt  $backup_dir/*.sql | awk '{print $9 }' | head -1`

#判断现在的备份数量是否大于$number
count=`ls -l -crt  $backup_dir/*.sql | awk '{print $9 }' | wc -l`
if [ $count -gt $number ]
then
  rm $delfile  //删除最早生成的备份只保留number数量的备份
  #写删除文件日志
  echo "delete $delfile" >> $backup_dir/log.txt
fi

执行以下命令创建 Job
1
kubectl create -f mysqldump-job.yaml

查看备份好的文件

# ls -lh /data/mysql/backup/
total 95M
-rw-r--r-- 1 root root 95M Feb  2 16:25 allDatabases-20230202.sql
-rw-r--r-- 1 root root  52 Feb  2 16:25 log.txt

查看需要备份的数据库是否都正常备份

# grep -R "CREATE DATABASE" /data/mysql/backup/allDatabases-20230202.sql 
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `nacos` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `vbaas_c_platform_prod` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `vbaas_c_custom_core_prod` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `vbaas_c_open_core_prod` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `vchain_order_prod` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `vbaas_s_platform_prod` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `vbaas_s_platform_node_prod` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `vbaas_c_platform_explorer_prod` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `vchain_base_msg_prod` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `xxl_job_vbaas4_prod` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `vbaas_c_ops_prod` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `vbaas_s_platform_explorer_prod` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `vchain_core_gateway_prod` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `vchain_core_xxl_prod` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `vbaas_c_cross_prod` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `chainmaker_ca_prod` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `chainmaker_explorer_prod` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

使用 CronJob 备份 MySQL

CronJob 资源清单如下

apiVersion: batch/v1beta1
kind: CronJob
metadata:
  name: mysqldump
spec:
  jobTemplate:
    spec:
      completions: 1
      template:
        spec:
          affinity:
            nodeAffinity:
              requiredDuringSchedulingIgnoredDuringExecution:
                nodeSelectorTerms:
                - matchExpressions:
                  - key: kubernetes.io/hostname
                    operator: In
                    values:
                    - k8s-prod-master01
          tolerations:
          - key: "groups"
            operator: "Equal"
            effect: "NoExecute"
            value: "vbaas"
          - key: "role"
            operator: "Equal"
            effect: "NoExecute"
            value: "master"
          restartPolicy: Never
          volumes:
            - name: "mysql-backup-scripts"
              hostPath:
                path: "/data/mysql/shell"
            - name: "mysql-backup-data"
              hostPath:
                path: "/data/mysql/backup"
            - name: "localtime"
              hostPath:
                path: "/etc/localtime"
            - name: resolv
              hostPath:
                path: /etc/resolv.conf  
          containers:
            - name: mysqldump
              image: nacos/nacos-mysql-master:latest
              volumeMounts:
                - mountPath: "/etc/localtime"
                  name: "localtime"
                - mountPath: "/data/mysql/shell"
                  name: mysql-backup-scripts
                - mountPath: "/data/mysql/backup"
                  name: mysql-backup-data
                - mountPath: "/etc/resolv.conf"
                  name: resolv
              command:
                - "/bin/sh"
                - "-c"
                - "/data/mysql/shell/mysqldump.sh"
  schedule: "50 01 * * *"