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

ADVERTISEMENTS

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.

🐧 If you liked this page, please support my work on Patreon or with a donation.
🐧 Get the latest tutorials on SysAdmin, Linux/Unix, Open Source & DevOps topics via:
CategoryList of Unix and Linux commands
File Managementcat
FirewallAlpine Awall CentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
Network Utilitiesdig host ip nmap
OpenVPNCentOS 7 CentOS 8 Debian 10 Debian 8/9 Ubuntu 18.04 Ubuntu 20.04
Package Managerapk apt
Processes Managementbg chroot cron disown fg jobs killall kill pidof pstree pwdx time
Searchinggrep whereis which
User Informationgroups id lastcomm last lid/libuser-lid logname members users whoami who w
WireGuard VPNAlpine CentOS 8 Debian 10 Firewall Ubuntu 20.04

ADVERTISEMENTS
50 comments… add one
  • nice Nov 28, 2006 @ 21:03

    nice and easy. Thanks

  • John Jan 16, 2007 @ 23:09

    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 Feb 8, 2007 @ 17:54

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

  • Raul Mar 27, 2007 @ 7:27

    Amazingly Simple 🙂
    Thanks a lot… U saved atleast a couple of hours for me 😀

  • Jason Apr 26, 2007 @ 17:09

    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 Jun 14, 2007 @ 18:16

    thank you good article

  • Sky Feb 6, 2008 @ 16:12

    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

    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 Jul 17, 2008 @ 15:16

    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 Aug 19, 2008 @ 21:34

    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 Nov 12, 2008 @ 16:36

    Can’t be better!

  • Student KMPP Nov 28, 2008 @ 2:21

    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 Jan 7, 2009 @ 13:17

    Thanks! Good Article!

  • rapidshare library Jan 24, 2009 @ 19:16

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

  • Safdar Imam Feb 10, 2009 @ 10:56

    Great Article!

    Thanks

    Safdar Imam

  • Ryan Mar 7, 2009 @ 21:57

    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 Sep 17, 2013 @ 15:05

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

  • Narendra Mar 16, 2009 @ 8:52

    Great Article!!!!!!Thanks alot

  • xps Mar 31, 2009 @ 13:07

    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

    Tell me how to copy with out data

  • SZMysqluser Oct 6, 2009 @ 16:23

    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! Oct 14, 2009 @ 5:31

    xps:

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

  • Mr. The plague Oct 14, 2009 @ 10:39

    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 Nov 26, 2009 @ 23:45

    This was really helpful and thanks.

  • namrata Jan 28, 2010 @ 6:29

    really nice ya…

  • amit Feb 26, 2010 @ 13:22

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

  • Henno Sep 16, 2010 @ 18:14

    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 Mar 31, 2011 @ 21:50

    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 Mar 31, 2011 @ 21:50

    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 Apr 21, 2011 @ 4:38

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

    thank you.
    omkar yadav

  • Henno Apr 21, 2011 @ 15:05

    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 Jun 22, 2011 @ 11:44

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

  • Arin Jul 15, 2011 @ 8:54

    Nice! worked perfectly!

  • Vab Media Jul 27, 2011 @ 2:12

    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 Sep 15, 2011 @ 9:25
  • Random Sep 15, 2011 @ 19:43

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

  • Aze Oct 11, 2011 @ 13:37

    Thanks. Great snippets!

  • remotewtf Dec 6, 2011 @ 16:46

    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 Dec 6, 2011 @ 16:50

    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 Feb 16, 2012 @ 9:47

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

  • Pedro Garcia Mar 21, 2012 @ 11:53

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

  • zamiere Nov 28, 2012 @ 13:28

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

  • Arun Jun 15, 2013 @ 8:27

    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 Aug 8, 2013 @ 22:37

    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 Dec 10, 2013 @ 23:36

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

    • Sergey Dec 11, 2013 @ 0:00

      oh! I’ve understood:

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

      Maybe will be useful to someone

  • Yibala Christopher Oct 12, 2014 @ 19:54

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

  • Cyrille Tarla Apr 11, 2015 @ 0:52

    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 Dec 22, 2015 @ 7:26
  • vijay Jan 23, 2016 @ 9:24

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

Leave a Reply

Your email address will not be published. Required fields are marked *

Use HTML <pre>...</pre>, <code>...</code> and <kbd>...</kbd> for code samples.