This page documents the logical backup approach for the MySQL server running in the prod-scentiadb LXC on proxmox1.
NAS export: configured on the NAS (e.g., /volume1/proxmox/mysql-backups)
Proxmox host mount (example):
/mnt/mysql-backupsLXC bind-mount (from host to container):
Proxmox container config (pct config 200):
mp0: /mnt/mysql-backups,mp=/mnt/mysql-backups
Inside prod-scentiadb container:
/mnt/mysql-backups/backupsConfirmed contents example:
root@prod-scentiadb:~# ls -l /mnt/mysql-backups
total 0
drwxrwxrwx 1 nobody nogroup 0 Nov 28 18:26 backups
root@prod-scentiadb:~# ls -lh /mnt/mysql-backups/backups/test.sql
-rwxrwxrwx 1 nobody nogroup 14G Nov 28 20:05 /mnt/mysql-backups/backups/test.sql
A dedicated MySQL user is used for backups:
backup@%mysqldump for all databases.SHOW GRANTS FOR 'backup'@'%';
Returns something similar to:
GRANT SELECT, RELOAD, PROCESS, LOCK TABLES ON *.* TO `backup`@`%`;
GRANT FLUSH_TABLES ON *.* TO `backup`@`%`;
🔒 The actual password for this account is not stored here. It is recorded in your password manager.
A custom backup script is installed at:
/usr/local/bin/mysql-backup.shBelow is a clean reference version of the script that matches the working behaviour we validated (no --master-data, using backup user, writing to NFS).
#!/bin/bash
#
# MySQL logical backup for prod-scentiadb
# Writes dumps to /mnt/mysql-backups/backups on NAS-backed storage.
set -euo pipefail
BACKUP_DIR="/mnt/mysql-backups/backups"
LOG_FILE="/var/log/mysql-backup.log"
MYSQL_USER="backup"
MYSQL_PASSWORD="CHANGEME_BACKUP_PASSWORD" # store the real value in your password manager
MYSQL_OPTS="--single-transaction --routines --events --hex-blob --set-gtid-purged=OFF"
TIMESTAMP="$(date +%F_%H-%M-%S)"
BACKUP_FILE="${BACKUP_DIR}/mysql-${TIMESTAMP}.sql"
mkdir -p "${BACKUP_DIR}"
{
echo "[$(date --iso-8601=seconds)] Starting MySQL backup..."
echo "Backup file: ${BACKUP_FILE}"
mysqldump ${MYSQL_OPTS} -u "${MYSQL_USER}" -p"${MYSQL_PASSWORD}" --all-databases > "${BACKUP_FILE}"
echo "[$(date --iso-8601=seconds)] Backup finished successfully."
echo "[$(date --iso-8601=seconds)] Cleaning up old backups (older than 14 days)..."
find "${BACKUP_DIR}" -type f -name "mysql-*.sql" -mtime +14 -print -delete || true
echo "[$(date --iso-8601=seconds)] Cleanup complete."
} >> "${LOG_FILE}" 2>&1
✅ This version avoids
--master-datato keep privileges simple and to match the working configuration you already tested.
Make sure the script is executable:
chmod 700 /usr/local/bin/mysql-backup.sh
And ensure the log file is writable:
touch /var/log/mysql-backup.log
chown root:root /var/log/mysql-backup.log
chmod 644 /var/log/mysql-backup.log
To run the backup daily at 02:00, create /etc/cron.d/mysql-backup with:
0 2 * * * root /usr/local/bin/mysql-backup.sh
Then reload cron (or wait for cron to pick it up automatically).
To manually test the backup end-to-end from inside prod-scentiadb:
/usr/local/bin/mysql-backup.sh
Then verify:
mysql-YYYY-MM-DD_HH-MM-SS.sql file exists in /mnt/mysql-backups/backups./var/log/mysql-backup.log contains no errors and shows a successful run.For a full logical restore into an empty MySQL instance:
mysql -u root -p < /path/to/mysql-YYYY-MM-DD_HH-MM-SS.sql
For partial restores, you can extract specific databases or tables from the dump using text tools or mysqldump with appropriate filters in future backups.