Marko Zivkovic
Check the connection status

How to connect to a remote MySQL server using SSL on Ubuntu

April 28, 2020 by

In this article, the steps to connect to remote MySQL databases using Secure Sockets Layer (SSL) will be shown. MySQL is one of the most popular relational database management systems and by default, is configured to accept only connections from the machine where MySQL is installed. To connect to the MySQL database which sits on another machine, the additional configuration must be set to accept the remote connection with secure SSL encryption.

Prerequisite

For this article, two instances of Linux Ubuntu 18.04 needs to be installed.

On one of the Ubuntu machines, install MySQL and configure the root user using the guide from the How to install MySQL on Ubuntu 18.04 page.

On the second machine install the MySQL client using the following command:

sudo apt install mysql-client

Otherwise, you will get the following message, when trying to connect to remote MySQL server:

sudo: mysql: command not found

Missing mysql-client to connect to remote MySQL server

In this article, the name of a machine on which MySQL server sits will be called SSLServer1 and the name of a machine from which we will establish a secure SSL connection to a MySQL server will be called SSLServer2.

Checking status of SSL

So, let’s start configuring SSL for the MySQL server on the SSLServer1 machine. First, let’s check the current status of SSL on the remote MySQL server instance.

Login to MySQL instance using the command below:

mysql -u root -p -h 127.0.0.1

Check SSL status of remote MySQL server

Enter a password for the root user and hit the Enter key from the keyboard.

Once we are logged in, type and execute the following command:

SHOW VARIABLES LIKE ‘%ssl%’;

Depending on the installed MySQL version on your machine, you can get different results. If you are running the MySQL server 5.7.27 and below versions, then you will probably get the following result:

Status of SSL displayed in the terminal of a remote MySQL Server instance

Disabled SSL

As can be seen, the have_openssl and have_ssl variables are disabled. To confirm that SSL is not used for the current connection, execute the following:

\s

or

status

Command for checking of MySQL connection

The output result will be similar like one below:

————–
mysql Ver 14.14 Distrib 5.7.29, for Linux (x86_64) using EditLine wrapper
Connection id: 3
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ”
Using delimiter: ;
Server version: 5.7.29-0ubuntu0.18.04.1 (Ubuntu)
Protocol version: 10
Connection: 127.0.0.1 via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 10 min 10 sec
Threads: 1 Questions: 6 Slow queries: 0 Opens: 106 Flush tables: 1 Open tables: 99 Queries per second avg: 0.009
————–

SSL connection is not in use

As can be seen from the image above, the SSL is not in use for this connection.

Another method to determine whether the current connection with the MySQL server uses encryption is to check the session value of the Ssl_cipher status variable:

SHOW SESSION STATUS LIKE ‘Ssl_cipher’;

If the value is empty, it indicates that the current connection does not use encryption:

Ssl_cipher variable is empty

In the situation where current connection uses encryption, then the value will not be empty and will look like shown below:

Ssl_cipher variable is not empty

  • Note: I use MySQL 5.7.29 version in which by default the SSL is enabled, but for this example, I disabled SSL by modifying the my.cnf file to show the DISABLED status of SSL:

MySQL configuration file

In this configuration file, I entered the following command to disable SSL:

skip_ssl

Command for disabling SSL encryption

To enable SSL and generates appropriate certification and key files you can use the mysql_ssl_rsa_setup utility:

sudo mysql_ssl_rsa_setup –uid=mysql

By default, MySQL stored SSL certification on this location: /var/lib/mysql

Let’s check that by executing this command:

sudo find /var/lib/mysql/ -name ‘*.pem’

Generated SSL certification for connection to remote MySQL server

But if you have installed the MySQL 5.7.28 version and above, then SSL certification will be automatically generated on the /var/lib/mysql location and SSL encryption enabled when starting MySQL:

Configurated SSL certification for connect to remote MySQL server

SSL encryption for MySQL connection

Configuring remote MySQL server

Now that we have enabled SSL, the next step in the configuration of MySQL is to enable (allow) remote connection. By default, MySQL is configured to listen to the connection from the machine on which is installed and only accept connection from the localhost AKA 127.0.0.1 IP address.

If we try now to connect from the client machine (SSLServer2) to MySQL that sit on SSLServer1 machine, we will probably get the following error message:

ERROR 2003 (HY000): Can’t connect to MySQL server on (111)

Connection to remote MySQL server refused

To allow connection to MySQL server from any remote IP address, in the MySQL configuration file /etc/mysql/my.cnf add the following:

bind-address = 0.0.0.0

Set MySQL to allow connection from any remote IP address

Now, restart the MySQL service for the changes to changes take effect:

sudo service mysql restart

Restart MySQL service

When we try again to connect to remote MySQL server, we will get an error message:

ERROR 1045 (28000): Access denied for user (using password: YES)

Access denied to connect to remote MySQL server

Currently, all MySQL users are configured to connect locally from the MySQL server. To connect to remote MySQL server, you will need to create a new user with the IP address of the client machine, in our case, we need the IP address of the SSLServer2 machine.

To view the IP address of the machine (SSLServer2), in terminal type and execute the following command:

Ifconfig

Machine IP address

Now, when we get all necessary information, let’s create a user that will be allowed to connect to remote MySQL with secure connection by adding REQUIRE SSL clause:

CREATE USER ‘SSL_USER’@'<SSLServer2_IP>’ IDENTIFIED BY ‘password’ REQUIRE SSL;

After we have created the user, let’s gave him all permissions to access all remote MySQL databases.

Execute the following command:

GRANT ALL ON *.* TO ‘SSL_USER’@'<SSLServer2_IP>’;

To apply these settings immediately, execute the following:

FLUSH PRIVILEGES;

One more thing that needs to be done on the SSLServer1 machine is to add the require_secure_transport option to the my.cnf file and set it to ON.

Enter require_secure_transport option to my.cnf file

This requires all remote connections to be made with SSL.

After this, restart the MySQL service, to apply the new settings:

sudo service mysql restart

With this, we allowed the SSL_USER to connect from a remote location. To confirm that, let’s connect with SSL_USER from the SSLServer2 machine.

On the SSLServer2 machine, open the Terminal and execute the following command:

mysql -u SSL_USER -p -h <SSLServer1_IP>

Connect to remote MySQL server using SSL_USER

After entering the password of SSL_USER, you’ll be able to connect to remote MySQL server that sits on the SSLServer1 machine:

Remote MySQL server connection established

Now, type the \s to confirm the status of the server and confirm you are using a secure connection:

Check the connection status

To confirm that MySQL server only accepts the secure connection and rejects insecure, try to connect with the same credentials but this time in connection string add –ssl-mode=disabled command:

mysql -u SSL_USER -p -h –ssl-mode=disabled

After executing, the following error may appear:

ERROR 1045 (28000): Access denied for user ‘SSL_USER’@’SSLServer2_IP’ (using password: YES)

Connection to remote MySQL server refused

By now, you have set MySQL server with an SSL certificate signed by a locally generated certificate authority (CA), this is enough to provide secure encryption for incoming connections.

To provide the fully trusted relationship between the SSLServer1 and SSLServer2 machines, from SSLServer1 machine will be copied the CA certificate with the client certificate and the client key to the SSLServer2 machine, also the SSL_USER will be modified to require a trusted certificate.

On the SSLServer2 machine first, create a folder where the client files will be placed:

mkdir ~/ssl-client

Use the SCP or SFTP to secure transfer files from one machine to another, or just use the cat command to read and copy the content from the CA certificate, client-cert.pem and client-key.pem files on the SSLServer1 machine and create those files and past the content in it on the SSLServer2 machine:

Creating SSL files to the ssl-client folder

After the appropriate files are copied, alter the SSL_USER, and put the REQUIRE X509 clause:

ALTER USER ‘SSL_USER’@'<SSLServer2_IP>’ REQUIRE X509;

Now, when connecting to remote MySQL server with the SSL_USER user, the –ssl-key and –ssl-cert options must be specified, also it is recommended but not required the –ssl-ca option to be specified. If we connect to remote MySQL server without specifying client certificates:

mysql -u SSL_USER1 -p -h <SSLServer1_IP>

The following error message may appear:

ERROR 1045 (28000): Access denied for user ‘SSL_USER’@’SLLServer2_IP’ (using password: YES)

Let’s specify appropriate client certificate by adding –ssl-ca, –ssl-cert, and –ssl-key options to connection string to point to the files under the ~/ssl-client directory:

mysql -u SSL_USER -p -h SSLServer1_IP –ssl-ca=~/ssl-client/ca.pem –ssl-cert=~/ssl-client/client-cert.pem –ssl-key=~/ssl-client/client-key.pem

After you hit the Enter, you should connect to remote MySQL server successfully:

Using SSL files to connect to remote MySQL server

Using the –ssl-ca, –ssl-cert, and –ssl-key options and entering a path to the relevant files every time you want to connect to remote MySQL server is a tidiest and time-consuming job, so let’s optimizing and improve connection usability.

In the SSLServer2 machine, if the my.cnf file doesn’t exist under these locations:

  • /etc/my.cnf
  • /etc/mysql/my.cnf
  • /usr/etc/my.cnf
  • ~/.my.cnf

We can create one in the home directory:

~/.my.cnf:

Execute the following command:

nano ~/.my.cnf

And in the .my.cnf file paste and save the following:

[client]
ssl-ca = ~/ssl-client/ca.pem
ssl-cert = ~/ssl-client/client-cert.pem
ssl-key = ~/ssl-client/client-key.pem

Set up SSL paths in the client .my.cnf file

Now, you can connect to remote MySQL server without adding the –ssl-ca, –ssl-cert, and –ssl-key options in the connection string:

mysql -u SSL_USER1 -p -h <SSLServer1_IP>

Connect to remote MySQL server without using SSL options in connection string

Conclusion

In this article, you’ve seen how to set up a remote MySQL database connection. We kicked off by explaining the prerequisites for this to work, then we checked the status of SSL, made some modifications to allow users to connect to remote MySQL server, and finally tweaked some settings a little bit to make life easier and save some typing every time we connect remotely.

Marko Zivkovic
Linux, MySQL, Ubuntu

About Marko Zivkovic

Marko is a Mechanical engineer, who likes to play basketball, foosball (table-soccer) and listen to rock music. He is interested in SQL code, PHP development, HTML and CSS techniques. Currently working for ApexSQL LLC as a Software Sales Engineer, he is helping customers with any technical issues and does quality assurance for ApexSQL Complete, ApexSQL Refactor and ApexSQL Search free add-ins. View all posts by Marko Zivkovic

1,574 Views