How to back up and restore a MySQL database

Introduction

Backups protect against data loss from hardware failure, human error, and corruption. MySQL offers logical backups via mysqldump and physical backups through tools like Percona XtraBackup.

mysqldump

mysqldump creates a text file of SQL statements that can recreate the database:

mysqldump -u root -p mydb > mydb_backup.sql

Restore by piping the file back into MySQL:

mysql -u root -p mydb < mydb_backup.sql

Automated Backups

Schedule backups with cron and rotate files to avoid filling disk:

0 2 * * * mysqldump -u root -p mydb > /backups/mydb_$(date +\%F).sql

Physical Backups

For large databases, physical backups are faster and less intrusive. Percona XtraBackup can create hot backups of InnoDB tables without locking.

Always test your restore process periodically. If you manage migrations, see how to implement database migrations for safe schema change workflows. For connection management, what is connection pooling and why is it important ensures backups do not starve the app of connections.