Secure MariaDB Connections with SSL: A Complete Step‑by‑Step Guide

Technical Background

Internet‑financial companies require high data‑security for their databases. The team synchronizes core business databases to a large aggregation database using MariaDB multi‑source replication for analytics and troubleshooting. As the number of DB accounts grows, connections made from insecure locations (e.g., a coffee shop) risk data leakage via packet sniffing.

To mitigate this risk, SSL (Secure Sockets Layer) is used to encrypt client‑server communication, ensuring that without the proper keys the transmitted data cannot be deciphered.

SSL Connection Overview

By default, clients such as PHP or Java connect to MySQL/MariaDB without encryption. You can verify the SSL status with: MariaDB [(none)]> SHOW VARIABLES LIKE 'have_ssl'; If the server supports SSL, the variable value is YES. A value of DISABLED means the TLS module is compiled but not started.

Check whether the TLS library is dynamically linked:

# ldd `which mysqld` | grep ssl

Configuring MariaDB SSL (Eight Steps)

Step 1 – Upgrade OpenSSL

CentOS 6.8 ships with an outdated OpenSSL version that contains vulnerabilities. Upgrade to the latest version:

# wget https://www.openssl.org/source/openssl-1.1.0e.tar.gz
# cd openssl-1.1.0e
# ./config
# make; make install
# ln -s /usr/local/lib64/libssl.so.1.1   /usr/lib64/libssl.so.1.1
# ln -s /usr/local/lib64/libcrypto.so.1.1 /usr/lib64/libcrypto.so.1.1
# mv /usr/bin/openssl /usr/bin/openssl_bak
# mv /usr/include/openssl /usr/include/openssl_bak
# ln -s /usr/local/bin/openssl /usr/bin/openssl
# ln -s /usr/local/include/openssl /usr/include/openssl

The new binaries are installed under /usr/local/bin.

Step 2 – Create CA Certificate

# mkdir -p /etc/mysql/ssl/
# cd /etc/mysql/ssl/

1. Generate the CA private key: # sudo /usr/local/bin/openssl genrsa 2048 > ca-key.pem 2. Generate the CA certificate (valid for 1000 years):

# sudo /usr/local/bin/openssl req -new -x509 -nodes -days 365000 -key ca-key.pem -out ca-cert.pem

Common Name values:

CA common Name: MariaDB admin

Server common Name: MariaDB server

Client common Name: MariaDB client

Step 3 – Create Server Certificate

1. Create a server key request:

# sudo /usr/local/bin/openssl req -newkey rsa:2048 -days 365000 -nodes -keyout server-key.pem -out server-req.pem

2. Convert the key to RSA format:

# sudo /usr/local/bin/openssl rsa -in server-key.pem -out server-key.pem

3. Sign the request to obtain the server certificate:

# sudo /usr/local/bin/openssl x509 -req -in server-req.pem -days 365000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

Step 4 – Create Client Certificate

1. Generate a client key request (valid for 7 days):

# sudo /usr/local/bin/openssl req -newkey rsa:2048 -days 7 -nodes -keyout client-key.pem -out client-req.pem

2. Convert the client key to RSA format:

# sudo /usr/local/bin/openssl rsa -in client-key.pem -out client-key.pem

3. Sign the client request:

# sudo /usr/local/bin/openssl x509 -req -in client-req.pem -days 7 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

Step 5 – Verify Certificates

# sudo /usr/local/bin/openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem

Both certificates should return OK.

Step 6 – Enable SSL on MariaDB Server

Edit my.cnf (under [mysqld]) to add:

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

Restart the server:

# mysqladmin shutdown
# mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &

Confirm activation: # mysql -e "SHOW VARIABLES LIKE '%ssl%';" Two YES values indicate SSL is enabled.

Step 7 – Connect Clients Using SSL

Create an SSL‑required MySQL user:

GRANT SELECT ON *.* TO 'demo'@'%' IDENTIFIED BY 'demo' REQUIRE SSL;

Copy ca-cert.pem, client-cert.pem, and client-key.pem to the client machine and connect:

# mysql --ssl-ca=ca-cert.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem -h192.168.143.244 -P3308 -udemo -pdemo

The status command shows SSL is in use. GUI tools such as Sqlyog or Navicat also require the latest OpenSSL libraries to succeed.

Step 8 – Test Encryption Effectiveness

Using a packet‑sniffing tool (e.g., MySQL Sniffer) shows no readable traffic when SSL is enabled. A bash loop that runs a simple query without SSL writes clear text to a capture file, while the same loop with SSL produces unreadable (encrypted) output.

Sample unencrypted capture command: # tcpdump -i em2 port 3308 -l -s 0 -w - | strings > 1.txt Sample encrypted capture shows only garbled data, confirming successful encryption.

Demo Video

A video demonstration is available at the provided cloud‑disk link.