Lewati ke konten
Kembali ke Blog
MariaDB MySQL Security

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

Hendra Wijaya
Β· Β· 9 menit baca

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.

Ditulis oleh

Hendra Wijaya

Hanya hamba Allah Ta'ala yang berusaha berbuat baik..

Artikel Sebelumnya Kebijakan Google AdSense Terbaru 2026: Compliance dan Avoid Banned Artikel Selanjutnya Cara Deploy Hugo ke Netlify Vercel dan GitHub Pages: Panduan Lengkap

Artikel Terkait

Tutorial Konfigurasi Fail2ban Ubuntu: Keamanan SSH

Tutorial Konfigurasi Fail2ban Ubuntu: Keamanan SSH

Februari 4, 2026
Hugo Security Best Practices: Mengamankan Website Hugo

Hugo Security Best Practices: Mengamankan Website Hugo

Februari 4, 2026
Cara Install MySQL di Windows 10 dan 11: Panduan Lengkap Server Setup

Cara Install MySQL di Windows 10 dan 11: Panduan Lengkap Server Setup

Februari 3, 2026

Tinggalkan Komentar

Email tidak akan ditampilkan.