0

we need remote connections from/to our mysql server for two reasons.

  1. testing/changes: we want to connect to a remote mysql server to manage databases with e.g. sqlYOG
  2. our production web server will be using a mysql server not running on the same IP, so we need to configure our application to use an external remote mysql server

I've read about using ssh tunnels and limiting user access to mysql based on IP. We have IP's that change around every 24 hours so that could be a bit of a party pooper.

We are looking for a safe, yet flexible way to set up remote connections to mysql. Any advice on this?

solsol
  • 1,121
  • 8
  • 21
  • 31

2 Answers2

1

Just use the SSL capabilities of MySQL itself. You could even use SSL client certificates for authentication.

See Using SSL for Secure Connections for details.

joschi
  • 20,747
  • 3
  • 46
  • 50
  • looks like there is a performance hit when doing this, is that correct? – solsol Jun 28 '10 at 14:08
  • Yes, in approximately the same order of magnitude like when using SSH or IPSec to tunnel the MySQL connection. – joschi Jun 28 '10 at 14:21
  • thanks, is limiting access by IP per user also a viable solution? – solsol Jun 28 '10 at 14:30
  • Depends on your connection method; each SSL-enabled connection must set up SSL, which is expensive in terms of time, data, and computation. This isn't true for tunneled connections through SSH. A single, SSL-enabled connection is just as fast, and possibly faster, than that same connection through an SSH tunnel, but if you're making multiple connections, SSH is going to be faster, especially given that SSH has an option to compress data across the wire, negating the advantage that native MySQL connections would otherwise have due to their compression option. – BMDan Jun 28 '10 at 14:39
1

Limit MySQL users to access from localhost/127.0.0.1, and then on each client machine, do:

ssh -L 127.0.0.1:3306:127.0.0.1:3306 user@remotehost

Now, you can connect to the MySQL database on each client machine simply by connecting to localhost. Already have a MySQL server running on the client machine? Just change the line to:

ssh -L 127.0.0.1:3307:127.0.0.1:3306 user@remotehost

and use port 3307. To visualize it, port 3306 (or 3307) on the client machine's loopback interface is actually, secretly, port 3306 on the remote machine's loopback interface. Worth noting that this works for all operating systems, although the exact command you issue might be a bit different.

BMDan
  • 7,129
  • 2
  • 22
  • 34
  • what about the production web server that needs to have access to the remote mysql server on another IP? – solsol Jun 28 '10 at 14:24
  • Just use the 3307 variant, above. – BMDan Jun 28 '10 at 14:35
  • I'm not really sure I understand the above examples correctly. My PHP application uses MySQL (on another server) as a database. How can they communicate? – solsol Jun 28 '10 at 15:56
  • 1
    The client's 127.0.0.1:3306 is actually 127.0.0.1:3306 on the server. Therefore, the client can connect to himself, and as far as he can tell, he's actually the DB server. Only we, the sysadmins, know that's not the case. Meanwhile, as far as the DB server is concerned, the client is running on the DB server. Basically, you avoid screwing around with @host parameters in MySQL or firewall settings, because everybody thinks they're where they need to be, even though, in reality, not only are they two different machines, they might not even be on the same continent. – BMDan Jun 28 '10 at 17:45
  • thanks BMDan. right now I'm running SSH with public/private keys and use that to login to mysql on the server. So that's an SSH tunnel right? – solsol Jun 28 '10 at 18:59
  • If MySQL is already listening on 3306, how can SSH listen on that port? The 3307 variant should be fine, but I doubt that the original tunnel suggestion will work. – Andrew Jun 29 '10 at 00:53
  • MySQL is running on the remote server on port 3306/tcp. `ssh` opens a *local* socket on 3306/tcp which is possible because there is no `mysqld` running on the *local* machine. – joschi Jun 29 '10 at 05:46