A while back, I wrote a blog post explaining how PERFORMANCE_SCHEMA improvements in MySQL Server 5.7 provides new visibility into the SSL/TLS status of each running client configuration.  An excellent recent post from Frederic Descamps at Percona covers similar territory.  Both of us use PERFORMANCE_SCHEMA tables directly – a powerful interface, but one that requires a query joining multiple tables.  Thanks to the excellent work of Mark Leith, and a contribution from Daniël van Eeden, access to this same information is made far easier via the SYS schema.

I overlooked the SYS schema support when I wrote my post, and not seeing it in Frederic’s post might suggest it’s not well known generally.  This post aims to help rectify that.

Information about TLS status can be found in the SYS.SESSION_SSL_STATUS view. The view definition provides an example of the output, as well as visibility into the more complex multiple-table JOIN required to collect the data when using PERFORMANCE_SCHEMA tables directly. Here’s the current version found on GitHub:

--
-- View: session_ssl_status
--
-- Shows SSL version, cipher and the count of re-used SSL sessions per connection
--
-- mysql> select * from session_ssl_status;
-- +-----------+-------------+--------------------+---------------------+
-- | thread_id | ssl_version | ssl_cipher         | ssl_sessions_reused |
-- +-----------+-------------+--------------------+---------------------+
-- |        26 | TLSv1       | DHE-RSA-AES256-SHA | 0                   |
-- |        27 | TLSv1       | DHE-RSA-AES256-SHA | 0                   |
-- |        28 | TLSv1       | DHE-RSA-AES256-SHA | 0                   |
-- +-----------+-------------+--------------------+---------------------+
-- 3 rows in set (0.00 sec)
--

CREATE OR REPLACE
  ALGORITHM = MERGE
  DEFINER = 'root'@'localhost'
  SQL SECURITY INVOKER
VIEW session_ssl_status (
  thread_id,
  ssl_version,
  ssl_cipher,
  ssl_sessions_reused
) AS
SELECT sslver.thread_id, 
       sslver.variable_value ssl_version, 
       sslcip.variable_value ssl_cipher,
       sslreuse.variable_value ssl_sessions_reused
  FROM performance_schema.status_by_thread sslver 
  LEFT JOIN performance_schema.status_by_thread sslcip 
    ON (sslcip.thread_id=sslver.thread_id and sslcip.variable_name='Ssl_cipher')
  LEFT JOIN performance_schema.status_by_thread sslreuse 
    ON (sslreuse.thread_id=sslver.thread_id and sslreuse.variable_name='Ssl_sessions_reused') 
 WHERE sslver.variable_name='Ssl_version';

Accessing this information via this view greatly simplifies things for DBAs.  Note that this view returns the thread_id column – if you want to see the user or process information, you’ll need use that to join to the thd_id column on SYS.PROCESSLIST (table definition here).

A big thanks to Daniël van Eeden for this useful community contribution, and to Mark Leith for his work on SYS schema – including the processing of Daniël’s pull request.