{ 46 comments… read them below or add one }

1 nice November 28, 2006 at 9:03 pm

nice and easy. Thanks

Reply

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

Reply

3 joel ferido February 8, 2007 at 5:54 pm

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

Reply

4 Raul March 27, 2007 at 7:27 am

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

Reply

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?

Thanks.
-Jason

Reply

6 Database June 14, 2007 at 6:16 pm

thank you good article

Reply

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.

Reply

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 ;)

Reply

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
Regards,
Rahul Bodhe

Reply

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.
=)

Reply

11 Cihan Dogan November 12, 2008 at 4:36 pm

Can’t be better!

Reply

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

Reply

13 Roman January 7, 2009 at 1:17 pm

Thanks! Good Article!

Reply

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

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

Reply

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

Great Article!

Thanks

Safdar Imam

Reply

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’”;
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’

Reply

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).

Reply

18 Narendra March 16, 2009 at 8:52 am

Great Article!!!!!!Thanks alot

Reply

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

Reply

20 vijesh May 20, 2009 at 10:26 am

Tell me how to copy with out data

Reply

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.

Reply

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

xps:

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

Reply

23 Mr. The plague October 14, 2009 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

Reply

24 Anupam November 26, 2009 at 11:45 pm

This was really helpful and thanks.

Reply

25 namrata January 28, 2010 at 6:29 am

really nice ya…

Reply

26 amit February 26, 2010 at 1:22 pm

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

Reply

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

Reply

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?

Reply

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?

Reply

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

Reply

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.

Reply

32 francis June 22, 2011 at 11:44 am

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

Reply

33 Arin July 15, 2011 at 8:54 am

Nice! worked perfectly!

Reply

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.
Thanks!!

Reply

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”

Reply

37 Aze October 11, 2011 at 1:37 pm

Thanks. Great snippets!

Reply

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?

Reply

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.

Reply

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

Reply

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

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

Reply

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!

Reply

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.

Reply

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?
Thanks

Reply

45 Sergey December 10, 2013 at 11:36 pm

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

Reply

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

Reply

Leave a Comment

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

Previous post:

Next post: