How To Migrate a MySQL Database Between Two Servers
How To Migrate a MySQL Database Between Two Servers
We hope you find this tutorial helpful. In addition to guides like this one, we provide simple cloud infrastructure for developers. Learn more →

How To Migrate a MySQL Database Between Two Servers

PostedAugust 9, 2012 328.9k views MySQL

Transferring a database between virtual private servers can be accomplished using a SCP (Secure Copy), a method of copying files derived from the SSH Shell. Keep in mind, you will need to know the passwords for both virtual servers.

In order to migrate the database, there are two steps:

Step One—Perform a MySQL Dump

Before transferring the database file to the new VPS, we first need to back it up on the original virtual server by using the mysqldump command.

mysqldump -u root -p --opt [database name] > [database name].sql

After the dump is performed, you are ready to transfer the database.

Step Two—Copy the Database

SCP helps you copy the database. If you used the previous command, you exported your database to your home folder.

The SCP command has the following syntax:

scp [database name].sql [username]@[servername]:path/to/database/

A sample transfer might look like this:

scp newdatabase.sql user@example.com:~/

After you connect, the database will be transferred to the new virtual private server.

Step Three—Import the Database

Once the data has been transferred to the new server, you can import the database into MySQL:

mysql -u root -p newdatabase < /path/to/newdatabase.sql

With that, your transfer via SCP will be complete.

By Etel Sverdlov

21 Comments

Creative Commons License