How to Export & Import All MySQL Databases

Jan 19, 2017 - 3 Comments

Exporting and importing databases in mySQL from command line

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!

.

Related articles:

Posted by: Paul Horowitz in Command Line, Tips & Tricks

3 Comments

» Comments RSS Feed

  1. To import all databases at once, the command to use is:

    mysql < all_databases_dump.sql

  2. ankit says:

    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

  3. Sebby says:

    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.

Leave a Reply

 

Shop on Amazon.com and help support OSXDaily!

Subscribe to OSXDaily

Subscribe to RSS Subscribe to Twitter Feed Follow on Facebook Subscribe to eMail Updates

Tips & Tricks

News

iPhone / iPad

Mac

Troubleshooting

Shop on Amazon to help support this site