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 firstname.lastname@example.org -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.