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
OR
$ mysql -u user -p 'password' db-name

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
50 comments… add one
  • vijay Jan 23, 2016 @ 9:24

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

  • DBA Dec 22, 2015 @ 7:26
  • 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

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

  • 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

  • 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

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

  • zamiere Nov 28, 2012 @ 13:28

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

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.