How to Secure Connections to MariaDB with SSL Encryption

Requirements

Procedure

Launch Alibaba Cloud ECS Instance

apt-get update -y

Install MariaDB Server

apt-get install mariadb-server mariadb-client -y
mysql_secure_installation
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
mysql -u root -p
MariaDB [(none)]> SHOW VARIABLES LIKE '%ssl%';
+---------------+----------+
| 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)
MariaDB [(none)]> status
mysql  Ver 15.1 Distrib 10.0.36-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2Connection 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
MariaDB [(none)]> EXIT;

Generate SSL/TLS Certificates and Keys

mkdir /etc/mysql/certs
cd /etc/mysql/certs
openssl genrsa 2048 > ca-key.pem
Generating RSA private key, 2048 bit long modulus
....................+++
....................................................+++
e is 65537 (0x10001)
openssl req -new -x509 -nodes -days 365000 -key ca-key.pem -out ca-cert.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
openssl req -newkey rsa:2048 -days 365 -nodes -keyout server-key.pem -out server-req.pem
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 []:
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 365 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
Signature ok
subject=/C=IN/ST=GUJARAT/L=AHMEDABAD/O=TECH/OU=IT/CN=HITESHJETHVA/emailAddress=hitjethva@gmail.com
Getting CA Private Key
lsca-cert.pem  ca-key.pem  server-cert.pem  server-key.pem  server-req.pem

Enable SSL On MariaDB Server

nano /etc/mysql/mariadb.conf.d/50-server.cnf
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 = *
systemctl restart mysql
mysql -u root -p
MariaDB [(none)]> SHOW VARIABLES LIKE '%ssl%';
+---------------+----------------------------------+
| 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

mysql -u root -p
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'client'@'172.20.10.3' IDENTIFIED BY 'password' REQUIRE SSL;
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> EXIT;

Generate The Client Certificate

cd /etc/mysql/certs
openssl req -newkey rsa:2048 -days 365 -nodes -keyout client-key.pem -out client-req.pem
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 []:
openssl rsa -in client-key.pem -out client-key.pem
openssl x509 -req -in client-req.pem -days 365 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
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

apt-get install mariadb-client -y
mkdir /etc/mysql/certs
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/
nano /etc/mysql/mariadb.conf.d/50-mysql-clients.cnf
ssl-ca=/etc/mysql/certs/ca-cert.pem
ssl-cert=/etc/mysql/certs/client-cert.pem
ssl-key=/etc/mysql/certs/client-key.pem

Test Remote Connection

mysql -u client -h 172.20.10.6 -p mysql
Enter password:
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]>
MariaDB [mysql]> status
mysql  Ver 15.1 Distrib 10.0.36-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2Connection 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

Original Source

--

--

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