MySQL Backup and Recovery: mysqldump and Scheduled Backups

Your database is often the single most irreplaceable thing on a server. You can reinstall the OS and redeploy your app, but lost business data rarely comes back. This guide walks through logical backups with mysqldump on an Ubuntu/Debian server (VPS), and how to put them on autopilot.

Dumping with mysqldump

mysqldump ships with the MySQL/MariaDB client. It writes your tables out as plain SQL, which you replay to restore.

# Dump a single database
mysqldump -u root -p mydb > mydb.sql

# Dump every database
mysqldump -u root -p --all-databases > all.sql

# Dump just one table
mysqldump -u root -p mydb users > users.sql

Add --single-transaction

The danger with live backups is that data keeps changing mid-dump, leaving you with an inconsistent snapshot. For InnoDB tables, --single-transaction reads everything inside one transactional snapshot, giving you a consistent view without locking the tables:

mysqldump -u root -p --single-transaction --quick mydb > mydb.sql

--quick streams rows one at a time instead of buffering a whole table in memory, which helps with large tables. Note that --single-transaction only works for InnoDB; if you still have MyISAM tables, consistency isn't guaranteed.

Restoring data

Restoring simply feeds the SQL back into mysql. Create the target database first:

mysqladmin -u root -p create mydb
mysql -u root -p mydb < mydb.sql

A dump made with --all-databases already contains the CREATE DATABASE statements, so you can pipe it in directly with mysql -u root -p < all.sql.

Compress to save space

SQL text compresses extremely well. Pipe the dump straight through gzip so you never write a huge intermediate file:

mysqldump -u root -p --single-transaction mydb | gzip > mydb.sql.gz

# Decompress and restore in one line
gunzip < mydb.sql.gz | mysql -u root -p mydb

Daily backups with cron

Wrap the command in a script at /usr/local/bin/db-backup.sh that names files by date and prunes anything older than N days:

#!/bin/bash
DIR=/var/backups/mysql
mkdir -p "$DIR"
DATE=$(date +%F)
mysqldump -u root -p'yourpassword' --single-transaction --all-databases \
  | gzip > "$DIR/all-$DATE.sql.gz"
# Keep only the last 7 days
find "$DIR" -name '*.sql.gz' -mtime +7 -delete

Make it executable and add it to crontab to run at 3 a.m. every day:

chmod 700 /usr/local/bin/db-backup.sh
crontab -e
# Add this line
0 3 * * * /usr/local/bin/db-backup.sh

Putting a password in the script is risky. A cleaner approach is to store credentials in /.my.cnf (with permissions set to 600) so the script never spells out a plaintext password.

Keep a copy off-site

If your backups live on the same machine as the database, one hardware failure takes out both. Always copy backups elsewhere — push them to another server with rsync or scp, or sync them to object storage:

rsync -avz /var/backups/mysql/ user@backup-host:/backups/db/

A word on physical backups

mysqldump is a logical backup: recovery means replaying SQL, which gets slow for very large datasets in both directions. For those cases, consider a physical backup that copies the data files directly. The open-source Percona XtraBackup supports hot (online) backups of InnoDB with fast backup and restore, which suits databases of tens of gigabytes and up. The trade-off is more setup and less flexibility for major-version migrations than logical dumps offer.

Summary

Good database backups come down to "automated, off-site, and verified": grab a consistent snapshot with mysqldump --single-transaction, pipe it through gzip, run it daily via cron while keeping N days, and rsync it somewhere else. Above all, actually rehearse a restore now and then — a backup you've never tested is no backup at all. Reach for XtraBackup physical backups only once your database outgrows logical dumps.