Step-by-step
-
1
Create the backup directory
Store backups somewhere that is not the web root. Lock it down so only root can read the files.
bashsudo mkdir -p /home/backups sudo chmod 700 /home/backups # Files created inside will inherit this directory's restricted access -
2
Store credentials securely in ~/.my.cnf
Passing the password on the command line puts it in the process list — readable by any user on the system with
ps aux. Store it in~/.my.cnfinstead. On MariaDB with unix_socket auth, you can skip the password entirely.bash# Create /root/.my.cnf (run as root or with sudo su) cat > /root/.my.cnf << "EOF" [mysqldump] user=root password=your_db_password_here EOF # Restrict permissions — only root may read this chmod 600 /root/.my.cnf # On MariaDB with unix_socket auth, the file can be: # [mysqldump] # user=root # (no password line needed — MariaDB authenticates via the OS user) -
3
Write the backup script
--single-transactionis the critical flag — it wraps InnoDB tables in a transaction so the dump is consistent without locking any tables.--quickstreams rows one at a time rather than loading the whole table into memory first.bashcat > /usr/local/bin/backup.sh << "SCRIPT" #!/bin/bash set -euo pipefail DB_NAME="myapp" BACKUP_DIR="/home/backups" DATE=$(date +%F) FILE="${BACKUP_DIR}/db_${DATE}.sql.gz" # Dump with no table locks, stream into gzip ionice -c3 nice -n19 \ mysqldump \ --single-transaction \ --quick \ --skip-lock-tables \ "${DB_NAME}" \ | gzip -9 > "${FILE}" chmod 600 "${FILE}" # Keep only the last 7 days find "${BACKUP_DIR}" -name "*.sql.gz" -mtime +7 -delete echo "[$(date)] Backup complete: ${FILE} ($(du -sh ${FILE} | cut -f1))" SCRIPT chmod +x /usr/local/bin/backup.sh -
4
Test the script manually first
Always run the script manually before scheduling it. Verify the output file exists and is non-zero in size.
bashsudo /usr/local/bin/backup.sh # Confirm the file was created ls -lh /home/backups/ # Peek at the compressed dump without extracting gunzip -c /home/backups/db_$(date +%F).sql.gz | head -20 -
5
Schedule with crontab
Run the backup daily at 02:00 as root. Cron runs in a minimal environment — use absolute paths everywhere in the script (already done above).
bash# Edit root's crontab sudo crontab -e # Add this line: 0 2 * * * /usr/local/bin/backup.sh >> /var/log/backup.log 2>&1 # View scheduled jobs for root sudo crontab -l # Confirm the log captures output sudo tail -f /var/log/backup.log -
6
Test the restore procedure
A backup you have never restored is a backup you do not have. Test on a staging DB, not production.
bash# Restore to an existing database (will overwrite all data) gunzip < /home/backups/db_2026-05-28.sql.gz | mysql myapp # Restore to a new database (safer for testing) mysql -e "CREATE DATABASE myapp_restore;" gunzip < /home/backups/db_2026-05-28.sql.gz | mysql myapp_restore # Verify key tables are present mysql myapp_restore -e "SHOW TABLES; SELECT COUNT(*) FROM users;" # Clean up the test database mysql -e "DROP DATABASE myapp_restore;" -
7
Copy backups off-host with rsync
A backup on the same server as the database is lost if the server dies. Push to a second server over SSH. Run this in the same cron job, after the dump completes.
bash# Add to the end of backup.sh (after the find -delete line): REMOTE_USER="backup" REMOTE_HOST="backup-server.example.com" REMOTE_DIR="/home/backups/myapp/" rsync -az --delete \ -e "ssh -i /root/.ssh/backup_key -o StrictHostKeyChecking=no" \ "${BACKUP_DIR}/" \ "${REMOTE_USER}@${REMOTE_HOST}:${REMOTE_DIR}" echo "[$(date)] Synced to remote: ${REMOTE_HOST}:${REMOTE_DIR}" # Pre-requisites: # 1. Generate a dedicated key: ssh-keygen -t ed25519 -f /root/.ssh/backup_key -N "" # 2. Add the public key to the remote server's authorized_keys # 3. Create the remote directory: ssh user@backup-server mkdir -p /home/backups/myapp -
8
Monitor backup health
Silent failures are the worst kind. Add a size check and an alert so you know within hours if a backup stopped working.
bash# Add to backup.sh after the gzip line: FILE_SIZE=$(stat -c%s "${FILE}") MIN_SIZE=1024 # 1 KB minimum — a valid dump is always larger if [ "${FILE_SIZE}" -lt "${MIN_SIZE}" ]; then echo "ERROR: backup file suspiciously small (${FILE_SIZE} bytes)" >&2 # Send an email alert (requires mailutils) echo "Backup may have failed. Check ${FILE}" | mail -s "ALERT: backup failure" admin@example.com exit 1 fi # Check the last backup from the command line any time ls -lht /home/backups/ | head -3
Tips & gotchas
- Back up <strong>all</strong> databases at once with <code>mysqldump --all-databases</code> if the server runs multiple projects — one cron job, one file.
- Use <code>--routines --triggers --events</code> flags to include stored procedures, triggers, and scheduled events in the dump. Omitting them is a common gotcha.
- Prefer <code>--set-gtid-purged=OFF</code> when dumping from a replica to avoid GTID errors on restore.
- Test your restore quarterly — not just that the file exists, but that the restored database actually serves your app correctly.
- Cloud storage options: <code>rclone copy /home/backups/ s3:mybucket/db/</code> works out-of-the-box with S3, Backblaze B2, and Google Cloud Storage. Install rclone once, configure once.
Wrapping up
You now have a daily automated backup: locked-down credentials, no table locks, gzip-compressed, retained for 7 days, and optionally mirrored off-host. More importantly, you have tested the restore — which is what separates a real backup strategy from a false sense of security. Schedule a calendar reminder to run the restore test again in 3 months.