How to Export & Import All MySQL Databases
Many developers and pro users rely on MySQL for their database needs. We’ll walk through how to export or dump all databases from MySQL, dump a single database, and also show how to import all of those databases from a database.sql file back into MySQL.
While there are GUI based tools to interact with MySQL, we’re going to focus on the command line here. The MySQL commands work with any version of the database software on any unix OS, including linux, Mac OS and Mac OS X, or whatever else you happen to be running mySQL on.
We’re going to assume you already have MySQL installed and running, if not you can learn about starting and stopping MySQL server on Mac OS here and can download MySQL here or if you’re looking for an entire web server stack, check out the easy to use MAMP for Mac.
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.sql
Importing 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!
To import all databases at once, the command to use is:
mysql < all_databases_dump.sql
i have dump all database ,now when i am trying to import
error
ERROR at line 1: ASCII ‘ ’ appeared in the statement, but this is not allowed unless option –binary-mode is enabled and mysql is run in non-interactive mode. Set –binary-mode to 1 if ASCII ‘ ’ is expected. Query: ‘PK
‘.
Please help
What’s the fascination with MySWL, anyway? (You may like to use one of its forks, like MariaDB, instead.)
OS X Server uses PostgreSQL. You might just like to install and use OS X Server, and its database. You may also, as a remedy for the PTSD likely incurred in following this advice, like to crawl over broken glass with your flies unzipped, for although PostgreSQL is undoubtedly excellent, OS X Server is not for all but the simplest jobs. But it is the approved Apple solution, and it’s vertically integrated.