In this article, I will show you how to dump a mysql database from one server to another remote server without having to export and import your SQL structure and data. We’ll dump a database directly to another empty database on another, remote server. In order to do this, you’ll need shell access to both servers and also create an empty database (with a valid login) on the remote server. Below is the command.
So in the command above, we first execute a mysqldump to create an SQL dump of the current database. You’ll need to replace “xxxxx” with the actual username and password of the current database. Lastly, we specify the name of the database to dump.
Immediately, we connect to the remote server via SSH and execute a mysql command which will import the SQL dump which was just created, into the remote database. Same as with the current database, you’ll need to replace “xxxxx” with the username and password and of course the name of the remote database.
You will be prompted for password once the current database has been dumped. The password is the ROOT password for connecting to the remote server via SSH.
You’ll also notice that I used the “-v” option which executes the command in verbose mode. This prevents all the SQL code from being output in your terminal. It will just show a summary of the connection established, the tables dumped and then the disconnection from the mysql server.
And that’s it! You’ve just transferred a database from one server to another.
November 24th, 2008 at 1:29 pm
but were we can get shell access?
November 25th, 2008 at 2:44 pm
@bright
You can get shell access from your hosting/server provider. It is possible that the login details could be the same as your FTP details though. Give it a try. Something like this : ssh username@domain.com … and then enter the FTP password.
All the best,
Antonie
June 16th, 2009 at 12:45 pm
This is a fantastic tip and I thank you a lot man. I used to export, upload via FTP and all that mess. The last database was 200MB so finding your article has saved me a world of boredom. Thnx.
July 20th, 2009 at 4:16 am
ssh root@remoteserver.com
if my domain name is dbanerjee.com, then should I write the above command like this
ssh root@dbanerjee.com
thank you
September 8th, 2009 at 10:00 am
I think the following link will help you….
http://hiox.org/index.php?id=162
November 8th, 2009 at 11:47 am
Hello everyone i m trying to copy a databses’s of one server to another server(2 different systems n different server)in my project as a part of copy database wizard(.net platfrom)as i m in entry level(fresher)pls let me know the steps n code required to do this…..thanks in dvance
March 2nd, 2010 at 2:17 am
Unless you receive errors during the transfer.
ERROR at line 160 Got a packet bigger than ‘max_allowed_packet bytes.
mysqldump: Got err no 32 on write.
::
Of course there’s no indication of why these errors occur or how to get around them. So, I guess for those who were able to use this, congratulations. I’ll go back to phpMyAdmin import… It’s cumbersome, but it always works.
May 6th, 2010 at 3:22 am
That’s a very good tip. Tried this with my site which is hosted by Yahoo! and it worked perfectly. There is a good yahoo web hosting review if you’re interested in switching.