Copy MySQL database from one server to another remote server

Copy MySQL database from one server to another remote server

by Vivek Gite · 22 comments

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. John 01.16.07 at 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 3 joel ferido 02.08.07 at 5:54 pm thanks for the informations in here. it really helped me, a newbie.. thanks.. 4 Raul 03.27.07 at 7:27 am Amazingly Simple :) Thanks a lot… U saved atleast a couple of hours for me :D 5 Jason 04.26.07 at 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 6 Database 06.14.07 at 6:16 pm thank you good article 7 Sky 02.06.08 at 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. 8 Carl 05.03.08 at 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 ;) 9 Rahul Bodhe 07.17.08 at 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 10 AbnerQC 08.19.08 at 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. =) 11 Cihan Dogan 11.12.08 at 4:36 pm Can’t be better! 12 Student KMPP 11.28.08 at 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
13 Roman 01.07.09 at 1:17 pm

Thanks! Good Article!
14 rapidshare library 01.24.09 at 7:16 pm

ya you can also use mysqlyog tool to maintain yourdatabase from your pc
15 Safdar Imam 02.10.09 at 10:56 am

Great Article!

Thanks

Safdar Imam
16 Ryan 03.07.09 at 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’
17 Narendra 03.16.09 at 8:52 am

Great Article!!!!!!Thanks alot
18 xps 03.31.09 at 1:07 pm

I just want to copy with a where condition for a table ,but the same remote machines databases …….. What to do
19 vijesh 05.20.09 at 10:26 am

Tell me how to copy with out data
20 SZMysqluser 10.06.09 at 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.
21 thesql! 10.14.09 at 5:31 am

xps:

use mysqldump’s -w switch to specify where
-w “dateColumn > ‘2009-10-06′”
22 Mr. The plague 10.14.09 at 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