Datenbank Cleanup Script von Jörg, 12.09.2024 13:36

Datenbank Cleanup Script

Jörg 11.09.2024 14:43

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

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

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

# 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="test"

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

# Liste von Datenbanken, die nicht bearbeitet werden sollen (durch Leerzeichen getrennt)
exclude_databases=("excluded_db1" "excluded_db2" "excluded_db3")

# Datei für Fehlermeldungen
error_log="cleanup_errors.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
}

# 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 "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}//")

    # SQL-Befehle ausführen (beispielsweise TRUNCATE, UPDATE, DELETE)
    # Leere Tabellen
    mysql -u $user -p$passwort -h $host -D $db -e "TRUNCATE TABLE ${prefix}edit;" 2>> "$error_log"

    # Löschen von Daten
    mysql -u $user -p$passwort -h $host -D $db -e "DELETE FROM ${prefix}logs WHERE Erstelldatum < NOW() - INTERVAL 30 DAY;" 2>> "$error_log"

    # UPDATE
    mysql -u $user -p$passwort -h $host -D $db -e "UPDATE ${prefix}logs2 SET IP ='', post='', get='' WHERE Erstelldatum < NOW() - INTERVAL 3 DAY;" 2>> "$error_log"

    # Optimierung der Tabellen
    mysql -u $user -p$passwort -h $host -D $db -e "OPTIMIZE TABLE ${prefix}logs;" 2>> "$error_log"
    mysql -u $user -p$passwort -h $host -D $db -e "OPTIMIZE TABLE ${prefix}logs2;" 2>> "$error_log"
    mysql -u $user -p$passwort -h $host -D $db -e "OPTIMIZE TABLE ${prefix}edit;" 2>> "$error_log"

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

Antwort verfassen Antwort mit Zitat verfassen Beitrag melden

– Informationen zu den Bewertungsregeln

Datenbank Cleanup Script

Jörg 12.09.2024 13:36

– Informationen zu den Bewertungsregeln

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

Antwort verfassen Antwort mit Zitat verfassen Beitrag melden

– Informationen zu den Bewertungsregeln

Datenbank Cleanup Script
Jörg 12.09.2024 15:07 (Versionen)

bash-script

datenbank
– Informationen zu den Bewertungsregeln
Da kommt immer 0 Bytes raus, obwohl ich definitiv Daten einspare, wie ich beim Backupvergleich vorher/nachher sehe.

Ok, habe es nun anders gelöst. Ich wende diese Funktion vor und nach dem Cleanup an und das funktioniert (und ist bei mir schneller als die DB-Größe aus dem information schema zu berechnen).

Jörg
```
# Funktion, um die Größe der gesamten Tabellen in einer Datenbank zu berechnen
get_total_db_size() {
  local db="$1"
  local total_size
  local query="SELECT ROUND(SUM(DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS \`Size (MB)\` FROM information_schema.TABLES WHERE table_schema = '${db}';"

  total_size=$(mysql -u "$user" -p"$passwort" -h "$host" -e "$query" --batch --skip-column-names 2>> "$error_log")
  echo "$total_size"
}
```
Antwort verfassen Antwort mit Zitat verfassen Beitrag melden

–
Informationen zu den Bewertungsregeln

SELFHTML Forum - Ergänzung zur Dokumentation Übersicht

Jörg: Datenbank Cleanup Script

Datenbank Cleanup Script

Datenbank Cleanup Script

Datenbank Cleanup Script

Datenbank Cleanup Script