How to import and export a database in MySQL

Lately I have been doing a lot of coverage of MySQL. Of course there are a lot of reasons for that - MySQL is as popular a database as it is flexible and reliable. And so far I've covered MySQL Workbench (see my article "Complete database administration package MySQL Workbench"), MySQL Administrator (see my article "Creating a database with MySQL Administrator"), MySQL Navigator (see my article "Manage you MySQL databases with an easy to use GUI"), or PhpMyAdmin (see my article "Install PhpMyAdmin for easy MySQL administration"). Through my LAMP article, "How to install a LAMP server", you learned how to install MySQL. This time around I am going to show you how to export a database and then import it into another MySQL server. And finally the ability to connect to a MySQL server from a remote machine was illustrated in my article "Allow remote connections to your MySQL server".

Why would you do this? One very good reason is for hardware update or replacing dying hardware on your MySQL server. If that hardware is dying, you will need to get your databases off the dying drive and onto a free drive. The most reliable, and easiest, way to do this, is by using built-in MySQL command line tools. That is exactly what we are going to do here.

The tools you need

First and foremost you will be using two commands, both of which are already installed on your MySQL server. The two commands are:

• mysqldump: This is the command to use to export your database.
• mysql: This is the command you will use to import your database.

You will also need a means to transport your database from one machine to another. I would recommend either writable CD/DVD (depending upon the size) or USB flash drive. If you use FTP there can be data corruption issues that you don't want to have to deal with. Plus, if you place those databases on a removable device, you will have a backup copy should something go awry.

Along with a means to transport the export the database, you will also need the MySQL administrators password. With everything in hand, let's get to exporting.

Exporting

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:

mysqldump -u USER -p DATABASE > FILENAME.sql

Where USER is the MySQL administrator user, DATABASE is the database you want to export, and FILENAME is the file name you want to name the exported file (best just to use the database name for the filename, so to avoid 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

Now that 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. Now open up a terminal window and issue the command:

mysql -u USER -p DATABASE < FILENAME.sql

Where USER is the MySQL admin username, DATABASE is the name of the database to be imported, and FILENAME.sql is the 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.

Advertisement