Backup one, multiple or all databases at once using mysqldump

mysqldump is a flexible tool when it comes to what data you want to include in the dump. It allows you to dump only some tables (we covered that here), an entire database, multiple databases or all databases. In this part we will cover how to dump one, multiple or all databases at once.

Dump a single database

The following command creates a dump file named my_backup.sql which contains the entire my_app database.

# Without authentication
mysqldump my_app > my_backup.sql

# With authentication
mysqldump -u some_guru_user -p my_app > my_backup.sql

# With Gzip compression
mysqldump my_app | gzip > my_backup.sql.gz

Dump multiple databases

# Without authentication
mysqldump --databases my_app1 my_app2 > my_backups.sql

# With authentication
mysqldump -u some_guru_user -p --databases my_app1 my_app2 > my_backups.sql

# With Gzip compression
mysqldump --databases my_app1 my_app2 | gzip > my_backups.sql.gz

The resulting my_backups.sql file contains both my_app1 and my_app2 databases.

Dump all databases

# Without authentication
mysqldump --all-databases > my_backups.sql

# With authentication
mysqldump -u some_guru_user -p --all-databases > my_backups.sql

# With Gzip compression
mysqldump --all-databases | gzip > my_backups.sql.gz

The resulting my_backups.sql file contains all the databases.

Info

When using the --all-databases flag, the internal mysql database is included as well.