3

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.

migrated from superuser.com Feb 8 '14 at 13:53

This question came from our site for computer enthusiasts and power users.

  • Why can't you connect to the MySQL server by its IP address rather than its hostname? dig +short user123.mydatabaseserver.com is your friend. – Denis Howe Feb 7 '14 at 13:12
  • I'm pretty sure I could, I'm just not sure I understand how this helps with the port forwarding/connection issue. Could you elaborate? – Dabu Feb 24 '14 at 7:27
1

You do not need an SSH tunnel to go from B to C, as the webapp on B connects to C using regular sockets, right ?

On server A, you should invoke SSH like this:

ssh -L44014:serverc:3306 user@serverb

This establishes an SSH tunnel between A and B. When your backup process, running on server A, connects to localhost:44014, the tunnel will cause the sshd process on server B to connect to port 3306 on server C. From server C's point of view, the connection will appear to come from server b on some random client port.

If you want to use that within a script, you can also pass the -N option to avoid spawning a shell, just do the port forwards. You can thus run the command in background and kill it when you no longer need the tunnel:

ssh -NL44014:serverc:3306 user@serverb &
TUNNEL_PID=$!
# do the backup on localhost:44014
kill $TUNNEL_PID

If the administrators of server B have disabled local port forwarding, you can use socat to emulate that behaviour, but it is a bit more complicated. Here we spawn one socat locally to make data go through ssh's stdio, and a remote socat to redirect it to the appropriate socket. For it to work you must have ssh passwordless login working already:

socat TCP-LISTEN:44014 EXEC:"ssh user@serverb socat STDIO TCP:serverc:3306"

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.