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.

What not use the built-in SSL support?
–
Mark WagnerOct 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
–
dabaylOct 26 '11 at 21:14

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?
–
dabaylOct 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

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.

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!
–
dabaylOct 26 '11 at 21:12