Tutorial

How To Migrate a MySQL Database Between Two Servers

Published on August 9, 2012
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

Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.

Learn more about our products

About the authors

Still looking for an answer?

Ask a questionSearch for more help

Was this helpful?
 
10 Comments


This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

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:~/

Kamal Nasser
DigitalOcean Employee
DigitalOcean Employee badge
July 17, 2013

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

Try DigitalOcean for free

Click below to sign up and get $200 of credit to try our products over 60 days!

Sign up

Join the Tech Talk
Success! Thank you! Please check your email for further details.

Please complete your information!

Featured on Community

Get our biweekly newsletter

Sign up for Infrastructure as a Newsletter.

Hollie's Hub for Good

Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.

Become a contributor

Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

Welcome to the developer cloud

DigitalOcean makes it simple to launch in the cloud and scale up as you grow — whether you're running one virtual machine or ten thousand.

Learn more