How to Secure Connections to MariaDB with SSL Encryption

By Hitesh Jethva, Alibaba Cloud Community Blog author. The Blog is a community-driven platform whose main aim is to demonstrate Alibaba Cloud’s technical capabilities, brand message, and thought leadership through relevant, compelling content.

MariaDB is a free, open source and drop-in replacement for MySQL database made by the developers of MySQL. It is the most popular relational database management system in the world. MariaDB offers a rich set of feature such as alternate storage engines, server optimizations, and patches. By default, MariaDB accepts only local connections. If you want to allow remote connections, you must secure it with SSL/TLS encryption.

In this tutorial, you will learn how to secure connections to MariaDB with SSL encryption on an Alibaba Cloud Elastic Compute Service (ECS) instance that is installed with Ubuntu 16.04.

Requirements

  • Two newly created ECS instances installed with Ubuntu 16.04.
  • The static IP address 172.20.10.6 is set up on the Server instance, and the IP address 172.20.10.3 is set up on the client instance.
  • A root password is set up for both instances.

Procedure

To secure connections to MariaDB by setting up SSL encryption, follow these steps:

Launch Alibaba Cloud ECS Instance

First, log on to your Alibaba Cloud ECS Console. Then, create a new ECS instance that is installed with Ubuntu 16.04 as the operating system and with at least 2GB RAM. Last, connect to your ECS instance and log on as the root user.

After you are logged on to your ECS instance, run the following command to update your base system with the latest available packages.

Install MariaDB Server

By default, MariaDB is available in the Ubuntu 16.04 default repository. You can install it by running the following command:

After installing MariaDB, you need to secure it first. You can secure it by running the following command:

Next, answer all the questions as shown below with Y or N:

Next, log on to MariaDB shell by running the following command:

Enter your root password when prompted. Then, check the status of the SSL/TLS variables by running the following command:

The output is as follows:

In the above output, you should see that SSL functionality is not yet enabled. Next, you need to check the status of your current MySQL connection by using the following command:

You will see SSL is not currently in use in the following output:

Now, exit from the MariaDB shell with the following command:

Generate SSL/TLS Certificates and Keys

First, you will need to create a directory to store all the certificates and keys. You can do this by running the following command:

Next, change the directory to the certs with the following command:

Next, generate the private key using the following command:

The output is as follows:

Next, generate the CA certificate running the following command:

Provide all the required details as shown below:

Next, create a private key for the server by running the following command:

Provide all the required details as shown below:

Next, export the server’s private key to an RSA-type key by running the following command:

Next, generate a server certificate using the CA certificate with the following command:

The output is as follows:

You can now list all the generated certificates by running the following command:

Enable SSL On MariaDB Server

Next, you will need to configure MariaDB to use SSL. You can do this by editing 50-server.cnf file:

Add the following lines in [mysqld] section:

Save and close the file. Then, restart MariaDB service to apply the changes:

Next, log in to MariaDB shell and check SSL variable:

Enter your root password, then run the following command:

You will see that SSL variables are now enabled:

Create Client User with SSL Privileges

Next, you will need to create a client user and grant privileges to access the MariaDB server over SSL. First, log on to MariaDB shell:

Enter your root password. Then, create a client user with client machine’s IP address and grant privilege to access the MariaDB server over SSL:

Next, flush the privileges and exit from the MariaDB shell:

Generate The Client Certificate

Next, you will need to create an SSL certificates and keys for Client. On the server instance, change the directory to the certs:

Next, create the client key by running the following command:

Provide all the details as shown below:

Next, process the client RSA key:

Next, sign the client certificate by running the following command:

The output is as follows:

Configure MariaDB Client

Now that your MariaDB server is now configured, it’s time to install and configure MariaDB client to use SSL. To do so, first log on to the MariaDB client instance and install the MariaDB client package with the following command:

Next, create a directory to store client certificates and key with the following command:

Next, copy client certificate and key from server instance to client instance with the following command:

Next, you will need to configure MariaDB client to use SSL. You can do this by editing 50-mysql-clients.cnf configuration file as shown below:

Add the following lines in [client] section

When finished, save and close the file.

Test Remote Connection

Now connections between the MariaDB server and MariaDB client is secured with SSL encryption. So, it’s time to test whether it’s all working or not. So, to do this, connect the server with the following command on the MariaDB client instance:

Enter your client user password when prompt. You will see the following output:

Now, check the status of connection with the following command:

You should see that your connection is now secured with SSL in the following output:

Original Source

Written by

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store