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 |
|---|---|
| File Management | cat |
| Firewall | Alpine Awall • CentOS 8 • OpenSUSE • RHEL 8 • Ubuntu 16.04 • Ubuntu 18.04 • Ubuntu 20.04 |
| Network Utilities | 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 |
ssh user@192.168.x.x mysql -u username -p'1234' db-name > /path/file.sqlPlease check this link …
https://gist.github.com/ecdundar/789660d830d6d40b6c90
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 am I to type the codes in phpmy Admin or as php code with any text editor.
Thanks, nice post. Is there any way to gzip transferred data?
oh! I’ve understood:
Maybe will be useful to someone
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
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.
Remember the –protocol=tcp parameter for mysqldump if you want to dump from locally forwarded remote port!
Very, very, VERY useful! Thanks a lot for sharing this!
thanks, this works really well to move database tables from one remote mysql server to another
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.
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?
Thanks. Great snippets!
You can pipe it locally using
“ssh removemachine command > pipe.out”
Found that: http://code.google.com/p/dbsynchro/
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!!
Nice! worked perfectly!
Great – thanks for a nicely written guide.
It wokred an absolute treat!
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.
hi i m omkar yadav your website is very nice and very powerfull
this website is my lucky website
thank you.
omkar yadav
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?
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
how cane get my sql backup on my server to another pc.
really nice ya…
This was really helpful and thanks.
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
xps:
use mysqldump’s -w switch to specify where
-w “dateColumn > ‘2009-10-06′”
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.
Tell me how to copy with out data
I just want to copy with a where condition for a table ,but the same remote machines databases …….. What to do
Great Article!!!!!!Thanks alot
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
Safdar Imam
ya you can also use mysqlyog tool to maintain yourdatabase from your pc
Thanks! Good Article!
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
Can’t be better!
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.
=)
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
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 😉
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.
thank you good article
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
Amazingly Simple 🙂
Thanks a lot… U saved atleast a couple of hours for me 😀
thanks for the informations in here.
it really helped me, a newbie..
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
nice and easy. Thanks