Backing Up and Restoring MySQL Databases

The commands you need and the habits that stop you losing data.

Why backups matter

Data is often the most valuable thing in your infrastructure. Backups protect it from hardware failure, human error, and malicious attacks. A backup strategy is not optional.

Backing up MySQL databases

Options range from simple SQL dumps to dedicated tools like MySQL Workbench or third-party backup software.

Using mysqldump

mysqldump creates a text file of SQL statements that can recreate the database structure and data.

To back up a single database:

mysqldump -u [username] -p[password] [database_name] > [backup_file].sql

To back up all databases:

mysqldump -u [username] -p[password] --all-databases > [backup_file].sql

Automated backup scripts

For regular backups, schedule the job with cron on Linux or Task Scheduler on Windows.

A cron job to back up a database every day at 2 AM:

0 2 * * * /usr/bin/mysqldump -u [username] -p[password] [database_name] > /path/to/backup/[backup_file].sql

Restoring from backup

Restoring is straightforward if you have a mysqldump file.

To restore a database:

mysql -u [username] -p[password] [database_name] < [backup_file].sql

If the database does not exist yet, create it first:

mysql -u [username] -p[password] -e "CREATE DATABASE [database_name]"

Advanced backup options

For larger or more complex setups:

• Binary log backups: for point-in-time recovery.
• MySQL Enterprise Backup: commercial tool with hot backups and extra features.
• Third-party tools: Percona XtraBackup and similar offer more flexibility.

Backup best practices

1. Back up regularly: how often depends on how much your data changes.
2. Store offsite: keep copies in a different physical location from the live database.
3. Test restores: a backup you have never restored is just a file you hope works.
4. Encrypt backups: especially if they contain sensitive data.
5. Document the process: write down exactly how to back up and restore so anyone on the team can follow it.

Set up automated backups, store them offsite, and test a restore at least once. That is the difference between a backup strategy and a backup you can actually rely on.