🔐 Complete Guide: Setting Up MySQL 8.0 with TLS Encryption on AWS EC2 Ubuntu 24.04

🚀 Introduction

In today's security-conscious environment, protecting database communications is not just a best practice—it's a necessity! 🛡️ This comprehensive guide walks you through setting up MySQL 8.0 Community Edition on AWS EC2 Ubuntu 24.04 with TLS encryption to ensure your data remains secure in transit.

By the end of this tutorial, you'll have a production-ready MySQL installation with enterprise-grade security features, all using open-source tools! 🎉

🔒 Why TLS is Critical for MySQL

The Security Imperative 🚨

Transport Layer Security (TLS) is essential for database security because it:

🔐 Encrypts Data in Transit: Protects sensitive data from network sniffing and man-in-the-middle attacks
✨ Ensures Data Integrity: Prevents tampering with data during transmission
🆔 Provides Authentication: Verifies the identity of both client and server
📜 Compliance Requirements: Meets regulatory standards like GDPR, HIPAA, and PCI-DSS

Key Benefits of TLS in MySQL: 🌟

✅ End-to-End Encryption: All communication between client and server is encrypted
✅ Certificate-Based Authentication: Mutual authentication using digital certificates
✅ ⚡ Performance Optimization: Modern TLS implementations have minimal performance overhead
✅ 📝 Regulatory Compliance: Satisfies most security compliance frameworks
✅ 🌐 Network Security: Protects against packet sniffing and network-based attacks

⚠️ Important: Without TLS, all MySQL traffic travels in plain text, making it vulnerable to interception and unauthorized access! 😱

📋 Prerequisites

Before starting, ensure you have: ✔️

🖥️ AWS EC2 Ubuntu 24.04 instance with root/sudo access
💽 Additional EBS volume (100GB recommended) for MySQL data
💻 Basic knowledge of Linux command line
🗄️ Understanding of MySQL fundamentals

🗄️ Part 1: Preparing Dedicated Storage

Step 1: 🔍 Examine Available Storage

First, let's check what storage devices are available on our system:

lsblk

Expected Output: 📊

NAME     MAJ:MIN RM  SIZE RO TYPE MOUNTPOINTS
loop0      7:0    0 27.2M  1 loop /snap/amazon-ssm-agent/11320
loop1      7:1    0 73.9M  1 loop /snap/core22/1981
loop2      7:2    0 50.9M  1 loop /snap/snapd/24505
xvda     202:0    0  100G  0 disk
├─xvda1  202:1    0   99G  0 part /
├─xvda14 202:14   0    4M  0 part
├─xvda15 202:15   0  106M  0 part /boot/efi
└─xvda16 259:0    0  913M  0 part /boot
xvdb     202:16   0  100G  0 disk
└─xvdb1  202:17   0  100G  0 part

Here we can see xvdb is our dedicated 100GB block device for MySQL! 🎯

Step 2: 🛠️ Create a Partition

Let's create a partition on our dedicated storage device:

sudo fdisk /dev/xvdb

Interactive Session: 💬

Welcome to fdisk (util-linux 2.39.3).
Changes will remain in memory only, until you decide to write them.
Be careful before using the write command.

Device does not contain a recognized partition table.
Created a new DOS (MBR) disklabel with disk identifier 0x28cb18cd.

Command (m for help): n
Partition type
   p   primary (0 primary, 0 extended, 4 free)
   e   extended (container for logical partitions)
Select (default p): p
Partition number (1-4, default 1):
First sector (2048-209715199, default 2048):
Last sector, +/-sectors or +/-size{K,M,G,T,P} (2048-209715199, default 209715199):

Created a new partition 1 of type 'Linux' and of size 100 GiB.

Command (m for help): w
The partition table has been altered.
Calling ioctl() to re-read partition table.
Syncing disks.

Step 3: 💾 Format the Partition

Create an ext4 filesystem on the new partition:

sudo mkfs.ext4 /dev/xvdb1

Expected Output: 📈

mke2fs 1.47.0 (5-Feb-2023)
Creating filesystem with 26214144 4k blocks and 6553600 inodes
Filesystem UUID: a8baa188-427d-4110-a04d-9eab9d44add7
Superblock backups stored on blocks:
        32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
        4096000, 7962624, 11239424, 20480000, 23887872

Allocating group tables: done ✅
Writing inode tables: done ✅
Creating journal (131072 blocks): done ✅
Writing superblocks and filesystem accounting information: done ✅

Step 4: 📂 Mount the Filesystem

Create a mount point and mount the filesystem:

sudo mkdir /mysql
sudo mount /dev/xvdb1 /mysql

Step 5: 🔄 Make Mount Persistent

Get the UUID of the partition and add it to /etc/fstab:

sudo blkid /dev/xvdb1

Output: 🆔

/dev/xvdb1: UUID="a8baa188-427d-4110-a04d-9eab9d44add7" BLOCK_SIZE="4096" TYPE="ext4" PARTUUID="28cb18cd-01"

Add the entry to /etc/fstab:

echo 'UUID=a8baa188-427d-4110-a04d-9eab9d44add7 /mysql ext4 defaults 0 2' | sudo tee -a /etc/fstab

Verify the mount after reboot: ✅

df -h

Expected Output: 📊

Filesystem      Size  Used Avail Use% Mounted on
...
/dev/xvdb1       98G   24K   93G   1% /mysql

🛠️ Part 2: Installing MySQL 8.0

Step 1: 🔄 Update System Packages

Always start with updated packages:

sudo apt update && sudo apt upgrade -y

Step 2: 📥 Install MySQL Server

Install MySQL 8.0 Community Edition:

sudo apt install mysql-server -y

Step 3: ✅ Verify Installation

Check the MySQL version:

mysql --version

Expected Output: 🎉

mysql  Ver 8.0.42-0ubuntu0.24.04.1 for Linux on x86_64 ((Ubuntu))

Step 4: 🔒 Secure MySQL Installation

Run the security script to harden the installation:

sudo mysql_secure_installation

Interactive Configuration: 🛡️

Validate Password Plugin: Yes ✅ (Level 1 - MEDIUM)
Remove Anonymous Users: Yes ✅
Disallow Root Login Remotely: No ❌ (keep default for local development)
Remove Test Database: Yes ✅
Reload Privilege Tables: Yes ✅

🔐 Part 3: Implementing TLS Security

Step 1: 📁 Create SSL Certificate Directory

Create a dedicated directory for our SSL certificates:

sudo mkdir /etc/mysql/ssl
cd /etc/mysql/ssl

Step 2: 🏛️ Generate Certificate Authority (CA)

Create the CA private key:

sudo openssl genrsa 2048 | sudo tee ca-key.pem > /dev/null

Generate the CA certificate: 📜

sudo openssl req -new -x509 -nodes -days 3650 -key ca-key.pem -out ca.pem

Sample Input: 📝

Country Name (2 letter code): US
State or Province Name: California
Locality Name: San Francisco
Organization Name: YourCompany
Organizational Unit Name: IT Department
Common Name: MySQL CA
Email Address: admin@yourcompany.com

Step 3: 🖥️ Generate Server Certificates

Create server private key and certificate request:

sudo openssl req -newkey rsa:2048 -days 3650 -nodes -keyout server-key.pem -out server-req.pem

Process the server key: 🔑

sudo openssl rsa -in server-key.pem -out server-key.pem

Generate the server certificate: 📋

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

Step 4: 👤 Generate Client Certificates

Create client private key and certificate request:

sudo openssl req -newkey rsa:2048 -days 3650 -nodes -keyout client-key.pem -out client-req.pem

Process the client key: 🔑

sudo openssl rsa -in client-key.pem -out client-key.pem

Generate the client certificate: 📋

sudo openssl x509 -req -in client-req.pem -days 3650 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

Step 5: 👥 Set Proper Ownership

Change ownership of all certificate files:

sudo chown mysql:mysql *.pem

Step 6: ⚙️ Configure MySQL for TLS

Edit the MySQL configuration file:

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

Add these lines under the [mysqld] section: ✨

# SSL/TLS Configuration 🔐
ssl-ca=/etc/mysql/ssl/ca.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem

Step 7: 🔄 Restart MySQL Service

Apply the configuration changes:

sudo systemctl restart mysql

Verify MySQL is running: ✅

sudo systemctl status mysql

Expected Output: 🟢

● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Mon 2024-01-15 10:30:45 UTC; 30s ago

Step 8: 🔑 Configure Root Authentication

Change the root user authentication method:

sudo mysql

MySQL Commands: 💻

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YourSecurePassword123#';
FLUSH PRIVILEGES;
EXIT;

✅ Part 4: Verifying TLS Configuration

Step 1: 🔗 Test TLS Connection

Connect to MySQL with TLS required:

mysql -u root -p --ssl-mode=REQUIRED

Step 2: 🔍 Verify SSL Variables

Check the SSL/TLS configuration:

SHOW VARIABLES LIKE '%ssl%';

Expected Output: 📊

+---------------------+----------------------------------+
| Variable_name       | Value                            |
+---------------------+----------------------------------+
| admin_ssl_ca        |                                  |
| admin_ssl_capath    |                                  |
| admin_ssl_cert      |                                  |
| admin_ssl_cipher    |                                  |
| admin_ssl_crl       |                                  |
| admin_ssl_crlpath   |                                  |
| admin_ssl_key       |                                  |
| have_openssl        | YES ✅                           |
| have_ssl            | YES ✅                           |
| ssl_ca              | /etc/mysql/ssl/ca.pem           |
| ssl_capath          |                                  |
| ssl_cert            | /etc/mysql/ssl/server-cert.pem  |
| ssl_cipher          |                                  |
| ssl_crl             |                                  |
| ssl_crlpath         |                                  |
| ssl_fips_mode       | OFF                              |
| ssl_key             | /etc/mysql/ssl/server-key.pem   |
+---------------------+----------------------------------+

Step 3: 📊 Check Connection Status

Verify the current connection is using SSL:

\s

Look for SSL line in output: 🔍

SSL: Cipher in use is TLS_AES_256_GCM_SHA384 🔐

Step 4: 🔒 Test Connection Encryption

Check if the connection is encrypted:

SHOW STATUS LIKE 'Ssl_cipher';

Expected Output: ✨

+---------------+---------------------------+
| Variable_name | Value                     |
+---------------+---------------------------+
| Ssl_cipher    | TLS_AES_256_GCM_SHA384   |
+---------------+---------------------------+

🛡️ Security Considerations

Important Security Notes 📝

🔐 Certificate Management:

Store certificates securely with proper file permissions (600 for private keys) 🔒
Consider using a proper Certificate Authority for production environments 🏢
Rotate certificates regularly (annually recommended) 🔄

💾 Data Encryption at Rest:

MySQL Community Edition doesn't support Transparent Data Encryption (TDE) ❌
Consider using disk-level encryption (LUKS) for additional security 🛡️
Implement proper backup encryption strategies 💾

🌐 Network Security:

Configure firewall rules to restrict MySQL port (3306) access 🔥
Use VPC security groups in AWS to limit network access ☁️
Consider using MySQL over SSH tunnels for additional security 🚇

👤 User Management:

Create dedicated users with minimal required privileges 🎭
Avoid using the root account for application connections ⚠️
Implement strong password policies 🔑

Production Recommendations 🚀

🏛️ Use External Certificate Authority: Replace self-signed certificates with CA-signed certificates
✅ Enable Certificate Validation: Configure clients to verify server certificates
🤝 Implement Mutual TLS: Require client certificates for enhanced security
📊 Monitor SSL Connections: Set up logging and monitoring for SSL/TLS connections
🔍 Regular Security Audits: Perform periodic security assessments

🎯 Conclusion

Congratulations! 🎉 You've successfully implemented a secure MySQL 8.0 installation with TLS encryption. Here's what we've accomplished:

✅ 🗄️ Dedicated Storage: Created and mounted a dedicated filesystem for MySQL data
✅ 🔒 Secure Installation: Installed and hardened MySQL 8.0 Community Edition
✅ 🔐 TLS Implementation: Generated and configured SSL/TLS certificates
✅ ✅ Security Verification: Confirmed TLS encryption is working properly
✅ 📜 Compliance Ready: Met basic security requirements for data encryption in transit

Key Takeaways 💡

🔐 TLS is Essential: Never run MySQL without encryption in production environments
📋 Certificate Management: Proper certificate lifecycle management is crucial
🛡️ Defense in Depth: TLS is one layer; implement additional security measures
🔧 Regular Maintenance: Keep certificates updated and monitor security regularly

Your MySQL installation now provides enterprise-grade security for data in transit, ensuring that sensitive information remains protected from network-based attacks and compliance requirements are met! 🚀

💡 By default, MySQL stores its data under `/var/lib/mysql`. However, for performance optimization or better storage management, you might prefer relocating it to a custom directory like `/mysql` — a step-by-step guide on this will be covered in my upcoming blog! Stay tuned.

DBA NAVIGATOR