Script to download a mysql/mariadb backup from a container running in a VM on Proxmox

mysql-container-vm-backup.sh

#!/bin/bash

VM_ID=<VM_ID>
NODE_NAME=<node-name>
RETENTION=30
VM_DIR=/var/tmp
DIR=/backupdir

declare -A CONTAINERS

CONTAINERS[container-name]="root-pw"

for CONTAINER in "${!CONTAINERS[@]}"; do
  EXITED=0

  BACKUP_DIR="${DIR}/${CONTAINER}"
  BACKUP_FILE="mysql_${CONTAINER}_$(date '+%F').databases.sql.xz"
  VM_BACKUP_FILE="${BACKUP_FILE}.base64"

  if [ ! -d "${BACKUP_DIR}" ]; then
    mkdir -p "${BACKUP_DIR}"
  fi

  # Start backup to VM disk
  CMD_PID=$(qm guest exec ${VM_ID} --synchronous 0 -- /bin/sh -c "docker exec ${CONTAINER} mysqldump --all-databases -u root -p${CONTAINERS[${CONTAINER}]} | xz | base64 > ${VM_DIR}/${VM_BACKUP_FILE}" | jq -r .pid)

  # Wait for backup to finish
  while [ ${EXITED} -eq 0 ]; do
    read EXITED EXITCODE ERROR < <(qm guest exec-status ${VM_ID} ${CMD_PID} | jq -r '[.exited, .exitcode // 0, ."err-data" // "None"]|@sh')
    sleep 5
  done

  if [ ${EXITCODE} -ne 0 ]; then
    echo "error occurred taking backup: ${ERROR}"
    exit 1
  fi

  # Download backup
  set -o pipefail
  pvesh get /nodes/cube/${NODE_NAME}/${VM_ID}/agent/file-read --output-format json --file ${VM_DIR}/${VM_BACKUP_FILE} 2>/dev/null | jq -r .content | base64 -d > ${BACKUP_DIR}/${BACKUP_FILE}
  if [ ${?} -ne 0 ]; then
    echo "error occurred downloading backup!"
    exit 1
  fi

  # Test backup
  unxz -t ${BACKUP_DIR}/${BACKUP_FILE}
 
  # Delete temporary file 
  read EXITED EXITCODE ERROR < <(qm guest exec ${VM_ID} --timeout=0 -- rm ${VM_DIR}/${VM_BACKUP_FILE} | jq -r '[.exited, .exitcode // 0, ."err-data" // "None"]|@sh')
  if [ ${EXITCODE} -ne 0 ]; then
    echo "error occurred deleting temporary file: ${ERROR}"
    exit 1
  fi
  
  # Cleanup local backup dir
  find ${BACKUP_DIR} -ctime +${RETENTION} -exec rm {} \; 
done