Revision as of 17:26, 10 November 2016

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.

Usage

• 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.

Switches

• 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

Helpful Tips

• 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.

Import MySQL

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.

Usage

• From the command line you will want to enter the following:

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

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