≡ 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.

Share this on:

Your support makes a big difference:
I have a small favor to ask. More people are reading the nixCraft. Many of you block advertising which is your right, and advertising revenues are not sufficient to cover my operating costs. So you can see why I need to ask for your help. The nixCraft, takes a lot of my time and hard work to produce. If you use nixCraft, who likes it, helps me with donations:
Become a Supporter →    Make a contribution via Paypal/Bitcoin →   

Don't Miss Any Linux and Unix Tips

Get nixCraft in your inbox. It's free:



{ 50 comments… add one }
  • 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

  • DBA December 22, 2015, 7:26 am
  • vijay January 23, 2016, 9:24 am

    ssh user@192.168.x.x mysql -u username -p'1234' db-name > /path/file.sql

Leave a Comment


   Tagged with: , , , , , , , , , , , , , , , , ,