MySQL Master-Master Replication over a Secure Stunnel Connection (SSL)

Random Entry: Data Warehousing and Metrics with Asterisk PBX
< Nagios check_ntp quits working in 2009 with Offset unknown | Turning the Iphone 3G into an almost useful always-on IM device >

MySQL Master-Master Replication over a Secure Stunnel Connection (SSL)

Posted by Admin • Saturday, February 7. 2009 • Category: Code and Hacks

I threw this together because I had to figure it out myself. I found info on how to setup master-master, and I found info on how to set up MySQL replication over stunnel. But master-master over stunnel turned out to be a little different. Not much, but here it is.

First of all - I won't go into the steps for setting up Master-Master itself - that's very well documented. Let's assume that you can convince one of your mysql's to slave off the other, and vice versa. (I used this page as a guide).

With that accomplished, let's assume that the boxes are not sitting next to each other but are in separate colo's or offices, and have some insecure internet to go between them (or maybe you don't even trust the local LAN). Yes, MySQL does have built-in SSL stuff, but so far the consensus is that it's rather difficult to work with. Moreover, I am more willing to trust stunnel (just my feeling on it).

Why not use ssh? Using something like ssh -n with a for loop that restarts it just doesn't sound enterprise-grade. I want something that actively reconnects when needed, based on active usage, and is actually intended for this. I still use SSH for port tunnelling all the time, but those are short-lived tunnels for my own personal use or debugging.

So our goals are: Secure transport and identity validation. Here we go.

Master-Master Replication over SSL/stunnel

Step By Step

I am doing this on ubuntu-server. Other distros should be similar.

Note also that I'm not explaining the security implications of these steps in great detail - you are expected to read up on this if you don't know already.

MySQL better be installed on both boxes and up and running. Note: this approach does not care if MySQL is bound to localhost or the public network interface, as it uses 3 ports to keep everything distinct
install stunnel (eg apt-get install stunnel)
generate some certs (This is well documented in openssl documentation, but I did it this way):
1. openssl req -new -x509 -days 3650 -nodes -config /etc/ssl/openssl.cnf -out stunnel.pem -keyout stunnel.pem
  Notice that I cranked up my days to 10 years for simplicity. This command generates both private and public keys at once, placing both in the same file.
2. Place stunnel.pem in /etc/stunnel/
3. Place same stunnel.pem in /etc/stunnel/client.pem on the other box
4. chmod 600 /etc/stunnel/*.pem
5. Repeat 1-4 on the other box
6. You should now have reciprocal cert files sitting around and doing nothing (yet)
If on Ubuntu, edit /etc/default/stunnel4 to enable stunnel altogether - set ENABLED to 1. Once you do this, the init.d script will start a new stunnel for each *.conf file it finds in /etc/stunnel/

On both boxes, copy original stunnel.conf to client.conf (filename is up to you). Edit these lines:

cert=/etc/stunnel/client.pem
pid = /client.pid  
output = /var/log/stunnel4/client.log ; optional, enable debug = 7 also
client = yes
[mysqlreplication]
accept = 3307
connect = box2:3308  ; Change to box1 on box2
retry = yes

Edit stunnel.conf in /etc/stunnel - that will be your server conf file. Do this on both boxes. Relevant items:

cert = /etc/stunnel/stunnel.pem
key = /etc/stunnel/stunnel.pem
verify = 2
CAfile = /etc/stunnel/stunnel.pem
[mysqlreplication]
accept = 3308
connect = 3306

start up stunnel and you should see the following (on both boxes):
- telnet localhost 3306 - should show you mysql garbage
- telnet localhost 3308 - should stay quiet and hang up on you (ssl handshake)
- telnet localhost 3307 - should show you mysql garbage only if the other box is fully working - take down mysql there to verify you are talking to the right place

Troubleshooting

Stunnel is kind of weird to troubleshoot, but here are some pointers.
You can run "stunnel /etc/stunnel/WHATEVER.conf -d7 -f" and it should (most of the time) output what it doesn't like about your configuration. That said, there was one case where it would not say anything, or log anything, and that was because that instance was already running! (ps aux | grep stunnel).

Do not be mislead by this error message:

You should check that you have specified the pid= in you configuration file

. Assuming that you did make the pid files distinct (as I did above), the error message merely means that stunnel returned 1 and not 0, for absolutely any reason.

Check what you have running using ps aux | grep stunnel. There should be several processes for each conf file, and when you stop stunnel, there should be none.

Stunnel is pretty resilient and reliable, but in my experience it doesn't like dynamic IPs. Because some of my facilities are not static, I have a cronjob that restarts stunnel nightly - in case the IP changed that day. I can tolerate a slight replication delay, but if you cannot then you may want to trigger a stunnel restart on IP change.

0 Trackbacks

Trackback specific URI for this entry

No Trackbacks

6 Comments

Display comments as (Linear | Threaded)

Dan on 2009-10-25 11:49:
Thx a lot mate!

I was searching for this solution for a couple of days... U saved my life, literally.. Great article!

Have a nice day and thx a lot!!
Nick on 2010-01-20 04:34:
Hi,

I have done mysql replication with this manual - http://www.howtoforge.com/mysql5_master_master_replication_debian_etch Very similar to this that you have used.

Stunnels get up with no problems:

tcp 0 0 0.0.0.0:3307 0.0.0.0: LISTEN 0 6108 2497/stunnel4 tcp 0 0 0.0.0.0:3308 0.0.0.0: LISTEN 0 6129 2511/stunnel4

But MySQL do not forward traffic from port 3306 to stunnel's port 3307, it just connect to the master:

tcp 0 0 192.168.75.130:3306 0.0.0.0:* LISTEN 106 10198 4122/mysqld tcp 0 0 192.168.75.130:41327 192.168.75.129:3306 ESTABLISHED 106 10673 4122/mysqld tcp 0 0 192.168.75.130:3306 192.168.75.129:46916 ESTABLISHED 106 10515 4122/mysqld

No errors in mysql's and stunnel's log files.

Have twice checked the configuration - identical to yours.

Next is part of my.cnf:

[client] port = 3306 socket = /var/run/mysqld/mysqld.sock

[mysqld]

REPLICATION

server-id = 2 replicate-same-server-id = 0 auto-increment-increment = 2 auto-increment-offset = 2

master-host = 192.168.75.129 master-user = replicator master-password = passwprd

master-port = 3307

master-connect-retry = 60 replicate-do-db = exampledb

log-bin = /var/log/mysql/mysql-bin.log binlog-do-db = exampledb

relay-log = /var/lib/mysql/slave-relay.log relay-log-index = /var/lib/mysql/slave-relay-log.index

user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp skip-external-locking

bind-address = 192.168.75.130

Maybe you have any ideas?

Thanks.
Akom on 2010-01-20 09:55:
Nick, I am somewhat unclear on what you're trying to achieve here. The design I outlined does the following:

1) mysql only needs to listen on localhost, default port (3306 in the example) 2) stunnel client on box A tunnels to stunnel server on box B, and vice-versa 3) mysql DOES NOT KNOW the IP of the other (master) server. That's because it connects to localhost (stunnel port 3307) and stunnel takes care of the rest by connecting to remote stunnel server on 3308. 4) when the connection "comes out" of the remote tunnel, it is sent to (again) localhost on 3306.

I don't see you doing this in your example. Your slave attempts to connect directly to its master, so no security is utilized. Moreover, if you managed to connect to the remote port 3307 the way you seem to be attempting to do, your connection would just be sent back to yourself via stunnel. I wonder if mysql can become its own slave?
Nick on 2010-01-28 09:26:
I am a little bit confused.

From you post "(I used this page as a guide)." but MySQL shouldn't know the IP address of the master. In that example ip address of master in written to my.cnf.

Maybe you can provide an example of you my.cnf?

Regards, Nick
Admin on 2010-02-16 09:46:
That page I refer to was not using stunnel. I am. Hence my configs are different. I think that you are still not understanding what stunnel does here. Each mysql thinks that its master is on the same box - they talk through stunnel! Try to re-read my post. Also, read about stunnel, try it out by itself, and don't attempt this until you understand what it is.

I have not changed my /etc/mysql/my.cnf (default as per ubuntu) Here is my /etc/mysql/conf.d/master-master.conf (on one of them): [mysqld]

old_passwords=1

log-bin binlog-do-db=firstdb #Db to replicate binlog-do-db=anotherdb
binlog-ignore-db=mysql # input the database that should be ignored for replication

binlog-ignore-db=test

server-id=2

information for becoming slave.

master-host = 127.0.0.1 master-user = replication master-password = somepassword master-port = 3307

auto increment - even

auto_increment_increment=2 auto_increment_offset=2

[mysql.server] user=mysql basedir=/var/lib

[mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
Jonathan Oliver on 2012-04-09 15:43:
This is a great post. There were a few little hiccups I bumped into that I wanted to mention.
1. When you generate a new key using openssl. If you specify the "days" parameter as a large value, it causes an integer wrap-around and has a negative expiration date. 3650 days (10 years) is a good number--as indicated in tutorial.
2. Obviously you need to make sure that you have port 3308 open in your firewall on both machines. That was one of those forehead moments for me.
3. Make sure your stunnel configuration files don't have unnecessary whitespace at the beginning of the line otherwise the /etc/init.d/stunnel (start|stop|restart) operations can't detect the chroot directory or pid files--the init.d scripts depend upon having those parameter at the start of the line with no whitespace preceding it.
Random tidbits, I know, but those were little hiccups on my end.

Akom's Tech Ruminations