MySQL by default is configured to only accept local connections, or connections that initiate from the same machine where MySQL is installed. If you need to access your MySQL database from a remote clients, make sure it is safe and secure.
This tutorial will show you how to implement SSL/TLS encryption for MySQL on Ubuntu 18.04.
Prerequisites
You will also need to replace red highlighted text according to your environment.
Installing MySQL Server
To install and configure MySQL on your Ubuntu 18.04 server, execute the following commands on labserver:
sudo apt update
sudo apt -y install mysql-server
sudo mysql_secure_installation
sudo mysql
SELECT user,authentication_string,plugin,host FROM mysql.user;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your-password';
FLUSH PRIVILEGES;
Verify the authentication methods employed by each of your users again to confirm that root no longer authenticates using the auth_socket plugin:
SELECT user,authentication_string,plugin,host FROM mysql.user;
exit
MySQL should have started running automatically. To test this, check its status.
systemctl status mysql.service
This means MySQL is up and running.
Verifying MySQL's Current SSL/TLS Status
Before you make any configuration changes, you can verify the current SSL/TLS status on the MySQL server instance.
sudo mysql -u root -p -h 127.0.0.1
You will be prompted for the MySQL root password that you chose when you installed and configured MySQL. After entering it you'll be dropped into an interactive MySQL session.
Show the state of the SSL/TLS variables issuing the following command:
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 | |
+---------------+----------+
9 rows in set (0.01 sec)
The have_openssl and have_ssl variables are both marked as DISABLED. This means that SSL functionality has been compiled into the server, but that it is not yet enabled.
Check the status of your current connection to confirm this:
\s
mysql Ver 14.14 Distrib 5.7.26, for Linux (x86_64) using EditLine wrapper
Connection id: 7
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.26-0ubuntu0.18.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: 40 min 11 sec
Threads: 1 Questions: 33 Slow queries: 0 Opens: 113 Flush tables: 1 Open tables: 106
exit
Generating SSL/TLS Certificates and Keys
sudo mysql_ssl_rsa_setup --uid=mysql
This will produce output that looks similar to the following:
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'
-----
These new files will be stored in MySQL's data directory, located by default at /var/lib/mysql. Check the generated files by typing:
sudo find /var/lib/mysql -name '*.pem' -ls
Output
258930 4 -rw-r--r-- 1 mysql mysql 1107 May 22 11:10 /var/lib/mysql/client-cert.pem
258919 4 -rw-r--r-- 1 mysql mysql 451 May 22 11:10 /var/lib/mysql/public_key.pem
258925 4 -rw------- 1 mysql mysql 1675 May 22 11:10 /var/lib/mysql/server-key.pem
258927 4 -rw-r--r-- 1 mysql mysql 1107 May 22 11:10 /var/lib/mysql/server-cert.pem
258922 4 -rw------- 1 mysql mysql 1675 May 22 11:10 /var/lib/mysql/ca-key.pem
258928 4 -rw------- 1 mysql mysql 1675 May 22 11:10 /var/lib/mysql/client-key.pem
258924 4 -rw-r--r-- 1 mysql mysql 1107 May 22 11:10 /var/lib/mysql/ca.pem
258918 4 -rw------- 1 mysql mysql 1679 May 22 11:10 /var/lib/mysql/private_key.pem
Now that you have the necessary certificate and key files, continue on to enable the use of SSL on your MySQL instance.
Enabling SSL Connections for MySQL
Latest versions of MySQL look for the appropriate certificate files within the MySQL data directory whenever the server starts. You just to need to restart MySQL service to enable SSL.
sudo systemctl restart mysql
sudo mysql -u root -p -h 127.0.0.1
Take a look at the same information we requested earlier and check the values of the SSL-related variables:.
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 |
+---------------+-----------------+
9 rows in set (0.00 sec)
The have_openssl and have_ssl variables now read YES instead of DISABLED. Furthermore, the ssl_ca, ssl_cert, and ssl_key variables have been populated with the names of the respective files that we just generated.
Next, check the connection details again:
\s
Output
SSL: Cipher in use is DHE-RSA-AES256-SHA
Connection: 127.0.0.1 via TCP/IP
This time, the specific SSL cipher is displayed, indicating that SSL is being used to secure the connection.
exit
Your MySQL server is now capable of using encryption, but some additional configuration is required to allow remote access and mandate the use of secure connections.
Secure Remote Connections
To enable this setting, open the MySQL configuration file in your preferred text editor:
sudo nano /etc/mysql/my.cnf
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
[mysqld]
# Require clients to connect either using SSL
# or through a local socket file
require_secure_transport = ON
bind-address = 0.0.0.0
After adding these lines, save and close the file. If you used nano to edit the file, you can do so by pressing CTRL+X, Y, then ENTER.
Next, restart MySQL to apply the new settings:
sudo systemctl restart mysql
Verify that MySQL is listening on 0.0.0.0 instead of 127.0.0.1 by typing:
sudo netstat -plunt
The output of this command will look like this:
Output
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 13317/mysqld
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1293/sshd
tcp6 0 0 :::22 :::* LISTEN 1293/sshd
The 0.0.0.0 highlighted in the above output indicates that MySQL is listening for connections on all available interfaces.
With that, remote connection attempts are now able to reach your MySQL server. However, you don't currently have any users configured that can connect from a remote machine. We'll create and configure a MySQL user that can connect from your client machine in the next step.
Creating a Dedicated MySQL User
You will need to create a dedicated user that will only be able to connect from your client machine.
To create such a user, log back into MySQL as the root user:
sudo mysql -u root -p
CREATE USER 'your_username'@'your_mysql_client_IP' IDENTIFIED BY 'your_password' REQUIRE SSL;
Next, grant the new user permissions on whichever databases or tables that they should have access to. To demonstrate, create an example database:
CREATE DATABASE sample;
Then give your new user access to this database and all of its tables:
GRANT ALL ON sample.* TO 'your_username'@'your_mysql_client_IP';
Next, flush the privileges to apply those settings immediately:
FLUSH PRIVILEGES;
exit
Your MySQL server is now set up to allow connections from your remote user.
Configuring MySQL Client
To verify that you can connect to MySQL successfully, you will need to install the mysql-client package on the labclient machine. Log in to your client machine and perform the following:
sudo apt update
sudo apt -y install mysql-client
sudo mysql -u your_username -p -h your_mysql_server_IP
After submitting the password, you will be logged in to the remote server. Use \s to check the server's status and confirm that your connection is secure:
\s
Output
SSL: Cipher in use is DHE-RSA-AES256-SHA
Connection: your_mysql_server_IP via TCP/IP
exit
You've confirmed that you're able to connect to MySQL over SSL. However, you've not yet confirmed that the MySQL server is rejecting insecure connections. To test this, try connecting once more, but this time append --ssl-mode=disabled to the login command. This will instruct mysql-client to attempt an unencrypted connection:
sudo mysql -u your_username -p -h mysql_server_IP --ssl-mode=disabled
After entering your password when prompted, your connection will be refused:
Output
ERROR 1045 (28000): Access denied for user 'your_username'@'mysql_server_IP' (using password: YES)
This shows that SSL connections are permitted while unencrypted connections are refused.
Wrapping up
Your MySQL server is now configured to accept secure connections from remote clients.
This tutorial will show you how to implement SSL/TLS encryption for MySQL on Ubuntu 18.04.
Prerequisites
To follow the steps mentioned in this guide, you will need two Ubuntu 18.04 (physical or virtual) machines. These steps can also be applied if you would like to implement SSL/TLS encryption on MySQL installed on Ubuntu 19.04.
Please note that throughout this tutorial, the machine on which you install MySQL will be referred to as the labserver and any commands that should be run on this machine will be highlighted with blue color. Similarly, the other machine as the labclient and any commands that must be run on that machine will be highlighted with pink color.
Please note that throughout this tutorial, the machine on which you install MySQL will be referred to as the labserver and any commands that should be run on this machine will be highlighted with blue color. Similarly, the other machine as the labclient and any commands that must be run on that machine will be highlighted with pink color.
You will also need to replace red highlighted text according to your environment.
Installing MySQL Server
To install and configure MySQL on your Ubuntu 18.04 server, execute the following commands on labserver:
sudo apt update
sudo apt -y install mysql-server
sudo mysql_secure_installation
sudo mysql
SELECT user,authentication_string,plugin,host FROM mysql.user;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your-password';
FLUSH PRIVILEGES;
Verify the authentication methods employed by each of your users again to confirm that root no longer authenticates using the auth_socket plugin:
SELECT user,authentication_string,plugin,host FROM mysql.user;
exit
MySQL should have started running automatically. To test this, check its status.
systemctl status mysql.service
This means MySQL is up and running.
Verifying MySQL's Current SSL/TLS Status
Before you make any configuration changes, you can verify the current SSL/TLS status on the MySQL server instance.
sudo mysql -u root -p -h 127.0.0.1
You will be prompted for the MySQL root password that you chose when you installed and configured MySQL. After entering it you'll be dropped into an interactive MySQL session.
Show the state of the SSL/TLS variables issuing the following command:
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 | |
+---------------+----------+
9 rows in set (0.01 sec)
The have_openssl and have_ssl variables are both marked as DISABLED. This means that SSL functionality has been compiled into the server, but that it is not yet enabled.
Check the status of your current connection to confirm this:
\s
mysql Ver 14.14 Distrib 5.7.26, for Linux (x86_64) using EditLine wrapper
Connection id: 7
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.26-0ubuntu0.18.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: 40 min 11 sec
Threads: 1 Questions: 33 Slow queries: 0 Opens: 113 Flush tables: 1 Open tables: 106
exit
Generating SSL/TLS Certificates and Keys
To enable SSL connections to MySQL, you first need to generate the appropriate certificate and key files. The MySQL process must be able to read the generated files, so use the --uid option to declare mysql as the system user that should own the generated files:
sudo mysql_ssl_rsa_setup --uid=mysql
This will produce output that looks similar to the following:
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'
-----
These new files will be stored in MySQL's data directory, located by default at /var/lib/mysql. Check the generated files by typing:
sudo find /var/lib/mysql -name '*.pem' -ls
Output
258930 4 -rw-r--r-- 1 mysql mysql 1107 May 22 11:10 /var/lib/mysql/client-cert.pem
258919 4 -rw-r--r-- 1 mysql mysql 451 May 22 11:10 /var/lib/mysql/public_key.pem
258925 4 -rw------- 1 mysql mysql 1675 May 22 11:10 /var/lib/mysql/server-key.pem
258927 4 -rw-r--r-- 1 mysql mysql 1107 May 22 11:10 /var/lib/mysql/server-cert.pem
258922 4 -rw------- 1 mysql mysql 1675 May 22 11:10 /var/lib/mysql/ca-key.pem
258928 4 -rw------- 1 mysql mysql 1675 May 22 11:10 /var/lib/mysql/client-key.pem
258924 4 -rw-r--r-- 1 mysql mysql 1107 May 22 11:10 /var/lib/mysql/ca.pem
258918 4 -rw------- 1 mysql mysql 1679 May 22 11:10 /var/lib/mysql/private_key.pem
Now that you have the necessary certificate and key files, continue on to enable the use of SSL on your MySQL instance.
Enabling SSL Connections for MySQL
Latest versions of MySQL look for the appropriate certificate files within the MySQL data directory whenever the server starts. You just to need to restart MySQL service to enable SSL.
sudo systemctl restart mysql
sudo mysql -u root -p -h 127.0.0.1
Take a look at the same information we requested earlier and check the values of the SSL-related variables:.
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 |
+---------------+-----------------+
9 rows in set (0.00 sec)
The have_openssl and have_ssl variables now read YES instead of DISABLED. Furthermore, the ssl_ca, ssl_cert, and ssl_key variables have been populated with the names of the respective files that we just generated.
Next, check the connection details again:
\s
Output
SSL: Cipher in use is DHE-RSA-AES256-SHA
Connection: 127.0.0.1 via TCP/IP
This time, the specific SSL cipher is displayed, indicating that SSL is being used to secure the connection.
exit
Your MySQL server is now capable of using encryption, but some additional configuration is required to allow remote access and mandate the use of secure connections.
Secure Remote Connections
To enable this setting, open the MySQL configuration file in your preferred text editor:
sudo nano /etc/mysql/my.cnf
Inside there will be two !includedir directives which are used to source additional configuration files. You must add your own configuration beneath these lines so that it overrides any conflicting settings found in these additional configuration files.
Start by creating a [mysqld] section to target the MySQL server process. Under that section header, set require_secure_transport to ON, which will force MySQL to only allow secure connections. To allow MySQL to listen for external connections, you must configure it to listen for connections on an external IP address. To do this, you can add the bind-address setting and point it to 0.0.0.0, a wildcard IP address that represents all IP addresses. Essentially, this will force MySQL to listen for connections on every interface:
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
[mysqld]
# Require clients to connect either using SSL
# or through a local socket file
require_secure_transport = ON
bind-address = 0.0.0.0
After adding these lines, save and close the file. If you used nano to edit the file, you can do so by pressing CTRL+X, Y, then ENTER.
Next, restart MySQL to apply the new settings:
sudo systemctl restart mysql
Verify that MySQL is listening on 0.0.0.0 instead of 127.0.0.1 by typing:
sudo netstat -plunt
The output of this command will look like this:
Output
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 13317/mysqld
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1293/sshd
tcp6 0 0 :::22 :::* LISTEN 1293/sshd
The 0.0.0.0 highlighted in the above output indicates that MySQL is listening for connections on all available interfaces.
With that, remote connection attempts are now able to reach your MySQL server. However, you don't currently have any users configured that can connect from a remote machine. We'll create and configure a MySQL user that can connect from your client machine in the next step.
Creating a Dedicated MySQL User
You will need to create a dedicated user that will only be able to connect from your client machine.
To create such a user, log back into MySQL as the root user:
sudo mysql -u root -p
CREATE USER 'your_username'@'your_mysql_client_IP' IDENTIFIED BY 'your_password' REQUIRE SSL;
Next, grant the new user permissions on whichever databases or tables that they should have access to. To demonstrate, create an example database:
CREATE DATABASE sample;
Then give your new user access to this database and all of its tables:
GRANT ALL ON sample.* TO 'your_username'@'your_mysql_client_IP';
Next, flush the privileges to apply those settings immediately:
FLUSH PRIVILEGES;
exit
Your MySQL server is now set up to allow connections from your remote user.
Configuring MySQL Client
To verify that you can connect to MySQL successfully, you will need to install the mysql-client package on the labclient machine. Log in to your client machine and perform the following:
sudo apt update
sudo apt -y install mysql-client
sudo mysql -u your_username -p -h your_mysql_server_IP
After submitting the password, you will be logged in to the remote server. Use \s to check the server's status and confirm that your connection is secure:
\s
Output
SSL: Cipher in use is DHE-RSA-AES256-SHA
Connection: your_mysql_server_IP via TCP/IP
exit
You've confirmed that you're able to connect to MySQL over SSL. However, you've not yet confirmed that the MySQL server is rejecting insecure connections. To test this, try connecting once more, but this time append --ssl-mode=disabled to the login command. This will instruct mysql-client to attempt an unencrypted connection:
sudo mysql -u your_username -p -h mysql_server_IP --ssl-mode=disabled
After entering your password when prompted, your connection will be refused:
Output
ERROR 1045 (28000): Access denied for user 'your_username'@'mysql_server_IP' (using password: YES)
This shows that SSL connections are permitted while unencrypted connections are refused.
Wrapping up
Your MySQL server is now configured to accept secure connections from remote clients.
No comments: