Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I want to dump the database on my remotely hosted site in regular intervals using a shell script. Unfortunately the server is locked down pretty tight, has no mysqldump installed, binary files can't be executed by normal users/in home directories (so I can't "install" it myself) and the database lives on a separate server, so I can't grab the files directly.

The only thing I can do is log into the webserver via SSH and establish a connection to the database server using the mysql command line client. How can I dump the contents to a file a la mysqldump in SQL format?
Bonus: If possible, how can I dump the contents directly to my end of the SSH connection?


I like the SSH tunnel idea proposed below, however, I'd need to double tunnel it.

localhost -> remote web server -> remote database server

This works okay:

$ ssh -f -L 3306:mysql.example.com:3306 user@ssh.example.com -N

However, trying to log in to mysql fails:

$ mysqldump -P 3306 -h localhost -u user -p db
Enter password: 
mysqldump: Got error: 1045: Access denied for user 'user'@'localhost'
   (using password: YES) when trying to connect

I guess it expects the user credentials to be 'user'@'mysql.example.com'. How can I coax it into using that instead of the "real" host?


Okay, I got it to work by adding 127.0.0.1 mysql.example.com to my hosts file and connecting via mysqldump -P 3306 -h mysql.example.com -u user -p db. Issue resolved! I'd still be interested in a solution that doesn't require me to edit the hosts file though.

share|improve this question
1  
I'd suggest that you grant permissions for 'user'@'localhost' in addition to 'user'@'mysql.example.com' in MySQL, instead of editing the hosts file. –  Znarkus Feb 1 '11 at 15:49

4 Answers 4

up vote 12 down vote accepted

You could set an ssh tunnel and use mysqldump on your local machine.

ssh -f -L 3306:localhost:3306 user@remoteserver -N
mysqldump -P 3306 -h localhost -u dbuser dbname
share|improve this answer
    
Thanks, figured it out! –  deceze Apr 21 '10 at 3:27
    
On Unix, 'localhost' is treated differently. In which, the port number is ignored, you need to use 127.0.0.1 to make this work. dev.mysql.com/doc/refman/5.0/en/connecting.html –  Adrian Gunawan Jan 19 at 23:41

http://sypex.net/en/ - you can use this script for database backup after uploading it to server. Very handy and quick

share|improve this answer

Upload this script to your web server (hope you have at least FTP access), start it, specify database name/host(address of your "separate server")/username/password and dump database to any location you want...

share|improve this answer

If your server is a Web server, you can use a PHP script which dump the database to screen. On your backup server, put a cron which wget the dump page. Then your database is dumped to your backup server just by HTTP.

Don't forget security : Everybody is available to dump your base. Put a password, or an IP limitation...

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.