Automatic MySQL backups
mysqldump, cron and encryption, from script to restore.
Automating MySQL or MariaDB backups takes a few lines of mysqldump and cron. Here is the full method, then its limits in production.
The code works with the standard MySQL or MariaDB client.
1. Export a database with mysqldump
mysqldump exports a database to an SQL file. Compress it on the fly to save space, and use an options file so the password never appears on the command line.
# ~/.my.cnf (chmod 600) holds the credentials:
# [client]
# user=backup
# password=secret
mysqldump --single-transaction --quick my_db | gzip > /var/backups/my_db_$(date +%F).sql.gz
--single-transaction avoids locking InnoDB tables during the export.
2. Automate with cron
Put the command in a script with a retention rule, then schedule it.
#!/usr/bin/env bash
set -euo pipefail
DEST=/var/backups/mysql
mkdir -p "$DEST"
mysqldump --single-transaction --quick my_db | gzip > "$DEST/my_db_$(date +%F_%H%M).sql.gz"
find "$DEST" -name '*.sql.gz' -mtime +7 -delete
In crontab -e: 30 2 * * * /usr/local/bin/backup-mysql.sh (every day at 2:30am).
3. Encrypt and move off-site
Encrypt the dump with age, then copy it off the server, to an S3 bucket at another provider or in another region.
age -r age1example... -o my_db.sql.gz.age my_db.sql.gz
aws s3 cp my_db.sql.gz.age s3://my-bucket/mysql/
4. Restore
To restore, decrypt, decompress, then import into a separate test database, never into production.
age -d -i my_key.txt my_db.sql.gz.age | gunzip | mysql my_db_restored
The limits of mysqldump + cron
- A cron that fails does so silently: you only find out the day you need to restore.
- No alert if the server is off, full, or if mysqldump crashes.
- Retention, encryption, off-site copy and key rotation are all yours to maintain.
- On large databases, an unoptimized mysqldump can slow production down.
- A backup you never tested is not a backup.
Do it in 5 minutes with rlbk
rlbk installs a lightweight agent on your server, schedules your MySQL and MariaDB backups, encrypts them with age on the spot, sends them to rlbk or to your own S3 bucket, and alerts you the moment one fails.
You test your restores in an isolated environment, without ever touching production.