
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.