3 ways to import and export a MySQL Database

How to import and export a MySQL database

June 07, 2013, 12:00 AM — Nobody ever really wants to move a database, but it is a frequent necessity. Servers get old, providers become unreliable, disk space runs out. In the case of MySQL database migration, you have many options for accomplishing the task. I’ll discuss three of the most common options and when to use each.

Option 1 - phpMyAdmin

Whether you’re renting servers or running your own, chances are you have phpMyAdmininstalled to manage your MySQL installations. This PHP staple has been the most common MySQL management tool for over a decade.

Source: wikipedia.org

Migrating a database is as simple as selecting the DB in the left column, clicking the Export link, and saving the database to a file. Then on the new server, select the DB in the left column, click the Import link, and choose the file you just exported. Easy.

Use this method when:

• Both servers have phpMyAdmin installed
• The database size is under 50MB
• The database export file is small enough to overcome upload size limits (note: you can compress the file before uploading to pack more in)

With large or very complex databases, you run the risk of the PHP request timing out before the operation completes. This will leave you with a partial import and likely a broken application. This method is best used for smaller databases like an average wordpress installation.

Option 2 - MySQL Workbench

MySQL Workbench is a desktop GUI product from MySQL itself. It does much more than importing and exporting with features like server health monitoring, SQL data modeling, and more.

Source: mysql.com

Migrating a database starts by establishing a Server Instance for each of the servers. Once they are configured, you can open the Server Admin tool by double clicking the instance of the server whose database is to be exported. Then, in the left column click the item labeled Data Export. Choose the database to export, configure the options and click Start Export. This will save the database export file(s) to your computer.

When the process completes, start a new Server Admin connection to the new server that you’ll be importing to. This time, click on the item labeled Data Import/Restore in the left column. Choose the export location from your local computer and set the target database for the import, then click the Start Import button.

Use this method when:

• You have remote access available to your MySQL servers
• You don’t have phpMyAdmin installed
• The database size is fairly large
• You’ve got a decent internet connection

This method works much better than phpMyAdmin on larger databases as you don’t usually run into a timeout issue. On the downside, if your up/download speed is low it can take a really long time. The software can be pretty buggy on occasion as well, it routinely crashes and needs restarting. The requirement for both MySQL servers to be network accessible can be a deal breaker for this method.

Option 3 - Command Line

Tried and true, the command line will always be the most efficient - though not the most friendly way - to accomplish a database export/import.

To start, SSH into the first server (using a tool like PuTTy) as a user with sufficient privileges to work with the server. Execute the following command at the terminal to dump the database to a file:

mysqldump -u [username] -p [database_name] > [dumpfilename.sql]

You will be prompted for the password and the export will begin. Note that you should replace each variable in brackets with the proper values and do not include the brackets.

When the export is complete, it’s helpful to compress the file to make it transfer to the new server faster. To do so, run the following command:

tar zcf dumpfilename.tar.gz dumpfilename.sql

replacing dumpfilename with your actual file name.

Next, transfer the compressed file to your new server, either by making it available at a web address at the old server and using wget, or by using FTP to download and upload the file.

Once the file is on the new server, start be decompressing it using the following command:

tar zxf dumpfilename.tar.gz

You’ll now have your original file dumpfilename.sql that you can import into your database. To begin the import process, use the command:

mysql -u [username] -p [database_name] < [dumpfilename.sql]

You will be prompted for the password and the import will begin. Note that you should replace each variable in brackets with the proper values and do not include the brackets.

When the terminal returns to the ready cursor, the import is complete.

Use this method when:

• You do not have phpMyAdmin installed
• You do not have remote web access to the database server
• You do have SSH access
• The database size is large
• You want total control over the process