MySQL: alle Datenbanken sichern

9

Um alle Datenbanken eines MySQL Servers zu sichern kann man entweder ein script schreiben, in dem alle Datenbank Namen fest eingetragen werden oder man zieht sich zum Zeitpunkt der Sicherung alle Datenbank Namen und sichert jede gefunden. Die letzte Möglichkeit möchte ich kurz beschreiben.

Nachfolgendes script speichert in der Variable DBASELIST alle Datenbanknamen. Anschließend werden die noch noch im Cache befindlichen Operationen geschrieben (flush tables) und jede Datenbank anschließend mit einem write lock versehen. Dadurch ist es nicht mehr möglich das während der Sicherung noch Veränderungen an der Datenbank vorgenommen werden.

Anschließend wird in einer for Schleife jede DB gesichert. Jede Datenbank wird dabei in einem tar.gz Archiv verpackt, dass das aktuelle Datum und den Datenbanknamen trägt.

Beispiel: 2008.Mai.8._web1sql1.tar.gz

Ist die Sicherung erfolgt so werden alle Tabellen wieder freigegeben.

#!/bin/bash
set $(date)
str_Tag=$(date +%A);
echo "********** saving MySQL-Databases ****"
   DBASELIST=`mktemp`
   mysqlshow -p<Password> | awk '{print $2}' | grep -v Databases | sort >$DBASELIST
# Wohin sollen die ganzen Backups geschrieben werden?
   cd /var/backup
# MySQL Cache auf HD schreiben und Tabellen vor Veränderungen schützen
  mysql -u root -p<Password> mysql -e "flush tables with read lock" -v
# Sicherung der einzelnen Tabellen
   for x in `cat $DBASELIST`; do
       echo "    saving database: $x";
       mysqldump --opt -p<Password> $x >$x.sql;
       tar cfvz $6.$3.$2_$x.tar.gz $x.sql > /dev/null 2>&1
   done;
   echo
# Tabellen nach der Sicherung wieder freigeben
  mysql -u root -p<Password> mysql -e "unlock tables" -v
  rm *.sql
   echo -e "33[40;1;32m Done 33[0m..."
echo ""

Sicherung auf ein Remote System

Sollte auf dem Sicherungsystem nicht genügend Platz vorhanden sein oder die Sicherung soll direkt auf einen zentralen Server laufen, so kann nach der Einrichtung der PublicKey Authentifizierung für einen Schlüssel ohne Kennwort das das obige script angepasst werden, indem die Zeile

tar cfvz $6.$3.$2_$x.tar.gz $x.sql > /dev/null 2>&1

durch die Zeile

tar zcvf - /var/backup/$x.sql | ssh -p 22 root@domain.tld "cat > /zielverzeichniss/auf/dem/Server/$6.$3.$2_$x.tar.gz"

 ersetzt wird. Servername etc. muss wie immer angepasst werden. Anschließend läuft die Sicherung direkt auf den Zeilserver.

Ein Beispielscript kann hier herunter geladen werden.

Datenbank wiederherstellen

mysqladmin -u root -p create <database>
mysql -u root -p --default-charachter-set=utf8 <database> < backup.sql
Teilen.

Über den Autor

Seit der Ausbildung zum Fachinformatiker Systemintegration (2002-2005) bei der DaimlerChrysler AG, beruflich im Bereich der E-Mail Kommunikation (Exchange, Linux) sowie des ActiveDirectory, mit entsprechenden Zertifizierungen (MCSE 2003, MCITP Ent.-Admin 2008, MCSE 2012, LPIC 1-3) tätig. Abgeschlossenes Studium zum Master of Science der IT-Management an der FOM sowie zertifizierter Datenschutzbeauftragter. Aktuell im Projektmanagement tätig.

9 Kommentare

  1. Echt tolles Script!
    Jetzt wo es so einfach ist ein Backup zu erstellen kann ich täglich ein Backup der gesamten Datenbanken machen.

    Und Du hast mich hierbei gerade noch auf eine Idee für eine andere Backup-Strategie gebracht…

    Vielen Dank!

  2. Hallo,

    wird ganz sicher auch die Datenbank gesperrt? Ich habe dein Skript eben einmal ausprobiert und während „mysql -u root -p mysql -e „flush tables with read lock“ -v“ lief, habe ich versucht eine Tabelle zu verändern, dies ging ohne Probleme während der Sperrung und nicht danach.

  3. Sollte so sein, ist es aber offensichtlich nicht..
    Also ich habe auch ein skript zur sicherung meiner dbs zuvor geschrieben und hatte es genauso geschrieben wie du. problem ist aber dann, dass wenn ich die datenbank per skript sperre, immer noch live änderungen an der db vornehmen kann.

    wo hingegen, wenn ich „FLUSH TABLES WITH READ LOCK“ händisch auf der konsole ausführe, dann keine änderungen mehr der db vornehmen kann. dahergehend liegt meine vermutung nahe, dass der read lock per skript nicht greift.

    das belegen auch andere quellen, die dann zu programmen wie nohup oder screen raten, denn:

    „Dieses Lock ist global über alle Datenbanken.

    mysql> flush tables with read lock;

    Es ist wichtig diese Session offen zu halten, da beim Beenden der Session das Lock sofort entfernt wird. Deshalb schieben wir den Prozess einfach in den Hintergrund.

    [Press ctrl-z]“ quelle: http://www.rootforum.org/forum/viewtopic.php?f=67&t=49484

  4. Das heißt im Umkehrschluss, dass du eigentlich nie ein konsistentes Backup erhälst 😉

    Ich muss aber zugeben, dass ich es auch noch nicht hinbekommen hab!

Antworten