DEV Community

Ayra Jett for Bytebase

Posted on β€’ Edited on β€’ Originally published at bytebase.com

4 2 2 2 2

How to Configure MariaDB 🦦 SSL Connection πŸ”Œ

This tutorial shows you how to configure MariaDB SSL connection using self-signed certificates. You'll learn to:

  1. Generate SSL certificates (CA, server, client)
  2. Configure MariaDB server for SSL
  3. Test SSL connections from clients

Prerequisites

# Verify MariaDB installation
mariadb --version

# Verify OpenSSL installation
openssl version
Enter fullscreen mode Exit fullscreen mode

Ensure you have MariaDB and OpenSSL installed.

Generate SSL Related Files

OpenSSL Config

Set up the configuration file:

cat >req.conf <<EOF
[ req ]
distinguished_name = req_distinguished_name
x509_extensions = v3_ca
prompt = no
[ req_distinguished_name ]
C = CN
ST = GD
O = Bytebase
CN = root
[ v3_ca ]
basicConstraints = critical,CA:TRUE
subjectKeyIdentifier = hash
authorityKeyIdentifier = keyid:always,issuer:always
[ v3_req ]
keyUsage = keyEncipherment, dataEncipherment
extendedKeyUsage = serverAuth
subjectAltName = @alt_names
[ alt_names ]
IP.1 = YOUR_SERVER_IP
DNS.1 = YOUR_SERVER_HOSTNAME
DNS.2 = localhost
IP.2 = 127.0.0.1
EOF
Enter fullscreen mode Exit fullscreen mode

Replace YOUR_SERVER_IP with your actual server IP address. You can find it with ifconfig or ip addr show.

Generate Certificates

Generate Root CA key and certificate:

openssl genrsa -out ca-key.pem 2048
openssl req -x509 -new -key ca-key.pem -sha256 -days 36500 -out ca-cert.pem -extensions 'v3_ca' -config req.conf
Enter fullscreen mode Exit fullscreen mode

Generate Server key and certificate:

openssl genrsa -out server-key.pem 2048
openssl req -new -sha256 -key server-key.pem -out server-req.pem -subj "/C=CN/ST=GD/O=Bytebase/CN=YOUR_SERVER_IP"
openssl x509 -req -days 36500 -sha256 -extensions v3_req -CA ca-cert.pem -CAkey ca-key.pem -CAcreateserial -in server-req.pem -out server-cert.pem
Enter fullscreen mode Exit fullscreen mode

Replace YOUR_SERVER_IP with your real server IP.

Generate Client key and certificate:

openssl genrsa -out client-key.pem 2048
openssl req -new -sha256 -key client-key.pem -out client-req.pem -subj "/C=CN/ST=GD/O=Bytebase/CN=mariadb-client"
openssl x509 -req -days 36500 -sha256 -extensions v3_req -CA ca-cert.pem -CAkey ca-key.pem -CAcreateserial -in client-req.pem -out client-cert.pem
Enter fullscreen mode Exit fullscreen mode

Configure MariaDB Server

Copy SSL files and set permissions:

For macOS (Homebrew):

# For Apple Silicon Macs
sudo mkdir -p /opt/homebrew/etc/mariadb/ssl
sudo cp ca-cert.pem server-cert.pem server-key.pem /opt/homebrew/etc/mariadb/ssl/
sudo chown -R $(whoami):admin /opt/homebrew/etc/mariadb/ssl/
sudo chmod 600 /opt/homebrew/etc/mariadb/ssl/*-key.pem
sudo chmod 644 /opt/homebrew/etc/mariadb/ssl/ca-cert.pem /opt/homebrew/etc/mariadb/ssl/server-cert.pem

# For Intel Macs
# sudo mkdir -p /usr/local/etc/mariadb/ssl
# sudo cp ca-cert.pem server-cert.pem server-key.pem /usr/local/etc/mariadb/ssl/
# sudo chown -R $(whoami):admin /usr/local/etc/mariadb/ssl/
# sudo chmod 600 /usr/local/etc/mariadb/ssl/*-key.pem
# sudo chmod 644 /usr/local/etc/mariadb/ssl/ca-cert.pem /usr/local/etc/mariadb/ssl/server-cert.pem
Enter fullscreen mode Exit fullscreen mode

For Linux systems:

sudo mkdir -p /etc/mariadb/ssl
sudo cp ca-cert.pem server-cert.pem server-key.pem /etc/mariadb/ssl/
sudo chown mysql:mysql /etc/mariadb/ssl/*
sudo chmod 600 /etc/mariadb/ssl/*-key.pem
sudo chmod 644 /etc/mariadb/ssl/ca-cert.pem /etc/mariadb/ssl/server-cert.pem
Enter fullscreen mode Exit fullscreen mode

Edit MariaDB configuration file:

# For macOS (Apple Silicon)
sudo nano /opt/homebrew/etc/my.cnf

# For macOS (Intel)
sudo nano /usr/local/etc/my.cnf

# For Linux (Ubuntu/Debian)
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

# For Linux (CentOS/RHEL)
sudo nano /etc/my.cnf
Enter fullscreen mode Exit fullscreen mode

Add SSL configuration:

For macOS (Apple Silicon):

[mysqld]
ssl-ca = /opt/homebrew/etc/mariadb/ssl/ca-cert.pem
ssl-cert = /opt/homebrew/etc/mariadb/ssl/server-cert.pem
ssl-key = /opt/homebrew/etc/mariadb/ssl/server-key.pem
bind-address = 0.0.0.0
port = 3306
Enter fullscreen mode Exit fullscreen mode

For macOS (Intel):

[mysqld]
ssl-ca = /usr/local/etc/mariadb/ssl/ca-cert.pem
ssl-cert = /usr/local/etc/mariadb/ssl/server-cert.pem
ssl-key = /usr/local/etc/mariadb/ssl/server-key.pem
bind-address = 0.0.0.0
port = 3306
Enter fullscreen mode Exit fullscreen mode

For Linux systems:

[mysqld]
ssl-ca = /etc/mariadb/ssl/ca-cert.pem
ssl-cert = /etc/mariadb/ssl/server-cert.pem
ssl-key = /etc/mariadb/ssl/server-key.pem
bind-address = 0.0.0.0
port = 3306
Enter fullscreen mode Exit fullscreen mode

Restart MariaDB:

# For macOS (Homebrew)
brew services restart mariadb

# For Linux (systemd)
sudo systemctl restart mariadb
Enter fullscreen mode Exit fullscreen mode

Test SSL Connection

mariadb -h localhost -u root -p
Enter fullscreen mode Exit fullscreen mode

So that you'll be entering MariaDB CLI. You can also verify remote connection by replacing the localhost above with your server IP to connect. Check your SSL connection with:

\s
Enter fullscreen mode Exit fullscreen mode

Seeing something like SSL: Cipher in use is TLS_AES_256_GCM_SHA384, cert is OK, so that the SSL connection is ready.

Or use command

SHOW STATUS LIKE 'Ssl_version';
Enter fullscreen mode Exit fullscreen mode

You'll see something like:

+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Ssl_version   | TLSv1.3 |
+---------------+---------+
1 row in set (0.006 sec)
Enter fullscreen mode Exit fullscreen mode

Summary

You have successfully configured SSL for MariaDB:

  1. Generated CA, server, and client certificates
  2. Configured MariaDB with SSL settings
  3. Tested secure connections from clients

Your MariaDB server now accepts encrypted connections only.

Warp.dev image

The best coding agent. Backed by benchmarks.

Warp outperforms every other coding agent on the market, and gives you full control over which model you use. Get started now for free, or upgrade and unlock 2.5x AI credits on Warp's paid plans.

Download Warp

Top comments (1)

Collapse
 
sawyerwolfe profile image
Sawyer Wolfe β€’

Great step-by-step guide! This makes setting up secure MariaDB connections much clearer. Thanks for sharing!

πŸ‘‹ Kindness is contagious

Take a moment to explore this thoughtful article, beloved by the supportive DEV Community. Coders of every background are invited to share and elevate our collective know-how.

A heartfelt "thank you" can brighten someone's dayβ€”leave your appreciation below!

On DEV, sharing knowledge smooths our journey and tightens our community bonds. Enjoyed this? A quick thank you to the author is hugely appreciated.

Okay