MySQL Bugs: #60986: How to check which SSL Cipher is used by slave IO thread connection

Bug #60986	How to check which SSL Cipher is used by slave IO thread connection
Submitted:	27 Apr 2011 15:17	Modified:	6 Aug 2015 14:51
Reporter:	Leandro Morgado	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.1, 5.5	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Slave's can be setup to use SSL connections to the master as described here:
 http://dev.mysql.com/doc/refman/5.1/en/replication-solutions-ssl.html

It's also stated in the page above:
"You can use the SHOW SLAVE STATUS statement to confirm that the SSL connection was established successfully."

However, the exact method is not show. I presume it's by looking having "Master_SSL_Allowed: Yes" on the slave's SHOW SLAVE STATUS output. 

So suppose we have established an SSL IO Thread connection and we want to know which SSL Cipher is being used. There is currently no way to display this. This bug report suggests this be implemented in the output of SHOW SLAVE STATUS and any corresponding Information Schema tables.

The current output only mention of SSL Ciphers is:

slave1 [localhost] {msandbox} ((none)) > SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
...
          Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /home/lmorgado/sandboxes/rsandbox_5_5_9/newcerts/ca-cert.pem
           Master_SSL_CA_Path: 
              Master_SSL_Cert: /home/lmorgado/sandboxes/rsandbox_5_5_9/newcerts/client-cert.pem
            Master_SSL_Cipher: 
               Master_SSL_Key: /home/lmorgado/sandboxes/rsandbox_5_5_9/newcerts/client-key.pem

We could be forgiven to think that Master_SSL_Cipher would be the current Cipher in use, but the MySQL Replication Team has confirmed that Master_SSL_Cipher is the list of available SSL Ciphers specified by the slave when doing SSL handshake with the master. The corresponding mysqld variable is:

http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.html#statvar_Ssl_cipher_lis...

And not the more similarly named:
 http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.html#statvar_Ssl_cipher

How to repeat:
Set up an SSL enabled slave as per:
 http://dev.mysql.com/doc/refman/5.1/en/replication-solutions-ssl.html

On the slave issue:

mysql> SHOW SLAVE STATUS\G

Try and find which SSL Cipher is being used.

Suggested fix:
I see two options, both require the code implementation of a mechanism to view the currently used SSL Cipher by the Slave's IO thread. The options differ in the naming:

1) Master_SSL_Cipher maintains it's current meaning and is equivalent to:
 http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.html#statvar_Ssl_cipher_lis...

A new variable (eg: Master_SSL_Current_Cipher) is added to represent:
 http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.html#statvar_Ssl_cipher

2) Master_SSL_Cipher takes on a new meaning and refers to:
 http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.html#statvar_Ssl_cipher

The old Master_SSL_Cipher variable is renamed to Master_SSL_Cipher_List and refers to:
  http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.html#statvar_Ssl_cipher_lis...

Option 2) has the advantage of having consistent names with the respective mysqld variables. The disadvantage is that it breaks backward compatibility because Master_SSL_Cipher has a new meaning (eg: monitoring tools). I however don't expect lots of users to be looking at Master_SSL_Cipher.

i assume this means we have a docs bug also?

http://dev.mysql.com/doc/refman/5.5/en/slave-logs-status.html
"Master_SSL_Cipher	The name of the cipher in use for the SSL connection"

Shane, the Docs Team is dealing with making the needed corrections to the manual. Thanks for alerting us.

Updated description of Master_SSL_Cipher column in docs; see
http://lists.mysql.com/commits/136276

This feature request is implemented in 5.7.8, as cipher information can be obtained via the PERFORMANCE_SCHEMA.STATUS_BY_THREAD table.  This is described in more detail in the following blog post:

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