DEV Community

Bytebase profile image Ayra Jett
Ayra Jett for Bytebase

Posted on • Originally published at bytebase.com

How to Configure MariaDB SSL Connection

#ssl #mariadb #database #sql

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.

Top comments (0)