SCHLOEBE.DEPersonal Portfolio of Oliver Schlöbe

Platzsparende Datenbank-Backups via Cronjob

Viele kennen das Problem: Ein tägliches manuelles Backup einer Datenbank zu machen ist aufwändig und zeitraubend. Warum also nicht alles automatisch ablaufen lassen? Ein Backup einer Datenbank per Kommandozeile erstellen ist nicht allzu schwer, das was hier oft vergessen wird ist der Platz, den all die Sicherungen benötigen.

Wird jeden Tag ein DB-Backup angelegt, werden ältere Backups natürlich nicht überschrieben bzw. gelöscht, was es dennoch notwendig machte, manuell die alten Dateien zu löschen. Mit diesem Unix-Backup-Script für Cronjobs (das mit Daniel P. Schenk aus einer Notwendigkeit heraus entstand) schlagen Sie zwei Fliegen mit einer Klappe:

  • Ein datumsorganisiertes Datenbank-Backup wird angelegt
  • Ältere Backups werden automatisch gelöscht, um Speicherplatz zu sparen

Folgend nun das Script für einen Cronjob:
[apache]#!/bin/sh
DIR="/ihr/serverpfad/zum/backup/"
DATUM=`date '+%d-%m-%Y'`
mysqldump -u BENUTZER -p PASSWORT -h localhost --opt DATENBANKNAME > $DIR/backup.sql
gzip -9 --best $DIR/backup.sql
mv $DIR/backup.sql $DIR/backup-${DATUM}.sql.gz
find $DIR/backup.sql -name \*.sql.gz -mtime +5 -exec rm {} \;[/apache]
Die zweite Zeile setzt eine Variable DIR mit dem Pfad zur Backup-Datei. Diese sollte erstellt werden, um bei einer Pfadänderung nicht an etlichen Stellen im Script Änderungen vornehmen zu müssen. Auf die Variable wird im weiteren Script per $DIR zugegriffen. Der Pfad muss absolut vom root angegeben werden, bspw. /home/www/web13/html/backup/. Wichtig: Der Pfad benötigt Schreibrechte (CHMOD755)!

Die dritte Zeile bestimmt das Datumsformat der Sicherungsdatei. In diesem Fall sähe es folgendermaßen aus, wenn es am 10.03.2007 gespeichert worden wäre: 10-03-2007 Dieser String, diese Datumsfolge wird dann dem Dateinamen hinzugefügt, um eindeutige Sicherungsdateien zu generieren.

Die vierte Zeile erzeugt nun das eigentliche Backup der Datenbank. Es werden einige Parameter erwartet, damit dieser Befehl erfolgreich ausgeführt werden kann:

  • -u Benutzer: Der Benutzernamen für den Zugriff auf die Datenbank, bspw. -u web123
  • -p Passwort: Das Passwort zur Datenbank, bspw. -p password123
  • -h localhost: Der Datenbankserver, kann eine IP sein oder localhost, was zu 99% der Fall ist
  • --opt DATENBANKNAME: Der Name der Datenbank, die gesichert werden soll, bspw. --opt db_span_1

Mit dem > - Zeichen wird der Dump nun in eine Datei geschrieben, in diesem Fall in die backup.sql im angegebenen Ordner. Den Namen können Sie selbstredend selbst bestimmen, sollten allerdings darauf achten, den Namen im weiteren Code ebenfalls zu ändern.

gzip in der fünften Spalte zippt das Backup, um Speicherplatz zu sparen. Der Pfad zur Datei und der Dateiname muss mit denen aus der zweiten und vierten Zeile übereinstimmen!

Die sechste Zeile des Scriptes schließt das Backup ab, indem es der Backupdatei einen eindeutigen Namen zuweist, der aus Dateiname und des in der zweiten Zeile eingestellten Datumsformats besteht. In diesem Fall würde die Sicherungsdatei nun heißen: backup-10-03-2007.sql.gz

Damit wäre das eigentliche Backup-Script abgeschlossen.

Um aber Dateiüberlauf zu verhindern, werden mit der letzten Zeile alle Dateien älter als 5 Tage automatisch gelöscht. Dazu wird der Unix-Befehl find genutzt. Folgende Parameter dienen uns hier:

  • -name \*.sql.gz: Alle Dateien mit der Endung .sql.gz werden angesprochen; das stellt sicher, dass ggf. nur Sicherungsdateien gelöscht werden, falls sich noch andere Dateien im selben Ordner befinden.
  • -mtime +5: Es werden alle Dateien gelöscht, die älter als 5 Tage sind, nachdem sie erstellt wurden. Ändern Sie diese Zahl je nach Belieben, bspw. auf 2 um alle Dateien älter als 2 Tage zu löschen
  • -exec rm {}: Werden Dateien entsprechend der Suchmaske gefunden, wird der Befehl rm ausgeführt (executed), der diese Dateien aus dem Verzeichnis löscht.

Testen Sie nun das Script, um sicherzustellen, dass es auch funktionert. Es gibt nichts Frustrierendes als vom Glauben an ein funktionierendes Sicherungssystem enttäuscht zu werden. 😉

Fragen bitte in die Kommentare.

37 Responses

  1. Wie sieht das aus wenn ich mehrere Datenbanken mit 1x sicher möchte? Was muss ich denn dann angeben?

    Danke

  2. Euch sollte man die Füße küssen ! Großes Lob das Beste Script das mir je untergekommen ist !

    1 + FÜR EUCH !

  3. Vielen Dank chris. 😉

    @Dümmling: Ab der 4. Zeile müsstest Du das Script einfach kopieren und die Dateinamen anpssen, damit sich nichts überschneidet.

    Also bspw. “[…]
    mysqldump -u BENUTZER2 -p PASSWORT2 -h localhost –opt DATENBANKNAME2 > $DIR/backup2.sql
    gzip -9 –best $DIR/backup2.sql
    […]”

    usw…

  4. Ich arbeite auch schon damit. Dies ist mittlerweile unabkömmlich geworden, alleins die Arbeit die man dadurch spart.
    Beste Grüße Michi

  5. Lob und Vielen Dank! Einen Cronjob hatte ich zwar bereits, allerdings wurde da immer die letzte Version überschrieben – “-mtime +5:” war genau das wonach ich gesucht habe.

  6. Als was für eine Datei muss ich das denn speichern? Im Cronjob kann ich nur eine aufzurufende URL wählen, aber nicht den COde da einfügen

  7. Bei mir entstehen immer leere .sql-Dateien, obwohl die db voll ist und die Daten stimmen. Kann es sein, dass das System kein mysqldump kennt? Wenn ja, gibt es Alternativn?

    Habe leider keinen direkten Zugriff per SSH, sehe also nicht die Fehlermeldungen.

  8. Hallo Oliver,

    das ganze Script hat ein paar Nachteile.
    Vorallem steht das Passwort im Klartext in der Prozessliste, waehrend das Script laeuft. Ein einfaches Auflisten der laufenden Prozesse in einer Mehrbenutzerumgebung foerdert somit das Passwort zur eigenen Datenbank zu Tage.
    Siehe http://dev.mysql.com/doc/refman/5.0/en/password-security.html

    Weiterhin legt es erst das Backup auf Festplatte und komprimiert es anschliessend – dies laesst sich sehr komfortabel in einem Schritt erledigen:

    mysqldump -u BENUTZER -p PASSWORT -h localhost –opt DATENBANKNAME | gzip -c -9 > $DIR/backup-`date ‘+%d-%m-%Y’`.sql.gz

    Gruesse
    Stefan

  9. Noch eine Anmerkung
    @chris: mit dem Schalter –databases kannst du mehrere Datenbanken angeben

    Es wuerde dann in etwa so aussehen:
    mysqldump -u BENUTZER -p PASSWORT -h localhost –opt –databases datenbankA datenbankB datenbankC

    Alternativ, wenn alle Datenbanken gesichtert werden sollen, benutzt man –all-databases

  10. Hallo zusammen,

    Das Script ist sehr nützlich und die Tipps von Stefan auch!

    Kann man den dieses Script dazu benutzen, auch andere Daten zu sichern, wie etwa den Ordner, in dem sich eine Webseite befindet, z.B html/wordpress/ … oder eine beliebige Datei?

    Grüsse

    Christo

  11. Christo,

    das ist natürlich möglich. Nur müsstest du dann dem Befehl mysqldump ‘tip’ oder ‘tar’ nutzen, um die Dateien/Ordner zu packen.

  12. Danke Oli !

    Das ist toll – wenn das auch geht. Hast du eigentlich ” “zip” oder “tar” ” gemeint?

    Bin ziemlich neu in dem Bereich und daher können meine Fragen etwas dumm erscheinen, aber: Wie würde dann, beispielsweise, die Befehlszeile aussehen?

    Gruss

    Christo

  13. Find ich gut dein Skript, aber muss der find Befehl nicht eher so lauten?
    find $DIR -name backup\*.sql.gz -mtime +5 -exec rm {} \;

    Du gibts an wo er suchen soll und dann was.

    Ich würde zum Datumsformat noch die Uhrzeit hinzu fügen
    DATUM=`date ‘+%d-%m-%Y-%H:%M:%S’`
    Damit bei Mehrfachausführung (auch unbeabsichtigte) die alten Dateien nicht überschrieben werden.

    Gruß
    koelli

  14. Ein alternativer Vorschlag für für MySQL-Backups wäre das Script AutoMySQLBackup, das ich für recht brauchbar halte. Macht rotierende Backups über Tage + Wochen + Monate.

  15. Hi,
    wenn ich das Script ausführe, erhalte ich folgende Fehlermeldung:

    “USER:/# /var/www/sqlbackup.sh
    -bash: /var/www/sqlbackup.sh: /bin/sh^M: bad interpreter: No such file or directory

    Wenn ich die erste Zeile aus dem Script entferne, kommt folgende Meldung beim ausführen:

    “USER:/# /var/www/sqlbackup.sh
    : No such file or directory 3: /var/www/web0/html/backups/
    : No such file or directoryhtml/backups/
    mv: cannot stat `/var/www/web0/html/backups/\r/backup.sql’: No such file or directory
    : No such file or directoryhtml/backups/”

    Die Datei hat die benötigten Rechte und das Zielverzeichnis ist vorhanden und auch beschreibbar. Habt ihr eine Idee woran das liegen könnte ?

  16. Hi Stefan

    -bash: /var/www/sqlbackup.sh: /bin/sh^M: bad interpreter: No such file or directory

    sieht für mich so aus also würdest du versuchen ein sh skript mit bash auszuführen. probier mal
    sh /var/www/sqlbackup.sh
    dabei muss natürlich die sh shell installiert sein.

    würde mir an deiner stelle überlegen, ob es sinnvoll ist das das backup skript im www ordner liegt. ich würde/hab es im home verzeichnis.
    nicht das du durch einen konfigurationsfehler jedem erlaubst das skript auszuführen.

    gruß
    koelli

  17. Gibt’s eigentlich auch ein Skript mit dem ich eine Vollsicherung von einer Mysql-DB zur einer andern machen? Ich hab insgesamt 3 Stück zur Verfügung.

  18. Merci!

    Heisst das, wenn ich im umgekehrten Fall vom Slave auf den Master repliziere, das alles wieder im Orginalzustand ist? Ich will damit das Thema Datensicherung vollständig erschlagen…

  19. Hallo,

    Wie und wo kann ich das Skipt testen bei einem Webhosting anbieter?
    wo muss ich das Skript hinkopieren auf dem Server und wie wird es mit Cronjob aufgerufen, ist ja eine .sh Datei?

  20. Hallo Jörg,

    in deinem Konfigurationsprogramm wie Plesk oder Confixx musst du das Script in eine Crontab eintragen.

    • Aber wo muss ich es hinkopieren auf meinem Webspace und wie heist die Dateiendung? Diese Antworten hätte ich noch gerne gewust (siehe oben)
      Ist die Dateiendung mit .sh am Ende korrekt?

      Jörg

      • Wo du die Datei hinkopierst, ist egal, sofern du in der Crontab den Pfad dorthin korrekt angibst. Und Shell-Scripte wie dieses haben die Endung .sh, ja. 🙂

  21. Hi Oliver,
    Danke für den Script. Ich habe die Vorschläge die als Kommentare gepostet wurden zu deinem Script hinzugefügt Nun sieht das ganze so aus:
    ——————————————————-
    DIR=”/srv/www/vhosts/domain/httpdocs/test_backup/”
    DATUM=`date ‘+%d-%m-%Y-%H:%M:%S’`
    mysqldump -hlocalhost -uadmin -p*** –quick –opt –default-character-set=latin1 usr_web1_7 | gzip -c -9 > $DIR/backup-`date ‘+%d-%m-%Y’`.sql.gz
    mv $DIR/backup.sql $DIR/backup-${DATUM}.sql.gz
    find
    ——————————————————-

    1. Ist das Script nun ok?
    2. unter /etc/ habe ich folgende Ordner liegen
    /cron.d
    /cron.daily
    /cron.hourly
    /cron.monthly
    /cron.daily
    /cron.weekly

    unter welchem Ordner soll das Script gespeichert werden?
    Soll das Script mit eine bestimme Endung gespeichert werden? z.B “sh”….etc

    Ist es ok, wenn ich das Script, wie oben eingefügt ist abspeichere? ich meine am Anfang oder Ende kommt nichts mehr?

    Danke für die Antwort

    • Hey Sebastian,

      bis auf dass am Ende der find-Befehl nicht vollständig ist, sieht es okay aus. Probieren geht über Studieren. 🙂

  22. Wie sind denn so die Erfahrungen mit dem Skript? Ich habe von Cronjobs absolut keine Ahnung, würde mir aber gern eine automatische Datenbank- und Ordnersicherung einstellen. Weiter oben hat ja jemand angemerkt, dass die Zugangsdaten im Klarnamen notiert werden. Das macht mir ein bisschen Sorgen.

    • Hi Ali,
      also ich benutzt dieses Skript in abgewandelter Form täglich und es mach genau das was es soll.

      Das Passwort so mitzugeben hat mir auch nicht gefallen, deswegen arbeite ich mit einer .my.cnf Datei. Diese liegt im Homeverzeichnis des Benutzers, der das Skript ausführt.

      Der Inhalt sieht so aus
      [client]
      password=meinPasswort

      Die mysqldump-Zeile im Skript ist dementsprechend abgeändert.
      mysqldump -u root -h localhost –opt DB > $DIR/DB-backup-${DATUM}.sql

      Da die Datenbank nicht so groß ist, komprimiere ich sie nicht.

      Weitere Lektüre für my.cnf findest du hier
      http://dev.mysql.com/doc/refman/5.1/de/option-files.html

  23. Vielen Dank für das Script. Ist wirklich sehr hilfreich.
    Aber vielleicht könntest du in deiner Erklärung noch kurz ergänzen, wofür der Parameter –opt steht. Hab nämlich gerade nach einer Möglichkeit gesucht “drop table” etc einzufügen, bis mir dann aufgefallen ist, dass –opt genau das macht. 😉

    • Werde ich bei Gelegenheit ergänzen, sobald ich etwas Zeit habe. Danke dir! 😉

  24. Der Post ist zwar schon sehr alt, aber dennoch haben sich da zwei Fehler im Script eingeschlichen 😉

    – Bei der -p Option darf kein Leerzeichen zum Passwort folgen, ie -pPASSWORT

    – Es wird die Datei backup.sql erzeugt, danach gezippt (backup.sql.gzip) und danach umbenannt. Beim Umbenennen scheitert es aber, da das mv command nach backup.sql sucht, welche aber nicht mehr vorhanden ist, weil wir sie gezipt haben. Also muss Zeile 6 des Scripts ebenfalls angepasst werden.

    lg Anni