Importing from and Exporting to Files Using the MySQL Command Line

To export or import with MySQL, begin by logging into your server, preferably with sudo (root) access.

Exporting from MySQL

The best tool for exporting a MySQL database to a text file is mysqldump.

To use mysqldump, you will need to know the login credentials of an appropriate MySQL user that has the necessary privileges to export the database in question.

With that information in hand, enter the mysqldump command with the appropriate flags and options:

$ mysqldump -u my_username -p database_name > output_file_path

The options in use are:

• The -u flag indicates that the MySQL username will follow.
• The -p flag indicates we should be prompted for the password associated with the above username.
• database_name is of course the exact name of the database to export.
• The > symbol is a Unix directive for STDOUT, which allows Unix commands to output the text results of the issued command to another location. In this case, that output location is a file path, specified by output_file_path.

Note: It is generally advisable to input the fully qualified path and filename for the output_file_path, so the resulting file is generated exactly where you want it.

For example, to export the books database as the book_admin user to the ~/backup/database directory, we might use the following command:

$ mysqldump -u book_admin -p books > ~/backup/database/books.sql
Enter password:

After entering our password when prompted above, this command then creates our backup file with a .sql suffix (which is completely optional but advisable) in the appropriate directory.

By default, mysqldump will not save commands which attempt to modify the existence of the actual database. Instead, by default, only actual tables (and their respective data) are saved and thus will be prepared for later import using this file. If you need the ability to export (and later recreate) one more more databases, read up on the --databases flag in the official documentation.

Importing Into MySQL

Now that you’ve learned how to export a backup of a MySQL database, we’ll explore how to reverse the process and import the backup into an existing database.

As you might imagine, to compliment the mysqldump command used for exporting, there is a similar mysqlimport command for importing.

In most cases, importing is just a matter of passing virtually identical options to the mysqlimport command. To import our saved books.sql file created earlier, we’d use many of the same flags and much the same syntax.

$ mysqlimport -u book_admin -p books_production ~/backup/database/books.sql
Enter password:

As before, the -u and -p flags are required for authentication, which is then followed by the name of the database to import into (in this case, we’re using different, production database), then lastly specifying the fully-qualified path to the SQL dump file that contains our import data, ~/backup/database/books.sql. It’s also worth noting that mysqlimport doesn’t require the < or > (STDIN/STDOUT) symbols that were used with mysqldump.

With that, you’ve learned how to both export/backup an existing database, and how to then import/restore that data into the same or even a different database.