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