Home / server / Questions / 261972
Accepted
Andrew Ensley
Asked: 2011-04-21 15:44:10 +0800 CST

Secure MySQL replication; SSL or SSH tunneling?

  • 772

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.

mysql ssh ssl ssh-tunnel mysql-replication

3 Answers

  1. 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.

    • 4
  2. Best Answer

    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.

    • 3

  3. 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 ...

    • 1