How to Back Up and Restore a MySQL Database

Last updated: August 25, 2026.

A logical MySQL backup is a portable SQL file containing the statements needed to recreate database objects and data. The mysqldump utility is a practical choice for small and medium databases, migrations, and scheduled backups.

Back up one database

Run the command from a shell on a machine that can reach the MySQL server. The password prompt keeps the password out of the command and shell history.

mysqldump --single-transaction \
  --routines --triggers --events \
  --default-character-set=utf8mb4 \
  -u backup_user -p app_database > app_database.sql

--single-transaction produces a consistent snapshot for transactional tables such as InnoDB without holding a table lock for the entire dump. Coordinate writes separately if the database contains nontransactional tables.

Create a compressed backup

mysqldump --single-transaction --routines --triggers --events \
  -u backup_user -p app_database | gzip > app_database.sql.gz

Restore the database

Create the target database if it does not already exist, then load the dump with the mysql client.

mysql -u root -p -e "CREATE DATABASE app_database CHARACTER SET utf8mb4"
mysql -u restore_user -p app_database < app_database.sql

For a compressed file, stream it directly into the client:

gzip -dc app_database.sql.gz | mysql -u restore_user -p app_database

Back up selected or multiple databases

mysqldump -u backup_user -p app_database customers orders > selected_tables.sql
mysqldump -u backup_user -p --databases app_database reporting > two_databases.sql
mysqldump -u backup_user -p --all-databases > all_databases.sql

Backup checklist

• Store backups away from the database server and restrict access to them.
• Encrypt backups containing personal data, credentials, or business records.
• Retain more than one recovery point.
• Monitor scheduled jobs and available disk space.
• Regularly restore a backup into a test database; a dump is useful only if it can be restored.

See the MySQL documentation for mysqldump backups for additional options and larger-scale alternatives.