Loading ...Copy MySQL database from one server to another remote server
Usually you run mysqldump to create database copy:
$ 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
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
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
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.
Want to stay up to date with the latest Linux tips, news and announcements? Subscribe to our free e-mail newsletter or RSS feed to get all updates.
You can Email this page to a friend.
You may also be interested in other helpful articles:
- Quickly Backup / dump MySql / Postgres database to another remote server securely
- How Do I Enable remote access to MySQL database server?
- Install and configure MySQL on Windows XP or Vista or 2003 server
- MySQL create an Anonymous or limited access only account
- MySQL avoid unauthorized reading and SQL Injection vulnerabilities in PHP
Discussion on This Article:
Leave a Reply
We encourage your comments, and suggestions. But please stay on topic, be polite, and avoid spam. Thank you very much for stopping by our site!
Tags: copy mysql to another computer, mysql command, mysql database, mysql server, mysqldump command, remote server, server login, ssh, unix linux
nice and easy. Thanks
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
thanks for the informations in here.
it really helped me, a newbie..
thanks..
Amazingly Simple
Thanks a lot… U saved atleast a couple of hours for me
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
thank you good article
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.
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