BLOG POSTS

MangoHost Blog / Configuring SSL/TLS for MySQL on Ubuntu 24

Configuring SSL/TLS for MySQL on Ubuntu 24

Setting up SSL/TLS encryption for MySQL on Ubuntu 24 is one of those “you really should be doing this” tasks that many developers postpone until they absolutely have to. But here’s the thing – encrypting your database connections isn’t just a nice-to-have security feature anymore, it’s practically mandatory if you’re handling any sensitive data or running production workloads. This guide will walk you through the entire process of configuring SSL/TLS for MySQL on Ubuntu 24, from generating certificates to testing encrypted connections, and I’ll throw in some real-world scenarios and gotchas that you won’t find in the official docs. Whether you’re setting up a new server or retrofitting an existing one, you’ll have everything you need to secure those database connections properly.

How SSL/TLS Works with MySQL

Before we dive into the commands, let’s quickly cover how MySQL handles SSL/TLS encryption. When you enable SSL/TLS on MySQL, the server and client establish an encrypted tunnel using certificates – think of it as a secure handshake before any actual data flows. MySQL supports both one-way SSL (server authentication only) and two-way SSL (mutual authentication where both server and client verify each other).

The process works like this:

MySQL server loads its SSL certificate and private key during startup
Client connects and requests SSL connection
Server presents its certificate to the client
Client verifies the certificate (optionally presents its own for mutual auth)
Encrypted communication begins using the negotiated cipher suite

MySQL uses OpenSSL under the hood, so you get access to all the modern cipher suites and TLS versions. By default, MySQL 8.0+ automatically generates self-signed certificates during installation, but for production use, you’ll want to create proper certificates or use ones from a trusted CA.

Step-by-Step SSL/TLS Configuration

Alright, let’s get our hands dirty. First, make sure you have a fresh Ubuntu 24 system ready. If you need a VPS to follow along, you can grab one from https://mangohost.net/vps or go big with a dedicated server if you’re planning serious production workloads.

Step 1: Install and Verify MySQL

# Update system packages
sudo apt update && sudo apt upgrade -y

# Install MySQL server
sudo apt install mysql-server -y

# Check if MySQL is running and SSL is enabled by default
sudo systemctl status mysql
mysql --version

Quick check to see if SSL is already working:

# Connect to MySQL and check SSL status
sudo mysql -u root -p -e "SHOW VARIABLES LIKE '%ssl%';"

You should see something like have_ssl = YES if SSL support is compiled in.

Step 2: Generate SSL Certificates

MySQL 8.0+ comes with a handy utility called mysql_ssl_rsa_setup that creates certificates automatically, but let’s do it manually for better control:

# Create directory for SSL files
sudo mkdir -p /etc/mysql/ssl
cd /etc/mysql/ssl

# Generate CA private key
sudo openssl genrsa 2048 > ca-key.pem

# Generate CA certificate
sudo openssl req -new -x509 -nodes -days 3650 -key ca-key.pem -out ca-cert.pem \
  -subj "/C=US/ST=State/L=City/O=Organization/CN=MySQL-CA"

# Generate server private key and certificate request
sudo openssl req -newkey rsa:2048 -days 3650 -nodes -keyout server-key.pem -out server-req.pem \
  -subj "/C=US/ST=State/L=City/O=Organization/CN=mysql-server"

# Generate server certificate
sudo openssl x509 -req -in server-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem \
  -set_serial 01 -out server-cert.pem

# Generate client private key and certificate request
sudo openssl req -newkey rsa:2048 -days 3650 -nodes -keyout client-key.pem -out client-req.pem \
  -subj "/C=US/ST=State/L=City/O=Organization/CN=mysql-client"

# Generate client certificate
sudo openssl x509 -req -in client-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem \
  -set_serial 02 -out client-cert.pem

Set proper permissions (this is crucial – MySQL is picky about file permissions):

# Set ownership and permissions
sudo chown mysql:mysql /etc/mysql/ssl/*
sudo chmod 600 /etc/mysql/ssl/*-key.pem
sudo chmod 644 /etc/mysql/ssl/*-cert.pem

Step 3: Configure MySQL for SSL

Now let’s update the MySQL configuration:

# Edit MySQL configuration
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Add these lines under the [mysqld] section:

[mysqld]
# SSL Configuration
ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem

# Optional: Require SSL for all connections
# require_secure_transport=ON

# TLS version configuration (recommended)
tls_version=TLSv1.2,TLSv1.3

# Cipher configuration for better security
ssl_cipher=ECDHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384

Restart MySQL to apply changes:

sudo systemctl restart mysql
sudo systemctl status mysql

Step 4: Create SSL-Enabled Users

Let’s create some users with different SSL requirements:

# Connect to MySQL as root
sudo mysql -u root -p

# Create user that requires SSL
CREATE USER 'ssl_user'@'%' IDENTIFIED BY 'StrongPassword123!' REQUIRE SSL;

# Create user that requires X.509 certificate
CREATE USER 'cert_user'@'%' IDENTIFIED BY 'StrongPassword123!' REQUIRE X509;

# Create user with specific certificate requirements
CREATE USER 'specific_cert_user'@'%' IDENTIFIED BY 'StrongPassword123!' 
REQUIRE ISSUER '/C=US/ST=State/L=City/O=Organization/CN=MySQL-CA';

# Grant privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'ssl_user'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'cert_user'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'specific_cert_user'@'%';

# Flush privileges
FLUSH PRIVILEGES;

Step 5: Test SSL Connections

Time for the moment of truth. Let’s test our SSL setup:

# Test basic SSL connection
mysql -u ssl_user -p --ssl-mode=REQUIRED -h localhost -e "SHOW STATUS LIKE 'Ssl_cipher';"

# Test connection with client certificates
mysql -u cert_user -p \
  --ssl-ca=/etc/mysql/ssl/ca-cert.pem \
  --ssl-cert=/etc/mysql/ssl/client-cert.pem \
  --ssl-key=/etc/mysql/ssl/client-key.pem \
  -h localhost -e "SHOW STATUS LIKE 'Ssl%';"

If everything is working, you should see output showing the SSL cipher being used.

Real-World Examples and Use Cases

Let me share some scenarios I’ve encountered in production environments, both the good and the ugly.

Production Web Application Setup

Here’s how I typically set up SSL for a web application connecting to MySQL:

# Application-specific user with SSL requirement
CREATE USER 'webapp'@'10.0.0.%' IDENTIFIED BY 'ComplexPassword456!' 
REQUIRE SSL;

# Grant specific database access
GRANT SELECT, INSERT, UPDATE, DELETE ON webapp_db.* TO 'webapp'@'10.0.0.%';

# Connection string in application (PHP example)
$dsn = "mysql:host=db.internal;dbname=webapp_db;charset=utf8mb4";
$options = [
    PDO::MYSQL_ATTR_SSL_CA => '/path/to/ca-cert.pem',
    PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => true,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
];

Multi-Environment Configuration

For staging vs production environments, I use different certificate validation levels:

Environment	SSL Requirement	Certificate Validation	Use Case
Development	Optional	Self-signed OK	Local testing
Staging	Required	CA-signed preferred	Production simulation
Production	Required + X.509	Strict CA validation	Maximum security

Common Gotchas and How to Fix Them

Problem 1: Permission Denied Errors

# If you see "SSL connection error: SSL_CTX_set_default_verify_paths failed"
# Check file permissions
ls -la /etc/mysql/ssl/
# Fix permissions
sudo chmod 600 /etc/mysql/ssl/*-key.pem
sudo chown mysql:mysql /etc/mysql/ssl/*

Problem 2: Certificate Verification Failures

# Test certificate validity
openssl x509 -in /etc/mysql/ssl/server-cert.pem -text -noout

# Check if certificate matches private key
openssl x509 -noout -modulus -in /etc/mysql/ssl/server-cert.pem | openssl md5
openssl rsa -noout -modulus -in /etc/mysql/ssl/server-key.pem | openssl md5

Problem 3: Connection Refused with SSL

# Check if MySQL is listening on the right port with SSL
sudo netstat -tlnp | grep 3306

# Verify SSL variables in MySQL
mysql -u root -p -e "SHOW VARIABLES LIKE '%ssl%';"

Performance Impact Analysis

Let’s talk numbers. SSL/TLS does add overhead, but it’s usually negligible for most applications:

Connection Type	Handshake Time	Throughput Impact	CPU Overhead
Unencrypted	~1ms	Baseline	Baseline
SSL/TLS 1.2	~5-8ms	-2% to -5%	+3% to +8%
SSL/TLS 1.3	~3-5ms	-1% to -3%	+2% to +5%

In my experience, the security benefits far outweigh the minimal performance cost, especially when using connection pooling.

Advanced Configuration and Automation

Here’s where things get interesting. Let’s automate certificate renewal and integrate with modern deployment pipelines.

Automated Certificate Renewal Script

#!/bin/bash
# /usr/local/bin/mysql-ssl-renew.sh

SSL_DIR="/etc/mysql/ssl"
BACKUP_DIR="/etc/mysql/ssl/backup"
DAYS_BEFORE_EXPIRY=30

# Create backup directory
mkdir -p $BACKUP_DIR

# Check certificate expiration
EXPIRY_DATE=$(openssl x509 -enddate -noout -in $SSL_DIR/server-cert.pem | cut -d= -f2)
EXPIRY_TIMESTAMP=$(date -d "$EXPIRY_DATE" +%s)
CURRENT_TIMESTAMP=$(date +%s)
DAYS_UNTIL_EXPIRY=$(( ($EXPIRY_TIMESTAMP - $CURRENT_TIMESTAMP) / 86400 ))

if [ $DAYS_UNTIL_EXPIRY -le $DAYS_BEFORE_EXPIRY ]; then
    echo "Certificate expires in $DAYS_UNTIL_EXPIRY days. Renewing..."
    
    # Backup existing certificates
    cp $SSL_DIR/*.pem $BACKUP_DIR/
    
    # Generate new certificates (using same process as above)
    # ... certificate generation commands ...
    
    # Restart MySQL
    systemctl restart mysql
    
    echo "Certificate renewed successfully"
else
    echo "Certificate is valid for $DAYS_UNTIL_EXPIRY more days"
fi

Add this to crontab for monthly checks:

sudo crontab -e
# Add this line:
0 2 1 * * /usr/local/bin/mysql-ssl-renew.sh >> /var/log/mysql-ssl-renew.log 2>&1

Docker Integration

If you’re running MySQL in containers, here’s a Docker Compose setup with SSL:

version: '3.8'
services:
  mysql:
    image: mysql:8.0
    environment:
      MYSQL_ROOT_PASSWORD: rootpassword
      MYSQL_DATABASE: webapp
      MYSQL_USER: appuser
      MYSQL_PASSWORD: apppassword
    volumes:
      - ./ssl:/etc/mysql/ssl:ro
      - ./custom.cnf:/etc/mysql/conf.d/custom.cnf:ro
    ports:
      - "3306:3306"
    command: --ssl-ca=/etc/mysql/ssl/ca-cert.pem 
             --ssl-cert=/etc/mysql/ssl/server-cert.pem 
             --ssl-key=/etc/mysql/ssl/server-key.pem

Monitoring SSL Connections

Keep track of your SSL usage with these monitoring queries:

# Check SSL connection statistics
SELECT 
    VARIABLE_NAME, 
    VARIABLE_VALUE 
FROM performance_schema.global_status 
WHERE VARIABLE_NAME LIKE 'Ssl%';

# Monitor users requiring SSL
SELECT 
    User, 
    Host, 
    ssl_type 
FROM mysql.user 
WHERE ssl_type != '';

# Check current SSL connections
SELECT 
    PROCESSLIST_ID,
    PROCESSLIST_USER,
    PROCESSLIST_HOST,
    CONNECTION_TYPE
FROM performance_schema.threads 
WHERE CONNECTION_TYPE = 'SSL/TLS';

Integration with Other Tools

MySQL SSL plays well with various monitoring and backup tools. Here are some integrations I’ve found useful:

Prometheus Monitoring

The mysqld_exporter can monitor SSL metrics:

# mysqld_exporter configuration with SSL
./mysqld_exporter \
  --config.my-cnf="/etc/mysql/ssl/exporter.cnf" \
  --collect.global_status \
  --collect.info_schema.processlist

Backup with SSL

Don’t forget to configure your backup tools for SSL:

# mysqldump with SSL
mysqldump --ssl-ca=/etc/mysql/ssl/ca-cert.pem \
          --ssl-cert=/etc/mysql/ssl/client-cert.pem \
          --ssl-key=/etc/mysql/ssl/client-key.pem \
          --single-transaction \
          --routines \
          --triggers \
          webapp_db > backup.sql

Replication with SSL

For master-slave setups, SSL is crucial:

# On slave server
CHANGE MASTER TO
  MASTER_HOST='master.example.com',
  MASTER_USER='replication_user',
  MASTER_PASSWORD='replication_password',
  MASTER_SSL=1,
  MASTER_SSL_CA='/etc/mysql/ssl/ca-cert.pem',
  MASTER_SSL_CERT='/etc/mysql/ssl/client-cert.pem',
  MASTER_SSL_KEY='/etc/mysql/ssl/client-key.pem';

Troubleshooting Common Issues

Here are the most frequent issues I’ve encountered and their solutions:

Debug Connection Issues

# Enable MySQL error logging
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# Add:
log_error_verbosity=3
log_error=/var/log/mysql/error.log

# Restart and check logs
sudo systemctl restart mysql
sudo tail -f /var/log/mysql/error.log

SSL Handshake Failures

# Test SSL connectivity with OpenSSL
openssl s_client -connect localhost:3306 -starttls mysql

# Check certificate chain
openssl verify -CAfile /etc/mysql/ssl/ca-cert.pem /etc/mysql/ssl/server-cert.pem

Performance Tuning for SSL

If you’re seeing performance issues with SSL:

# Optimize SSL cipher selection
SET GLOBAL ssl_cipher = 'ECDHE-RSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256';

# Monitor SSL performance
SHOW STATUS LIKE 'Ssl_accepts';
SHOW STATUS LIKE 'Ssl_finished_accepts';

Security Best Practices

Let me share some security practices that have served me well in production:

Certificate rotation: Rotate certificates every 90 days, automate the process
Strong ciphers only: Disable weak cipher suites and old TLS versions
Network segmentation: Use SSL even on internal networks – lateral movement is real
Client certificate validation: For high-security environments, require client certificates
Regular audits: Monitor who’s connecting with and without SSL

Compliance Considerations

For compliance frameworks like PCI DSS, HIPAA, or SOX:

# Force SSL for all connections
SET GLOBAL require_secure_transport = ON;

# Audit SSL usage
SELECT 
    PROCESSLIST_USER,
    PROCESSLIST_HOST,
    CONNECTION_TYPE,
    PROCESSLIST_INFO
FROM performance_schema.threads 
WHERE PROCESSLIST_USER IS NOT NULL;

What This Opens Up

Configuring SSL/TLS for MySQL isn’t just about ticking a security checkbox – it opens up several possibilities:

Compliance readiness: Meet regulatory requirements without last-minute scrambling
Multi-cloud deployments: Secure database connections across different cloud providers
Zero-trust architecture: Encrypt everything, even internal communication
Advanced monitoring: SSL metrics provide insights into connection patterns and security posture
Automated deployments: Infrastructure as code with built-in security

The automation possibilities are particularly exciting. Once you have SSL properly configured, you can build deployment pipelines that automatically provision secure database connections, rotate certificates, and maintain compliance across your entire infrastructure.

Conclusion and Recommendations

Setting up SSL/TLS for MySQL on Ubuntu 24 is straightforward once you know the steps, but the devil is in the details – file permissions, certificate chains, and proper user configuration can trip you up if you’re not careful. The performance overhead is minimal in most cases, and the security benefits are substantial.

My recommendations:

Start with SSL required: Don’t make it optional – enforce it from day one
Automate certificate management: Manual certificate rotation will bite you eventually
Monitor SSL usage: Track who’s connecting and how to identify security gaps
Test your backups: Make sure your backup and restore processes work with SSL
Document everything: Future you (and your teammates) will thank you

Use SSL/TLS for MySQL in any environment where data travels over a network – which is pretty much everywhere these days. The only exception might be single-server development environments, but even then, it’s good practice to match your production setup.

Whether you’re running on a small VPS or a beefy dedicated server, the principles remain the same. Start with the basics, test thoroughly, and gradually add advanced features like client certificate authentication and automated monitoring as your needs grow.

Remember, security isn’t a destination – it’s a journey. Getting SSL/TLS configured is just the first step in building a robust, secure database infrastructure. Keep learning, keep improving, and most importantly, keep those connections encrypted!

This article incorporates information and material from various online sources. We acknowledge and appreciate the work of all original authors, publishers, and websites. While every effort has been made to appropriately credit the source material, any unintentional oversight or omission does not constitute a copyright infringement. All trademarks, logos, and images mentioned are the property of their respective owners. If you believe that any content used in this article infringes upon your copyright, please contact us immediately for review and prompt action.

This article is intended for informational and educational purposes only and does not infringe on the rights of the copyright owners. If any copyrighted material has been used without proper credit or in violation of copyright laws, it is unintentional and we will rectify it promptly upon notification. Please note that the republishing, redistribution, or reproduction of part or all of the contents in any form is prohibited without express written permission from the author and website owner. For permissions or further inquiries, please contact us.