How to Secure Connections to MariaDB with SSL Encryption

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

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

--

--

--

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Android CameraX Java Example

Importing data into Firestore using Python

Starting Our Journey with Hackathon

Journal to be a Web Developer (II): Classic, it’s CRUD.

What happens when I type any URL in the browser ?

TryHackMe | Mustacchio

How To Get Affordable App Solutions?

What you should know about P4 programming language& P4 programmable switch

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
Alibaba Cloud

Alibaba Cloud

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com

More from Medium

Hot-Reload Webapps on Remote Machine

Consolidated list of papers on distributed database systems and parallel computing papers from…

APACHE SPARK-RDD

Routing OSPF Configuration with Cisco Packet Tracer