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.
🐧 50 comments so far... add one ↓
| Category | List of Unix and Linux commands |
|---|---|
| Disk space analyzers | df • ncdu • pydf |
| File Management | cat • cp • mkdir • tree |
| Firewall | Alpine Awall • CentOS 8 • OpenSUSE • RHEL 8 • Ubuntu 16.04 • Ubuntu 18.04 • Ubuntu 20.04 |
| Network Utilities | NetHogs • dig • host • ip • nmap |
| OpenVPN | CentOS 7 • CentOS 8 • Debian 10 • Debian 8/9 • Ubuntu 18.04 • Ubuntu 20.04 |
| Package Manager | apk • apt |
| Processes Management | bg • chroot • cron • disown • fg • jobs • killall • kill • pidof • pstree • pwdx • time |
| Searching | grep • whereis • which |
| User Information | groups • id • lastcomm • last • lid/libuser-lid • logname • members • users • whoami • who • w |
| WireGuard VPN | Alpine • CentOS 8 • Debian 10 • Firewall • Ubuntu 20.04 |
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 😉
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’
PHP probably doesn’t have the environment variables setup for your ssh-agent process (assuming you’re using one).
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.
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
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?
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?
hi i m omkar yadav your website is very nice and very powerfull
this website is my lucky website
thank you.
omkar yadav
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.
Great – thanks for a nicely written guide.
It wokred an absolute treat!
Nice! worked perfectly!
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!!
Found that: http://code.google.com/p/dbsynchro/
You can pipe it locally using
“ssh removemachine command > pipe.out”
Thanks. Great snippets!
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?
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.
thanks, this works really well to move database tables from one remote mysql server to another
Very, very, VERY useful! Thanks a lot for sharing this!
Remember the –protocol=tcp parameter for mysqldump if you want to dump from locally forwarded remote port!
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.
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
Thanks, nice post. Is there any way to gzip transferred data?
oh! I’ve understood:
Maybe will be useful to someone
please am I to type the codes in phpmy Admin or as php code with any text editor.
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
Please check this link …
https://gist.github.com/ecdundar/789660d830d6d40b6c90
ssh user@192.168.x.x mysql -u username -p'1234' db-name > /path/file.sql