Find SSL status of all frontend connections

[Daniel Henninger]

Hi folk!

I've recently set up backend and frontend ssl support and I was wondering -- is there any way I can list out all of the current connections and their ssl status?  I do not want to enforce ssl just yet, but I'd love to be able to see who is actually connected via ssl.  Trying to get a gauge as to how many applications "just worked" and switched over to ssl.  As such it wouldn't be super easy for me to get inside their sessions and see it from their perspective.

This is doable, but a tad awkward, from the MySQL server end of things, but of course that's only telling me that proxysql is speaking to the backends over SSL.

Thanks!

Daniel

[René Cannaò]

Hi Daniel,

For free backend connections this is quite easy, you just need to run this query:

SELECT JSON_EXTRACT(mysql_info, '$.options.use_ssl') use_ssl , COUNT(*) FROM stats_mysql_free_connections GROUP BY JSON_EXTRACT(mysql_info, '$.options.use_ssl');

For frontend connections you need to enable extended_info , and then query stats_mysql_processlist :

SET mysql-show_processlist_extended=1;

LOAD MYSQL VARIABLES TO RUNTIME;

SELECT JSON_EXTRACT(extended_info, '$.client.encrypted') use_ssl , COUNT(*) FROM stats_mysql_processlist GROUP BY JSON_EXTRACT(extended_info, '$.client.encrypted');

For backend connections in use the query is a bit more complicated because backend information is stored in an JSON array for each client connection.

This is the query:

SELECT

  JSON_EXTRACT(backends.value,'$.conn.mysql.options.use_ssl') use_ssl ,

  COUNT(*)

FROM stats_mysql_processlist

  JOIN JSON_EACH(JSON_EXTRACT(stats_mysql_processlist.extended_info, '$.backends')) backends

  GROUP BY JSON_EXTRACT(backends.value,'$.conn.mysql.options.use_ssl');

Thanks,

René

--
You received this message because you are subscribed to the Google Groups "proxysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+u...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/proxysql/1268b793-220c-483c-9562-5b7036b0cdccn%40googlegroups.com.

[Daniel Henninger]

That's perfect!  Thank you René!

--

Daniel Henninger - Systems & Programming Lead

College of Humanities and Social Sciences - Information Technology

dan...@ncsu.edu - 919-515-3512 - Campus Box 8101