Cloud Hosting/MySQL import export

From XMission Wiki
Jump to: navigation, search

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.

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

Switches


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.

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

Archives.png


Cloud Hosting Control Panel - https://xmission.com/account/


Additional Tasks and Information