MariaDB TLS and Troubleshooting on AlmaLinux 10: SSL Connections, Certificates and Common Errors

MySQL/MariaDB on AlmaLinux series, part 7 (final part). Part 1 installed MariaDB, part 2 covered users and remote access, part 3 backups, part 4 GTID replication, part 5 built the three-node Galera cluster and part 6, performance tuning, made it fast. This part encrypts every connection to the cluster and then walks through the errors you will actually meet when running MariaDB.

Out of the box, MariaDB 10.11 on AlmaLinux 10 sends queries, results and password handshakes across the network in clear text: have_ssl is DISABLED until you give the server a certificate. This guide builds a small private CA with openssl, issues server certificates with proper subject alternative names (SANs) and a client certificate, turns on MariaDB TLS with ssl_cert, ssl_key and ssl_ca, forces encryption per account (REQUIRE SSL, REQUIRE X509) or server-wide (require_secure_transport), configures clients to verify the server, encrypts Galera replication and SST traffic, and covers HAProxy, SELinux and certificate rotation. The second half is a troubleshooting reference for the common MariaDB errors: 1045, 2002/2003, 1040, 1205, 1213, 2013, 1146, InnoDB start failures, a full disk, SELinux denials, Galera 1047 and TLS certificate errors.

Every error message quoted below was reproduced on MariaDB 10.11.18 using the AlmaLinux 10 mariadb client binary. Option names and behaviour were checked against the MariaDB documentation.

Applies to: AlmaLinux 10.x with MariaDB 10.11 from AppStream (currently 10.11.18) and OpenSSL 3.5. The same steps work on RHEL 10, Rocky Linux 10 and Oracle Linux 10. Differences in MariaDB 11.4 and later (the mariadb11.8 stream) and in MySQL 8.4 are called out where they matter.

Lab layout

HostIPRole
db1.example.lan192.168.1.40Galera node (part 5), current writer behind HAProxy
db2.example.lan192.168.1.41Galera node
db3.example.lan192.168.1.42Galera node
db.example.lan192.168.1.30HAProxy write endpoint (part 5)
app1.example.lan192.168.1.50Application server and test client (part 2)

The accounts are the ones created in part 2: shop_app@'192.168.1.%', report@'192.168.1.60' and shop_migrate@'192.168.1.50', all working on the shop database. If you run a single server, ignore the Galera and HAProxy sections; everything else applies as written.

Prerequisites

  • MariaDB installed as in part 1, with root reaching the server through the Unix socket (sudo mariadb).
  • Remote access and the dbclients firewalld zone from part 2. For the Galera sections, the cluster from part 5 with all three nodes Synced.
  • Working name resolution for db1, db2, db3 and db.example.lan on the clients (DNS or /etc/hosts). Clients check the name they connect to against the certificate.
  • The openssl package (installed by default) and time in sync on every host (chronyc tracking). A clock that is off can make a brand-new certificate look “not yet valid”.
  • A maintenance window for one rolling restart. Turning TLS on for the first time needs a restart; later certificate changes do not.

How MariaDB TLS works (and the 10.11 client defaults)

  • TLS is negotiated inside the MariaDB protocol: the client connects to port 3306, reads the server greeting and then asks to switch to TLS. There is no separate TLS port, and HAProxy or a load balancer cannot terminate it like HTTPS.
  • The server needs a certificate (ssl_cert), its private key (ssl_key) and, to check client certificates, the CA (ssl_ca). The variables keep the ssl_ prefix, but only TLS 1.2 and 1.3 are enabled by default in 10.11 (tls_version = TLSv1.2,TLSv1.3).
  • The MariaDB 10.11 client (Connector/C 3.3 inside the mariadb package) tries TLS by default, but ssl-verify-server-cert is off. Tested behaviour: against a server without TLS it silently connects in clear text, and that still happens when you pass only --ssl-ca. Only --ssl-verify-server-cert makes TLS mandatory and checks the certificate and host name.
  • MariaDB 11.4 and later behave differently: the server generates a self-signed certificate in memory at startup if none is configured, and the 11.4+ client verifies the server certificate by default. Applications linked against the system mariadb-connector-c 3.4 library on AlmaLinux 10 (used by Python mysqlclient, Perl DBD::MariaDB and other C-based drivers) also turn on TLS and require a verified server certificate on non-local connections. Give those clients your CA file.

Step 1: Check the current state

sudo mariadb -e "SELECT VERSION();
 SHOW GLOBAL VARIABLES WHERE Variable_name IN
 ('have_ssl','have_openssl','version_ssl_library','tls_version','require_secure_transport');"

On a node that has never had TLS configured you will see have_ssl = DISABLED, have_openssl = YES, version_ssl_library = OpenSSL 3.5.x, tls_version = TLSv1.2,TLSv1.3 and require_secure_transport = OFF. From app1, the client status confirms the connection is plain text:

mariadb -h db1.example.lan -u shop_app -p shop -e "status" | grep SSL
SSL:                    Not in use

Step 2: Create a private CA

A private CA keeps things simple: every node and client trusts one CA certificate, and you can issue or replace node certificates without touching the clients. Do the CA work as root in a directory only root can read. Here that is /root/db-ca on db1; an admin workstation that is not a database server is better. The CA key never leaves this directory. Node keys and certificates are copied out; ca.key is not.

sudo -i
install -d -m 700 /root/db-ca && cd /root/db-ca

# CA private key (4096-bit RSA) and a 10-year CA certificate
openssl genpkey -quiet -algorithm RSA -pkeyopt rsa_keygen_bits:4096 -out ca.key
chmod 600 ca.key
openssl req -x509 -new -key ca.key -sha256 -days 3650 \
  -subj "/O=Example Lab/CN=Example Lab MariaDB CA" \
  -addext "basicConstraints=critical,CA:TRUE" \
  -addext "keyUsage=critical,keyCertSign,cRLSign" \
  -out db-ca.crt

openssl x509 -in db-ca.crt -noout -subject -enddate

RSA 2048/4096 with SHA-256 signatures is accepted by the AlmaLinux 10 DEFAULT crypto policy. Do not use SHA-1 or keys shorter than 2048 bits: the client library uses the system OpenSSL and rejects them.

Step 3: Issue server certificates with SANs

Clients check the host name they connected to against the certificate’s subject alternative names; the CN is not enough. Each node certificate lists:

  • the node’s own names and IP (db1.example.lan, db1, 192.168.1.40),
  • the HAProxy name and IP (db.example.lan, 192.168.1.30), because HAProxy passes TLS through and the client receives whichever node’s certificate is behind it,
  • localhost and 127.0.0.1. The 10.11 client also negotiates TLS over the Unix socket, and once verification is on (step 6) it checks the certificate against localhost. Without that SAN, sudo mariadb on the node fails with a validation error.

The extended key usage includes both serverAuth and clientAuth, because Galera nodes act as both server and client when they talk to each other (step 9).

cd /root/db-ca
declare -A IP=([db1]=192.168.1.40 [db2]=192.168.1.41 [db3]=192.168.1.42)
for n in db1 db2 db3; do
  openssl genpkey -quiet -algorithm RSA -pkeyopt rsa_keygen_bits:2048 -out $n.key
  openssl req -new -key $n.key -subj "/O=Example Lab/CN=$n.example.lan" -out $n.csr
  cat > $n.ext <<EOF
basicConstraints = critical, CA:FALSE
keyUsage = critical, digitalSignature, keyEncipherment
extendedKeyUsage = serverAuth, clientAuth
subjectAltName = DNS:$n.example.lan, DNS:$n, DNS:db.example.lan, DNS:localhost, IP:${IP[$n]}, IP:192.168.1.30, IP:127.0.0.1
EOF
  openssl x509 -req -in $n.csr -CA db-ca.crt -CAkey ca.key -CAcreateserial \
    -days 825 -sha256 -extfile $n.ext -out $n.crt
done

Check the result before going further:

openssl verify -CAfile db-ca.crt db1.crt db2.crt db3.crt
db1.crt: OK
db2.crt: OK
db3.crt: OK

openssl x509 -in db2.crt -noout -subject -enddate -ext subjectAltName,extendedKeyUsage
subject=O=Example Lab, CN=db2.example.lan
notAfter=Feb 17 09:22:37 2029 GMT
X509v3 Extended Key Usage:
    TLS Web Server Authentication, TLS Web Client Authentication
X509v3 Subject Alternative Name:
    DNS:db2.example.lan, DNS:db2, DNS:db.example.lan, DNS:localhost, IP Address:192.168.1.41, IP Address:192.168.1.30, IP Address:127.0.0.1

# the key really belongs to the certificate (both hashes must match)
openssl pkey -in db2.key -pubout | openssl sha256
openssl x509 -in db2.crt -noout -pubkey | openssl sha256

825 days keeps node certificates comfortably inside what most clients accept, and gives you a reason to practise rotation (step 11).

Step 4: Issue a client certificate

A client certificate lets an account prove who it is with something stronger than a password (REQUIRE X509 / REQUIRE SUBJECT). The private key should be created on the client and never travel: generate the key and CSR on app1, copy only the CSR to the CA, sign it there and copy the certificate back.

# on app1, as the user that runs the migration job
install -d -m 700 ~/.mariadb-tls && cd ~/.mariadb-tls
openssl genpkey -quiet -algorithm RSA -pkeyopt rsa_keygen_bits:2048 -out shop_migrate.key
chmod 600 shop_migrate.key
openssl req -new -key shop_migrate.key -subj "/O=Example Lab/CN=shop_migrate" -out shop_migrate.csr
scp shop_migrate.csr admin@db1.example.lan:/tmp/
# on the CA host (db1), as root in /root/db-ca
cp /tmp/shop_migrate.csr . && rm -f /tmp/shop_migrate.csr
openssl req -in shop_migrate.csr -noout -subject      # check what you are signing
printf 'basicConstraints = critical, CA:FALSE\nkeyUsage = critical, digitalSignature, keyEncipherment\nextendedKeyUsage = clientAuth\n' > client.ext
openssl x509 -req -in shop_migrate.csr -CA db-ca.crt -CAkey ca.key -CAserial db-ca.srl \
  -days 825 -sha256 -extfile client.ext -out shop_migrate.crt
openssl x509 -in shop_migrate.crt -noout -subject -issuer -nameopt compat
subject=/O=Example Lab/CN=shop_migrate
issuer=/O=Example Lab/CN=Example Lab MariaDB CA

Note the -nameopt compat output: that slash-separated form is exactly what REQUIRE SUBJECT and REQUIRE ISSUER expect in step 7. Copy shop_migrate.crt and db-ca.crt back to ~/.mariadb-tls/ on app1.

Step 5: Install the certificates on each node

Part 5 already used the standard AlmaLinux locations for the Galera example, so this guide keeps them: certificates in /etc/pki/tls/certs, keys in /etc/pki/tls/private. Copy db-ca.crt, dbN.crt and dbN.key to each node (scp to an admin account, or your configuration management), then install them with the right owner, mode and SELinux label:

N=db1          # db2 on db2, db3 on db3
SRC=/root      # where you copied the files (/root/db-ca on db1)

sudo install -o root -g root  -m 644 $SRC/db-ca.crt /etc/pki/tls/certs/db-ca.crt
sudo install -o root -g root  -m 644 $SRC/$N.crt    /etc/pki/tls/certs/$N.crt
sudo install -o root -g mysql -m 640 $SRC/$N.key    /etc/pki/tls/private/$N.key
sudo restorecon -v /etc/pki/tls/certs/db-ca.crt /etc/pki/tls/certs/$N.crt /etc/pki/tls/private/$N.key
[ "$SRC" != /root/db-ca ] && sudo shred -u $SRC/$N.key     # remove the copied key

sudo ls -lZ /etc/pki/tls/certs/db-ca.crt /etc/pki/tls/certs/$N.crt /etc/pki/tls/private/$N.key

Expected: the key is -rw-r----- root mysql ... cert_t, the certificates -rw-r--r-- root root ... cert_t. Three details matter here:

  • Ownership. mariadbd runs as mysql. A key left at the usual root:root 600 cannot be read, and MariaDB 10.11 then refuses to start (it does not fall back to running without TLS). See the troubleshooting table.
  • SELinux. The mysqld_t domain may read generic certificates (cert_t) and its own config (mysqld_etc_t, everything under /etc/my.cnf.d). Use install or cp, not mv, to put files in place: mv keeps the source label (for example admin_home_t from /root), which causes an AVC denial. restorecon fixes it either way.
  • Custom locations. If the files must live elsewhere, for example /srv/dbcerts, label the directory: sudo semanage fcontext -a -t cert_t "/srv/dbcerts(/.*)?" then sudo restorecon -Rv /srv/dbcerts.

Step 6: Enable TLS on the server (zz-tls.cnf)

Settings go in their own drop-in, read after zz-galera.cnf and zz-tuning.cnf. The unquoted heredoc fills in the node name:

N=db1   # db2 / db3 on the other nodes
sudo tee /etc/my.cnf.d/zz-tls.cnf <<EOF
[mariadb]
# --- client/server TLS (part 7) ---
ssl_ca      = /etc/pki/tls/certs/db-ca.crt
ssl_cert    = /etc/pki/tls/certs/$N.crt
ssl_key     = /etc/pki/tls/private/$N.key
tls_version = TLSv1.2,TLSv1.3
# require_secure_transport = ON     # see step 8 before enabling

[sst]
# encrypt Galera state snapshot transfers (step 9)
ssl-mode = VERIFY_CA
EOF
sudo chown root:mysql /etc/my.cnf.d/zz-tls.cnf
sudo chmod 640 /etc/my.cnf.d/zz-tls.cnf

The ssl_* paths are read at startup, so the first activation needs a restart. On the Galera cluster restart one node at a time and wait for Synced before moving on: db3, then db2, then db1 (the writer last, so HAProxy fails over only once). On a standalone server just restart it.

sudo systemctl restart mariadb
sudo mariadb -e "SHOW GLOBAL STATUS LIKE 'wsrep_local_state_comment';"    # Synced
sudo mariadb -e "SHOW GLOBAL VARIABLES LIKE 'have_ssl';"                 # YES
sudo grep -iE 'ssl|tls' /var/log/mariadb/mariadb.log | tail -5            # no errors

have_ssl = YES means the server loaded the certificate. If MariaDB does not come back, read /var/log/mariadb/mariadb.log: a Failed to setup SSL line names the file it could not use.

Step 7: Configure clients to verify the server

Server-side TLS on its own only protects against passive sniffing. To defeat a man-in-the-middle, the client must check the certificate against your CA and the host name. Put that in a client drop-in on every node and on app1 (copy db-ca.crt to the same path on app1 first). Use the [client-mariadb] group: the MySQL 8 client does not know ssl-verify-server-cert and would stop with an “unknown option” error if it found it in [client].

sudo install -o root -g root -m 644 db-ca.crt /etc/pki/tls/certs/db-ca.crt   # app1 only

sudo tee /etc/my.cnf.d/zz-client-tls.cnf <<'EOF'
[client-mariadb]
ssl-ca = /etc/pki/tls/certs/db-ca.crt
ssl-verify-server-cert
EOF
sudo chmod 644 /etc/my.cnf.d/zz-client-tls.cnf

This file must be world-readable, which is why it is separate from the server’s zz-tls.cnf (mode 640). Clients silently skip option files they cannot read. Now connect from app1 and look at the session:

mariadb -h db1.example.lan -u shop_app -p shop
MariaDB [shop]> \s
--------------
mariadb  Ver 15.1 Distrib 10.11.18-MariaDB, for Linux (x86_64) using  EditLine wrapper

Connection id:          41
Current database:       shop
Current user:           shop_app@192.168.1.50
SSL:                    Cipher in use is TLS_AES_256_GCM_SHA384
...
Connection:             db1.example.lan via TCP/IP

MariaDB [shop]> SHOW SESSION STATUS WHERE Variable_name IN
    -> ('Ssl_version','Ssl_cipher','Ssl_server_not_before','Ssl_server_not_after');
+-----------------------+--------------------------+
| Variable_name         | Value                    |
+-----------------------+--------------------------+
| Ssl_cipher            | TLS_AES_256_GCM_SHA384   |
| Ssl_server_not_after  | Feb 17 09:22:37 2029 GMT |
| Ssl_server_not_before | Nov 15 09:22:37 2026 GMT |
| Ssl_version           | TLSv1.3                  |
+-----------------------+--------------------------+

An empty Ssl_version or SSL: Not in use means the session is not encrypted. To check exactly what the server presents without logging in, OpenSSL can speak the MySQL STARTTLS handshake:

openssl s_client -starttls mysql -connect db1.example.lan:3306 \
  -CAfile /etc/pki/tls/certs/db-ca.crt -verify_hostname db1.example.lan </dev/null 2>&1 \
  | grep -E 'subject=|issuer=|Protocol|Verify return'
subject=O=Example Lab, CN=db1.example.lan
issuer=O=Example Lab, CN=Example Lab MariaDB CA
Protocol: TLSv1.3
Verify return code: 0 (ok)

For two-way TLS, the migration job on app1 also presents its certificate. Keep these options in a per-user file such as ~/.my.cnf (mode 600) so other accounts do not use them:

cat > ~/.my.cnf <<EOF
[client-mariadb]
ssl-cert = $HOME/.mariadb-tls/shop_migrate.crt
ssl-key  = $HOME/.mariadb-tls/shop_migrate.key
EOF
chmod 600 ~/.my.cnf
mariadb -h db1.example.lan -u shop_migrate -p shop -e "SELECT CURRENT_USER(); SHOW STATUS LIKE 'Ssl_version';"

Application drivers have their own option names (for example PDO::MYSQL_ATTR_SSL_CA and PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT in PHP, ssl_ca plus ssl_verify_cert in Python mysqlclient, sslMode=verify-full with serverSslCert in MariaDB Connector/J). They all need the same CA file.

Step 8: Require encrypted connections

Per account: REQUIRE SSL, X509, SUBJECT and ISSUER

Account-level requirements are the precise tool: they apply to the accounts that cross the network and leave local admin and health-check accounts alone. Run these on any node; Galera replicates them.

sudo mariadb <<'EOF'
-- encryption required, no client certificate needed
ALTER USER 'shop_app'@'192.168.1.%' REQUIRE SSL;
ALTER USER 'report'@'192.168.1.60'  REQUIRE SSL;

-- a client certificate from our CA with exactly this subject (two-way TLS)
ALTER USER 'shop_migrate'@'192.168.1.50'
  REQUIRE SUBJECT '/O=Example Lab/CN=shop_migrate'
  AND ISSUER '/O=Example Lab/CN=Example Lab MariaDB CA';

SELECT user, host, ssl_type, x509_subject FROM mysql.user WHERE ssl_type <> '';
EOF
ClauseWhat the client must dossl_type
REQUIRE NONENothing (default)(empty)
REQUIRE SSLUse TLSANY
REQUIRE X509Use TLS and present any valid certificate signed by a CA in the server’s ssl_caX509
REQUIRE SUBJECT '...' AND ISSUER '...'Present a valid certificate with exactly that subject and issuerSPECIFIED

A client that does not meet the requirement gets a plain ERROR 1045 (28000): Access denied, the same message as a wrong password. If an account that used to work suddenly gets 1045 after this step, check its TLS settings first. Undo a requirement with ALTER USER ... REQUIRE NONE;.

Server-wide: require_secure_transport

require_secure_transport = ON (MariaDB 10.5.2 and later, dynamic) rejects every TCP connection that is not encrypted, for every account. Unix socket connections still count as secure, so sudo mariadb keeps working. In 10.11 a rejected client also just sees ERROR 1045 (28000): Access denied; MySQL returns a clearer error 3159.

sudo mariadb -e "SET GLOBAL require_secure_transport = ON;"   # takes effect for new connections
# then make it permanent: uncomment require_secure_transport = ON in zz-tls.cnf

Before you enable it on the Galera cluster, fix the HAProxy health check: option mysql-check user haproxy_check from part 5 logs in over plain TCP, so every backend would be marked DOWN. Step 10 replaces it with a check that does not use the MariaDB port. If you would rather keep the old check, stay with per-account requirements; haproxy_check has no privileges and nothing to protect.

Step 9: Encrypt Galera replication and SST traffic

Client TLS does not cover the cluster’s own traffic. Galera replication on 4567 (and IST on 4568) uses the provider’s TLS settings in wsrep_provider_options; full state snapshot transfers on 4444 are configured separately in the [sst] group. Both are unencrypted by default.

Replication traffic (socket.ssl_*)

wsrep_provider_options is one string, and the last definition read wins. Edit the existing line in zz-galera.cnf instead of adding a second one in zz-tls.cnf, or you will silently lose gcache.size=1G. Encrypted and unencrypted nodes cannot talk to each other unless socket.dynamic=true (default false, not dynamic), so there are two ways to switch:

  • Full cluster restart: stop all three nodes, add the socket.ssl_* options everywhere, bootstrap the most advanced node with galera_new_cluster and start the others, exactly as in part 5.
  • Rolling, no downtime (three rounds): round 1, add socket.dynamic=true and restart node by node; round 2, add the socket.ssl_* options and restart node by node; round 3, remove socket.dynamic and restart node by node so plain-text peers are refused again.

The final line on db1 (use each node’s own certificate and key on db2 and db3):

wsrep_provider_options = "gcache.size=1G;socket.ssl_cert=/etc/pki/tls/certs/db1.crt;socket.ssl_key=/etc/pki/tls/private/db1.key;socket.ssl_ca=/etc/pki/tls/certs/db-ca.crt"

Setting any socket.ssl_* path implicitly turns on socket.ssl. Verify on every node after its restart:

sudo mariadb -e "SHOW GLOBAL VARIABLES LIKE 'wsrep_provider_options'\G" | tr ';' '\n' | grep -E 'socket\.(ssl|dynamic)'
sudo grep -E 'listening at|ssl://' /var/log/mariadb/mariadb.log | tail -5     # endpoints now ssl://
sudo mariadb -e "SHOW GLOBAL STATUS WHERE Variable_name IN ('wsrep_cluster_size','wsrep_local_state_comment');"

A node that cannot complete the TLS handshake stays out of the cluster and logs SSL handshake errors. The usual causes are a different CA file on that node, a key the mysql user cannot read, or a certificate without clientAuth in its extended key usage. If you use the Galera arbitrator (garbd), give it the same socket.ssl_* options with a certificate from the same CA.

State snapshot transfers (mariabackup SST)

The [sst] section added to zz-tls.cnf in step 6 is all that is needed with the mariabackup method. With ssl-mode = VERIFY_CA the SST script reuses the server’s ssl_ca, ssl_cert and ssl_key, switches socat to OpenSSL (encrypt=3) and verifies the peer against your CA. VERIFY_IDENTITY also checks the peer’s address against the certificate. That works with the SANs above but adds little inside a private CA. The setting must be on every node, because the donor and the joiner both need it. Check that socat was built with OpenSSL, then confirm on the next SST:

socat -V | grep -i openssl          # "#define WITH_OPENSSL 1"
sudo grep 'SSL configuration' /var/log/mariadb/mariadb.log | tail -2
WSREP_SST: [INFO] SSL configuration: CA='/etc/pki/tls/certs/db-ca.crt', CAPATH='', CERT='/etc/pki/tls/certs/db3.crt', KEY='/etc/pki/tls/private/db3.key', MODE='VERIFY_CA', encrypt='3'

To test it on purpose, force a full SST on db3: stop it, move /var/lib/mysql/grastate.dat to /root, and start it again. db3 then copies the whole data set from a donor, so do this outside business hours. The older encrypt=2/3/4 with tca/tcert/tkey options still work but override ssl-mode; do not mix the two styles.

MariaDB Enterprise Server has an extra wsrep_ssl_mode variable (SERVER/SERVER_X509) that reuses the server certificate for replication. The community 10.11 packages on AlmaLinux use the socket.ssl_* provider options shown here.

Step 10: HAProxy and TLS

Because MariaDB negotiates TLS inside its own protocol, HAProxy in mode tcp simply passes the encrypted stream through. Clients connect to db.example.lan and verify the backend’s certificate end to end, which is why every node certificate carries db.example.lan and 192.168.1.30. Things to keep in mind:

  • Client certificates work through the proxy, because the TLS session is between the client and the node. REQUIRE X509 accounts are fine.
  • The server sees HAProxy’s address. Through the proxy, every client appears as 192.168.1.30, so shop_app@'192.168.1.%' matches but shop_migrate@'192.168.1.50' does not. Either create accounts for 192.168.1.30 or connect those clients straight to a node. (MariaDB can accept the PROXY protocol from HAProxy with proxy_protocol_networks and send-proxy-v2 on the server lines; that is a separate, all-or-nothing change for every connection through the proxy.)
  • Idle timeouts. The stock haproxy.cfg uses timeout client 1m and timeout server 1m, so idle pooled connections are cut after a minute and applications report 2013/2006 errors. Raise them for the database listener.
  • Health check. With require_secure_transport = ON, the plaintext mysql-check login fails. An HTTP check on a separate port that asks the node over its local socket is both TLS-proof and better: it reports Synced, not just “port open”.

A minimal socket-activated check on each node. It answers HTTP 200 when wsrep_local_state is 4 (Synced) and 503 otherwise:

sudo useradd -r -s /sbin/nologin galerachk
sudo mariadb -e "CREATE USER 'galerachk'@'localhost' IDENTIFIED VIA unix_socket;"   # once; replicated

sudo tee /usr/local/sbin/galera-check <<'EOF'
#!/bin/bash
state=$(mariadb --no-defaults -u galerachk -N -B \
  -e "SHOW GLOBAL STATUS LIKE 'wsrep_local_state'" 2>/dev/null | awk '{print $2}')
if [ "$state" = "4" ]; then
  printf 'HTTP/1.1 200 OK\r\nContent-Type: text/plain\r\nConnection: close\r\n\r\nsynced\r\n'
else
  printf 'HTTP/1.1 503 Service Unavailable\r\nContent-Type: text/plain\r\nConnection: close\r\n\r\nnot synced\r\n'
fi
EOF
sudo chmod 755 /usr/local/sbin/galera-check

sudo tee /etc/systemd/system/galera-check.socket <<'EOF'
[Unit]
Description=Galera health check for HAProxy

[Socket]
ListenStream=9200
Accept=yes

[Install]
WantedBy=sockets.target
EOF

sudo tee /etc/systemd/system/galera-check@.service <<'EOF'
[Unit]
Description=Galera health check for HAProxy

[Service]
User=galerachk
ExecStart=/usr/local/sbin/galera-check
StandardInput=socket
StandardOutput=socket
StandardError=journal
EOF

sudo systemctl daemon-reload
sudo systemctl enable --now galera-check.socket
sudo firewall-cmd --permanent --zone=dbclients --add-port=9200/tcp && sudo firewall-cmd --reload
curl -s http://192.168.1.40:9200/        # from the HAProxy host: "synced"

Then switch the listener on the HAProxy host (the haproxy_connect_any boolean from part 5 already lets HAProxy reach port 9200):

listen galera_write
    bind 192.168.1.30:3306
    mode tcp
    option tcpka
    timeout client 8h
    timeout server 8h
    option httpchk
    http-check send meth GET uri /
    http-check expect status 200
    server db1 192.168.1.40:3306 check port 9200 inter 2s fall 3 rise 2
    server db2 192.168.1.41:3306 check port 9200 inter 2s fall 3 rise 2 backup
    server db3 192.168.1.42:3306 check port 9200 inter 2s fall 3 rise 2 backup
sudo haproxy -c -f /etc/haproxy/haproxy.cfg && sudo systemctl reload haproxy
mariadb -h db.example.lan -u shop_app -p shop -e "SELECT @@hostname; SHOW STATUS LIKE 'Ssl_version';"

Once the new check is green on all three backends you can set require_secure_transport = ON (step 8) and drop the old haproxy_check account.

Step 11: Rotate certificates without a restart

FLUSH SSL (MariaDB 10.4 and later, needs the RELOAD privilege) re-reads the files at the paths already configured in ssl_cert, ssl_key and ssl_ca. New connections get the new certificate; established sessions keep theirs. According to the MariaDB Galera documentation it also reloads the provider’s TLS context when wsrep is active; SET GLOBAL wsrep_provider_options='socket.ssl_reload=1'; reloads only the Galera side. Paths cannot be changed at runtime, so replace the files in place:

# new db1 certificate (and optionally key) issued from the same CA, copied to /root
N=db1
openssl verify -CAfile /etc/pki/tls/certs/db-ca.crt /root/$N.crt          # must say OK
sudo install -o root -g mysql -m 640 /root/$N.key /etc/pki/tls/private/$N.key.new
sudo install -o root -g root  -m 644 /root/$N.crt /etc/pki/tls/certs/$N.crt.new
sudo mv -f /etc/pki/tls/private/$N.key.new /etc/pki/tls/private/$N.key
sudo mv -f /etc/pki/tls/certs/$N.crt.new   /etc/pki/tls/certs/$N.crt
sudo restorecon -v /etc/pki/tls/private/$N.key /etc/pki/tls/certs/$N.crt

sudo mariadb -e "FLUSH SSL; SHOW GLOBAL STATUS LIKE 'Ssl_server_not_after';"

If the new key and certificate do not match, FLUSH SSL fails with ERROR 1105 (HY000): Failed to refresh SSL, error: Private key does not match the certificate public key and the server keeps using the old certificate. Fix the files and run it again. Never restart a node in that state: at startup the same mismatch stops MariaDB completely.

Replacing the CA takes two passes. First put the old and new CA certificates in one bundle (cat old-ca.crt new-ca.crt > db-ca.crt) on every node and client and run FLUSH SSL. Then issue node and client certificates from the new CA and rotate them as above. Finally remove the old CA from the bundle.

Watch the expiry dates. Ssl_server_not_after shows what the server is actually serving. A daily cron job warns through syslog 30 days ahead:

sudo tee /etc/cron.d/db-cert-check <<'EOF'
0 7 * * * root for f in /etc/pki/tls/certs/db[0-9]*.crt /etc/pki/tls/certs/db-ca.crt; do openssl x509 -in "$f" -noout -checkend 2592000 >/dev/null || logger -p daemon.warning -t db-cert "$f expires within 30 days"; done
EOF

Verification checklist

# server side, on every node
sudo mariadb -e "SHOW GLOBAL VARIABLES WHERE Variable_name IN ('have_ssl','tls_version','require_secure_transport');"
sudo mariadb -e "SHOW GLOBAL STATUS LIKE 'Ssl_server_not_after';"
sudo mariadb -e "SELECT user, host, ssl_type FROM mysql.user WHERE host NOT IN ('localhost','');"

# from app1: verified TLS to each node and through HAProxy
for h in db1 db2 db3 db; do
  mariadb -h $h.example.lan -u shop_app -p"$DBPASS" -N -e "SELECT @@hostname, (SELECT VARIABLE_VALUE FROM information_schema.SESSION_STATUS WHERE VARIABLE_NAME='SSL_VERSION');"
done

# plaintext is refused for REQUIRE SSL accounts (expect ERROR 1045)
mariadb --no-defaults --skip-ssl -h db1.example.lan -u shop_app -p -e "SELECT 1"

# Galera replication encrypted and cluster healthy
sudo grep -c 'ssl://' /var/log/mariadb/mariadb.log
sudo mariadb -e "SHOW GLOBAL STATUS LIKE 'wsrep_cluster_size';"

The loop reads the password from a DBPASS variable you set in the shell; do not leave it in your history on a shared host.

Troubleshooting TLS errors

All messages below were reproduced with the AlmaLinux 10 MariaDB 10.11 client. Client errors appear as ERROR 2026 (HY000): TLS/SSL error: ...; the server usually logs only Aborted connection ... (This connection closed normally without authentication) for them.

ErrorMeaning and fix
TLS/SSL error: unable to get local issuer certificateVerification is on but the client has no CA file, or the wrong one. Set ssl-ca to db-ca.crt in [client-mariadb] or pass --ssl-ca.
TLS/SSL error: self-signed certificateThe server presents a self-signed certificate, not one from your CA: MySQL’s or MariaDB 11.4’s auto-generated certificate, or a leftover test certificate. Check ssl_cert on the server and that have_ssl uses your files.
TLS/SSL error: self-signed certificate in certificate chainThe server sends a chain ending in a root your ssl-ca file does not contain, for example a new CA that clients have not received yet. Distribute the right CA (or the old+new bundle).
TLS/SSL error: Validation of SSL server certificate failedHost name or IP mismatch: the name you connected to is not in the certificate’s SANs. Check with openssl x509 -noout -ext subjectAltName. Common cases: connecting by an IP that is not listed, the HAProxy name missing, or localhost missing (socket connections with verification on).
TLS/SSL error: certificate has expiredRenew and rotate (step 11). Also check the clock with chronyc tracking.
TLS/SSL error: tlsv1 alert unknown caThe server rejected the client certificate: it is not signed by a CA in the server’s ssl_ca.
TLS/SSL error: key values mismatchThe client’s ssl-key does not belong to its ssl-cert. Compare the public key hashes (step 3).
TLS/SSL error: (13)The client cannot read one of its files (errno 13, permission denied), often a key in another user’s home or a CA with mode 600.
TLS/SSL error: SSL is required, but the server does not support itThe client requires TLS (ssl-verify-server-cert) but the server has have_ssl = DISABLED. Configure the server (step 6).
Server will not start: [ERROR] Failed to setup SSL / SSL error: Unable to get private key / [ERROR] Abortingmysql cannot read the key (ownership, mode or SELinux label) or the path is wrong. Fix with chown root:mysql, chmod 640 and restorecon; check ausearch -m AVC -ts recent.
ERROR 1105 (HY000): Failed to refresh SSL, error: Private key does not match the certificate public keyFLUSH SSL found a mismatched pair. The old context stays active; install the matching key and certificate and run it again.
ERROR 1045 (28000): Access denied right after adding REQUIREThe client connected without TLS or without the required certificate. Check SHOW CREATE USER and the client’s TLS options.

Troubleshooting common MariaDB errors

Start every investigation in the same three places: the client’s exact error number, the server log (/var/log/mariadb/mariadb.log on AlmaLinux) and journalctl -u mariadb for systemd-level failures.

ERROR 1045 (28000): Access denied for user

ERROR 1045 (28000): Access denied for user 'shop_app'@'192.168.1.30' (using password: YES)

The part after the @ is the address the server saw, and that is usually the clue. Here it is HAProxy’s address, not app1’s. MariaDB matches the most specific user@host row, and with skip-name-resolve (part 1) host names in accounts never match.

sudo mariadb -e "SELECT user, host, plugin, ssl_type, password_expired FROM mysql.user WHERE user='shop_app';"
sudo mariadb -e "SHOW CREATE USER 'shop_app'@'192.168.1.%';"
sudo grep 'Access denied' /var/log/mariadb/mariadb.log | tail -5
  • Wrong password or no account for that host: ALTER USER ... IDENTIFIED BY ..., or create the account for the right host.
  • Anonymous ''@'localhost' rows (if mariadb-secure-installation was never run) can win over 'user'@'%' for local TCP connections. Drop them.
  • TLS requirement not met (step 8), or the account is locked (ACCOUNT LOCK in SHOW CREATE USER).
  • ERROR 1698 (28000): Access denied for user 'root'@'localhost' is the unix_socket plugin: use sudo mariadb, not mariadb -u root -p.

ERROR 2002 / 2003: Can’t connect

ERROR 2002 (HY000): Can't connect to local server through socket '/var/lib/mysql/mysql.sock' (2)
ERROR 2002 (HY000): Can't connect to server on 'db1.example.lan' (113)
ERROR 2003 (HY000): Can't connect to MySQL server on 'db1.example.lan:3306' (113)    # MySQL client

The socket form (errno 2, no such file) means the server is not running or uses another socket path. The MariaDB 10.11 client reports TCP failures as 2002 too (“Can’t connect to server on”), while MySQL clients and many drivers use 2003. The number in parentheses is the OS errno: 113 no route to host, typically a firewalld reject; 111 connection refused, nothing listening; 110 timed out, a silent drop on the way. The 10.11 client can also report 115 for a refused port.

sudo systemctl status mariadb --no-pager
sudo ss -ltnp | grep 3306                        # listening on 0.0.0.0 or the node IP?
sudo mariadb -e "SELECT @@bind_address, @@port, @@socket, @@skip_networking;"
sudo firewall-cmd --get-active-zones
sudo firewall-cmd --zone=dbclients --list-all    # client subnet in sources, mysql service allowed?
nc -zv db1.example.lan 3306                       # from the client (nmap-ncat)

Allow the client’s source in the dbclients zone as in part 2 (the firewalld cheat sheet has the zone commands), set bind_address to the node IP or 0.0.0.0, and if the server runs on a non-standard port, label it for SELinux with semanage port -a -t mysqld_port_t -p tcp 3307.

ERROR 1040 (HY000): Too many connections

All max_connections slots are in use. MariaDB keeps one extra connection for an account with the CONNECTION ADMIN (or SUPER) privilege, so sudo mariadb usually still gets in:

sudo mariadb -e "SHOW GLOBAL STATUS WHERE Variable_name IN ('Threads_connected','Max_used_connections','Connection_errors_max_connections');"
sudo mariadb -e "SELECT user, SUBSTRING_INDEX(host,':',1) AS client, command, COUNT(*) n, MAX(time) max_s
  FROM information_schema.PROCESSLIST GROUP BY 1,2,3 ORDER BY n DESC LIMIT 10;"
sudo mariadb -e "SET GLOBAL max_connections = 300;"     # immediate relief; persist in zz-tuning.cnf

Hundreds of Sleep connections from one host point at an application pool that is too large or leaking. Fix the pool, then consider lowering wait_timeout. Memory sizing for more connections is in part 6. The related ERROR 1129: Host '192.168.1.30' is blocked because of many connection errors; unblock with 'mariadb-admin flush-hosts' appears when a host (often a load balancer health check) aborts more than max_connect_errors (100) handshakes in a row. Run sudo mariadb -e "FLUSH HOSTS" and fix the check.

ERROR 1205 (HY000): Lock wait timeout exceeded

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

A statement waited longer than innodb_lock_wait_timeout (50 s default) for a row lock held by another transaction, very often one that is idle and never committed. Find the blocker:

sudo mariadb -e "SELECT r.trx_mysql_thread_id AS waiting_thread, r.trx_query AS waiting_query,
  b.trx_mysql_thread_id AS blocking_thread, b.trx_started, b.trx_query AS blocking_query
  FROM information_schema.INNODB_LOCK_WAITS w
  JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id
  JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id\G"
sudo mariadb -e "SELECT trx_mysql_thread_id, trx_started, trx_state, trx_rows_locked
  FROM information_schema.INNODB_TRX ORDER BY trx_started LIMIT 5;"
# KILL <blocking_thread>;   -- only once you know what it is

A blocking query of NULL with an old trx_started is a client that opened a transaction and went quiet. Fix the code path (commit or roll back promptly) rather than raising the timeout.

ERROR 1213 (40001): Deadlock found

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Two transactions locked rows in opposite order; InnoDB rolled one back immediately. Occasional deadlocks are normal and the application should simply retry the transaction. On Galera, 1213 also signals a certification conflict when two nodes change the same row at the same time, which is one more reason to send writes to a single node through HAProxy. The last deadlock is in the InnoDB status; log all of them with innodb_print_all_deadlocks = ON:

sudo mariadb -e "SHOW ENGINE INNODB STATUS\G" | sed -n '/LATEST DETECTED DEADLOCK/,/WE ROLL BACK/p'
sudo mariadb -e "SET GLOBAL innodb_print_all_deadlocks = ON;"   # persist in zz-tuning.cnf

Fixes: touch tables and rows in the same order everywhere, keep transactions short, and index the columns in WHERE clauses so updates lock fewer rows.

ERROR 2013 / 2006: Lost connection or server has gone away

ERROR 2013 (HY000): Lost connection to server during query
ERROR 2006 (HY000): Server has gone away

The connection died under the client. Check in this order:

  • Packet too large. A row, BLOB or multi-row INSERT bigger than max_allowed_packet (16 MiB default in 10.11). The server log shows Aborted connection ... (Got a packet bigger than 'max_allowed_packet' bytes). Raise it on the server and in the client ([mariadb-dump] max_allowed_packet = 1G for dumps).
  • Idle timeout. wait_timeout (8 hours) or, more often, a proxy timeout such as HAProxy’s timeout client/server (step 10) closed an idle pooled connection.
  • Server restart or crash. Look for a fresh ready for connections in the log, and check dmesg for the OOM killer (Out of memory: Killed process ... (mariadbd)).
  • Galera. A node that drops out of the primary component closes or rejects client work (see 1047).
sudo mariadb -e "SELECT @@max_allowed_packet/1024/1024 AS max_packet_mb, @@wait_timeout, @@net_read_timeout, @@net_write_timeout;"
sudo grep -E "Aborted connection|ready for connections" /var/log/mariadb/mariadb.log | tail -5
sudo journalctl -k | grep -i 'out of memory' | tail -3

ERROR 1146 (42S02): Table doesn’t exist

ERROR 1146 (42S02): Table 'shop.nope' doesn't exist

Usually a typo, the wrong default database, or case: table names are case-sensitive on Linux (lower_case_table_names = 0), so Orders and orders are different tables. That catches applications moved from Windows or macOS. On Galera, a table created as MyISAM or Aria on one node does not exist on the others. If the log also shows InnoDB: Table ... does not exist in the InnoDB internal data dictionary, the .frm/.ibd files and the data dictionary disagree, typically after files were copied by hand. Restore the table from backup (part 3).

sudo mariadb -e "SELECT table_schema, table_name, engine FROM information_schema.TABLES WHERE table_name LIKE '%order%';"
sudo mariadb -e "SELECT @@lower_case_table_names;"

MariaDB will not start (InnoDB errors)

Read the end of the log; the first [ERROR] line is the cause, the rest is fallout (Plugin 'InnoDB' registration as a STORAGE ENGINE failed, Unknown/unsupported storage engine: InnoDB, Aborting):

sudo journalctl -u mariadb -b --no-pager | tail -20
sudo tail -n 60 /var/log/mariadb/mariadb.log
Log lineCause and fix
InnoDB: Operating system error number 13 in a file operation / The error means mariadbd does not have the access rights to the directory.Ownership or SELinux on the data directory: sudo chown -R mysql:mysql /var/lib/mysql and sudo restorecon -Rv /var/lib/mysql; for a custom datadir, label it mysqld_db_t with semanage fcontext.
InnoDB: Unable to lock ./ibdata1 error: 11Another mariadbd is using the same data directory (a manual start, a stuck old process). pgrep -a mariadbd, stop it cleanly.
Can't start server: Bind on TCP/IP port. Got error: 98 / Do you already have another server running on port: 3306 ?Port in use: sudo ss -ltnp | grep 3306.
Failed to setup SSLTLS files unreadable or mismatched (TLS table above).
unknown variable '...'Typo or removed option in a .cnf file; see part 6 for options removed in 10.6+.
Corruption, crash-recovery assertion, Database page corruptionHardware or filesystem damage. See forced recovery below; on Galera, simply rebuild the node with an SST from a healthy one.

Forced recovery is a last resort, and only for getting data out. Stop the server and copy the whole data directory first, then start with the lowest level that works:

sudo systemctl stop mariadb
sudo cp -a /var/lib/mysql /root/mysql-before-recovery
printf '[mariadb]\ninnodb_force_recovery = 1\n' | sudo tee /etc/my.cnf.d/zz-recovery.cnf
sudo systemctl start mariadb          # try 1, then 2, then 3; 4 and above can lose data
sudo mariadb-dump --all-databases --single-transaction --routines --events > /root/rescue.sql
sudo rm /etc/my.cnf.d/zz-recovery.cnf  # never leave it set

Then rebuild from the dump or from the last good backup (part 3).

Disk full

[Warning] mariadbd: Disk is full writing './mariadb-bin.000042' (Errcode: 28 "No space left on device"). Waiting for someone to free space... (Expect up to 60 secs delay for server to continue after freeing disk space)

MariaDB does not crash on a full disk; writes stop and wait until space is freed. Find what grew and free space safely:

df -h /var/lib/mysql /tmp
sudo du -sh /var/lib/mysql/* | sort -h | tail
sudo mariadb -e "SHOW BINARY LOGS;"
sudo mariadb -e "PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY;"   # never rm binlog files by hand

Usual suspects: binary logs (set binlog_expire_logs_seconds, part 5), a huge slow or general log (rotate it as in part 6), a forgotten backup in the data directory, a temporary table from a runaway query in /tmp, or an ibtmp1 that grew (it is recreated at restart). On Galera, one full node pauses replication for the whole cluster through flow control.

SELinux denials (AVC)

If something works with setenforce 0 and fails in enforcing mode, SELinux is the cause. Do not disable it; read the denial and fix the label or port:

sudo ausearch -m AVC -ts recent -c mariadbd
sudo ausearch -m AVC -ts recent | audit2why
Denial onFix
A non-standard data directory or log pathsemanage fcontext -a -t mysqld_db_t "/data/mysql(/.*)?" (or mysqld_log_t for logs), then restorecon -Rv
A non-standard portsemanage port -a -t mysqld_port_t -p tcp 3307 (part 5 did this for 4444/4567/4568)
Certificate or key filesrestorecon -v the files; they must be cert_t or mysqld_etc_t, not admin_home_t or user_home_t
Outbound connections from mariadbd (FederatedX, CONNECT, SST to an unusual port)setsebool -P mysql_connect_any on, only if you really need it

ERROR 1047 (08S01): WSREP has not yet prepared node for application use

A Galera node refuses queries because it is not part of a primary component or not yet Synced: it lost contact with the majority, is still joining, or the whole cluster lost quorum.

sudo mariadb -e "SHOW GLOBAL STATUS WHERE Variable_name IN
  ('wsrep_ready','wsrep_connected','wsrep_cluster_status','wsrep_cluster_size','wsrep_local_state_comment');"
  • wsrep_cluster_status = non-Primary on every node: quorum is lost, for example two of three nodes died together. Find the most advanced node and re-bootstrap the primary component (part 5): SET GLOBAL wsrep_provider_options='pc.bootstrap=YES'; on that node only.
  • Only one node non-Primary: a network partition or firewall issue on that node. Test ports 4567/4568/4444 between the nodes and check that the TLS settings from step 9 are identical.
  • Joining / Donor/Desynced: wait for the SST/IST to finish, and keep HAProxy health checks on wsrep_local_state (step 10) so clients never land on such a node.

MySQL 8.4 notes

  • MySQL generates a self-signed CA and server certificate in the data directory on first start (ca.pem, server-cert.pem), so TLS is on by default. Replace them with your own via ssl_ca, ssl_cert and ssl_key.
  • Clients use --ssl-mode instead of --ssl-verify-server-cert: PREFERRED is the default; use VERIFY_CA or VERIFY_IDENTITY with --ssl-ca. Put these in [client] or [mysql], and MariaDB-only options in [client-mariadb].
  • Reload certificates with ALTER INSTANCE RELOAD TLS instead of FLUSH SSL.
  • With require_secure_transport=ON, an insecure connection gets ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
  • REQUIRE SSL/X509/SUBJECT/ISSUER work the same way; TCP connection failures are reported as 2003.
  • Group Replication encrypts its traffic with group_replication_ssl_mode and the recovery channel with group_replication_recovery_use_ssl, the equivalent of the Galera settings in step 9.

Series wrap-up

This completes the MySQL/MariaDB on AlmaLinux series: part 1, install and secure MariaDB/MySQL, part 2, users, grants and remote access, part 3, backups and point-in-time recovery, part 4, GTID primary/replica replication, part 5, MariaDB Galera Cluster, part 6, performance tuning, and this part on TLS and troubleshooting. For the same TLS setup on PostgreSQL, see PostgreSQL SSL/TLS and PgBouncer connection pooling.

Official references: MariaDB: securing connections for client and server, MariaDB: SSL/TLS system variables, MariaDB: zero-configuration SSL (11.4+), MariaDB: securing communications in Galera Cluster, MariaDB: mariadb-backup SST method, MariaDB: InnoDB recovery modes and MariaDB error code reference.

Leave a Reply

Your email address will not be published. Required fields are marked *