Menu
📱 Lihat versi lengkap (non-AMP)
MySQL MariaDB Security

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

Editor: Hendra WIjaya
Update: 3 February 2026
Baca: 6 menit

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

  1. Selalu gunakan SSL untuk production
  2. Use strong cipher suites: Minimal TLS 1.2
  3. Rotate certificates: Setiap 1-2 tahun
  4. Monitor certificate expiry: Setup alerts 30 hari sebelum expiry
  5. Use certificate pinning untuk aplikasi critical
  6. Separate CA untuk different environments
  7. 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.

Artikel Terkait

Bagikan:

Link Postingan: https://www.tirinfo.com/konfigurasi-ssl-mysql-server/