48

I have configured my server to allow SSL, and have modified my client ~/.my.cnf so I use SSL:

[client]
ssl
ssl-cipher=DHE-RSA-AES256-SHA
ssl-ca=~/certs/ca-cert.pem

When I log in with my client and view the status, it lists a cipher on the SSL line:

mysql> \s
--------------
SSL:            Cipher in use is DHE-RSA-AES256-SHA

Without installing something like wireshark to verify that the connection is secure, can I assume that I'm connecting via SSL based on this information?

chris
  • 1,222
  • 5
  • 17
  • 29

7 Answers7

54

From the client, just run status. If this connection is using SSL, you'll get something interesting in the SSL row.

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.5.30, for Linux (x86_64) using readline 5.1

Connection id:      12
Current database:
Current user:       replicator@domU-12-31-39-10-54-BD.compute-1.internal
SSL:            Cipher in use is DHE-RSA-AES256-SHA
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     5.5.30-log MySQL Community Server (GPL)
Protocol version:   10
Connection:     boston.hugskeep.wstudent.com via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:       3306
Uptime:         44 min 49 sec

Threads: 2  Questions: 16  Slow queries: 0  Opens: 34  Flush tables: 1  Open tables: 27  Queries per second avg: 0.005
--------------

mysql>

If this connection is not using SSL, you'll get:

SSL:            Not in use

You can also use:

mysql> SHOW STATUS LIKE 'Ssl_cipher';
+---------------+--------------------+
| Variable_name | Value              |
+---------------+--------------------+
| Ssl_cipher    | DHE-RSA-AES256-SHA |
+---------------+--------------------+
1 row in set (0.00 sec)

mysql>

But I think the first is more attractive, and sure easier to type.

Jeremy Wadhams
  • 948
  • 1
  • 9
  • 13
  • 5
    show status like 'Ssl_version' can also be useful to determine the SSL/TLS protocol version being used. – Joao Costa Feb 13 '19 at 16:54
  • do you know if there's a way to do this for a specific user. not just the current user? - https://stackoverflow.com/questions/56203365/how-to-check-if-a-user-requires-ssl-in-mysql/56203435#56203435 – committedandroider May 18 '19 at 22:51
  • 1
    I am not sure this has been changed now. For me even I am not using SSL, it shows SSL: Cipher in use is DHE-RSA-AES256-SHA for me. – Sadee Oct 23 '19 at 14:52
5

Force SSL per user:

alter user 'my_user'@'%' REQUIRE SSL;
mysql> \s
peterh
  • 2,077
  • 8
  • 28
  • 40
Mary Ciricean
  • 51
  • 1
  • 1
3

This is applicable to MariaDB (haven't tried it in pure MySQL):

mysql -h xxx.xxx.xxx.xxx -u testuser --ssl

The --ssl option will tell you if SSL is enabled. If it is disabled, the command will return "not in use"

Rafael Tavares
  • 117
  • 1
  • 1
  • 9
user2677034
  • 151
  • 4
  • 3
    WARNING: --ssl is deprecated and will be removed in a future version. Use --ssl-mode instead. MySQL 5.6.4 – Sadee Oct 23 '19 at 14:53
3

The status command doesn't tell you if the connection is using SSL. Clients can disable using SSL from their side.

Use show session status and look for Ssl_accepts and Ssl_finished_accepts to find the number of connections using SSL.
These numbers increase when a new connection is made to the MySQL server using SSL.

Note that the variable Ssl_client_connects reflects the number of SSL connection attempts to an SSL-enabled replication source server, and has nothing to do with client applications connecting to the MySQL server that are using SSL.

See Ssl_client_connects (MySQL Documentation)

Most client applications (e.g. a PHP application) do not automatically use SSL connections when connecting to a database server. Most of the times you have to perform additional steps to securely connect to a remote database.

One of the easiest ways to ensure all connections to your database use a secure connection, is to require secure transport altogether. For MySQL you can use SET GLOBAL require_secure_transport=1;. Once enabled, any insecure connection will fail.

John K. N.
  • 17,649
  • 12
  • 51
  • 110
Chayne P. S.
  • 131
  • 3
3

OFFICIAL SOLUTION ACCORDING TO MYSQL WEBSITE

Run this in the session you want to verify:

SELECT * FROM performance_schema.session_status 
WHERE VARIABLE_NAME IN ('Ssl_version','Ssl_cipher');

If you get the cipher and version strings, then the connection is encrypted. If it is not encrypted, you will get empty strings.

Source: https://dev.mysql.com/doc/refman/8.0/en/encrypted-connection-protocols-ciphers.html

kintsukuroi
  • 141
  • 4
1

MySQL 5.6.4

I am not sure this

SHOW STATUS LIKE 'Ssl_cipher';

has been changed in later versions. For me even if I am not using SSL, it shows SSL: Cipher in use is DHE-RSA-AES256-SHA for me.

You can use following to get confirmed SSL is using or not.

ubuntu@ip-111-22-3-444:~$ mysql -h 111.22.3.444 -u dbuser --ssl-mode=VERIFY_IDENTITY -p
ERROR 2026 (HY000): SSL connection error: CA certificate is required if ssl-mode is VERIFY_CA or VERIFY_IDENTITY
Sadee
  • 111
  • 2
0

Using Mysql Workbench:

If you are connected to the server with Mysql Workbench you can see the SSL status variable in Status and System Variable section under SSL category-

enter image description here

If SSL_Cipher value is blank that means SSL is not enabled.

In my case: Yes, SSL is enabled.

Aatif Akhter
  • 101
  • 1
  • 1
    Your screenshot show 'Ssl_client_connects', '0'. It means that no one is currently connected using ssl, including you. So even though you enable SSL you are still not using it, right? – Yevgeniy Afanasyev May 19 '22 at 06:40