How to remotely configure and secure connection for MySQL on Ubuntu 16.04 VPS

In this article, we will show you how to remotely configure and secure connection for MySQL on Ubuntu 16.04 VPS. If you are using an unencrypted connection to connect to a remote MariaDB / MySQL server, then everyone with network access can monitor and analyze the data sent or received between the client and the server. This guide should work on other Linux VPS systems as well, but has been tested and written for an Ubuntu 16.04 VPS. Login to your VPS via SSH

ssh [email protected]_server

The steps in this tutorial assume that you have installed MySQL 5.7 with the default configuration.

Enabling SSL connections

Create a new local certification authority

We will use a local CA as a self-signed root certificate to sign both the client and the certificate server. To create a new local certificate authority file, run the following commands:

sudo openssl genrsa -out /etc/mysql/ca-key.pem 2048
sudo chmod 600 /etc/mysql/ca-key.pem
sudo openssl req -new -key /etc/mysql/ca-key.pem -out /etc/mysql/ca-csr.pem -subj /CN=mysql-CA/
sudo openssl x509 -req -in /etc/mysql/ca-csr.pem -out /etc/mysql/cacert.pem -signkey /etc/mysql/ca-key.pem -days 3650
sudo echo 01 > /etc/mysql/cacert.srl

Generating server certificate and key

To get the server certificate and key, run the following commands:

sudo openssl genrsa -out /etc/mysql/server-key.pem 2048
sudo chmod 600 /etc/mysql/server-key.pem
sudo openssl req -new -key /etc/mysql/server-key.pem -out /etc/mysql/server-csr.pem -subj /CN=mysql/
sudo openssl x509 -req -in /etc/mysql/server-csr.pem -out /etc/mysql/server-cert.pem -CA /etc/mysql/cacert.pem -CAkey /etc/mysql/ca-key.pem -days 365

Generating client certificate and key

Next, we need to generate a client certificate. Run the following commands to generate a client certificate and key:

sudo openssl genrsa -out /etc/mysql/client-key.pem 2048
sudo chmod 600 /etc/mysql/client-key.pem
sudo openssl req -new -key /etc/mysql/client-key.pem -out /etc/mysql/client-csr.pem -subj /CN=mysql/
sudo openssl x509 -req -in /etc/mysql/client-csr.pem -out /etc/mysql/client-cert.pem -CA /etc/mysql/cacert.pem -CAkey /etc/mysql/ca-key.pem -days 365

If you would like to have a free SSL certificate, you can install a trusted SSL certificate following the guide provided here.

Enabling SSL connections on MySQL server

Open the MySQL configuration file with a text editor:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

and uncomment the following lines:

ssl-ca=/etc/mysql/cacert.pem
ssl-cert=/etc/mysql/server-cert.pem
ssl-key=/etc/mysql/server-key.pem

Save the file and restart the MySQL service:

sudo systemctl restart mysql 

To make sure SSL is enabled, log into the MySQL server

mysql -uroot -p

and run the following command:

mysql> show variables LIKE '%ssl%';

The output should be similar to the following:

+---------------+----------------------------+
| Variable_name | Value                      |
+---------------+----------------------------+
| have_openssl  | YES                        |
| have_ssl      | YES                        |
| ssl_ca        | /etc/mysql/cacert.pem      |
| ssl_capath    |                            |
| ssl_cert      | /etc/mysql/server-cert.pem |
| ssl_cipher    |                            |
| ssl_crl       |                            |
| ssl_crlpath   |                            |
| ssl_key       | /etc/mysql/server-key.pem  |
+---------------+----------------------------+
9 rows in set (0.15 sec)

Enabling remote connection over SSL

By default, MySQL listens for connections on the localhost. To allow remote connections, you need to reconfigure MySQL on the server to listen on all interfaces, to do this, open the MySQL configuration file:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

and change

bind-address = 127.0.0.1

on

bind-address = 0.0.0.0

For the changes to take effect, restart the MySQL server with the following command:

sudo systemctl restart mysql 

Make sure your MySQL server is listening on all interfaces:

sudo netstat -anp | grep 3306

the output should be similar to the following:

tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 938/mysqld

To allow the MySQL client to connect to the MySQL server, you need to grant the user database access to the database on the remote server.

For example, if you want to grant access to a user dbuser to the database database_name and enable SSL, connect to MySQL shell as root and run the following command:

GRANT ALL ON database_name.* TO [email protected] IDENTIFIED BY 'dbuserpassword' REQUIRE SSL;

Where 192.168.1.10 your IP address of the client’s MySQL machine.

Next, you need to configure your MySQL client to use the previously generated SSL certificate. Copy the following files from the MySQL server to the MySQL client machine:

/etc/mysql/cacert.pem
/etc/mysql/client-cert.pem
/etc/mysql/client-key.pem

open your MySQL client configuration and add the following lines:

[client]
ssl-ca=/etc/mysql/cacert.pem
ssl-cert=/etc/mysql/client-cert.pem
ssl-key=/etc/mysql/client-key.pem

Now you can test the connection from the client computer to the MySQL database server:

mysql -udbuser -pdbuserpassword -h 192.168.1.5

Where 192.168.1.5 IP – address of the MySQL database server location.

That’s all. You have successfully remotely configured and secured the connection for MySQL on your Ubuntu 16.04 VPS. For more information on MySQL secure connections, please refer to the official MySQL documentation (https://dev.mysql.com/doc/refman/5.7/en/secure-connections.html).