Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

--- mysql_mit_ssl_verschluesselten_verbindungen [2024-04-11 09:47:22] – manfred
+++ mysql_mit_ssl_verschluesselten_verbindungen [2026-03-02 15:58:52] (aktuell) – manfred
@@ Zeile 232: / Zeile 232: @@
   | sys                                |
   +------------------------------------+
+===== MySQL sein SSL-Zertifikat prüfen =====
+<code bash /root/bin/Zertifikatsende_-_UNI.sh>
+#!/bin/bash
+# diese Methode ist universell und funktioniert bei MySQL und bei MariaDB;
+# der Pfad zum Zertifikat wird aus der DB ausgelesen;
+# die Informationen werden dann direkt aus der PEM-Datei gelesen;
+MYSQL_LOGIN="mysql"
+#MYSQL_LOGIN="mysql --defaults-file=/root/.my.cnf"
+(
+cd $(echo "SELECT @@datadir;" | ${MYSQL_LOGIN} -N)
+#SSL_ZERTIFIKAT="$(echo "SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Current_tls_cert';" | ${MYSQL_LOGIN} -N)"
+SSL_ZERTIFIKAT="$(echo "SELECT @@ssl_cert;" | ${MYSQL_LOGIN} -N)"
+BIS="$(date -d"$(openssl x509 -noout -enddate -in "${SSL_ZERTIFIKAT}" | awk -F'=' '{print $2}')" +'%F %T')"
+echo "${SSL_ZERTIFIKAT}";
+echo "$(date -d "${BIS}" +%s) $(date +%s)" | awk '{printf "%u %s\n", ($1 - $2) / 86400,"Tage"}'
+echo "${BIS}";
+openssl x509 -noout -subject -issuer -in "${SSL_ZERTIFIKAT}" | sed 's/^[^=]*=//'
+) | tr -s '\n' '|' | sed 's/[|]/; /g'
+echo
+</code>
+<code py /root/bin/ssl_check_mysql.py>
+#!/usr/bin/env python3
+import glob
+import subprocess
+import datetime
+import os
+WARN_DAYS = 30
+CRIT_DAYS = 10
+now = datetime.datetime.now()
+sockets = sorted(glob.glob("/var/run/mysqld/mysqld*.sock"))
+for sock in sockets:
+    if not os.path.exists(sock):
+        continue
+    try:
+        ssl_cert = subprocess.check_output(
+            ["mysql", "-NS", f"{sock}"],
+            input="SELECT @@ssl_cert;",
+            text=True,
+            stderr=subprocess.DEVNULL
+        ).strip()
+        ssl_ca = subprocess.check_output(
+            ["mysql", "-NS", f"{sock}"],
+            input="SELECT @@ssl_ca;",
+            text=True,
+            stderr=subprocess.DEVNULL
+        ).strip()
+        if not ssl_cert.startswith("/"):
+            datadir = subprocess.check_output(
+                ["mysql", "-NS", f"{sock}"],
+                input="SELECT @@datadir;",
+                text=True,
+                stderr=subprocess.DEVNULL
+            ).strip()
+            cert_path = datadir + ssl_cert
+        else:
+            cert_path = ssl_cert
+    except Exception:
+        print(f"3 mysql_ssl_cert_{os.path.basename(sock)} UNKNOWN - mysql error")
+        continue
+    try:
+        out = subprocess.check_output(
+            ["openssl", "x509", "-in", cert_path, "-noout",
+             "-enddate", "-subject", "-issuer"],
+            text=True,
+            stderr=subprocess.DEVNULL
+        )
+    except Exception:
+        print(f"3 mysql_ssl_cert_{os.path.basename(sock)} UNKNOWN - cannot read cert {cert_path}")
+        continue
+    expiry = None
+    subject = ""
+    issuer = ""
+    for line in out.splitlines():
+        if line.startswith("notAfter="):
+            raw = line.split("=", 1)[1].strip()
+            expiry = datetime.datetime.strptime(raw, "%b %d %H:%M:%S %Y %Z")
+        elif line.startswith("subject="):
+            subject = line.replace("subject=", "").strip()
+        elif line.startswith("issuer="):
+            issuer = line.replace("issuer=", "").strip()
+    if not expiry:
+        print(f"3 mysql_ssl_cert_{os.path.basename(sock)} UNKNOWN - cannot parse expiry")
+        continue
+    # ----------- days_left --------------
+    days_left = (expiry - now).days
+    if days_left < CRIT_DAYS:
+        status = 2
+    elif days_left < WARN_DAYS:
+        status = 1
+    else:
+        status = 0
+    expiry_str = expiry.strftime("%Y-%m-%d %H:%M:%S")
+    svc = f"mysql_ssl_cert_{os.path.basename(sock)}"
+    summary = f"{cert_path}; {days_left} Tage; {expiry_str};"
+    if subject:
+        summary += f" {subject};"
+    if issuer:
+        summary += f" {issuer};"
+    print(f"{status} {svc} days_left={days_left};{WARN_DAYS};{CRIT_DAYS};0; {summary}")
+</code>