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, 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:
-u flag indicates that the MySQL
username will follow.
-p flag indicates we should be prompted for the
password associated with the above
database_name is of course the exact name of the database to export.
> 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
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.
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
-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
> (STDIN/STDOUT) symbols that were used with
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.