Exporting and Importing Databases

EXPORTING DATABASE

You will not need your administrator password to issue the mysqldump command. Nor will you have to use sudo to issue this command. So, to export your database open up a terminal window and issue the following command:

mysql > mysqldump -u USER -p DATABASE > FILENAME.sql

Where,

USER - MySQL administrator user.

DATABASE - Database you want to export.

FILENAME - Exported file name (Best, use database name for file name to avoiding confusion)

When you issue this command you will be prompted for the MySQL admin password. Enter that password and hit the Enter key. In the directory you issued the command you will now have a file with the .sql extension which is the file you then need to copy to your CD, DVD, or USB flash drive.

IMPORTING DATABASE

Now, you have that file on a removable media, transport that file to the new machine, insert the media, mount the media (if necessary), and copy the file to your users’ home directory. Next, open up a terminal window and issue the command:

mysql > mysql -u USER -p DATABASE < FILENAME.sql

Where,

USER - MySQL admin username, 

DATABASE - Name of the database to be imported.

FILENAME.sql - Dump that was exported from the initial machine.

You will be prompted for the MySQL administrator password and then, most likely, you will be returned to your prompt, sans errors.

That’s it. You have officially exported and imported a database from one machine to another.