SSL Configuration in MySQL

Default SSL Configuration in MySQL

By default, MySQL server always installs and enables SSL configuration.
However, it is not enforced that clients connect using SSL.

Configuration metric	MySQL 5.6	MySQL 5.7	MySQL 8.0	cnf section	Purpose
have_openssl	DISABLED	YES	YES	Set to on when the SSL files are declared	Enable SSL
have_ssl	DISABLED	YES	YES	Set to on when the SSL files are declared
ssl_ca		ca.pem	ca.pem	mysqld
ssl_cert		server-cert.pem	server-cert.pem	mysqld
ssl_key		server-key.pem	server-key.pem	mysqld
require_secure_transport	(not exists)	OFF	OFF	mysqld	Force SSL
ssl_capath
ssl_cipher
ssl_crl
ssl_crlpath
ssl_fips_mode	(not exists)	(not exists)	OFF
admin_ssl_ca	(not exists)	(not exists)
admin_ssl_capath	(not exists)	(not exists)
admin_ssl_cert	(not exists)	(not exists)
admin_ssl_cipher	(not exists)	(not exists)
admin_ssl_crl	(not exists)	(not exists)
admin_ssl_crlpath	(not exists)	(not exists)
admin_ssl_key	(not exists)	(not exists)
mysqlx_ssl_ca	(not exists)	(not exists)
mysqlx_ssl_capath	(not exists)	(not exists)
mysqlx_ssl_cert	(not exists)	(not exists)
mysqlx_ssl_cipher	(not exists)	(not exists)
mysqlx_ssl_crl	(not exists)	(not exists)
mysqlx_ssl_crlpath	(not exists)	(not exists)
mysqlx_ssl_key	(not exists)	(not exists)
performance_schema_show_processlist	(not exists)	(not exists)	OFF

How to check SSL Configuration

show global variables like '%ssl%';

show global variables like '%require_secure_transport%';

status

SSL enabling for MySQL

1. SSL Certificate Files

By default, MySQL has its own SSL certificate files in the '/var/lib/mysql' directory.

If required: Generate New Self-Signed Certificate Files For MySQL .

Identity verification with VERIFY_IDENTITY does not work with self-signed certificates that are created automatically.

2. Enable SSL for MySQL

Edit Cnf file:

[mysql]

ssl-ca=/var/lib/mysql/ca.pem

ssl-cert=/var/lib/mysql/server-cert.pem

ssl-key=/var/lib/mysql/server-key.pem

 

[client]

ssl-ca=/var/lib/mysql/ca.pem

ssl-cert=/var/lib/mysql/client-cert.pem

ssl-key=/var/lib/mysql/client-key.pem

• These changes in cnf file require a restart of the MySQL instance.
• Pay attention that the files paths are correct.
• If new certificate files were created – update names and paths

In order to Force SSL login:

Edit Cnf file:

[mysqld]

require_secure_transport = ON

• These changes in cnf file require a restart of the MySQL instance.
• if require_secure_transport is set as OFF (as the default value) - it means that a user can use certificates and also can login without SSL certificates.

3. Enable Remote Connection

Edit Cnf file:

[mysqld]

#bind-address = 0.0.0.0

bind-address = *

• These changes in cnf file require a restart of the MySQL instance.

Login to an SSL instance from a client

1. Copy and save the certificate files in the client's server.
2. Add SSL properties to the connection (mysql or other) command.

No	-	-	No SSL and other certificate is required	mysql -h 10.240.86.5 -P 3307 -u root -p
Yes	No SSL	DISABLED	Connecting without SSL	mysql -h 10.240.86.5 -P 3306 -u root --ssl-mode=DISABLED -p	When the instance requires SSL, it is not possible to connect without SSL
	If available	PREFERRED (the default if --ssl-mode is not specified)	Establish an encrypted connection if the server supports encrypted connections, falling back to an unencrypted connection if an encrypted connection cannot be established.	mysql -h 10.240.86.5 -P 3307 -u root -p	Same as without any SSL definitions.
	Require	REQUIRED	Establish an encrypted connection if the server supports encrypted connections. The connection attempt fails if an encrypted connection cannot be established.	mysql -h 10.240.86.5 -P 3307 -u root \ --ssl-cipher=ECDHE-RSA-AES128-GCM-SHA256 -p
	Require and verify CA	VERIFY_CA	Like REQUIRED, but additionally verify the server Certificate Authority (CA) certificate against the configured CA certificates. The connection attempt fails if no valid matching CA certificates are found.	mysql -h 10.240.86.5 -P 3307 -u root \ --ssl-mode=VERIFY_CA \ --ssl-ca=/etc/certs/ca.pem \ --ssl-cert=/etc/certs/client-cert.pem \ --ssl-key=/etc/certs/client-key.pem -p
	Require and verify Identity	VERIFY_IDENTITY	Like VERIFY_CA, but additionally perform host name identity verification by checking the host name the client uses for connecting to the server against the identity in the certificate that the server sends to the client:	mysql -h MySQL -P 3306 -u root \ --ssl-mode=VERIFY_IDENTITY \ --ssl-ca=/etc/certs3306/ca.pem \ --ssl-cert=/etc/certs3306/client-cert.pem \ --ssl-key=/etc/certs3306/client-key.pem -p	Host name identity verification with VERIFY_IDENTITY does not work with self-signed certificates that are created automatically by the server or manually using mysql_ssl_rsa_setup. Such self-signed certificates do not contain the server name as the Common Name value. In the connection – use host name and not an IP.

This is how it looks at MySQL Workbench: