MySQL : Transfer database from server to another server

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.

mysqldump -v -uxxxxx -pxxxxx database_name | ssh root@remoteserver.com mysql -uxxxxx -pxxxxx database_name

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.

  • bright

    but were we can get shell access?

  • http://tribulant.com Antonie Potgieter

    @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

  • http://www.cabezabomba.com/webcidentes Alex

    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.

  • http://www.dbanerjee.com Dipankar

    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

  • http://www.weblocal.com.br Hospedagem de Sites

    I think the following link will help you….
    http://hiox.org/index.php?id=162

  • Rakesh Reddy

    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

  • Kirk

    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.

  • http://www.webhostingsearch.com/ Steve

    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.

  • http://gateopener.org gate openers

    Can you show me some references to this? I want to know more information. Thanks

  • Jill Han

    Hi,
    it was what I typed at command line
    mysql> mysqldump -v test | ssh root@remote.college.edu mysql -uroot -prootpassword test;
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your
    MySQL server version for the right syntax to use near ‘mysqldump -v test | ssh root@remote.college.edu mysql -uroot -prootpassword test’ at line 1

    any thoughts on what could cause that?

    thanks for your help,

    Jill

  • RB

    will this work for SQLServer as well?

  • http://www.thecurrentaffairs.net Junaid

    My wordpress database have crashed and my server is doing 100% cpu usage and it is creating a lot of headache for me.

    Is there a possibility to move the posts, categories, tags and users data to a new wordpress database?

    Thanks

  • Nicolas

    Thank you so much man, an easy way to move mysql database to another server.