{ 47 comments… read them below or add one }

1 nice November 28, 2006 at 9:03 pm

nice and easy. Thanks


2 John January 16, 2007 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 February 8, 2007 at 5:54 pm

thanks for the informations in here.
it really helped me, a newbie..


4 Raul March 27, 2007 at 7:27 am

Amazingly Simple :)
Thanks a lot… U saved atleast a couple of hours for me :D


5 Jason April 26, 2007 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?



6 Database June 14, 2007 at 6:16 pm

thank you good article


7 Sky February 6, 2008 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 May 3, 2008 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 July 17, 2008 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
Rahul Bodhe


10 AbnerQC August 19, 2008 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 November 12, 2008 at 4:36 pm

Can’t be better!


12 Student KMPP November 28, 2008 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 January 7, 2009 at 1:17 pm

Thanks! Good Article!


14 rapidshare library January 24, 2009 at 7:16 pm

ya you can also use mysqlyog tool to maintain yourdatabase from your pc


15 Safdar Imam February 10, 2009 at 10:56 am

Great Article!


Safdar Imam


16 Ryan March 7, 2009 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'”;

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.


/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 DG September 17, 2013 at 3:05 pm

PHP probably doesn’t have the environment variables setup for your ssh-agent process (assuming you’re using one).


18 Narendra March 16, 2009 at 8:52 am

Great Article!!!!!!Thanks alot


19 xps March 31, 2009 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


20 vijesh May 20, 2009 at 10:26 am

Tell me how to copy with out data


21 SZMysqluser October 6, 2009 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.


22 thesql! October 14, 2009 at 5:31 am


use mysqldump’s -w switch to specify where
-w “dateColumn > ‘2009-10-06′”


23 Mr. The plague October 14, 2009 at 10:39 am

This needs to be:

mysqldump -u root –password=pwordhere puaSite | ssh paul@ mysql -u root –password=pwordhere puaSite

If you are prompted for passwords


24 Anupam November 26, 2009 at 11:45 pm

This was really helpful and thanks.


25 namrata January 28, 2010 at 6:29 am

really nice ya…


26 amit February 26, 2010 at 1:22 pm

how cane get my sql backup on my server to another pc.


27 Henno September 16, 2010 at 6:14 pm

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


28 Ray March 31, 2011 at 9:50 pm

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?


29 Ray March 31, 2011 at 9:50 pm

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?


30 omkar yadav April 21, 2011 at 4:38 am

hi i m omkar yadav your website is very nice and very powerfull
this website is my lucky website

thank you.
omkar yadav


31 Henno April 21, 2011 at 3:05 pm

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.


32 francis June 22, 2011 at 11:44 am

Great – thanks for a nicely written guide.
It wokred an absolute treat!


33 Arin July 15, 2011 at 8:54 am

Nice! worked perfectly!


34 Vab Media July 27, 2011 at 2:12 am

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.


35 Bob September 15, 2011 at 9:25 am
36 Random September 15, 2011 at 7:43 pm

You can pipe it locally using
“ssh removemachine command > pipe.out”


37 Aze October 11, 2011 at 1:37 pm

Thanks. Great snippets!


38 remotewtf December 6, 2011 at 4:46 pm

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?


39 remotewtf December 6, 2011 at 4:50 pm

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.


40 RK February 16, 2012 at 9:47 am

thanks, this works really well to move database tables from one remote mysql server to another


41 Pedro Garcia March 21, 2012 at 11:53 am

Very, very, VERY useful! Thanks a lot for sharing this!


42 zamiere November 28, 2012 at 1:28 pm

Remember the –protocol=tcp parameter for mysqldump if you want to dump from locally forwarded remote port!


43 Arun June 15, 2013 at 8:27 am

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.


44 montanaembassy August 8, 2013 at 10:37 pm

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?


45 Sergey December 10, 2013 at 11:36 pm

Thanks, nice post. Is there any way to gzip transferred data?


46 Sergey December 11, 2013 at 12:00 am

oh! I’ve understood:

mysqldump dbname | gzip -c | ssh vs3front.pre "gzip -d | mysql dbname"

Maybe will be useful to someone


47 Yibala Christopher October 12, 2014 at 7:54 pm

please am I to type the codes in phpmy Admin or as php code with any text editor.


Leave a Comment

Tagged as: , , , , , , , , , , , , , , , , ,

Previous post:

Next post: