How to Backup and Restore a MySQL Database

In this tutorial, you'll learn about How to Backup and Restore a MySQL Database. We cover key concepts, practical examples, and best practices.

The Problem

You need to create a database backup before making schema changes, migrating to a new server, or recovering from accidental data loss. Without a backup strategy, a single mistake can result in permanent data loss.

Quick Fix

Step 1: Back up a single database

Dump a specific database to a SQL file:

mysqldump -u root -p my_database > my_database_backup.sql

Enter the MySQL root password when prompted. The file contains all CREATE TABLE and INSERT statements needed to rebuild the database.

Step 2: Back up multiple databases

Dump several databases at once:

mysqldump -u root -p --databases db1 db2 db3 > multiple_dbs.sql

Step 3: Back up all databases

Dump every database including system tables:

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

Step 4: Add compression and timestamping

Compress the backup and include a date for organization:

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

Step 5: Restore a single database

Import a backup into an existing database:

mysql -u root -p my_database < my_database_backup.sql

Or from a compressed file:

gunzip < my_database_20260624.sql.gz | mysql -u root -p my_database

Step 6: Restore all databases

Restore a full backup:

mysql -u root -p < all_databases.sql

Step 7: Back up with specific options

Exclude data (schema only) or add a drop-table statement:

# Schema only, no data
mysqldump -u root -p --no-data my_database > schema.sql

# Include DROP TABLE statements before each CREATE
mysqldump -u root -p --add-drop-table my_database > backup.sql

Alternative Solutions

Use MySQL Workbench for GUI backups

Export and import through the administration panel.

Use Percona XtraBackup for large databases

Perform hot backups of large InnoDB databases without locking:

sudo apt install percona-xtrabackup-80
xtrabackup --backup --target-dir=/backups/

Common Mistakes to Avoid

Not testing backups. A backup that has never been restored is not a backup. Test restoration in a staging environment regularly.

Storing backups on the same server as the database. If the server fails, both the database and backups are lost. Copy backups to a separate location.

Using mysqldump without --single-transaction on InnoDB. This causes a table lock during backup. Add --single-transaction for consistency without locking.

Pro Tips

Use --routines and --events flags. By default, mysqldump does not include stored procedures and events. Add --routines --events to capture them.

Use --master-data for replication setup. Adding --master-data=2 includes the binary log position in the dump, useful for setting up replicas.

Automate backups with a cron job and rotation. Keep 7 daily, 4 weekly, and 3 monthly backups with a rotation script to avoid filling the disk.

Prevention

• Schedule daily backups with cron: 0 2 * * * mysqldump -u root -p'password' my_db | gzip > /backups/db_$(date +\%Y\%m\%d).sql.gz.
• Store backups in a separate location from the database server.
• Test restoration periodically to ensure backups are valid and complete.