secure MySQL-replication with stunnel 2

I use stunnel to secure my MySQL-replication. MySQL itself offers SSH, but it´s not so simple to install. If stunnel works on your sytsme, you can use it for much more daemons/ports (secure imap, pop3, http).

With stunnel only the remote connects to the master server use the tunnel. Local connects will use the socket-file (i.e. Websites who use mysql over localhost or 127.0.0.1 on port 3306). So you can setup stunnel without changing anything else. Stunnel accepts connects on port 3307 and forwards them to local:3306.

Installation
./configure
make
make install
mkdir /usr/local/var/lib/stunnel/etc

I miss the init-script in /ect/init.d somehow – You can download it here.
Create a pem-File for MySQL over stunnel on each server:

openssl genpkey -algorithm RSA -pkeyopt rsa_keygen_bits:4096 -out key.pem
openssl req -new -x509 -key key.pem -out cacert.pem -days 1095
openssl dhparam -2 1024 -out dhparam.pem
cat key.pem cacert.pem dhparam.pem > /usr/local/etc/stunnel/mysql.pem
chmod 400 /usr/local/etc/stunnel/mysql.pem


Next add two entries to the stunnel.conf. Make sure to change the values for accept and connect on the second server.

[repliserver]
accept = server1:3307
connect=127.0.0.1:3306
cert = /usr/local/etc/stunnel/mysql.pem

[repliclient]
accept=127.0.0.1:3307
connect= server2:3307
client=yes
cert = /usr/local/etc/stunnel/mysql.pem

The complete stunnel.conf on my system looks like:

chroot = /usr/local/var/lib/stunnel/
setuid = nobody
setgid = nogroup
pid = /stunnel.pid
options = NO_SSLv2
[repliserver]
accept = server1:3307
connect=127.0.0.1:3306
cert = /usr/local/etc/stunnel/mysql.pem
[repliclient]
accept=127.0.0.1:3307
connect= server2:3307
client=yes
cert = /usr/local/etc/stunnel/stunnel.pem

Start stunnel on both servers. To check if everything works as expected, use netstat -pln | grep :3307.


tcp 0 0 176.9.24.113:3307 0.0.0.0:* LISTEN 20828/stunnel

You can connect to the master with a simple mysql -h 127.0.0.1 -P 3307.
By now the replication uses port 3306 and as the master-server not 127.0.0.1. So we must change the master-settings within mysql.
Never run CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=3307;.

If you specify the MASTER_HOST or MASTER_PORT option, the slave assumes that the master server is different from before (even if the option value is the same as its current value.) In this case, the old values for the master binary log file name and position are considered no longer applicable.

Correct is the following procedure:
stop slave;
show slave status \G

You only need

Master_Log_File: mysql-bin.000229
Read_Master_Log_Pos: 416847212

Reconfigure the master-server with this values:

CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=3307, MASTER_LOG_FILE='mysql-bin.000229', MASTER_LOG_POS=410672418;

Retart the replication with start slave;.
WARNING: stunnel doesn´t use certificates by default. I`ll post how to setup stunnel with certs within the next days.