MySQL Bugs: #75370: Indicate if SSL is used for replication

Bug #75370	Indicate if SSL is used for replication
Submitted:	1 Jan 2015 10:43	Modified:	6 Aug 2015 14:46
Reporter:	Daniël van Eeden (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S4 (Feature request)
Version:	5.6.19	OS:	Any
Assigned to:	Todd Farmer	CPU Architecture:	Any
Tags:	replication, SSL, tls

Description:
When the replication user doesn't have REQUIRE SSL/X509/etc set but the server allows SSL and the slave is configured for SSL it is really hard to tell if SSL is actually used.

Related:
 - Bug #60986
 - Bug #75368

How to repeat:
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: repl
                  Master_Port: 5622
...
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
...
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /foo/CAcert.pem
           Master_SSL_CA_Path: 
              Master_SSL_Cert: /foo/client-cert.pem
            Master_SSL_Cipher: 
               Master_SSL_Key: /foo/client-key.pem
...
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
...
1 row in set (0.00 sec)

2014-12-31 12:16:19 10366 [Note] Slave SQL thread initialized, starting replication in log 'mysql_sandbox5622-bin.000008' at position 219, relay log './mysql_sandbox5619-relay-bin.000011' position: 394
2014-12-31 12:16:19 10366 [Note] Slave I/O thread: connected to master 'repl@127.0.0.1:5622',replication started in log 'mysql_sandbox5622-bin.000008' at position 219

I did use wireshark to inspect the traffic and it wasn't using SSL (I had SSL disabled on the master), but I could find any information about whether SSL was used or not in the SHOW SLAVE STATUS output or the error log.

Suggested fix:
Add info to 'show slave status\G' output to indicate if SSL is in use or not.
Add info to the 'Slave I/O thread' initialization message in the error log.

Hi Daniel,

Thanks for the feature request!

Would you be OK if this information was found somewhere *other* than SHOW SLAVE STATUS?  I ask because I'm interested in seeing connection type information exposed for all connections (in PROCESSLIST and P_S tables).  I'm curious whether this would be sufficient for your needs, or whether this information needs to be duplicated into SHOW SLAVE STATUS?

Thanks!

Hi Todd,

It would be better if this was possible via P_S for all connections. Having it in SHOW SLAVE STATUS would be nice, but the output is already too long (and changing the output might break parsers). If it's in P_S then a view in SYS could allow nice output showing the state of each connection.

And a P_S solution would also be better for Bug #60986. 

There were multiple reasons to file this bug:
 - For my work on the CIS Benchmark for MySQL 5.6
 - I will probably rollout SSL to make it possible to use the cleartext authentications which is required for authentication_pam.

This feature request is implemented in 5.7.8, as PERFORMANCE_SCHEMA.THREADS table now contains a CONNECTION_TYPE column.  Additionally, the actual cipher in use can be obtained via the PERFORMANCE_SCHEMA.STATUS_BY_THREAD table.  Both are described in more detail in the following blog post:

http://mysqlblog.fivefarmers.com/2015/08/05/identifying-which-connections-are-secure/