Perl MySQL Backup

Einleitung

Datenbanken sind beim Backup besonders kritisch. Während die meisten Admins (wie auch ich) gerne auf Technologien wie Snapshots verzichten und alle Dateien der Festplatte mit einer dateibasierten Software sichern, gilt es bei einer Datenbank einige Schwierigkeiten zu klären.

Die Binärdateien der Datenbank sollte man im laufenden Betrieb nicht wegsichern, da hier aufgrund des Caches der Datenbank und ständigen Schreibzugriffen Datenverlust und Inkonsistenz drohen.

Viele Serveradmins bevorzugen daher den SQL-Dump: Man erzeugt eine Textdatei, die alle SQL-Kommandos enthält, mit der man die Datenbank komplett wieder herstellen kann.

Ich persönlich bevorzuge keine SQL-Dumps, denn hier steht man bei jedem Einspielen (meist Jahre nach dem Einrichten des Backups) immer wieder vor neuen Problemen. Mal sind bestimmte Feldnamen neuerdings reservierte Begriffe, mal stimmen die Quotes in der Datei nicht, dann wird mal wieder ein Tabellentyp angeblich nicht unterstützt oder die Zeichenkodierung hat sich geändert und zerstört dadurch alle Umlaute und Sonderzeichen.

Abgesehen von diesen Problemen stört aber auch die relativ lange Zeit der Wiederherstellung: Das Lösen der üblichen Probleme kostet dabei die meiste Zeit, doch auch das Einspielen von 160 GByte SQL-Kommandos benötigt sein Zeit.

Ich persönlich bevorzuge daher ein Backup der Binärdaten der Datenbank. Solange mein MySQL-Server auf aktuellem Stand ist (Upgrade erledigt ich ja regelmäßig), kann ich nach einem Crash jederzeit ein Neusystem aufsetzen, die Binärdateien zurückspielen, Datenbank starten und schon läuft die Datenbank wieder – inklusive aller Benutzer und Berechtigungen.

Vorgehen bei der Sicherung

Ein Problem ergibt sich hier nur: Wie schon gesagt unterliegen die Binärdateien der Kontrolle des Datenbankservers und stehen ständig unter Schreibzugriff. Ohne die Hilfe der Datenbank ist eine Sicherung also nicht möglich. Aber solange man als Storage Engine MyISAM einsetzt, läßt sich ganz einfach das lösen:

Zuerst mal erzeugen wir lokal eine konsistente Kopie der MySQL-Datendateien, die meist unter /var/lib/mysql/ oder auch mal unter /usr/local/mysql/data/ liegen. Beim Backup sichern wir dann statt der Datenbank selbst einfach die zuvor erzeugte Kopie.

Doch auch beim lokalen Kopieren stoßen wir an die oben genannten Probleme. Doch lokal dauert das Kopieren nur sehr kurze Zeit, daher läßt sich das in den Griff kriegen

Flush & Lock

Sendet man MySQL das Kommando FLUSH TABLES; so werden alle Caches geleert, also alle noch ausstehenden Daten wirklich in die Binärdateien geschrieben. Jedoch würde MySQL während dem Kopieren schon weiter zwischenspeichern und damit den Effekt zunichte machen.

Das Kommando FLUSH TABLES WITH READ LOCK; dagegen leert ebenfalls die Caches und lockt alle Tabellen, so dass kein anderer Prozess Schreibzugriffe in der Datenbank ausführen kann, bis das Kommando UNLOCK TABLES; gesendet wird. Alle anderen müssen also mit dem Schreiben abwarten, bis die Backupsoftware fertig mit den Lesen der Dateien ist.

Soweit klappt das schonmal, nun müssen wir nur sehen, dass der Kopiervorgang so schnell wie möglich geht, denn wenn die Datenbank 2 Minuten vollständig gelockt ist, ist das für den Betrieb eine starke Beeinträchtigung.

Kopieren mit RSync

RSync erlaubt das Kopieren (besser: synchronisieren) von Daten. Dabei wird vor dem Kopiervorgang geprüft, welche Dateien sich seit der letzten Ausführung geändert haben und nur diese werden übertragen.

Beim ersten Aufruf wird natürlich alles übertragen, da am Zielort ja noch keine Daten sind. Ruft man RSync danach nochmal mit den gleichen Parametern auf, werden nur noch die Änderungen seit der letzten Kopie übertragen.

Dies in Kombination mit den Flushes und Locks sollte uns in die Lage versetzen, eine Kopie der Datenbank anzufertigen, ohne die Datenbank lange zu locken.

Das Konzept

Zuerst müssen wir sehen, dass wir eine initiale Kopie der Datenbank erhalten:

  • Step 1: Wir leeren die Caches der Datenbank (Flush)
  • Step 2: Wie kopieren per RSync die Binärdateien in ein Backup-Verzeichnis.

Da das kopieren vermutlich länger dauert, wiederholen wir diesen Vorgang erneut. Dabei werden dann nur noch die wenigen geänderten Daten übertragen und das RSync-Kommando ist schon nach einem Sekundenbruchteil fertig.

  • Step 3: Erneut Flush
  • Step 4: Erneut RSync

Nun liegt die Aktualität unsere Kopie nur noch einen Sekundenbruchteil hinter der originalen Datenbank. Da ein erneutes RSync nun nur noch minimale Daten übertragen würde, können wie hierbei die Datenbank locken und damit dafür sorgen, dass keine neuen Daten während dem letzten (finalen) Durchlauf geschrieben werden können.

  • Step 5: Flush und Lock
  • Step 6: Erneut RSync

Damit haben wir unsere 1:1 Kopie der Datenbank ohne dass währemd des Kopierens ein Schreibzugriff die Daten hätte vernichten können und dabei war die Datenbank nur für einen sehr kurzen Moment gelockt. Dies dürfte keine Applikation überhaupt registriert haben. Wobei das natürlich nur zutrifft, wenn wir die Tabellen nun auch gleich wieder freigeben:

  • Step 7: Unlock

In Kommandos sieht das ganze dann so aus:

  MySQL: FLUSH TABLES;
  Shell: rsync -av /var/lib/mysql/ /var/backups/mysql/

  MySQL: FLUSH TABLES;
  Shell: rsync -av /var/lib/mysql/ /var/backups/mysql/

  MySQL: FLUSH TABLES WITH READ LOCK;
  Shell: rsync -av /var/lib/mysql/ /var/backups/mysql/
  MySQL: UNLOCK TABLES;

Ein Problem muss dabei aber noch gelöst werden:

Per Shell ist das schwer zu lösen, denn weist man MySQL an, die Tabellen zu locken, so muss die Verbindung so lange offen bleiben, bis ein Unlock erfolgt. Bricht die Verbindung ab, gibt MySQL die Tabellen automatisch wieder frei. Mit dem Kommandozeilentool mysql können wir hier also nicht arbeiten.

Backupscript mit Perl

Perl kann mit DBI (aus dem cpan Archiv) MySQL-Verbindungen aufbauen und sie über mehrere externe Shell-Kommandos hinweg offen halten. Also öffnen wir zuerst die Verbindung:

 #!/usr/bin/perl
 use DBI;
 my @DSN  = ('DBI:mysql:host=localhost', 'backup', 'XXXXXXXX');
 my $DB = DBI->connect(@DSN) || exit 1;

Nun kommt die oben genannte Logik zum Zug: Flush, Rsync

 $RESULT = $DB->prepare('FLUSH TABLES;') || exit 2;
 $RESULT->execute() || exit 3;
 system('rsync -a /var/lib/mysql/ /var/backups/mysql');

Diesen Part nochmals ausführen, um beim zweiten Lauf alle Änderungen seit der (langsamen) ersten Kopie nachzuziehen, Dann geht es weiter mit Flush & Lock und RSync:

 $RESULT = $DB->prepare('FLUSH TABLES WITH READ LOCK;') || exit 2;
 $RESULT->execute() || exit 3;
 system('rsync -a /var/lib/mysql/ /var/backups/mysql');

Am Ende das Freigeben des Locks und damit dann auch schon die Beendigung der MySQL-Verbindung:

 $RESULT = $DB->prepare('UNLOCK TABLES;');
 $RESULT->execute();
 $DB->disconnect();

Mehr ist nicht wirklich nötig für ein sauberes, schnelles Backup ohne viel Beeinträchtigung der Dienste und ohne hohe Last auf dem Server, wie es ein MySQL-Dump gerne mal erzeugt.

Das fertige Script

Packt man die paar Perl-Zeilen in ein Script mit etwas Ausgabe und schreibt alle Pfade und Zugansdaten in Variablen – zur einfacheren Verwaltung der Scripte sinnvoll – so erhält man das Endergebnis:

#!/usr/bin/perl
use DBI;

##
## CONFIGURATION
##

## WARNING: USE ONLY WITH MyISAM!

my $SRC_DIR = "/var/lib/mysql/";
my $DST_DIR = "/var/backups/mysql/";

my $MYSQL_HOST = "localhost";
my $MYSQL_USER = "backup";
my $MYSQL_PASS = "XXXXXXXX";

##
## END OF CONFIGURATION
##

my $RSYNC = "/usr/bin/rsync -av --exclude=datenbank/nichtgesicherte_tabellen.*";

# Open Database
my @DSN  = ('DBI:mysql:host='.$MYSQL_HOST, $MYSQL_USER , $MYSQL_PASS);
my $DB = DBI->connect(@DSN) || exit 1;

# Output
print stdout "n";
print stdout "===============================================================================n";
print stdout "MYSQL BINARY BACKUPn";
print stdout "===============================================================================n";
print stdout "n";
print stdout "-------------------------------------------------------------------------------n";
print stdout "Initial RSyncn";
print stdout "-------------------------------------------------------------------------------n";
print stdout "n";

# Flush & RSync
$RESULT = $DB->prepare('FLUSH TABLES;') || exit 2;
$RESULT->execute() || exit 3;
system($RSYNC.' '.$SRC_DIR.' '.$DST_DIR);

# Output
print stdout "n";
print stdout "-------------------------------------------------------------------------------n";
print stdout "Second RSyncn";
print stdout "-------------------------------------------------------------------------------n";
print stdout "n";

# Flush & RSync
$RESULT = $DB->prepare('FLUSH TABLES;');
$RESULT->execute();
system($RSYNC.' '.$SRC_DIR.' '.$DST_DIR);

# Output
print stdout "n";
print stdout "-------------------------------------------------------------------------------n";
print stdout "Final RSync with locked tablesn";
print stdout "-------------------------------------------------------------------------------n";
print stdout "n";

# Flush, Lock, RSync, Unlock
$RESULT = $DB->prepare('FLUSH TABLES WITH READ LOCK;');
$RESULT->execute();
system($RSYNC.' '.$SRC_DIR.' '.$DST_DIR);
$RESULT = $DB->prepare('UNLOCK TABLES;');
$RESULT->execute();

# Output
print stdout "n";
print stdout "-------------------------------------------------------------------------------n";
print stdout "MySQL Backup done.n";
print stdout "-------------------------------------------------------------------------------n";

# End of script
$DB->disconnect();
exit 0;

Fertig ist das MySQL Backup Script. Natürlich läßt sich das Script noch beliebig erweitern, so wäre z.B. die Übergabe der Daten per Kommandozeile schön und eine schönere Ausgabe wäre auch was feines. Aber das Script wurde bewußt klein gehalten, denn so ist es noch einfach lesbar und damit schnell angepaßt, wenn neue Aufgaben anstehen.

Wie immer natürlich der klare Hinweis: Weder für Konzept noch für Software wird eine Garantie übernommen.