Helm集成Cronjob备份mysql

 2025/07/28 

计算备份数据量

先进行一手全量备份，看看占用空间咋样

1 2	mysqldump --single-transaction --routines --triggers --events \ -u root -pPassword --all-databases > full_backup.sql

一个full备份的sql出来57M，相当于备份一次只需要57M的空间

那么如果每天备份，一个月测试恢复一次，那就需要至少1.7G的空间，考虑到以后可能还会涨，最后搞个30G的pvc

创建需求PVC

{{- if .Values.mysqlBackupCronJob }}
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: mysql-backup-pvc
  namespace: rg-biz
spec:
  storageClassName: nfs-provisioner
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 30Gi
{{- end }}

kubectl get pvc -A
NAMESPACE    NAME               STATUS   VOLUME                                     CAPACITY   ACCESS MODES   STORAGECLASS      AGE
xxxxxx       mysql-backup-pvc   Bound    pvc-7df2d37a-a850-423f-b49a-27c8605c27a3   30Gi       RWO            nfs-provisioner   40m

创建Cronjob

{{- if .Values.mysqlBackupCronJob.enabled }}
apiVersion: batch/v1
kind: CronJob
metadata:
  name: mysql-daily-backup
spec:
  schedule: {{ .Values.mysqlBackupCronJob.schedule }}
  concurrencyPolicy: Forbid
  startingDeadlineSeconds: 600
  timeZone: "Asia/Shanghai"
  jobTemplate:
    spec:
      template:
        spec:
          containers:
          - name: backup
            image: easzlab.io.local:5000/mysql:8.0
            command:
            - /bin/bash
            - -c
            - |
              # sleep 3600 用于测试

              BACKUP_DIR="/backup/$(date +%Y%m%d)"
              mkdir -p $BACKUP_DIR

              databases=$(mysql -u ${MYSQL_USER} -p"${MYSQL_PASSWORD}" -h ${MYSQL_SERVICE} -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema)")
              for db in $databases; do
                mysqldump \
                  -h ${MYSQL_SERVICE} \
                  -u ${MYSQL_USER} \
                  -p"${MYSQL_PASSWORD}" \
                  --single-transaction \
                  --routines \
                  --events \
                  --triggers \
                  --max-allowed-packet=512M \
                  --databases $db > $BACKUP_DIR/$db.sql
              done

              # 保留60天备份
              find /backup -type d -mtime +60 | xargs rm -rf {}

            env:
            - name: MYSQL_SERVICE
              value: arg-mysql-headless
            - name: MYSQL_USER
              valueFrom:
                configMapKeyRef:
                  name: mysql
                  key: MYSQL_USER
            - name: MYSQL_PASSWORD
              valueFrom:
                configMapKeyRef:
                  name: mysql
                  key: MYSQL_PASSWORD
            - name: TZ
              value: Asia/Shanghai
            volumeMounts:
            - name: backup-storage
              mountPath: /backup
          volumes:
          - name: backup-storage
            persistentVolumeClaim:
              claimName: mysql-backup-pvc
          restartPolicy: OnFailure
{{- end }}

helm应用

kubectl get cronjobs.batch -A
NAMESPACE   NAME                 SCHEDULE    SUSPEND   ACTIVE   LAST SCHEDULE   AGE
xxxxxx      mysql-daily-backup   0 3 * * *   False     0        <none>          103s

测试

手动触发测试：
kubectl create job --from=cronjob/mysql-daily-backup manual-backup-$(date +%Y%m%d%H%M) -n rg-biz
job.batch/manual-backup-202507241819 created

ls /data/nfs/rg-biz-mysql-backup-pvc-pvc-7df2d37a-a850-423f-b49a-27c8605c27a3/20250724/
xxx.sql  xxx.sql  xxx.sql  xxx.sql   xxx.sql  xxx.sql
xxx.sql     xxx.sql    xxx.sql    xxx.sql  xxx.sql  xxx.sql

变量文件配置

这个略了，这个部分其实是父子chart的部分

mysql:
  enabled: true
  mysqlBackupCronJob:
    enabled: true
    schedule: "0 14 * * *"
...

备份数据恢复测试

本地起一个测试用mysql：

sudo docker run -d --name my-mysql \
  -e MYSQL_ROOT_PASSWORD=root \
  -e MYSQL_DATABASE=myapp \
  -e MYSQL_USER=appuser \
  -e MYSQL_PASSWORD=apppass \
  -p 3306:3306 \
  -v $(pwd)/mysql-data:/var/lib/mysql \
  mysql:8.0 \
  --character-set-server=utf8mb4 \
  --collation-server=utf8mb4_unicode_ci

247c0aa6e5fea11575fcf90efa9237e68a2881c485a69c8eaf4f400f7c01cea9

# 数据恢复
for sql_file in *.sql; do
  echo "恢复: $sql_file"
  mysql -h127.0.0.1 -uroot -proot < "$sql_file"
done

其中mysql与sys数据库不建议恢复