MySQL dump (Export)

mysqldump is a tool used to dump a database or a collection of databases for backup or transfer to another SQL server. It can be used to migrate databases to and from your Cloud Servers.

General

Simply put, mysqldump outputs the SQL statements that were used to create a database, tables and all of the data in the tables. It can output these statements in multiple formats and with more or less output information as needed.

Since mysqldump just outputs SQL statements it is usually helpful to truncate the output to a file. A common mysqldump may look like this:

mysqldump -h localhost -u <user> -p databasename > databasefile.sql

Looking at this command piece by piece makes it easy to understand.

mysqldump:	invokes the mysqldump command
-h localhost:	specifies the host to use (if left out mysqldump will default to localhost)
-u <user>:	specifies the user to login to mysql as. DO NOT INCLUDE < >
-p:	tells mysql to ask for a password
databasename:	specifies which database to dump
> databasefile.sql:	truncates the output of the mysql command to a file

Different switches can be used to change the output of the mysqldump. See the following link for a list of mysqldump switches: http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

The -D switch can be used to dump a skeleton of a database without any data in it.
The --add-drop-table switch will ensure that when the SQL is uploaded to another SQL server it drops the tables before creating them.

Just like mysqldump to export a database we can use mysql to import a database. Here is how it works:

The quickest way to do this is from the command line. Especially if the file is larger than 2Mb.
First you will want to make sure the file is located on your server. A program like Filezilla can be used to FTP your database file to your server.
SSH to your server with a program like Putty, for Windows, or Terminal on Mac or Linux.
Be sure you are in the directory containing the file you want to import.

mysql -h localhost -u <user> -p databasename < databasefile.sql

Looking at this command piece by piece makes it easy to understand.

mysqldump:	invokes the mysqldump command
-h localhost:	specifies the host to use (if left out mysqldump will default to localhost)
-u <user>:	specifies the user to login to mysql as. DO NOT INCLUDE < >
-p:	tells mysql to ask for a password
databasename:	specifies which database to dump
< databasefile.sql:	tells mysql to import a file called databasefile.sql