MySQL is an open-source relational database system that works on many Operating Systems including Windows, Linux, and MacOS. By default, MySQL is configured to only accept local connections. If you need to allow remote connections, it is important to do so securely. Use the following instruction to configure MySQL to accept remote connections with SSL/TLS encryption.
Before you start, you can check the current SSL/TLS status.
#mysql -u root -p -h 127.0.0.1
mysql> SHOW VARIABLES LIKE '%ssl%';
Output
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| have_openssl | DISABLED |
| have_ssl | DISABLED |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | |
+---------------+----------+
Check the status of our current connection to confirm:
mysql> \s
Output
--------------
Connection id: 30
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.17-0ubuntu0.16.04.1 (Ubuntu)
Protocol version: 10
Connection: 127.0.0.1 via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 1 hours 11 min 54 sec
--------------
The above output indicates SSL is not currently in use.
Generate SSL/TLS Certificates and Keys
To enable SSL connections to MySQL, at first need to generate the certificate and key files. We can use a utility called mysql_ssl_rsa_setup to simplify this process. The files will be created in MySQL’s data directory, located at /var/lib/mysql. We need the MySQL process to be able to read the generated files, so we will pass mysql as the user that should own the generated files.
#mysql_ssl_rsa_setup --uid=mysql
The generation will produce output that looks something like this:
Output
Generating a 2048 bit RSA private key
...................................+++
.....+++
writing new private key to 'ca-key.pem'
-----
Generating a 2048 bit RSA private key
......+++
.................................+++
writing new private key to 'server-key.pem'
-----
Generating a 2048 bit RSA private key
......................................................+++
.................................................................................+++
writing new private key to 'client-key.pem'
-----
SHOW VARIABLES LIKE '%ssl%';
Verify the generated files by typing:
#find /var/lib/mysql -name '*.pem' -ls
These files are the key and certificate pairs for the certificate authority (starting with “ca”), the MySQL server process (starting with “server”), and for MySQL clients (starting with “client”). Additionally, the private_key.pem and public_key.pem files are used by MySQL to securely transfer password when not using SSL.
Enable SSL Connections on the MySQL Server
We don’t actually need to modify the MySQL configuration to enable SSL, restart the MySQL service instead.
#systemctl restart mysql
After restarting, connect to MySQL using the same command as before. The MySQL client will automatically attempt to connect using SSL if it is supported by the server.
Check the values of the SSL related variables:
mysql> SHOW VARIABLES LIKE '%ssl%';
Output
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | ca.pem |
| ssl_capath | |
| ssl_cert | server-cert.pem |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | server-key.pem |
+---------------+-----------------+
The have_openssl and have_ssl variables read “YES” instead of “DISABLED” this time. Furthermore, the ssl_ca, ssl_cert, and ssl_key variables have been populated with the names of the relevant certificates that we generated.
Configure Remote Access with Mandatory SSL
Currently, the MySQL server is configured to accept SSL connections from clients. However, it will still allow unencrypted connections if requested by the client.
Let’s turn on the require_secure_transport option for all connections to be made with SSL.
#nano /etc/mysql/my.cnf
Under [mysqld] section header, set require_secure_transport to ON:
[mysqld]
require_secure_transport = ON
To allow MySQL to accept connections on any of its interfaces, we can set bind-address to “0.0.0.0”.
[mysqld]
require_secure_transport = ON
bind-address = 0.0.0.0
Next, restart MySQL to apply the new settings.
Configure a Remote MySQL User
Log into MySQL as the root user to get started.
Inside, you can create a new remote user using the CREATE USER command. We will use our client machine’s IP address in the host portion of the user specification to restrict connections to that machine.
mysql> CREATE USER 'remote_user'@'mysql_client_IP' IDENTIFIED BY 'password' REQUIRE SSL;
Next, grant the new user permissions on the databases or tables they should have access to.
mysql> CREATE DATABASE example;
mysql> GRANT ALL ON example.* TO 'remote_user'@'mysql_client_IP';
Next, flush the privileges to apply those settings immediately.
mysql> FLUSH PRIVILEGES;
We can exit from shell now.
mysql> exit
