How to Back Up a MySQL Database

Regular database backups protect against data loss.

Backup a Single Database

mysqldump -u root -p myapp > myapp_backup.sql

Backup All Databases

mysqldump -u root -p --all-databases > all_databases.sql

Compressed Backup

mysqldump -u root -p myapp | gzip > myapp_$(date +%Y%m%d).sql.gz

Restore

mysql -u root -p myapp < myapp_backup.sql

# From compressed
gunzip < myapp_backup.sql.gz | mysql -u root -p myapp

Automated Backups

Add to crontab:

0 2 * * * mysqldump -u root -pYOUR_PASSWORD myapp | gzip > /backups/myapp_$(date +\%Y\%m\%d).sql.gz