Datenbank Cleanup Script

[Jörg]

Hallo Forum,

ich muss mal aufgrund der Datenmenge in meinen Datenbanken ein wenig aufräumen und habe mir das wie nachfolgend gedacht.

Habt Ihr Verbesserungsvorschläge oder seht Ihr irgendwelche "NoGos"?

Jörg

habe nun das sytem noch etwas ausgeweitet, aber leider scheint meine Berechnug des freigegebenen Speicherplatzes nicht zu funktionieren.

Da kommt immer 0 Bytes raus, obwohl ich definitiv Daten einspare, wie ich beim Backupvergleich vorher/nachher sehe.

Sieht einer von Euch einen Fehler bei der Berechnung?

Gruß, Jörg

#!/bin/bash
### Code in utf-8

# MySQL-Verbindungsinformationen
host="localhost"
port="3306"
user=""
passwort=""

# Liste von Datenbanken, die nicht bearbeitet werden sollen (durch Leerzeichen getrennt)
exclude_databases=("db2" "db4")

# Schemas, die nicht berücksichtigt werden sollen
schemasName="databases"
nodump="Database|information_schema|mysql|performance_schema|sys"

# Tabellensuffix (der Teil des Tabellennamens ohne Präfix)
table_suffix="doku"

# Assoziatives Array: Datenbankname -> Präfix
declare -A db_prefixes

# Datei für Fehlermeldungen
error_log="cleanup_errors.log"
size_log="freed_space.log"

# Funktion zum Überprüfen, ob eine Datenbank ausgeschlossen werden soll
is_excluded() {
  local db="$1"
  for excluded_db in "${exclude_databases[@]}"; do
    if [[ "$db" == "$excluded_db" ]]; then
      return 0  # Datenbank ist in der Ausschlussliste
    fi
  done
  return 1  # Datenbank ist nicht in der Ausschlussliste
}

# Funktion, um die Größe einer Tabelle zu ermitteln
get_table_size() {
  local db="$1"
  local table="$2"
  # Query to get Data_length and Index_length
  local query="SHOW TABLE STATUS LIKE '${table}';"
  # Run the query and capture the output
  local result=$(mysql -u "$user" -p"$passwort" -h "$host" -D "$db" -e "$query" --batch --skip-column-names 2>> "$error_log")

  # Check if the result is empty or has an error
  if [ -z "$result" ]; then
    echo "Fehler: Keine Daten für Tabelle $table in DB $db gefunden." >> "$error_log"
    echo "0"
    return
  fi

  # Extract Data_length and Index_length
  local size=$(echo "$result" | awk '{print $7 + $8}')

  # Check if size is empty or invalid
  if [ -z "$size" ] || [ "$size" -lt 0 ]; then
    echo "Fehler: Ungültige Größe für Tabelle $table in DB $db" >> "$error_log"
    size="0"
  fi

  echo "$size"
}

# Funktion, um die Größe in MB und KB anzugeben
convert_bytes() {
  local bytes=$1
  local gb=$(echo "scale=2; $bytes / 1073741824" | bc)
  local mb=$(echo "scale=2; $bytes / 1048576" | bc)
  local kb=$(echo "scale=2; $bytes / 1024" | bc)
  echo "$gb GB ($mb MB, $kb KB)"
}

# Funktion zum Ausführen einer SQL-Abfrage
execute_sql() {
  local db="$1"
  local query="$2"

  # Führe die Query aus
  mysql -u "$user" -p"$passwort" -h "$host" -D "$db" -e "$query" 2>> "$error_log"

  # Prüfe den Exit-Status des vorherigen Befehls
  if [ $? -ne 0 ]; then
    echo "$(date '+%Y-%m-%d %H:%M:%S') - Fehler bei der Ausführung der Query: $query in DB $db" >> "$error_log"
  fi
}

# Datenbanken auslesen und filtern
databases=$(echo "SHOW ${schemasName};" | mysql --host="$host" --port="$port" --user="$user" --password="$passwort" --column-names=FALSE 2>> "$error_log" | grep -vP "$nodump")

# Überprüfen und hinzufügen von Präfixen zu den Datenbanken
for db in $databases
do
  # Überprüfe, ob die Datenbank ausgeschlossen werden soll
  if is_excluded "$db"; then
    echo "$(date '+%Y-%m-%d %H:%M:%S') - Datenbank $db ist ausgeschlossen, wird nicht bearbeitet." >> "$error_log"
    continue
  fi

  # Tabellen in der Datenbank auslesen, die das bekannte Suffix haben
  tables=$(mysql -u "$user" -p"$passwort" -h "$host" -D "$db" -e "SHOW TABLES LIKE '%${table_suffix}%';" --batch --skip-column-names 2>> "$error_log")

  # Tabellen durchlaufen und Präfixe ermitteln
  for table in $tables
  do
    # Extrahiere Präfix
    prefix=$(echo "$table" | sed "s/${table_suffix}//")

    # Initiale Größe vor der Bereinigung
    initial_size=$(get_table_size "$db" "$table")

    # SQL-Befehle ausführen 
    ########################################################################################################################
    # Leere Tabellen
    ########################################################################################################################
    execute_sql "$db" "TRUNCATE TABLE ${prefix}edit;"

    ########################################################################################################################
    # Löschen von Daten
    ########################################################################################################################
    execute_sql "$db" "DELETE FROM ${prefix}loge WHERE Erstelldatum < NOW() - INTERVAL 30 DAY;"

    ########################################################################################################################
    # UPDATE
    ########################################################################################################################
    execute_sql "$db" "UPDATE ${prefix}logs2 SET post='', get='' WHERE Erstelldatum < NOW() - INTERVAL 30 DAY;"

    ########################################################################################################################
    # Optimierung der Tabellen
    ########################################################################################################################
    execute_sql "$db" "OPTIMIZE TABLE ${prefix}edit;"
    execute_sql "$db" "OPTIMIZE TABLE ${prefix}logs;"
    execute_sql "$db" "OPTIMIZE TABLE ${prefix}logs2;"

    # Finalgröße nach der Bereinigung
    final_size=$(get_table_size "$db" "$table")

    # Berechnung des freigewordenen Speicherplatzes
    freed_space=$((initial_size - final_size))

    # Logging der Speichergrößen und des freigewordenen Platzes
    echo "$(date '+%Y-%m-%d %H:%M:%S') - DB: $db, Tabelle: $table, Freier Speicher: $(convert_bytes ${freed_space})" >> "$size_log"

    # Ausgabe von Datenbankname und Präfix
    echo "Datenbank: $db -> Präfix: $prefix"
  done
done