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!
Become a contributor for community
Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.
DigitalOcean Documentation
Full documentation for every DigitalOcean product.
Resources for startups and SMBs
The Wave has everything you need to know about building a business, from raising funding to marketing your product.
The developer cloud
Scale up as you grow — whether you're running one virtual machine or ten thousand.
Get started for free
Sign up and get $200 in credit for your first 60 days with DigitalOcean.*
*This promotional offer applies to new accounts only.
I’ve tried to execute Step 3 two different ways now, both of which resulted in an error:
1.) <pre>root@ch1:/home/pablo# mysql -u root -p wordpress < /home/pablo/wordpress.sql</pre> and the system responded with: ERROR 1049 (42000): Unknown database ‘wordpress’
2.) <pre>root@ch1:/home/pablo# mysql -u root -p newdatabase < /home/pablo/wordpress.sql</pre> and the system responded with: ERROR 1049 (42000): Unknown database ‘newdatabase’
On the VPS that’s receiving the database, I’ve (obviously) already installed MySQL. Do I also need to create a database named ‘wordpress’?
@Pablo: Try creating the database first and then importing the dump.
Thanks @Kamal; but I still can’t get it to work. So, I went ahead and created a new database named wordpress. I then executed: <pre>mysql -u root -p wordpress < /home/pablo/wordpress.sql</pre>
to which, the system responded with: <pre>Enter password:</pre>
I then entered the MySQL root password and the system appeared to accept it and returned to a normal terminal window. I then executed: <pre> mysql -u root -p [entered the root MySQL password] show databases; use wordpress; show tables; </pre>
and the system responded with: <pre>Empty set (0.00 sec)</pre>
What am I missing, here?
@Pablo: Weird. Does wordpress.sql have any ‘CREATE TABLE *’ lines?
How do I check that?
Also, does wordpress.sql’s ownership matter; b/c right now it’s root:root w/644 permission.
@Pablo: It’s o+r so everyone can read it. What’s the output of “grep -i ‘CREATE TABLE’ wordpress.sql”?
Why use 3 steps when you can just use one: mysql -u root -p --opt [database name] | ssh [username]@[servername] mysql -u root -p newdatabase
Linux has a very powerful piping system, and you can pipe any data with stdin/stdout directory over an SSH tunnel. This eliminates the use of SCP in most cases when you don’t need to retain the file being processed. You can move over entire directory structures using an SSH pipe and TAR for example, with a single line. Amazing!
@kveroneau: You’re correct. However, to make this article more readable and easy to understand, splitting that into 3 commands would be better :]
I do step 3 and then I get this error: ERROR 1045 (28000): Access denied for user ‘rbl91_wrdp1’@‘localhost’ (using password: YES)
rbl91_wrdp1 is the user name in database named rbl91_wrdp1
@robert.lee: Does that use have proper permissions? Run the following MySQL command as root:
<pre>> GRANT ALL PRIVILEGES ON
rbl91_wrdp1.* TO ‘rbl91_wrdp1’@‘localhost’ IDENTIFIED BY ‘yourpassword’; > FLUSH PRIVILEGES;</pre>