8

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.

  • 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
15

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
1

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

0

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

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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