How to Dump All Databases from MySQL via Command Line
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.
How to Export a Specific Database from MySQL
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.
How to Import All Databases into MySQL
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.sqlImporting a Specific Database into MySQL
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!
