// Tutorial //

How To Migrate a MySQL Database Between Two Servers

Published on August 9, 2012
Default avatar
By Etel Sverdlov
Developer and author at DigitalOcean.
How To Migrate a MySQL Database Between Two Servers

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

Want to learn more? Join the DigitalOcean Community!

Join our DigitalOcean community of over a million developers for free! Get help and share knowledge in our Questions & Answers section, find tutorials and tools that will help you grow as a developer and scale your project or business, and subscribe to topics of interest.

Sign up
About the authors
Default avatar
Developer and author at DigitalOcean.

Still looking for an answer?

Was this helpful?

is there a similar method for postgresql

If anyone is getting a permission denied error when trying to scp, try the following command.

scp -i /path/to/privatekey newdatabase.sql user@example.com:~/

@Pablo: Try creating the database first and then importing the dump.

Hi, incase anybody is having issues running the third command, note that you need to create the database before running that. you can login to mysql, and run mysql create database newdatabase; then you can proceed to run mysql -u root -p newdatabase < /path/to/newdatabase.sql

This is extraordinarily basic. Given that the majority of DO droplets are extremely small I find this article rather useless.

Step One Check the Size of your Database.

Step Two Check your servers free space.

Step Three Then and only then follow the steps outlined in this article. Which I suspect is likely rare given the disk space included with most DO droplets is dismal.

As a final note, backups should always be offsite. Which means storing a backup of your Database on another droplet at DO is NOT recommended. Get S3CMD and sync it to Amazon S3 for safe keeping.

I want to import my localhost DB to hostinger DB everyday at night 10PM can anyone suggest me any way of doing it…?? Thanks In Advance…

I followed this exact procedure to update a live WP site from the development site. The pages that have not changed are fine. But a new page I created cannot be edited, and returns a 404 error when I try to load it. I can’t figure this out. Identical db contents, but one page won’t load.

Has anyone had this issue, or has anyone any ideas?

You can do this as a one liner by running mysqldump from the host and piping that into the server/database with the mysql command:

mysqldump -ppassword -uuser -hhost -B database1 database2 | mysql -uuser -ppassword

This will dump from the first server to the second listed. This command is done with the assumption that you are running the command from the destination server or recipient. You can run the command in either direction or run this from your workstation to transfer between servers (take security precautions or ideally use your private IP addresses on your hosted servers). Just simply put in the host information on both sides of the command to implement this:

mysqldump -ppassword -uuser -hhost -B database1 database2 | mysql -uuser -ppassword -hhost

This will include drop database and create database SQL commands within the dump so be careful not to replace out any unintended data.

Can we please get a rewrite of this article with more details. Particularly for part 3 where the database needs to be created first before transferring, else you get an error message.

A reader shouldn’t have to go to the comments to find this out, the article should cover it!

It worked for me. But not without issuing “create database newdatabase” in the new server’s mysql. Thanks.