Automatisches Sichern Ihrer MySQL-Datenbanken auf der lokalen Festplatte mit Python

In diesem Blogbeitrag zeige ich dir ein einfaches, aber wirkungsvolles Python-Skript, mit dem du deine MySQL-Datenbanken automatisch auf deiner lokalen Festplatte sichern kannst.

Das Python-Skript

import os
import pymysql
from datetime import datetime


def escape_quotes(value):
    return value.replace("'", "''") if isinstance(value, str) else value


def progress_percentage(current, total):
    return round((current / total) * 100, 2)

# Aktuelles Datum holen
current_date = datetime.now()

# Jahr, Monat und Tag formatieren
year = current_date.strftime('%Y')
month_day = current_date.strftime('%m-%d')

# MySQL-Verbindungsinformationen für mehrere Datenbanken
db_configs = [
    {
        'host': 'HOST_1',
        'user': 'USERNAME_1',
        'password': 'PASSWORD_1',
        'database': 'DATABASE_1',
        'backup_folder': fr"C:\Backups\Folder_1\{year}\{month_day}"
    },
    {
        'host': 'HOST_2',
        'user': 'USERNAME_2',
        'password': 'PASSWORD_2',
        'database': 'DATABASE_2',
        'backup_folder': fr"C:\Backups\Folder_2\{year}\{month_day}"
    },
    # Fügen Sie hier weitere Datenbankkonfigurationen hinzu
]

timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')

for db_config in db_configs:
    backup_folder = db_config['backup_folder']
    os.makedirs(backup_folder, exist_ok=True)

    backup_file = os.path.join(backup_folder, f"{db_config['database']}_{timestamp}.sql")

    connection = pymysql.connect(host=db_config['host'],
                                 user=db_config['user'],
                                 password=db_config['password'],
                                 db=db_config['database'])

    cursor = connection.cursor()
    cursor.execute("SHOW TABLES")
    tables = cursor.fetchall()

    total_tables = len(tables)
    processed_tables = 0

    with open(backup_file, 'w', encoding='utf8') as f:
        f.write("SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';\n")
        f.write("SET AUTOCOMMIT = 0;\n")
        f.write("START TRANSACTION;\n")
        f.write("SET time_zone = '+00:00';\n\n")

        for table in tables:
            table_name = table[0]
            cursor.execute(f"SHOW CREATE TABLE `{table_name}`")
            create_table = cursor.fetchone()
            f.write(f"{create_table[1]};\n\n")

            cursor.execute(f"SELECT * FROM `{table_name}`")
            rows = cursor.fetchall()

            for row in rows:
                values = ', '.join([f"'{escape_quotes(value)}'" if isinstance(value, str) else (f"'{value}'" if isinstance(value, datetime) else ("NULL" if value is None else str(value))) for value in row])
                f.write(f"INSERT INTO `{table_name}` VALUES ({values});\n")
            f.write("\n")

            processed_tables += 1
            progress = progress_percentage(processed_tables, total_tables)
            print(f"{db_config['database']}: {processed_tables}/{total_tables} Tabellen verarbeitet ({progress}%)")

        f.write("COMMIT;\n")

    cursor.close()
    connection.close()

    print(f"Backup von {db_config['database']} wurde erstellt: {backup_file}")

Das Herzstück dieses Skripts ist das pymysql Modul, eine reine Python-MySQL-Client-Bibliothek, die es uns ermöglicht, mit MySQL-Servern zu kommunizieren und Daten abzurufen.

Das Skript durchläuft eine Liste von Datenbank-Konfigurationen, die in db_configs gespeichert sind. Jede Konfiguration enthält Verbindungsinformationen für eine bestimmte Datenbank und den Pfad, an dem das Backup gespeichert werden soll.

Es stellt eine Verbindung zur Datenbank her, liest alle Tabellen und speichert sie in einer .sql Datei. Der Dateiname enthält den Namen der Datenbank und einen aktuellen Zeitstempel, um sicherzustellen, dass jedes Backup eindeutig ist und sortiert werden kann.

Für jede Tabelle schreibt es zuerst den CREATE TABLE Befehl, gefolgt von INSERT INTO Befehlen für jede Zeile in der Tabelle.

Das Skript zeigt den Fortschritt auf der Konsole an, indem es die Anzahl der verarbeiteten Tabellen und den Prozentsatz des Fortschritts anzeigt.

Wichtige Funktionen

• escape_quotes(value): Diese Funktion ersetzt in Zeichenketten einfache Anführungszeichen durch zwei einfache Anführungszeichen, um SQL-Injektionen zu verhindern.
• progress_percentage(current, total): Diese Funktion berechnet den Fortschrittsprozentsatz, indem sie die Anzahl der aktuell verarbeiteten Elemente durch die Gesamtzahl der Elemente teilt.
• os.makedirs(backup_folder, exist_ok=True): Dieser Befehl erstellt die notwendigen Verzeichnisse für die Backup-Dateien, falls sie noch nicht vorhanden sind.

Hinweise zur Verwendung

Bevor du das Skript ausführst, stelle sicher, dass du die richtigen Verbindungsinformationen in db_configs eingetragen hast. Du kannst so viele Datenbanken hinzufügen, wie du möchtest, indem du einfach weitere Dictionaries zur Liste db_configs hinzufügst.

Auch musst du sicherstellen, dass der Pfad zum Backup-Ordner auf deinem System existiert oder erstellt werden kann. Du kannst den Pfad anpassen, um ihn deinen Bedürfnissen anzupassen.

Schließlich solltest du bedenken, dass dieses Skript für sehr große Datenbanken nicht geeignet ist, da es alle Daten in den Speicher lädt, bevor es sie in die Datei schreibt. Für große Datenbanken wirst du wahrscheinlich eine effizientere Methode benötigen, wie das Streaming der Daten direkt in die Datei.

Automatisiere die Ausführung deines Python-Skripts mit einer Batch-Datei auf Windows

Um das Ausführen deines Python-Skripts noch weiter zu vereinfachen, kannst du unter Windows eine Batch-Datei (.bat) erstellen. Diese Datei kann dein Python-Skript automatisch ausführen. Hier sind die Schritte, die du befolgen musst:

• Erstelle eine neue Textdatei.
• Öffne die Textdatei und füge folgenden Text ein:

@echo off
python C:\Pfad\zu\deiner\Pythondatei.py
pause

• Ersetze C:\Pfad\zu\deiner\Pythondatei.py mit dem tatsächlichen Pfad zu deiner Python-Datei.
• Speichere die Datei mit der Erweiterung .bat statt .txt. Zum Beispiel könnte der Dateiname Backup_now.bat lauten.

Jetzt kannst du einfach auf diese Batch-Datei doppelklicken, um dein Python-Skript auszuführen.

Wichtig zu beachten ist, dass der Befehl python nur funktioniert, wenn Python zur Systemumgebungsvariable PATH hinzugefügt wurde. Wenn das nicht der Fall ist, musst du den vollen Pfad zur Python-Exe-Datei angeben, z.B.: C:\Python39\python.exe.

Mit dieser Methode kannst du die Ausführung deines Backup-Skripts noch weiter automatisieren. Du kannst die Batch-Datei sogar zu deinen geplanten Aufgaben hinzufügen, um das Skript zu einer bestimmten Zeit oder in bestimmten Abständen auszuführen. So stellst du sicher, dass deine Datenbanken immer sicher und aktuell sind.