2

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!

|improve this question
  • 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
1

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;
|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
  • 1
    @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
0

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

|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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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