≡ Menu

Copy MySQL Database From One Server To Another Remote Server

Usually you run mysqldump to create a database copy and backups as follows:

$ mysqldump -u user -p db-name > db-name.out

Copy db-name.out file using sftp/ssh to remote MySQL server:
$ scp db-name.out user@remote.box.com:/backup

Restore database at remote server (login over ssh):
$ mysql -u user -p db-name < db-name.out
OR
$ mysql -u user -p 'password' db-name < db-name.out

How do I copy a MySQL database from one computer/server to another?

Short answer is you can copy database from one computer/server to another using ssh or mysql client.

You can run all the above 3 commands in one pass using mysqldump and mysql commands (insecure method, use only if you are using VPN or trust your network):
$ mysqldump db-name | mysql -h remote.box.com db-name

Use ssh if you don't have direct access to remote mysql server (secure method):
$ mysqldump db-name | ssh user@remote.box.com mysql db-name
OR
$ mysqldump -u username -p'password' db-name | ssh user@remote.box.com mysql -u username -p'password db-name

You can just copy table called foo to remote database (and remote mysql server remote.box.com) called bar using same syntax:
$ mysqldump db-name foo | ssh user@remote.box.com mysql bar
OR
$ mysqldump -u user -p'password' db-name foo | ssh user@remote.box.com mysql -u user -p'password' db-name foo

This will not just save your time but you can impress your friend too ;). Almost all commands can be run using pipes under UNIX/Linux oses.

Tweet itFacebook itGoogle+ itPDF itFound an error/typo on this page?

Comments on this entry are closed.

  • nice November 28, 2006, 9:03 pm

    nice and easy. Thanks

  • John January 16, 2007, 11:09 pm

    mysql connections can be made securely to a remove server via SSL. Just thought I would mention that as it is an additional option to consider. http://dev.mysql.com/doc/refman/5.0/en/secure-connections.html

  • joel ferido February 8, 2007, 5:54 pm

    thanks for the informations in here.
    it really helped me, a newbie..
    thanks..

  • Raul March 27, 2007, 7:27 am

    Amazingly Simple :)
    Thanks a lot… U saved atleast a couple of hours for me :D

  • Jason April 26, 2007, 5:09 pm

    I am trying your method to copy database from server A (sqldev100) to server B (sql101). I created a user refresh_oper on B with all privilege on database test. But failed for each of them.
    1) I got following error when running mysqldump -urefresh_oper -p -hlocalhost test | mysql -urefresh_oper -p -hsql101 test: Error 2003 (HY000): cannot connect to MYSQL server on ‘sql101′ (111).
    mysqldump Got error 32 on write.

    I am not sure what is the issue.
    2) I got prompted of password for refresh_oper@sql101 when I run mysqldump -urefresh_oper -p -hlocalhost test |ssh refresh_oper@sql101 mysql test.

    This appears ssh assume refresh_oper is os user?

    Can you help on these?

    Thanks.
    -Jason

  • Database June 14, 2007, 6:16 pm

    thank you good article

  • Sky February 6, 2008, 4:12 pm

    Jason, (I know its about a year later, but this is in case someone else has this problem)
    I have had the same problem and its because you have an empty “-p” on both sides of your pipe. You will have to specify the password on at least one them.

  • Carl May 3, 2008, 10:39 am

    Great tip! This morning I was doing this process manually step by step. And then I stumbled upon this page – Im learning more and more every day!

    Only downside is that I dont have any colleagues to impress as Im alone in the IT dept ;)

  • Rahul Bodhe July 17, 2008, 3:16 pm

    it is very nice site & very much help ful to me I learned a lot from this website
    please send me updated query & resoluation for the same
    Regards,
    Rahul Bodhe

  • AbnerQC August 19, 2008, 9:34 pm

    Hello, i was wondering, if it is possible to do a dump from a base i have on a website to my localhost.

    In other words, does mysqldump has an address and port parameters?

    I looked into mysql manual but didnt find anything, thank you for your help.
    =)

  • Cihan Dogan November 12, 2008, 4:36 pm

    Can’t be better!

  • Student KMPP November 28, 2008, 2:21 am

    my friend told me that we also can backup mySQL database in .db extension instead of .sql

    as i can read from this page… i can this command in shell

    # mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

    could you all help me on using command in shell to backup in .db

    thanks in advanced

  • Roman January 7, 2009, 1:17 pm

    Thanks! Good Article!

  • rapidshare library January 24, 2009, 7:16 pm

    ya you can also use mysqlyog tool to maintain yourdatabase from your pc

  • Safdar Imam February 10, 2009, 10:56 am

    Great Article!

    Thanks

    Safdar Imam

  • Ryan March 7, 2009, 9:57 pm

    I have a problem that is frustrating the crap out of me…. I’m trying to do a mysqldump to another server (obviously don’t want to keep the backup on the same server)…. I’m trying to do this through PHP like the following:

    $remote_backup = “/usr/bin/mysqldump $dbname –opt -h$host -u$user -p$password | gzip -c | ssh user@111.222.333.444 ‘cat > /home/httpd/vhosts/domain.com/backups/2009-03-07-15-13-56.gz'”;
    system($remote_backup);

    This not working from PHP, however when I run it direct from SSH shell below it works perfectly.

    /usr/bin/mysqldump $dbname –opt -h$host -u$user -p$password | gzip -c | ssh user@111.222.333.444 ‘cat > /home/httpd/vhosts/domain.com/backups/2009-03-07-15-13-56.gz’

    I setup key authentication so that SSH does not prompt for a password, but it is still not working from PHP.

    What am I doing wrong? I’ve spend tons of time searching for this solution…. any help would be greatly appreciated.

    Thanks.

    /usr/bin/mysqldump $dbname –opt -h$host -u$user -p$password | gzip -c | ssh user@111.222.333.444 ‘cat > /home/httpd/vhosts/domain.com/backups/2009-03-07-15-13-56.gz’

    • DG September 17, 2013, 3:05 pm

      PHP probably doesn’t have the environment variables setup for your ssh-agent process (assuming you’re using one).

  • Narendra March 16, 2009, 8:52 am

    Great Article!!!!!!Thanks alot

  • xps March 31, 2009, 1:07 pm

    I just want to copy with a where condition for a table ,but the same remote machines databases …….. What to do

  • vijesh May 20, 2009, 10:26 am

    Tell me how to copy with out data

  • SZMysqluser October 6, 2009, 4:23 pm

    We are using jasper reports and moved everything on Server A to Server B and sysadmin wiped out Server A. He did backup all files on Server A first. Now the developer wants the db from Server A restored (the jasper tables). Could we take the .frm files from the database directory and replace the ones on Server B with the ones from Server A? This was an application we didn’t know used mysql so we didn’t back anything up using mysqldump. Thanks.

  • thesql! October 14, 2009, 5:31 am

    xps:

    use mysqldump’s -w switch to specify where
    -w “dateColumn > ‘2009-10-06′”

  • Mr. The plague October 14, 2009, 10:39 am

    This needs to be:

    mysqldump -u root –password=pwordhere puaSite | ssh paul@140.203.210.11 mysql -u root –password=pwordhere puaSite

    If you are prompted for passwords

  • Anupam November 26, 2009, 11:45 pm

    This was really helpful and thanks.

  • namrata January 28, 2010, 6:29 am

    really nice ya…

  • amit February 26, 2010, 1:22 pm

    how cane get my sql backup on my server to another pc.

  • Henno September 16, 2010, 6:14 pm

    I’m using exactly this to export live database to development server. Recently it has started to fail:

    root@mybox:~# mysqldump -q -u root -psecret1 –add-drop-database mydb | gzip -c | ssh root@dev.local “gunzip | mysql -u root -psecret2 mydb”
    root@dev.local‘s password:
    {{{ a few minutes of silence, then: }}}
    mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `cache` at row: 4004

  • Ray March 31, 2011, 9:50 pm

    mysqldump -u user -p db-name > db-name.out
    After this command, I can’t find the db-name.out in any directory,
    where is it?

  • Ray March 31, 2011, 9:50 pm

    mysqldump -u user -p db-name > db-name.out
    After this command, I can’t find the db-name.out in any directory,
    where is it?

  • omkar yadav April 21, 2011, 4:38 am

    hi i m omkar yadav your website is very nice and very powerfull
    this website is my lucky website

    thank you.
    omkar yadav

  • Henno April 21, 2011, 3:05 pm

    Fix to Error 2013: Lost connection to MySQL server during query when dumping table:

    mysqldump -q -u root -pMYSQLROOTPASSWORD –max-allowed-packet=256M –add-drop-database MYDB | ssh root@mydevmachine.local “cat – > /tmp/tmp.sql;mysql -u root -pDEVMYSQLROOTPASSWRD MYDB < /tmp/tmp.sql"

    Not as fast but very reliable.

  • francis June 22, 2011, 11:44 am

    Great – thanks for a nicely written guide.
    It wokred an absolute treat!

  • Arin July 15, 2011, 8:54 am

    Nice! worked perfectly!

  • Vab Media July 27, 2011, 2:12 am

    This finally helped me figure out how to dump the stuff I needed into the remote database. There was stuff I looked at before, but you finally explained it correctly.
    Thanks!!

  • Bob September 15, 2011, 9:25 am
  • Random September 15, 2011, 7:43 pm

    You can pipe it locally using
    “ssh removemachine command > pipe.out”

  • Aze October 11, 2011, 1:37 pm

    Thanks. Great snippets!

  • remotewtf December 6, 2011, 4:46 pm

    what the hell does user@remote.box mean? you’re sending it to his server’s email??? why can’t u linux pèople speak in simpler terms?

  • remotewtf December 6, 2011, 4:50 pm

    what i mean is i wanted to copy a mysql database to another domain, and now yet again i will fail because you linux ppl don’t speak in moron friendly terms.

  • RK February 16, 2012, 9:47 am

    thanks, this works really well to move database tables from one remote mysql server to another

  • Pedro Garcia March 21, 2012, 11:53 am

    Very, very, VERY useful! Thanks a lot for sharing this!

  • zamiere November 28, 2012, 1:28 pm

    Remember the –protocol=tcp parameter for mysqldump if you want to dump from locally forwarded remote port!

  • Arun June 15, 2013, 8:27 am

    i have one Linux VPS and the hard drive on this VPS has been full because of IBdata1 file. now i want to take backup of Mysql database.

    is it possible that i can take backup from linux VPS to my windows system directly.

    Please Help on this.

  • montanaembassy August 8, 2013, 10:37 pm

    Hi, I tried this and find that the tables of the database have their links removed and moreover all relationships are removed. I wonder what I could be doing wrong?
    Thanks

  • Sergey December 10, 2013, 11:36 pm

    Thanks, nice post. Is there any way to gzip transferred data?

    • Sergey December 11, 2013, 12:00 am

      oh! I’ve understood:

      mysqldump dbname | gzip -c | ssh vs3front.pre "gzip -d | mysql dbname"

      Maybe will be useful to someone

  • Yibala Christopher October 12, 2014, 7:54 pm

    please am I to type the codes in phpmy Admin or as php code with any text editor.

  • Cyrille Tarla April 11, 2015, 12:52 am

    i tried the syntax $ mysqldump db-name | mysql -h remote.box.com db-name and it gave me an error ERROR 1045 (28000): Access denied for user what can be the exact syntax to copy a database from one server to another using command line thank you