Automate MySQL/MariaDB Backups on a VPS with Cron

If you're relying on your VPS provider's nightly snapshot as your only backup, you've got a single point of failure. Snapshots protect against a bad update or a botched config change, but they don't give you a portable, restorable copy of just your database — the one thing that changes every minute on a live WordPress, WooCommerce, or custom app. A proper mysqldump routine, scheduled with cron and rotated automatically, closes that gap and costs you about fifteen minutes to set up.

Why File-Level Backups Aren't Enough

Rsync-based backups (copying /var/www or /home) are great for your code and uploads, but a raw copy of MySQL's data directory while the server is running is not a reliable backup. InnoDB tables can be mid-write when the copy happens, and you'll end up with a corrupted or inconsistent dataset the one time you actually need to restore it. mysqldump talks to the database engine directly and produces a consistent, portable .sql file — the same format you'd use to import into phpMyAdmin or a fresh server.

What You'll Need

• Root or sudo SSH access to your VPS
• MySQL or MariaDB already running (this works the same on both)
• 5-10 minutes and a text editor

Step 1: Create a Dedicated Backup User

Don't reuse your root MySQL account in a script. Create one with just enough privilege to dump databases:

mysql -u root -p

CREATE USER 'backupuser'@'localhost' IDENTIFIED BY 'a-strong-random-password';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER, RELOAD, PROCESS ON *.* TO 'backupuser'@'localhost';
FLUSH PRIVILEGES;
EXIT;

RELOAD and PROCESS are needed for --single-transaction to get a clean, non-locking snapshot on InnoDB tables.

Step 2: Store Credentials Safely

Never hardcode a password in the cron command itself — it'll show up in ps aux and cron logs. Put it in a credentials file instead:

nano /root/.mysql_backup.cnf

[client]
user=backupuser
password=a-strong-random-password

chmod 600 /root/.mysql_backup.cnf

Step 3: Write the Backup Script

mkdir -p /var/backups/mysql
nano /root/mysql_backup.sh

BACKUP_DIR="/var/backups/mysql"
DATE=$(date +%F_%H-%M)
CNF="/root/.mysql_backup.cnf"

DATABASES=$(mysql --defaults-extra-file=$CNF -N -e "SHOW DATABASES;" | grep -Ev "^(information_schema|performance_schema|mysql|sys)$")

for DB in $DATABASES; do
  mysqldump --defaults-extra-file=$CNF     --single-transaction --quick --routines --triggers --events     "$DB" | gzip > "$BACKUP_DIR/${DB}_${DATE}.sql.gz"
done

This loops through every real database on the server (skipping MySQL's internal system schemas) and dumps each one into its own compressed file, tagged with the date and time. --single-transaction keeps InnoDB tables consistent without locking the whole database; --routines --triggers --events makes sure stored procedures and scheduled events come along too, which a lot of quick-and-dirty backup scripts forget. Run it with bash rather than marking it executable — that way cron doesn't depend on an interpreter line at the top of the file.

Step 4: Schedule It with Cron

crontab -e

0 2 * * * bash /root/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1

That runs the dump every night at 2 AM and logs any errors. Run it manually once first to check for typos before you trust the schedule:

bash /root/mysql_backup.sh
ls -lh /var/backups/mysql

Step 5: Rotate Old Backups Automatically

Without cleanup, /var/backups/mysql will quietly fill your disk over a few weeks. Add a rotation line to the same script, right after the loop, to delete anything older than 14 days:

find "$BACKUP_DIR" -name "*.sql.gz" -mtime +14 -delete

Adjust the retention window to match how much disk you can spare and how far back you realistically need to roll back. For anything you can't afford to lose, don't stop at local retention — also sync the backup directory to offsite storage (S3, Backblaze B2, or another VPS) with rclone or rsync, so a problem with this VPS doesn't take the backups down with it.

Step 6: Test the Restore — Don't Skip This

A backup you've never restored is a guess, not a backup. Spin up a throwaway database and confirm the dump actually works:

gunzip -c /var/backups/mysql/yourdb_2026-07-30_02-00.sql.gz | mysql -u root -p yourdb_test

If that import completes without errors and the table counts look right, your pipeline is solid. Do this check every time you change PHP, MySQL, or plugin versions — dump formats and character sets can shift under you.

Handling Large Databases

If a single database is several gigabytes, a plain gzip dump can start taking a long time and a lot of CPU. A few adjustments help:

Prevention Checklist

• Confirm the cron job actually ran — check /var/log/mysql_backup.log weekly, or add an email/webhook alert on failure
• Keep at least one backup copy off the VPS itself
• Test a full restore quarterly, not just once at setup
• Monitor /var/backups/mysql disk usage so rotation is actually keeping up
• Rotate the backupuser password if the server is ever compromised

Fifteen minutes of setup now is a lot cheaper than rebuilding a production database from memory later. Once this is in place, snapshots and file backups become what they should be — a second layer, not your only one.

Frequently Asked Questions

Does mysqldump lock my database during the backup?

With --single-transaction on InnoDB tables, no — reads and writes continue normally during the dump. This flag doesn't help MyISAM tables, which still get briefly locked; if you're on MyISAM, schedule backups for your lowest-traffic hours.

How do I restore just one database from these backups?

Unzip and pipe the file straight into MySQL: gunzip -c yourdb_2026-07-30_02-00.sql.gz | mysql -u root -p yourdb. Make sure the target database already exists (CREATE DATABASE yourdb;) before you run it.

Can I use this same approach in cPanel/WHM hosting?

Yes, if you have SSH access on your plan. Without SSH, use WHM's built-in backup configuration (Backup > Configure Backups) to schedule database dumps, or cPanel's cron job manager to run a script under your account instead of root.

How often should I actually run this?

For an active WordPress or WooCommerce site, nightly is a reasonable baseline. If you're processing orders or taking form submissions constantly, consider adding an hourly incremental job using binary log backups on top of the nightly full dump.

My dump file is huge and gzip is slow — what should I change first?

Switch to pigz for multi-core compression, and check whether you're dumping databases you don't actually need daily backups of (staging copies, old test databases). Excluding those from the loop is often the fastest win before you touch compression settings at all.