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

Login to your VPS via SSH

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

Enable SSL Connections

Create a new local certificate authority

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

Generate Server Certificate and Key

To generate a server certificate and key issue the following commands:

Generate Clients Certificate and Key

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

If you want to have a CA signed SSL certificate, you can purchase a trusted SSL certificate here.

Enable MySQL Server SSL Connections

Open the the MySQL configuration file with your text editor:

and uncomment the following lines:

Save the file and restart the MySQL service:

To verify that SSL is enabled, login to the MySQL server

and run the following command:

The output should be similar to the following one:

Enable Remote Connections via SSL

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

and change

to

In order for the changes to take effect, restart the MySQL server with the following command:

Verify that your MySQL server listens on all interfaces:

the output should be similar to the following one:

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

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

where 192.168.1.10 is your MySQL client machine IP address.

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

open your MySQL client configuration and add the following lines:

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

where 192.168.1.5 is your MySQL database server IP address.