Advanced Topics

Using Mysql with SSL (Contributed by Markus Jantti)

Some institutions allow only a few services through their firewalls, typically say telnet and ssh. If the host that has your gmail database, say 'remotehost', is on the inside of such a firewall, but you want to contact it from elsewhere, you must either persuade the administrator to allow MySQL connections or use ssh. Using a ssh tunnel may be a good idea, as this encrypts the traffic and allows for data compression, which can be useful with slow connections. If, as in my case, you use 'remotehost' as the pop server as well, you need to open a tunnel for that also.

The machine that is the POP3 and MySQL server inside the firewall is 'remotehost' and the machine on the outside on which you are running gmail and with which you wish to accces 'remotehost' is 'thishost'.

In order to use ssh, you must do the following:

1. In the Preferences -> Mysql Server dialog box, set the field "Mysql Server Hostname" to "127.0.0.1" (omitting the "). Setting it to "localhost" or its actual hostname or IP number does not work, not at least for me. The username, password and database name should be as they are on 'remotehost'.

2. Under Preferences -> Mail-Setups choose the Setup you need to modify (if you have just one, it is likely do be the one called "default") and set the field "POP3 Server" to "localhost". The POP username and password should be set as they are on 'remotehost'. If you use that host as the SMTP server, you'll need to modify that field as well. I don't, but if you do you will need to open a ssh tunnel for smtp as well, which should not be very hard to do.

3. Redirect the MySQL port (3306) on 'thishost' to be a tunnel to the MySQL port on 'remotehost', i.e., issue the command

% ssh -L 3306:remotehost:3306 username@remotehost

You probably should not have MySQL running on 'thishost'. In any case, what this does is it redirects connections to port 3306 on 'thishost' to port 3306 on 'remotehost', which is what you want. You can test this by running, on the command line

% mysql -p -P 3306 -h 127.0.0.1 'database'

where 'database' is e.g. the name of you gmail database on 'remotehost'.

4. Redirect the pop3 port (110 on my Red Hat 6.2 Linux by default) on 'thishost' to the same port on 'remotehost'. On my machine, I needed to be root to do this, although it appears that this can be circumvented (see below). So, do

% su -
(give the password)

and do

# ssh -L 110:remotehost:110 username@remotehost

(Note that I have logged into 'remotehost' as 'username' rather than root, as for security reasons ssh root logins are not allowed.)

Alternatively, change the pop3 port number in gmail code to a non-priviliged port. In gmail 0.7.0, the relevant variable is set on line 425 in gmail-0.7.0/src/pop3.c:

[root@markus src]# diff pop3.c.orig pop3.c 
425c425
< 	gint port = 110;
---
> 	gint port = 1111;

I changed the port number to 1111 and recompiled gmail and redirected 1111 to point to remotehost:110.

This should be it. The inconvenience here is that the two ssh shell commands must be issued on this model explicitly. They can be set in a configuration file. See the ssh man-page. I use ~/.ssh/config which looks like this:

LocalForward 3306 remotehost:3306
LocalForward 1111 remotehost:110

This way you only need to open a single ssh session to remotehost, start gmail and off you go.

If you need to redirect SMTP as well, that seems to listen to port 25.

Written by: markus.jantti@uta.fi, April 2001.