Skip to content
The Chat Archive
chatgpt-4o-latest-20250326· OpenAI LMArena Human Preference 140K

enable ssl connection between 2 mariadb on galera with selfsigned cert at 10y

  1. User

    enable ssl connection between 2 mariadb on galera with selfsigned cert at 10y

  2. Assistant

    Enabling SSL connections between two MariaDB nodes in a Galera cluster with self-signed certificates valid for 10 years involves several steps:


    ✅ Overview

    You’ll need to:

    1. Generate self-signed SSL certificates and keys.
    2. Configure MariaDB on both nodes to use SSL.
    3. Configure Galera to use SSL for replication traffic.
    4. Restart MariaDB services and verify SSL connection.

    🧱 Assumptions

    • You are running MariaDB with Galera (e.g., MariaDB 10.5+).
    • Both nodes: node1 and node2.
    • IPs: node1 = 192.168.1.101, node2 = 192.168.1.102
    • MariaDB installed in default paths (/etc/mysql, /etc/my.cnf.d, etc.)

    1️⃣ Generate Self-Signed Certificate (Valid for 10 Years)

    On node1, create a CA and generate certificates:

    mkdir -p /etc/mysql/ssl
    cd /etc/mysql/ssl
    

    Create CA Key and Certificate (valid for 10 years):

    openssl genrsa -out ca-key.pem 4096
    openssl req -x509 -new -nodes -key ca-key.pem -sha256 -days 3650 \
        -subj "/CN=Galera-CA" \
        -out ca-cert.pem
    

    Create Server Key and CSR:

    openssl genrsa -out server-key.pem 2048
    openssl req -new -key server-key.pem \
        -subj "/CN=Galera-Server" \
        -out server.csr
    

    Sign Server Certificate with CA:

    openssl x509 -req -in server.csr -CA ca-cert.pem -CAkey ca-key.pem \
        -CAcreateserial -out server-cert.pem -days 3650 -sha256
    

    Repeat similar steps for client certificate (optional).


    📤 Copy Certificates to Node2

    On node2, create the same /etc/mysql/ssl directory and copy:

    scp /etc/mysql/ssl/* node2:/etc/mysql/ssl/
    

    Set proper permissions:

    chmod 600 /etc/mysql/ssl/*.pem
    chown mysql:mysql /etc/mysql/ssl/*.pem
    

    Repeat on both nodes.


    2️⃣ Configure SSL in MariaDB Configuration

    On each node, update MariaDB configuration file (e.g., /etc/mysql/mariadb.conf.d/50-server.cnf):

    [mysqld]
    ssl-ca=/etc/mysql/ssl/ca-cert.pem
    ssl-cert=/etc/mysql/ssl/server-cert.pem
    ssl-key=/etc/mysql/ssl/server-key.pem
    
    # Optionally enforce SSL connections
    require_secure_transport=ON
    

    This configures client/server SSL, not Galera replication yet.


    3️⃣ Enable SSL for Galera (gcomm) Replication

    MariaDB Galera supports SSL in wsrep_provider_options.

    Edit the Galera section:

    [mysqld]
    # Galera basic config
    wsrep_on=ON
    wsrep_provider=/usr/lib/galera/libgalera_smm.so
    wsrep_cluster_address=gcomm://192.168.1.101,192.168.1.102
    wsrep_node_address=192.168.1.101  # or 102 on the other node
    wsrep_node_name=node1             # or node2
    
    # Galera SSL
    wsrep_provider_options="socket.ssl_key=/etc/mysql/ssl/server-key.pem; \
    socket.ssl_cert=/etc/mysql/ssl/server-cert.pem; \
    socket.ssl_ca=/etc/mysql/ssl/ca-cert.pem; \
    socket.ssl_cipher=AES128-SHA"
    
    # SST with mysqldump or mariabackup
    wsrep_sst_method=rsync  # or use xtrabackup / mariabackup with ssl
    

    ⚠ If using mariabackup SST, you need to configure SSL for that too (wsrep_sst_auth, wsrep_sst_ssl_* options).


    4️⃣ Restart MariaDB and Bootstrap Galera

    On the first node (cluster founder):

    galera_new_cluster
    

    On the second node:

    systemctl start mariadb
    

    5️⃣ Test SSL Connection

    From CLI:

    mysql -u root --ssl-ca=/etc/mysql/ssl/ca-cert.pem \
                   --ssl-cert=/etc/mysql/ssl/server-cert.pem \
                   --ssl-key=/etc/mysql/ssl/server-key.pem \
                   -h 192.168.1.101
    
    # Inside MariaDB:
    SHOW STATUS LIKE 'Ssl_%';
    

    You should see:

    Ssl_cipher	AES128-SHA
    Ssl_version	TLSv1.2
    

    Also, monitor Galera cluster syncing correctly:

    SHOW STATUS LIKE 'wsrep_%';
    

    ✅ Done

    You now have SSL-secured:

    • Client connections to MariaDB (require_secure_transport=ON).
    • Replication traffic between Galera nodes using SSL.

    🛡️ Tips

    • For production, use a proper CA infrastructure or at least stronger cert management.
    • Manage certificates with tools like cfssl, vault, or step-ca if complexity grows.
    • Rotate certificates before 10 years!

    Let me know if you need help automating this with Ansible/Shell or using XtraBackup with SSL SST.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.