up vote 1 down vote favorite
share [g+] share [fb]

I need to secure MySQL database replication between two CentOS 5.7 servers. Server 1 has the live database, and replication is already working fine to Server 2 which is located elsewhere on the internet.

Obviously this isn't good as the replication traffic could easily be read, so it needs to be secured.

I created an SSH tunnel by entering this into the Server 2:

ssh -f root@server1ip -L 3305:server1ip:3306 -N

I confirmed I was able to successfully log into MySQL on Server 1 from Server 2 using:

mysql -h27.0.0.1 -uuser -ppassword -P 3305

Now, does this mean that the mysql replication is now all going over SSH automatically? Is there anyway I can confirm this? Or is Server 2 forced to connect to Server 1 over SSH because of this port forwarding rule I have established?

I noticed if I reboot Server 2, the SSH tunnel is gone when it restarts, but the replication continues (I assumed back in plain text). Is there something I can do make sure the replication stops when the tunnel drops? I want to make sure it won't automatically continue replicating in clear text without SSH.

Thank you!

link|improve this question
67% accept rate
1  
What not use the built-in SSL support? – Mark Wagner Oct 26 '11 at 19:02
I tried for days but could not get it work. It became very complex when it came to generating and self-signing certificates... this SSH options seems much faster to configure – dabayl Oct 26 '11 at 21:14
feedback

2 Answers

up vote 1 down vote accepted

You need to set the slave's replication master configuration so that it'll travel over the tunnel's bound port, instead of the external address of the replication peer:

CHANGE MASTER TO MASTER_HOST='127.0.0.1' MASTER_PORT=3305;
link|improve this answer
Thanks Shane! That makes sense now as I can see how the slave will be forced to use the tunnel. Will this also mean replication will stop (break) if the tunnel drops? Such as a server reboot? – dabayl Oct 26 '11 at 21:11
@dabayl Absolutely! Take a look at autossh for lighting the tunnel back up when it dies or the server reboots. – Shane Madden Oct 26 '11 at 21:13
will do. Cheers! – dabayl Oct 26 '11 at 21:15
feedback
up vote 0 down vote

Instead of a SSH-Tunnel i would recommend using stunnel which i find easier to manage for non-interactive connection. But that's not a must.

After setting up the conncetion (via SSH or stunnel or something else) you have to start the replication - see Shane Madden's answer. Be sure to use the right MASTER_*-options (see CHANGE MASTER TO Syntax).

Also i would prefer to use the build-in SSL-support of mysqld, just as embobo suggested. So you do not have any external dependencies for your replication setup.

All setup mysql should automatic start replication after reboot/restart/... - but be sure to monitor your mysql-replication-setup because there are many situations and circumstances which can break your setup. I really recommend a tool like pt-heartbeat (successor of mk-heartbeat from the maatkit-tools) because Seconds_Behind_Master from SHOW SLAVE STATUS \G isn't reliable.

HTH

link|improve this answer
Thank you very much for the info and links and ideas shared... I'll check them out! I did try for days to do it with SSL but I couldn't get it work and my understanding of certificates is not very good. Please let me know if you of any good resources to explain SSL setup. Thanks! – dabayl Oct 26 '11 at 21:12
Good starting points for mysql/SSL are the mysql docs on SSL in general and the mysql docs on SSL and replication. What exact problems do you have with SSL? – m.sr Oct 27 '11 at 16:38
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.