3

I have a Master-Master MySQL replication setup between two servers. I'm about to move one to another location, so replication will be over the Internet and will - of course - need to be secured.

From what I can tell, I basically have two options:

  1. Use built-in SSL support in MySQL replication.
  2. Use SSH tunneling to forward ports used for replication.

Are there significant security, performance, or maintenance benefits to choosing one over the other?

The below output makes me think I can't use option 1 at all. However, I'd still like to know the pros and cons of each.

EDIT: I looked further, and I do have ssl support. It's just disabled at the moment.

mysql> SHOW VARIABLES LIKE '%ssl%';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_openssl  | DISABLED |
| have_ssl      | DISABLED |
| ssl_ca        |          |
| ssl_capath    |          |
| ssl_cert      |          |
| ssl_cipher    |          |
| ssl_key       |          |
+---------------+----------+
7 rows in set (0.00 sec)

Thank you for any help/advice.

Andrew Ensley
  • 932
  • 2
  • 17
  • 30

3 Answers3

4

erm, no clear answer on this one.

Using the embedded ssl wrapper is a lower overhead approach - but you're potentially tying up a heavy msyql processes with network I/O - but this is only really a problem if you're replicating to lots of other boxes and/or you're very tight for memory. Routing the traffic through an additional process would add a very slight amount of latency - but if the boxes are in different networks this is unlikely to be significant.

If it were me I would be comparing the merits of the in-process SSL with a full VPN link (not necessarily ssh based). The latter would support more than just mysql replication (private network DNS/LDAP, remote access, logging....) and using an implementation with NAT (e.g. running ppp through ssh/ssl) could be quickly re-jigged to run across a backup route. This approach also provides for simpler and more consistent diagnostic of communication problems.

symcbean
  • 21,009
  • 1
  • 31
  • 52
  • +1 for a full VPN - you get the encryption of either of the above methods but don't take the performance hit (or have to maintain non/less standard configurations). – James Yale Apr 21 '11 at 13:13
  • Interesting. I hadn't considered setting up a VPN. For my implementation, it's overkill, but a good idea for others, I'm sure. Fortunately, I'm only replicating between two servers, and I have plenty of RAM, so that's not a concern right now. I'll probably go with SSL thanks to your and caelyx's advice. – Andrew Ensley Apr 21 '11 at 20:52
  • re performance the opposite is true: with a VPN all traffic is routed through the VPN server so it's definitely going to be slower unless one of the two SQL end points is on the same machine as the VPN server (in which case it's going to be equivalent to a SSH tunnel). – capr Nov 14 '21 at 11:26
3

I prefer SSL replication.

I find it's usually cleaner to use built-in functionality than attempting to graft in an independent security layer (e.g. SSH). The more you customise, the more work you have to do, in building, debugging, monitoring, and fixing it, and the harder it is for 3rd parties (e.g. mailing lists) to help you if it doesn't perform as expected.

caelyx
  • 699
  • 3
  • 7
  • Thanks for the advice. I'm going to head down the SSL road and see how it goes. – Andrew Ensley Apr 21 '11 at 20:54
  • Finally was able to test this out, and I had absolutely no problems at all (other than forgetting to open up the firewalls for replication). I'm glad I went this route. It was much simpler than setting up and monitoring a yet another service. – Andrew Ensley May 23 '11 at 15:48
1

As per MySQL documentation, you may not have SSL enabled. Please take a look at http://dev.mysql.com/doc/refman/5.1/en/secure-using-ssl.html and how to start on the server ... Also, MySQL provides good reference for MySQL replication with SSL at http://dev.mysql.com/doc/refman/5.1/en/replication-solutions-ssl.html ...

user74596
  • 66
  • 2