We customize and support standard web applications that our customers host on managed servers from different web hosters. Usually, the application consists of a web application in PHP and a database (mostly MySQL).
For our customers, we want to offer a a backup service that expands beyond what the web hosters offers (usually four weeks retention time). We want to backup the web application (no problem) and the database (problem).
One of the web hosters has a setup that prevents the user from accessing the MySQL database using mysql.sock
. Instead, they have a separate name (like user123.mydatabaseserver.com) for the managed server itself. A connection to the database is only possible by using the -h
parameter to mysql
, like this:
mysql -u mydbuser -p -h user123.mydatabaseserver.com
Now, usually we backup the databases from our backup server by using an SSH tunnel that connects to the live managed server and port forwards the MySQL port to the backup server, then connect to the database on the backup server through the tunnel and run mysqldump
with the forwarded port. However, due to the forced host name parameter, this is not possible in this case.
We are aware that we could connect to the managed server, dump the database there, then copy the resulting backup file using scp
or some other measure, but I wanted to see if I'm not missing something that would enable us to not use the storage and/or computing time of the live managed server for that backup.
Is there a solution using only port forwarding?
Edit 1: Trying to make the problem more clear
Server A (the backup system) Server B (the application server) Server C (the database server)
Server A should backup the database on server C. However, the MySQL access permissions allow only server B to connect to server C and access the database.
In this specific case, server B and server C have the same IP address, but it seems the hoster disabled access to MySQL through sockets and only allows TCP/IP connections.
In the mean time, I had the idea of chaining the SSH port forwards:
Bind port 22222 on server B to port 3306 on server C, then bind 44014 on server A to 22222 on server B. However, trying to SSH into server C from server B results in an error message: host address xxx.xx.xxx.xx not allowed.
dig +short user123.mydatabaseserver.com
is your friend.