ioagel/mysql-rsync repository overview

apiVersion: batch/v1
kind: CronJob
metadata:
  name: mysql-backup-job
spec:
  # take a backup every day at 00:00
  schedule: "0 0 * * *"
  jobTemplate:
    spec:
      template:
        spec:
          restartPolicy: OnFailure
          containers:
            - name: mysql-backup
              image: ioagel/mysql-rsync:8.0
              command: [ "/scripts/backup_mysql.sh" ]
              env:
                - name: MYSQL_HOST
                  value: mysql-cluster
                - name: MYSQL_PORT
                  value: "6447"
                - name: MYSQL_USER
                  valueFrom:
                    secretKeyRef:
                      key: rootUser
                      name: mysql-cluster-secret
                - name: MYSQL_PASS
                  valueFrom:
                    secretKeyRef:
                      key: rootPassword
                      name: mysql-cluster-secret
                - name: MYSQL_DB
                  valueFrom:
                    secretKeyRef:
                      key: database
                      name: mysql-cluster-secret
                - name: SSH_USER
                  valueFrom:
                    secretKeyRef:
                      key: ssh-user
                      name: mysql-cluster-secret
                - name: BACKUP_HOST
                  valueFrom:
                    secretKeyRef:
                      key: backup-host
                      name: mysql-cluster-secret
                - name: REMOTE_BACKUP_DIR
                  valueFrom:
                    secretKeyRef:
                      key: remote-backup-dir
                      name: mysql-cluster-secret
              volumeMounts:
                - name: backup-script
                  mountPath: /scripts
                  readOnly: true
                - mountPath: /etc/ssh-key
                  name: ssh-private-key
                  readOnly: true
          volumes:
            - name: backup-script
              configMap:
                name: backup-script-cm
                defaultMode: 0755
            - name: ssh-private-key
              secret:
                secretName: mysql-cluster-secret
                defaultMode: 0600
                items:
                  - key: ssh-private-key
                    path: id_rsa

apiVersion: v1
kind: ConfigMap
metadata:
  name: backup-script-cm
data:
  backup_mysql.sh: |
    #!/bin/sh
    
    # Variables
    # prepare mysql options file so that we do not have password warning in output
    cat <<EOF >> my.cnf
    [client]
    host = "$MYSQL_HOST"
    user = "$MYSQL_USER"
    password = "$MYSQL_PASS"
    port = "$MYSQL_PORT"
    EOF
    mysql_opts="--defaults-file=my.cnf"
    ssh_opts="-i /etc/ssh-key/id_rsa -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null -o LogLevel=ERROR"

    # Date.
    CMD_DATE=/bin/date
    YEAR="$($CMD_DATE +%Y)"
    MONTH="$($CMD_DATE +%m)"
    DAY="$($CMD_DATE +%d)"
    TIME="$($CMD_DATE +%H.%M.%S)"
    TIMESTAMP="$YEAR.$MONTH.$DAY-$TIME"
    
    ROOT_BACKUP_DIR=/mysql-dumps
    DB_BACKUP_DIR="$ROOT_BACKUP_DIR/$YEAR/$MONTH"

    # Check and create directories.
    [ ! -d "$DB_BACKUP_DIR" ] && mkdir -p "$DB_BACKUP_DIR" 2>/dev/null
    
    output_file="$DB_BACKUP_DIR/$MYSQL_DB-$TIMESTAMP.sql.xz"
    
    # execute backup with xz compression
    mysqldump "$mysql_opts" \
      --set-gtid-purged=OFF --column-statistics=0 --single-transaction \
      --triggers --routines --events "$MYSQL_DB" | xz > "$output_file"
    
    rsync -r -e "ssh $ssh_opts" "$ROOT_BACKUP_DIR"/* "$SSH_USER"@"$BACKUP_HOST":"$REMOTE_BACKUP_DIR"