In this article, we will show you how to configure remote and secure connections for MySQL on Ubuntu 16.04 VPS. If you are using an unencrypted connection to connect to your remote MariaDB/MySQL server, then everyone with access to the network could monitor and analyze the data being sent or received between the client and the server. This guide should work on other Linux VPS systems as well but was tested and written for an Ubuntu 16.04 VPS.
Login to your VPS via SSH
ssh my_sudo_user@my_server
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:
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
Generate Server Certificate and Key
To generate a server certificate and key issue 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
Generate Clients Certificate and Key
Next, we need to generate the 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 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:
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 verify that SSL is enabled, login to the MySQL server
mysql -uroot -p
and run the following command:
mysql> show variables LIKE '%ssl%';
The output should be similar to the following one:
+---------------+----------------------------+ | 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)
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:
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
and change
bind-address = 127.0.0.1
to
bind-address = 0.0.0.0
In order for the changes to take effect, restart the MySQL server with the following command:
sudo systemctl restart mysql
Verify that your MySQL server listens on all interfaces:
sudo netstat -anp | grep 3306
the output should be similar to the following one:
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 938/mysqld
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:
GRANT ALL ON database_name.* TO [email protected] IDENTIFIED BY 'dbuserpassword' REQUIRE SSL;
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:
/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
You can now test your connection from the client machine to the MySQL database server:
mysql -udbuser -pdbuserpassword -h 192.168.1.5
where 192.168.1.5 is your MySQL database server IP address.
That’s it. You have successfully configured remote and secure connections for MySQL on your Ubuntu 16.04 VPS. For more information about MySQL secure connections, please refer to the official MySQL documentation.