Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Is a MySQL Connection Using SSL or Not?

DZone's Guide to

Is a MySQL Connection Using SSL or Not?

In this blog post, we’ll discuss how we can determine if a MySQL client connection is using SSL. Check out these two options.

· Database Zone
Free Resource

Learn how to move from MongoDB to Couchbase Server for consistent high performance in distributed environments at any scale.

In this blog post, we’ll discuss how we can determine if a MySQL connection is using SSL.

Since MySQL version 5.7.5, the server generates SSL certificates (see auto_generate_certs) by default if compiled with SSL, or uses mysql_ssl_rsa_setup if compiled with YaSSL.

But, how can we check to see if our MySQL client connection uses SSL?

When using an interactive client, it’s easy! You have two options:

1. Check the Status:

mysql> s
--------------
mysql  Ver 14.14 Distrib 5.7.11, for Linux (x86_64) using  EditLine wrapper
Connection id:7
Current database:
Current user:root@localhost
SSL:Cipher in use is DHE-RSA-AES256-SHA
Current pager:stdout
Using outfile:''
Using delimiter:;
Server version:5.7.11-log MySQL Community Server (GPL)
Protocol version:10
Connection:Localhost via UNIX socket
Server characterset:latin1
Db     characterset:latin1
Client characterset:utf8
Conn.  characterset:utf8
UNIX socket:/var/lib/mysql/mysql.sock
Uptime:36 min 33 sec

As you can see, for that connection, we are indeed using SSL: 

Image title

2. Use the Status Variables Ssl_version and Ssl_cipher

mysql> show  status like 'Ssl_version';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Ssl_version   | TLSv1.1 |
+---------------+---------+
mysql> show  status like 'Ssl_cipher';
+---------------+--------------------+
| Variable_name | Value              |
+---------------+--------------------+
| Ssl_cipher    | DHE-RSA-AES256-SHA |
+---------------+--------------------+

But, is there a way to verify this on all of the connections? For example, if we have some applications connected to our database server, how do we verify which connections are using SSL and which are not?

Yes, there is a solution: Performance_Schema!

This is how:

mysql> SELECT sbt.variable_value AS tls_version,  t2.variable_value AS cipher,
              processlist_user AS user, processlist_host AS host
       FROM performance_schema.status_by_thread  AS sbt
       JOIN performance_schema.threads AS t ON t.thread_id = sbt.thread_id
       JOIN performance_schema.status_by_thread AS t2 ON t2.thread_id = t.thread_id
      WHERE sbt.variable_name = 'Ssl_version' and t2.variable_name = 'Ssl_cipher' ORDER BY tls_version;
+-------------+--------------------+------+-----------+
| tls_version | cipher             | user | host      |
+-------------+--------------------+------+-----------+
|             |                    | root | localhost |
| TLSv1       | DHE-RSA-AES256-SHA | root | localhost |
| TLSv1.1     | DHE-RSA-AES256-SHA | root | localhost |
+-------------+--------------------+------+-----------+

That’s it. Now, isn’t that easy? 

Want to deliver a whole new level of customer experience? Learn how to make your move from MongoDB to Couchbase Server.

Topics:
mysql ,ssl

Published at DZone with permission of Frederic Descamps, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}