Image Source

MySQL is a free and widely accepted data management system all across the globe. It helps in storing and managing data efficiently. Its popularity lies in its easy usability, functionality, and its enticing features. The more the popularity, the greater the risk of being attacked by cyber-criminals. MySQL too needs to be secured from such unauthorized intruders for maintaining data privacy.

By default, this data management system is configured and permitted to accept local connections or MySQL installed machine connections. But, in the case of remote locations, access to the MySQL database needs strong security in the form of encryption.

In this article, we will discuss some of the most vital aspects of configuring and managing SSL certificate or Wildcard SSL (Unlimited Subdomain) on a MySQL server. This blog will include the default configuration and how to disable and enable SSL on the MySQL server.

So, let’s begin.

  • Default SSL Configuration in MySQL:

MySQL server always prefers SSL configuration and hence by default this server installs and enables this security configuration. But this SQL server permits connections to all types of clients, i.e., clients who connect using SSL or without the same. This states that SSL connection is not mandatory for connecting with the server.

Let’s check out some symptoms which portray the behavior of the MySQL server in the presence of SSL configuration.

  1. *.pem files will be visible in the MySQL data directory. This includes various client and server certificates and the private/public keys used in the SSL encryption process.
  • There will be a note in the mysqld error log file during the server start, such as:

[Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.

  • Value of ‘have_ssl’ variable will be YES:
mysql> show variables like ‘have_ssl’; +—————+——-+ | Variable_name | Value | +—————+——-+ | have_ssl      | YES | +—————+——-+

Even MySQL client always tries to opt for an encrypted connection with the MySQL server. In case of failure to connect, it may again fall in an unencrypted mode.

You can connect to the MySQL server using the command:

mysql -h <hostname> -u <username> -p

This will enable you to check whether the current client connection is encrypted or not. Use the status command:

mysql> status ————– mysql  Ver 14.14 Distrib 5.7.21, for Linux (x86_64) using  EditLine wrapper Connection id:          75 Current database: Current user:           root@127.0.0.1 SSL:                    Cipher in use is DHE-RSA-AES256-SHA Current pager:          stdout Using outfile:          ” Using delimiter:        ; Server version:         5.7.21-log MySQL Community Server (GPL) Protocol version:       10 Connection:             127.0.0.1 via TCP/IP …………………………..  

When the SSL field is highlighted, as shown above, it is an indication that the connection between the client and the server is encrypted. We can also ask the MySQL client to connect without SSL by using the below-mentioned command.

mysql -h <hostname> -u <username>  -p –ssl-mode=DISABLED  mysql> status ————– Connection id:          93 Current database: Current user:           sgroot@127.0.0.1 SSL:                    Not in use Current pager:          stdout Using outfile:          ” Using delimiter:        ; Server version:         5.7.21-log MySQL Community Server (GPL) Protocol version:       10 Connection:             127.0.0.1 via TCP/IP ……………………………  

This shows that even though the SSL is enabled on the server, we can connect to it without SSL.

  • Disabling SSL in MySQL:

By default, in the MySQL server, the option is on “enabled, but optional mode”. In case you wish to disable the SSL (the above-stated option) on the MySQL server, follow the below stated steps.

  1.  Delete the *.pem certificate and key files stored in the MySQL data directory.
  2. Start MySQL with the SSL option turned off. This can be done by adding a line entry:
ssl=0  in the my.cnf file.

You will notice that:

  1. There won’t be any note in mysqld logs such as :

[Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.

  •  Value of ‘have_ssl’ variable will be DISABLED:
mysql> show variables like ‘have_ssl’; +—————+——-+ | Variable_name | Value | +—————+——-+ | have_ssl      | DISABLED | +—————+——-+
  • Enforcing SSL in MySQL:

We have noticed that by default, SSL is already enabled in the MySQL server. In case of the absence of SSL, we were able to connect with the client.

If you wish your MySQL server accepts only SSL connections, set the “require_secure_transport” system variable.

To verify the same, try to connect MySQL server by writing this command:

mysql -h <hostname> -u sgroot -p –ssl-mode=DISABLED

You will notice that the connection is refused by the server, by showing the below-stated error message:

ERROR 3159 (HY000): Connections using insecure transport are prohibited while –require_secure_transport=ON.
  • SSL Considerations for Replication Channels

In a MySQL replication setup, the slaves connect to the master without encryption by default.

To connect to a master in a secure way for replication traffic, slaves must use:

MASTER_SSL=1; as part of the ‘CHANGE MASTER TO’ command which specifies all the parameters for connecting to the master.

Kindly note: This option is also mandatory in case your master is configured to enforce SSL connection using require_secure_transport.

Author