Salin dan Bagikan

Konfigurasi SSL untuk MySQL dan MariaDB: Panduan Lengkap Keamanan Transport Layer

Data yang beredar tanpa encryption adalah target empuk untuk attacker. Setelah mengaudit beberapa sistem yang mengirimkan data database dalam plaintext, saya menyaksikan betapa pentingnya SSL/TLS untuk setiap koneksi production. Artikel ini akan membahas setup SSL lengkap.

Mengapa SSL/TLS Penting?

Risiko Tanpa Encryption

Eavesdropping: Data bisa diintercept
Man-in-the-Middle: Session hijacking
Data Breach: Credential dan data sensitive terlihat jelas
Compliance Issues: PCI-DSS, GDPR, HIPAA require encryption

Benefits SSL/TLS

End-to-end encryption
Server authentication
Client authentication (optional)
Compliance requirements met

Generate SSL Certificates

1. Using OpenSSL

# Buat direktori untuk certificates
sudo mkdir -p /etc/mysql/ssl
sudo chmod 755 /etc/mysql/ssl
cd /etc/mysql/ssl

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

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

# Generate server private key
sudo openssl req -newkey rsa:4096 -days 3650 -nodes -keyout server-key.pem -out server-req.pem \
    -subj "/C=ID/ST=Jakarta/L=Jakarta/O=YourOrg/CN=mysql-server"

# Remove passphrase dari server key
sudo openssl rsa -in server-key.pem -out server-key.pem

# Generate server certificate (signed by CA)
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
sudo openssl req -newkey rsa:4096 -days 3650 -nodes -keyout client-key.pem -out client-req.pem \
    -subj "/C=ID/ST=Jakarta/L=Jakarta/O=YourOrg/CN=mysql-client"

# Remove passphrase dari client key
sudo openssl rsa -in client-key.pem -out client-key.pem

# 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 permission
sudo chmod 600 *.pem
sudo chown -R mysql:mysql /etc/mysql/ssl

2. Verifikasi Certificates

# Verifikasi server certificate
openssl verify -CAfile ca-cert.pem server-cert.pem

# Verifikasi client certificate  
openssl verify -CAfile ca-cert.pem client-cert.pem

# Lihat certificate details
openssl x509 -in server-cert.pem -text -noout

3. Using Let’s Encrypt (Untuk Public Server)

# Install certbot
sudo apt install certbot

# Generate certificates
sudo certbot certonly --standalone -d db.yourdomain.com

# Copy ke MySQL directory
sudo cp /etc/letsencrypt/live/db.yourdomain.com/fullchain.pem /etc/mysql/ssl/ca-cert.pem
sudo cp /etc/letsencrypt/live/db.yourdomain.com/privkey.pem /etc/mysql/ssl/server-key.pem
sudo cp /etc/letsencrypt/live/db.yourdomain.com/cert.pem /etc/mysql/ssl/server-cert.pem

# Setup auto-renewal
sudo certbot renew --dry-run

Konfigurasi MySQL dengan SSL

1. Edit my.cnf

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

[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

# Require SSL untuk semua connections
require_secure_transport=ON

# SSL Cipher (optional - untuk security ekstra)
ssl-cipher=DHE-RSA-AES256-GCM-SHA384

# Disable weak protocols
tls_version=TLSv1.2,TLSv1.3

# SSL untuk replication (jika menggunakan)
server-id=1
log_bin=mysql-bin
binlog_format=ROW

# Master SSL
ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem

[client]
# Default SSL untuk client
ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-cert=/etc/mysql/ssl/client-cert.pem
ssl-key=/etc/mysql/ssl/client-key.pem
ssl-mode=REQUIRED

2. Restart MySQL

sudo systemctl restart mysql

# Verifikasi status SSL
mysql -u root -p -e "SHOW VARIABLES LIKE '%ssl%';

3. Verifikasi SSL Status

-- Cek SSL variables
SHOW VARIABLES LIKE '%ssl%';

-- Expected output:
-- have_ssl = YES
-- ssl_ca = /etc/mysql/ssl/ca-cert.pem
-- ssl_cert = /etc/mysql/ssl/server-cert.pem
-- ssl_key = /etc/mysql/ssl/server-key.pem

-- Cek current connection
SHOW STATUS LIKE 'Ssl_cipher';
-- Seharusnya menunjukkan cipher yang digunakan

Konfigurasi MariaDB dengan SSL

1. Edit my.cnf

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

[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

# Require SSL
require-secure-transport = on

# TLS Version (MariaDB 10.4+)
tls_version = TLSv1.2,TLSv1.3

# SSL untuk replication
server-id = 1
log_bin = mariadb-bin

# Master SSL
ssl-ca = /etc/mysql/ssl/ca-cert.pem
ssl-cert = /etc/mysql/ssl/server-cert.pem
ssl-key = /etc/mysql/ssl/server-key.pem

[client-mariadb]
ssl-ca = /etc/mysql/ssl/ca-cert.pem
ssl-cert = /etc/mysql/ssl/client-cert.pem
ssl-key = /etc/mysql/ssl/client-key.pem
ssl-verify-server-cert

2. Restart MariaDB

sudo systemctl restart mariadb

-- Verifikasi
mysql -u root -p -e "SHOW VARIABLES LIKE '%ssl%';

Force SSL untuk Specific Users

1. Require SSL untuk User

-- Buat user yang wajib pakai SSL
CREATE USER 'secure_user'@'%' IDENTIFIED BY 'Secure#Pass123';
GRANT SELECT, INSERT, UPDATE, DELETE ON app_database.* TO 'secure_user'@'%';

-- Require SSL
ALTER USER 'secure_user'@'%' REQUIRE SSL;

-- Require X509 (certificate-based authentication)
ALTER USER 'secure_user'@'%' REQUIRE X509;

-- Require specific CN (Common Name)
ALTER USER 'secure_user'@'%' REQUIRE SUBJECT '/CN=mysql-client';

2. Require SSL untuk Semua Users

-- Buat user tanpa SSL akan error
CREATE USER 'no_ssl_user'@'%' IDENTIFIED BY 'Password123';
-- Connection attempt tanpa SSL akan gagal dengan:
-- ERROR 1045 (28000): Access denied... SSL connection error

3. Check User SSL Requirements

-- Lihat SSL requirements untuk semua users
SELECT 
    User, 
    Host, 
    ssl_type,
    ssl_cipher,
    x509_issuer,
    x509_subject
FROM mysql.user
WHERE ssl_type != '';

Client Configuration

1. Command Line dengan SSL

# Connect dengan SSL
mysql -u secure_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

# Atau dengan SSL mode
mysql -u secure_user -p --ssl-mode=REQUIRED -h localhost

# SSL mode options:
# DISABLED - No SSL
# PREFERRED - SSL if available (default)
# REQUIRED - Must use SSL
# VERIFY_CA - SSL + verify CA
# VERIFY_IDENTITY - SSL + verify CA + hostname

2. MySQL Client Configuration File

nano ~/.my.cnf

[client]
ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-cert=/etc/mysql/ssl/client-cert.pem
ssl-key=/etc/mysql/ssl/client-key.pem
ssl-mode=REQUIRED

3. Application Connection String

PHP (PDO):

$dsn = "mysql:host=localhost;dbname=app_database;charset=utf8mb4";
$options = [
    PDO::MYSQL_ATTR_SSL_CA => '/etc/mysql/ssl/ca-cert.pem',
    PDO::MYSQL_ATTR_SSL_CERT => '/etc/mysql/ssl/client-cert.pem',
    PDO::MYSQL_ATTR_SSL_KEY => '/etc/mysql/ssl/client-key.pem',
    PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => true,
];

$pdo = new PDO($dsn, 'secure_user', 'password', $options);

Python (mysql-connector-python):

import mysql.connector

config = {
    'user': 'secure_user',
    'password': 'password',
    'host': 'localhost',
    'database': 'app_database',
    'ssl_ca': '/etc/mysql/ssl/ca-cert.pem',
    'ssl_cert': '/etc/mysql/ssl/client-cert.pem',
    'ssl_key': '/etc/mysql/ssl/client-key.pem',
    'ssl_verify_cert': True,
    'ssl_disabled': False
}

cnx = mysql.connector.connect(**config)

Java (JDBC):

String url = "jdbc:mysql://localhost:3306/app_database?" +
    "useSSL=true&" +
    "requireSSL=true&" +
    "verifyServerCertificate=true&" +
    "trustCertificateKeyStoreUrl=file:/etc/mysql/ssl/ca-cert.pem&" +
    "clientCertificateKeyStoreUrl=file:/etc/mysql/ssl/client-cert.pem&" +
    "clientCertificateKeyStorePassword=password";

Connection conn = DriverManager.getConnection(url, "secure_user", "password");

SSL untuk Replication

1. Master Configuration

# my.cnf master
[mysqld]
server-id=1
log_bin=mysql-bin
binlog_format=ROW

# SSL untuk master
ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem
require_secure_transport=ON

2. Slave Configuration

# my.cnf slave
[mysqld]
server-id=2
relay_log=mysql-relay-bin

# SSL untuk slave
ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-cert=/etc/mysql/ssl/client-cert.pem
ssl-key=/etc/mysql/ssl/client-key.pem

3. Setup Replication dengan SSL

-- Di slave
CHANGE MASTER TO
    MASTER_HOST='master_host',
    MASTER_USER='replica',
    MASTER_PASSWORD='ReplicaPass123',
    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',
    MASTER_AUTO_POSITION=1;

START SLAVE;

-- Verifikasi SSL replication
SHOW SLAVE STATUS\G
-- Perhatikan: Slave_IO_Running, Slave_SQL_Running, Master_SSL_Allowed

Troubleshooting SSL

1. Check SSL Errors

# Di MySQL error log
tail -f /var/log/mysql/error.log | grep -i ssl

2. Common Errors dan Solusi

Error: “SSL connection error: Unable to get private key”

# Fix permission
sudo chmod 600 /etc/mysql/ssl/server-key.pem
sudo chown mysql:mysql /etc/mysql/ssl/server-key.pem

Error: “SSL connection error: certificate verify failed”

# Verifikasi CA certificate
openssl verify -CAfile ca-cert.pem server-cert.pem

# Jika menggunakan self-signed, tambahkan ke trust store
sudo cp ca-cert.pem /usr/local/share/ca-certificates/
sudo update-ca-certificates

Error: “SSL is required but the server doesn’t support it”

-- Cek SSL support di server
SHOW VARIABLES LIKE 'have_ssl';
-- Seharusnya: YES

-- Jika DISABLED, check konfigurasi

3. Test SSL Connection

# Test dengan OpenSSL
openssl s_client -connect localhost:3306 -CAfile /etc/mysql/ssl/ca-cert.pem

# Test dengan MySQL client
mysql -u root -p --ssl-mode=REQUIRED -e "STATUS;" | grep SSL

Monitoring SSL

1. Check Active SSL Connections

-- Cek current SSL connections
SELECT 
    id,
    user,
    host,
    db,
    command,
    time,
    state,
    SUBSTRING(info, 1, 50) as query_preview
FROM information_schema.PROCESSLIST
WHERE command != 'Sleep';

-- Cek SSL status variable
SHOW STATUS LIKE 'Ssl_%';

2. SSL Performance Monitoring

-- SSL tidak signifikan impact performance
-- Check general performance metrics
SHOW GLOBAL STATUS WHERE Variable_name IN (
    'Threads_connected',
    'Threads_running',
    'Queries',
    'Slow_queries'
);

Best Practices

Selalu gunakan SSL untuk production
Use strong cipher suites: Minimal TLS 1.2
Rotate certificates: Setiap 1-2 tahun
Monitor certificate expiry: Setup alerts 30 hari sebelum expiry
Use certificate pinning untuk aplikasi critical
Separate CA untuk different environments
Automate certificate renewal jika menggunakan Let’s Encrypt

Kesimpulan

SSL/TLS encryption adalah non-negotiable untuk database production:

Setup relatif simple dengan OpenSSL
Minimal performance impact (2-5%)
Melindungi data in transit
Memenuhi compliance requirements

Dengan panduan ini, Anda memiliki database yang aman dari eavesdropping dan MITM attacks.

Konfigurasi SSL untuk MySQL dan MariaDB: Panduan Lengkap Keamanan Transport Layer

Konfigurasi SSL untuk MySQL dan MariaDB: Panduan Lengkap Keamanan Transport Layer

Mengapa SSL/TLS Penting?

Risiko Tanpa Encryption

Benefits SSL/TLS

Generate SSL Certificates

1. Using OpenSSL

2. Verifikasi Certificates

3. Using Let’s Encrypt (Untuk Public Server)

Konfigurasi MySQL dengan SSL

1. Edit my.cnf

2. Restart MySQL

3. Verifikasi SSL Status

Konfigurasi MariaDB dengan SSL

1. Edit my.cnf

2. Restart MariaDB

Force SSL untuk Specific Users

1. Require SSL untuk User

2. Require SSL untuk Semua Users

3. Check User SSL Requirements

Client Configuration

1. Command Line dengan SSL

2. MySQL Client Configuration File

3. Application Connection String

SSL untuk Replication

1. Master Configuration

2. Slave Configuration

3. Setup Replication dengan SSL

Troubleshooting SSL

1. Check SSL Errors

2. Common Errors dan Solusi

3. Test SSL Connection

Monitoring SSL

1. Check Active SSL Connections

2. SSL Performance Monitoring

Best Practices

Kesimpulan

Artikel Terkait