The simplest way to dump all databases from MySQL into a .sql file, for backup or migration or otherwise, is using the –all-databases flag like so:

mysqldump --all-databases > all_databases_dump.sql

Because this command exports all databases, there is no need to specify a database name. All databases stored in mySQL will be dumped into the “all_databases_dump.sql” export file in the present working directory.

If need be you can also specify a username and password when dumping all databases like so, in this case with the username being root:

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

After the mysql database has been dumped, my personal preference is to create a tar gzip from it as described here but that’s entirely optional.

If you do want to dump a specific database by name rather than export all databases, that is equally as simple:

mysqldump database_name > database_name_dump.sql

The mysqldump command has many parameters and flags which can be helpful for exporting and backing up databases, you can learn more from the manual page with “man mysqldump” or by reading here on dev.mysql website.

Of course if you have a database dump, importing that into MySQL is important. Here’s the simplest way to import all databases from a database.sql file into MySQL via command line:

mysql database_name < database_dump.sql

And like exporting a database, when importing you can also specify a username if desired:

mysql -u root -p < database_dump.sql

You specify a different username or database if desired as well:

mysql -u user -p database_name < database_dump.sql

You can also import a specific database in a large dump by name:

mysql --one-database database_name < all_databases.sql

As before, if you're having difficulties with importing databases into mysql you can turn to the manual page with 'man mysql' or to the official documentation here at the mysql developer site.

Know any interesting tricks for exporting databases and importing databases in MySQL? Let us know in the comments!