MySQL - exporting and importing databases

Once a database has been created it can be very useful to be able to export the data and then import it on another Slice or server.

This procedure is very simple and can greatly ease any migration of data.


Let's get stuck right in and look at exporting the database.

Export

In this example, we will export the 'mytestdb' database into a file called 'mytestdb.sql':

mysqldump -u root -p mytestdb > mytestdb.sql

Er, that's it.

Once done, the sql file can be copied to a new Slice or server ready to be imported into MySQL.

Import

Importing the data is just as easy but involves two steps.

The first step is to create a blank database ready to receive the data (remember this is on the Slice or server to which the database is going to imported):

mysqladmin -u root -p create mytestdb2

Once done, all that is left is to actually import the data:

mysql -u root -p mytestdb2 < mytestdb.sql

Done. It really is as simple as that.

Summary

Although a short article, it really does show the power of using the command line when administering MySQL.

One command to export the database, and then two simple commands to import the data into a new Slice MySQL installation.

PickledOnion

Article Comments:

Eric commented Fri Aug 22 02:47:44 UTC 2008:

I'm no expert but shouldn't there be a trailing semi-colon?

and where do the exported files get deposited?

Cheers, E

PickledOnion commented Fri Aug 22 08:48:10 UTC 2008:

Hi,

No there shouldn't be a trailing semi-colon. I am not sure why you suspect there should be?

The commands are given from the terminal.

The files are in the folder you give the command.

PickledOnion

vladimir prieto commented Wed Aug 27 14:21:09 UTC 2008:

hi

what about innodb? as far as i know, this command only works with myisam.

Adam DiCarlo commented Mon Jan 05 06:06:12 UTC 2009:

If you keep your database dumps in source control (I keep mine in git with the rest of the website), you may find the --skip-extended-insert flag handy:

mysqldump --skip-extended-insert -uroot -p $DB_NAME > db-backup.sql

This makes the database dumps a bit bigger, but allows your source control software to efficiently store the changes between each database dump (as it puts each table row on its own line).

Reed Botwright commented Wed Feb 04 21:52:04 UTC 2009:

For importing, is there any difference between this and logging into the database with the appropriate privileges and sourcing the SQL file?

KP commented Tue Jun 29 17:10:19 UTC 2010:

where is the sql files stored? and can use this command to get excel file or csv file?

Jered commented Wed Jun 30 00:11:49 UTC 2010:

The sql file is written to the directory you're in when you run the command, if you follow the steps in the article.

To get a CSV file (which you could then import into Excel), you'll usually need to run an SQL command. There's a brief description of the command on this site, but you'd need to modify the command to tailor it to your database. Alternately, this other site covers using mysqldump to create the CSV file, which is a bit more involved but spares you from messing with SQL.

nithin commented Sun May 29 19:18:49 UTC 2011:

Is this procedure is same for postgres also? If I want to start up my new site (which has postgres database)with slicehost, how can I upload my database and restore it in server. I searched the site article but couldn't find much about backup/restore of postgres database :(..

Want to comment?


(not made public)

(optional)

(use plain text or Markdown syntax)