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.
Best Answer
You could set an ssh tunnel and use
mysqldump
on your local machine.