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?
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.
Force SSL per user:
alter user 'my_user'@'%' REQUIRE SSL;
mysql> \s
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
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"
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.
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
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-
If SSL_Cipher value is blank that means SSL is not enabled.
In my case: Yes, SSL is enabled.
