MySQL Migration of Large Database In One Command

I was recently attempting a MySQL migration of a 60GB database from an Amazon RDS instance to a different server.  Not only is the database 60GB, but it contains multiple full-text indexes too.  I wanted to complete the entire migration in one step as opposed to creating a dump file, transferring, and importing manually.  Here’s what I did.

MySQL Migration Steps

Note: This explanation assumes that you’ve connected to your target server, have created a target database and have granted permission for your target server to connect to your source server.

The code

# mysqldump -h [SOURCE_LOCATION] -u[SOURCE_USERNAME] -p[SOURCE_PASSWORD] [SOURCE_DB_NAME] -K | mysql -u[TARGET_USERNAME] -p[TARGET_PASSWORD] [TARGET_DB_NAME]

ex.

# mysqldump -h 123321.tterwre.us-east-1.rds.amazonaws.com -utestuser -ptestpassword testdbname -K | mysql -uanotheruser -panotherpassword anotherdbname

The explanation

Lets break the command into two pieces.

First piece of the MySQL Migration (source database):

mysqldump -h [SOURCE_LOCATION] -u[SOURCE_USERNAME] -p[SOURCE_PASSWORD] [SOURCE_DB_NAME] -K

1. mysqldump
   This initiates MySQL’s database backup program.
2. -h [SOURCE_LOCATION]
   Tells the mysqldump program where on the internet the server containing the source database is.
3. -u[SOURCE_USERNAME]
   Tells the mysqldump program what username to use when connecting to the source database.
4. -p[SOURCE_PASSWORD]
   Tells the mysqldump program what password to use when connecting to the source database.
5. [SOURCE_DB_NAME]
   Tells the mysqldump program what particular database to connect to after connecting to the database server.
6. -K
   This is optional, but helps to make large data migrations faster.  “This makes loading the dump file faster because the indexes are created after all rows are inserted. This option is effective only for nonunique indexes of MyISAM tables.“

In plain English:

Initiate the MySQL backup program and connect to the source database server located at [SOURCE_LOCATION]. Next, connect to the database named [SOURCE_DB_NAME] with [SOURCE_USERNAME] and [SOURCE_PASSWORD], and copy everything in the database.

Second piece of the MySQL Migration (target database):

mysql -u[TARGET_USERNAME] -p[TARGET_PASSWORD] [TARGET_DB_NAME]

1. mysql
   This initiates the mysql program to access the target database (on the same machine you’re typing commands on).
2. -u[TARGET_USERNAME]
   Tells the mysql program what username to use when connecting to the target database.
3. -p[TARGET_PASSWORD]
   Tells the mysql program what password to use when connecting to the target database.
4. [TARGET_DB_NAME]
   Tells the mysql program what database to use when connecting to the target database server (I didn’t specify a location because MySQL will assume you’re using a local database server if you don’t specify a host location).

In plain English:

Connect to [TARGET_DB_NAME] with its server located on my local machine using [TARGET_USERNAME] and [TARGET_PASSWORD].

Piping the two parts of the MySQL Migration together

There’s one last piece that we haven’t talked about. In Linux, the pipe | is used to give the output from one command to another command. Because the first and second piece of the MySQL Migration command are separated by a |, together the commands become…

Initiate the MySQL backup program and connect to the source database server located at [SOURCE_LOCATION]. Next, connect to the database named [SOURCE_DB] with [SOURCE_USERNAME] and [SOURCE_PASSWORD], and copy everything in the database. Take the copy made from the source database, and give it to the target database located on the local machine.

That’s all folks. Questions? Hit me in the comments below.