How to configure remote and secure connections for MySQL on Ubuntu 16.04

In this tutorial, we will teach you how to configure remote and secure connections for MySQL on Ubuntu 16.04 VPS.

If you happen to be using an unencrypted connection to connect to your remote MariaDB/MySQL server, then everyone with access to the network will be able to monitor and analyze any data which gets sent or received between the client and the server.

Note that this tutorial could work on other Linux VPS systems too, however, it was made and tested for Ubuntu 16.04.

Step 1: Logging into your VPS via SSH

ssh my_sudo_user@my_server

The steps in this guide are going to assume that you have MySql 5.8 installed with the default configuration.

2. Create a new local certificate authority

You are going to use the local certificate authority as a self-signed root certificate and you will be using it to sign both the server and client certificates.

To create a new local certificate authority file execute the commands below.

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

Step 3: Generate a Server Certificate and Key

In order to generate a server certificate and key you will need to run the commands below.

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

Step 4: Generate Clients Certificate and Key

Now, you have to generate the client certificate; execute the commands below to generate a client certificate and key.

Step 5: Enabling MySQL Server SSL Connections

Open the MySQL configuration file using an editor of your choosing.

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

Now, you will need to uncomment the lines below.

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 by running the command below.

sudo systemctl restart mysql

To confirm whether SSL is enabled, log into the MySQL server.

mysql -uroot -p

Afterwards, execute the command below.

mysql> show variables LIKE '%ssl%';

You should receive an output similar to the one below:

+---------------+----------------------------+
| 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)

Step 6: Enable Remote Connections via SSH

By default, MySQL is going to listen only to connections on localhost. To enable remote connections you have to reconfigure MySQL on your server so it listens on all interfaces. To do this open the MySQL configuration file.

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

Replace the following code.

bind-address            = 127.0.0.1

With this following code.

bind-address            = 0.0.0.0

If you want the changes to go into effect, restart the MySQL server using the following command.

sudo systemctl restart mysql

Confirm that your MySQL server is listening on all interfaces.

sudo netstat -anp | grep 3306

You should receive an output similar to the one below.

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

In order to enable your MySQL client to connect to the MySQL server, you have to grant the database user access to the database on the remote server.
For example If you’d like to give access to the dbuser to the database_name database and to force SSL, login to the MySQL shell as root and execute the command below:

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

192.168.1.10 is going to be your MySQL client machine IP address
Now, you have to configure your MySQL client to use the previously generated SSL certificate.
Copy the files below from your MySQL server to your MySQL client machine:

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

Step 7: Configure MySQL Client

Open your MySQL client configuration and append the lines below to it:

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

Step 8: Testing your Connection

You may now test your connection from the client machine to the MySQL database server:

mysql -udbuser -pdbuserpassword -h 192.168.1.5

192.168.1.5 is your MySQL database server IP address.
Also, here we have a few hand-picked guides that you must read next:

Updated on December 23, 2018

Was this article helpful?

Yes No