Tutorial
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.
Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.
author
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!
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.
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 runmysql -u root -p newdatabase < /path/to/newdatabase.sqlThis 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.