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.
To secure connections to MariaDB by setting up SSL encryption, follow these steps:
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
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;
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
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)
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;
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
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.
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
How to Install Fail2ban to Protect Against Brute Force Login Attacks
2,599 posts | 763 followers
FollowAlibaba Cloud MVP - December 6, 2019
Alibaba Clouder - May 20, 2020
Alibaba Clouder - December 6, 2017
Alibaba Clouder - March 28, 2018
Alibaba Clouder - January 25, 2018
Cherish Wang - September 16, 2019
2,599 posts | 763 followers
FollowProtect, backup, and restore your data assets on the cloud with Alibaba Cloud database services.
Learn MoreApsaraDB RDS for MariaDB supports multiple storage engines, including MySQL InnoDB to meet different user requirements.
Learn MoreYou can use Certificate Management Service to issue, deploy, and manage public and private SSL/TLS certificates.
Learn MoreAlibaba Cloud is committed to safeguarding the cloud security for every business.
Learn MoreMore Posts by Alibaba Clouder