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.

apt-get update -y

Install MariaDB Server

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

apt-get install mariadb-server mariadb-client -y

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

mysql_secure_installation

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

    Enter current password for root (enter for none):
    Set root password? [Y/n]: N
    Remove anonymous users? [Y/n]: Y
    Disallow root login remotely? [Y/n]: Y
    Remove test database and access to it? [Y/n]:  Y
    Reload privilege tables now? [Y/n]:  Y

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

mysql -u root -p

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

MariaDB [(none)]> SHOW VARIABLES LIKE '%ssl%';

The output is as follows:

+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_openssl  | NO       |
| have_ssl      | DISABLED |
| ssl_ca        |          |
| ssl_capath    |          |
| ssl_cert      |          |
| ssl_cipher    |          |
| ssl_crl       |          |
| ssl_crlpath   |          |
| ssl_key       |          |
+---------------+----------+
9 rows in set (0.00 sec)

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:

MariaDB [(none)]> status

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

mysql  Ver 15.1 Distrib 10.0.36-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Connection id:        50
Current database:    
Current user:        root@localhost
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server:            MariaDB
Server version:        10.0.36-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04
Protocol version:    10
Connection:        Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:        /var/run/mysqld/mysqld.sock
Uptime:            4 min 50 sec

Threads: 2  Questions: 1746  Slow queries: 0  Opens: 259  Flush tables: 1  Open tables: 138  Queries per second avg: 6.020

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

MariaDB [(none)]> EXIT;

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:

mkdir /etc/mysql/certs

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

cd /etc/mysql/certs

Next, generate the private key using the following command:

openssl genrsa 2048 > ca-key.pem

The output is as follows:

Generating RSA private key, 2048 bit long modulus
....................+++
....................................................+++
e is 65537 (0x10001)

Next, generate the CA certificate running the following command:

openssl req -new -x509 -nodes -days 365000 -key ca-key.pem -out ca-cert.pem

Provide all the required details as shown below:

You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:IN
State or Province Name (full name) [Some-State]:GUJARAT
Locality Name (eg, city) []:AHMEDABAD
Organization Name (eg, company) [Internet Widgits Pty Ltd]:TECH
Organizational Unit Name (eg, section) []:IT
Common Name (e.g. server FQDN or YOUR name) []:HITESHJETHVA
Email Address []:hitjethva@gmail.com

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

openssl req -newkey rsa:2048 -days 365 -nodes -keyout server-key.pem -out server-req.pem

Provide all the required details as shown below:

Generating a 2048 bit RSA private key
..........................................+++
.......................................................................................+++
writing new private key to 'server-key.pem'
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:IN
State or Province Name (full name) [Some-State]:GUJARAT
Locality Name (eg, city) []:AHMEDABAD
Organization Name (eg, company) [Internet Widgits Pty Ltd]:TECH
Organizational Unit Name (eg, section) []:IT
Common Name (e.g. server FQDN or YOUR name) []:HITESHJETHVA
Email Address []:hitjethva@gmail.com

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:admin@123
An optional company name []:

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

openssl rsa -in server-key.pem -out server-key.pem

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

openssl x509 -req -in server-req.pem -days 365 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

The output is as follows:

Signature ok
subject=/C=IN/ST=GUJARAT/L=AHMEDABAD/O=TECH/OU=IT/CN=HITESHJETHVA/emailAddress=hitjethva@gmail.com
Getting CA Private Key

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

ls

ca-cert.pem  ca-key.pem  server-cert.pem  server-key.pem  server-req.pem

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:

nano /etc/mysql/mariadb.conf.d/50-server.cnf

Add the following lines in [mysqld] section:

ssl-ca=/etc/mysql/certs/ca-cert.pem
ssl-cert=/etc/mysql/certs/server-cert.pem
ssl-key=/etc/mysql/certs/server-key.pem

bind-address            = *

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

systemctl restart mysql

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

mysql -u root -p

Enter your root password, then run the following command:

MariaDB [(none)]> SHOW VARIABLES LIKE '%ssl%';

You will see that SSL variables are now enabled:

+---------------+----------------------------------+
| Variable_name | Value                            |
+---------------+----------------------------------+
| have_openssl  | NO                               |
| have_ssl      | YES                              |
| ssl_ca        | /etc/mysql/certs/ca-cert.pem     |
| ssl_capath    |                                  |
| ssl_cert      | /etc/mysql/certs/server-cert.pem |
| ssl_cipher    |                                  |
| ssl_crl       |                                  |
| ssl_crlpath   |                                  |
| ssl_key       | /etc/mysql/certs/server-key.pem  |
+---------------+----------------------------------+
9 rows in set (0.00 sec)

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:

mysql -u root -p

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:

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'client'@'172.20.10.3' IDENTIFIED BY 'password' REQUIRE SSL;

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

MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> EXIT;

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:

cd /etc/mysql/certs

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

openssl req -newkey rsa:2048 -days 365 -nodes -keyout client-key.pem -out client-req.pem

Provide all the details as shown below:

Generating a 2048 bit RSA private key
......................................................................................+++
..............+++
writing new private key to 'client-key.pem'
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:IN
State or Province Name (full name) [Some-State]:GUJARAT
Locality Name (eg, city) []:AHMEDABAD
Organization Name (eg, company) [Internet Widgits Pty Ltd]:TECH
Organizational Unit Name (eg, section) []:IT
Common Name (e.g. server FQDN or YOUR name) []:HITESHJETHVA
Email Address []:hitjethva@gmail.com

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:admin@123
An optional company name []:

Next, process the client RSA key:

openssl rsa -in client-key.pem -out client-key.pem

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

openssl x509 -req -in client-req.pem -days 365 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

The output is as follows:

Signature ok
subject=/C=IN/ST=GUJARAT/L=AHMEDABAD/O=TECH/OU=IT/CN=HITESHJETHVA/emailAddress=hitjethva@gmail.com
Getting CA Private Key

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:

apt-get install mariadb-client -y

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

mkdir /etc/mysql/certs

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

scp root@172.20.10.6:/etc/mysql/certs/client-* /etc/mysql/certs/
scp root@172.20.10.6:/etc/mysql/certs/ca-cert.pem /etc/mysql/certs/

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:

nano /etc/mysql/mariadb.conf.d/50-mysql-clients.cnf

Add the following lines in [client] section

ssl-ca=/etc/mysql/certs/ca-cert.pem
ssl-cert=/etc/mysql/certs/client-cert.pem
ssl-key=/etc/mysql/certs/client-key.pem

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:

mysql -u client -h 172.20.10.6 -p mysql
Enter password:

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

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 42
Server version: 10.0.36-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [mysql]>

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

MariaDB [mysql]> status

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

mysql  Ver 15.1 Distrib 10.0.36-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Connection id:        42
Current database:    mysql
Current user:        client@172.20.10.3
SSL:            Cipher in use is DHE-RSA-AES256-SHA
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server:            MariaDB
Server version:        10.0.36-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04
Protocol version:    10
Connection:        172.20.10.6 via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
TCP port:        3306
Uptime:            54 min 44 sec

Threads: 3  Questions: 10037  Slow queries: 0  Opens: 42  Flush tables: 1  Open tables: 105  Queries per second avg: 3.056

Community

How to Secure Connections to MariaDB with SSL Encryption

Requirements

Procedure

Launch Alibaba Cloud ECS Instance

Install MariaDB Server

Generate SSL/TLS Certificates and Keys

Enable SSL On MariaDB Server

Create Client User with SSL Privileges

Generate The Client Certificate

Configure MariaDB Client

Test Remote Connection

Read previous post:

Read next post:

Alibaba Clouder

You may also like

Comments

Alibaba Clouder

Related Products

Database Security Solutions

ApsaraDB RDS for MariaDB

Certificate Management Service

Security Solution