MySQL Backup & Restore
No matter how robust your code is, hardware fails and data becomes corrupt. Regularly backing up your MySQL databases is the only way to guarantee recovery. This article covers dump strategies and restore techniques.
Using mysqldump for backups
The simplest way to create a logical backup is with mysqldump. The following command exports a single database to a SQL file:
mysqldump -u myuser -p --single-transaction --routines --triggers mydatabase > /backups/mydatabase_$(date +%F).sql
The --single-transaction flag ensures a consistent snapshot for InnoDB tables without locking. Include --routines and --triggers to capture stored procedures and triggers.
Automating backups
Create a cron job that runs the dump script daily. Compress the output to save space:
0 3 * * * /usr/bin/mysqldump -u backup_user -pPASSWORD mydatabase | gzip > /backups/mydatabase_$(date +\%F).sql.gz
Always store backups off‑site or replicate them to another server. Local backups are useless if the disk dies.
Restoring data
To restore from a SQL file, create the database and then import the dump using mysql:
mysql -u myuser -p -e "CREATE DATABASE IF NOT EXISTS mydatabase"
mysql -u myuser -p mydatabase < /backups/mydatabase_2025-01-01.sql
If you compressed the dump, pipe it through gunzip:
gunzip -c /backups/mydatabase_2025-01-01.sql.gz | mysql -u myuser -p mydatabase
Always test restores in a staging environment. A backup is only as good as your ability to recover from it.