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.
Featured Articles:
- 20 Linux System Monitoring Tools Every SysAdmin Should Know
- 20 Linux Server Hardening Security Tips
- 10 Greatest Open Source Software Of 2009
- My 10 UNIX Command Line Mistakes
- Top 5 Email Client For Linux, Mac OS X, and Windows Users
- Top 20 OpenSSH Server Best Security Practices
- Top 10 Open Source Web-Based Project Management Software
- Top 5 Linux Video Editor Software
- Email this to a friend
- Download PDF version
- Printable version
- Comment RSS feed
- Last Updated: Feb/6/2008
{ 25 comments… read them below or add one }
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 :D
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 ;)
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
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.
=)
Can’t be better!
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
Thanks! Good Article!
ya you can also use mysqlyog tool to maintain yourdatabase from your pc
Great Article!
Thanks
Safdar Imam
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’
Great Article!!!!!!Thanks alot
I just want to copy with a where condition for a table ,but the same remote machines databases …….. What to do
Tell me how to copy with out data
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.
xps:
use mysqldump’s -w switch to specify where
-w “dateColumn > ‘2009-10-06′”
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
This was really helpful and thanks.
really nice ya…
how cane get my sql backup on my server to another pc.