Butschek.de

Warnung: Diese Seite ist eine statische Kopie eines früher betriebenen Blogs. Es findet keine Aktualisierung mehr statt. Kommentare und die Suche funktionieren nicht.

Linux, Server & Co

Perl MySQL Backup

9 comments

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.

Written by Michael Butschek

April 21st, 2009 at 8:49 pm

Posted in

9 Responses to 'Perl MySQL Backup'

Subscribe to comments with RSS or TrackBack to 'Perl MySQL Backup'.

  1. Hallo,

    eine tolle Idee. Genau das was ich suche um eine Downtime von mittlerweile über 20 Minuten in den frühen Morgenstunden zu umgehen. Ich werde es mir bei Zeiten einmal anschauen und umsetzten.

    Mark

    20 Mai 09 at 12:54

  2. Ich möchte nochmal einen Satz unterstreichen, der im Text nicht so deutlich rüber kommt: Das Script ist nur brauchbar, wenn als Storage Engine MyISAM zum Einsatz kommt!

    MyISAM speichert jede Tabelle in einer Datei, daher geht das mit dem RSync und Lock noch, wenn keine Mega-Tabellen dabei sind. Bei InnoDB dagegen werden alle Daten des gesamten Datenbankservers in einer Datei gespeichert, womit der Rsync bei JEDEM Update-Query alle Daten erneut kopieren muss – der Lock würde so viel zu lange dauern.

    Mike

    20 Mai 09 at 15:21

  3. Moin Moin …

    .. genau einen solchen Ansatz habe ich gebraucht für unser Projekt, das bei einem Datenbankausfall fatale folgen hätte und daher stündlich gesichert wird. Bisher hatte ich das auch immer im laufenden Betrieb mit PHP gemacht, hatte aber zu den Zeiten immer einen erhöhten LOAD auf dem Server. Ich habe dies nun mit deinem Ansatz geändert und beobachtet. Ich konnte keinen wesentliche Erhöhung des LOAD nun feststellen, und das Backup geht nun auch innerhalb von Sekunden, ohne das das Projekt beeinträchtigt wird. Super Sache … Ich sag ganz herzlich DANKE

    Gruß Jürgen

    Jürgen Lerch

    9 Aug 09 at 15:27

  4. Wir betreiben MySQL auf Solaris und ZFS, welches sich für Backupszenarien sehr eignet. In diesem Fall beschränkt sich ein Backup auf das Locken der DBs, zfs snapshot und zum Schluss Unlock. Das ganze dauert ein paar wenige Sekunden!

    Hier meine abgewandelte Scriptvariante:

    #!/usr/bin/perl
    use DBI;
    use POSIX qw(strftime);

    ##
    ## CONFIGURATION
    ##

    ## WARNING: USE ONLY WITH MyISAM!

    my $MYSQL_HOST = „localhost“;
    my $MYSQL_USER = „backup“;
    my $MYSQL_PASS = „XXXXXXXX“;
    my $ZFS_DATASET = „datapool/export/db/myisam“;

    ##
    ## END OF CONFIGURATION
    ##

    my $SNAP_CMD = „/usr/sbin/zfs snapshot“;
    # define a meaningful snapshot name including date and time
    my $SNAP_SUFFIX = strftime „mysqlbackup-%Y-%m-%d-%H%M“, localtime;

    # 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 „MYSQL BINARY BACKUPn“;
    print stdout „===============================================================================n“;
    print stdout „n“;
    print stdout „——————————————————————————-n“;
    print stdout „Flush and lock tables, take ZFS snapshot, unlock tablesn“;
    print stdout „——————————————————————————-n“;
    print stdout „n“;

    # Flush, Lock, ZFS snapshot, Unlock
    $RESULT = $DB->prepare(‚FLUSH TABLES WITH READ LOCK;‘);
    $RESULT->execute();
    system($SNAP_CMD.‘ ‚.$ZFS_DATASET.’@‘.$SNAP_SUFFIX);
    $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();

    Philipp Tobler

    18 Jan 10 at 16:31

  5. Spontaner Gedanke bzgl. der „nur MyISAM“-Problematik:

    InnoDB unterstützt „per Table“-Tablespaces“:

    http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html

    Damit könnte man das Skript auch mit InnoDB einsetzen.

    Zumal wird die zentrale ibdata-Datei nicht unnötig aufgebläht, wenn man Dateien löscht.

    Anton

    15 Feb 10 at 18:07

  6. Danke für den Tipp. Möchte die Backup-Performance noch erhöhen und werde es mit rsync mal probieren.

    Eine weitere Alternative um lock Probleme etc. zu umgehen:
    /etc/init.d/mysql stop
    cp /var/lib/mysql/* -R /var/backups/mysql/
    /etc/init.d/mysql stop

    deutlich schneller als ein sql dump.

    Michael

    11 Jan 12 at 02:24

  7. Die „Alternative“ ist keine echte Alternative. Bei geplanten Wartungen, zu denen der Server aus dem Produktivbetrieb genommen wird, mache ich es auch so. Aber bei einem Produktivserver geht das nicht.

    Nehmen wir eine halbwegs aktuelle SATA2 Platte, die schafft um die 70 MByte/s. Das Kopieren von 10 Gigabyte Daten dauert dann selbst unterschiedlichen Quell- und Zielplatten im Optimalfall 2,5 Minuten.

    Stell dir vor, zehntausende Webseiten eines großen Hosters würden jede Nacht für 2,5 Minuten nur „Error: Can’t connect to MySQL“ statt der Webseiten anzeigen. Alle Blogs, alle Typo3-Seiten, alle Portale, Scripte, also einfach fast alles.

    Stell dir vor, große Webauftritte (nennen wir mal Facebook, Twitter, heise.de & Co) würden 1x pro Nacht für 2,5 Minuten „Error: Can’t connect to MySQL“ anzeigen. Wie peinlich wäre das wohl für die Betreiber.

    Außerdem sind vernünftige SLA damit nicht erreichbar. Bei einer Backupzeit von 2,5 Minuten ergibt das selbst bei kurzen Monaten eine Gesamtausfallzeit von über 60 Minuten, im Jahr hätten wir so über 15 Stunden Ausfall wegen dem MySQL-Backup. Die Verfügbarkeit würde also nur wegen dem Backup auf 99,8% sinken. Damit wären weder 99,9% noch 99,99% möglich, was viele Hoster versprechen und viele Kunden fordern.

    Ein Rsync kopiert nur veränderte Daten und ist damit im Schnitt deutlich schneller als ein cp und dank dem Lock steht die Datenbank für diese kurze Dauer nur zum Schreiben nicht bereit, kann aber weiterhin alle Daten anzeigen. Damit haben wir keine Fehlermeldungen. So muss es sein :-)

    Natürlich – so der jetzt vermutlich kommende Einwand – sind die Daten eines kleinen Privatservers nicht so groß und daher geht es schneller und stellt kein Problem dar. In diesem Fall kann man die stop-cp-start Methode durchaus anwenden, dennoch finde ich es auch bei kleinen Seiten unprofessionell, wenn sie einem Besucher plötzlich kurz eine Fehlerseite anzeigen, weil das Backup läuft. Dann würde ich doch eher den MySQL Dump empfehlen.

    Michael Butschek

    11 Jan 12 at 08:16

  8. PS: Ist es euch schon mal passiert, dass ihr so einen Text (wie ich eben im Kommentar) geschrieben habt und beim Absenden kam dann eine Fehlermeldung und der Text war weg? Wenn ja versteht ihr, warum ich es nicht mag, die Datenbank zu stoppen und dem Anwender damit eine Fehlermeldung zu präsentieren :-)

    Michael Butschek

    11 Jan 12 at 08:17

  9. Super sache das nach sowas habe ich gesucht. Für alle die genau wie ich das ganze auf einen anderen Server als Backup sichern wollen für die muss

    my $DST_DIR ungefähr so aussehen

    my $DST_DIR = ‚Serverip::FTP‘;

    Serverip steht für die ip des Servers auf den ihr sichern wollt versteht sich ^^
    Das FTP am ende erkläre ich gleich noch.

    Rsync kann auch einen daemon bereitstellen den ihr auf dem Empfängerserver auf den das backup soll aktivieren müsst. Dazu erstellt ihr unter /etc ein rsyncd.conf

    Diese sieht bei mir wie folgt aus

    gid = mysql
    uid = mysql
    read only = false
    use chroot = no
    transfer logging = true
    log format = %h %o %f %l %b
    log file = /var/log/rsyncd.log
    hosts allow = *.meinehomepage.com oder eine ip im aufbau 127.0.0.1
    hosts deny = * (um alle Hosts zu verbieten)

    [FTP]
    path = /var/lib/mysql/einesqldatenbank/
    comment = An Example

    Hier seht ihr auch wieder in den eckigen klammern das FTP das zeigt dem Server das verzeichnis an wo er hinsichern soll wenn im script am Ende nach :: das FTP kommt. Ihr könnt natürlich auch ein 2tes verzeichnis nutzen zum beispiel

    [FTP]
    path = /var/lib/mysql/einesqldatenbank/
    comment = An Example
    [Home]
    path = /var/lib/mysql/einezweitedatenbank/
    comment = An Example

    Wollt ihr jetzt auf die 2te Datenbank sichern die Zeile in der Backup.pl (so habe ich sie genannt) wäre dann

    my $DST_DIR = ‚Serverip::Home‘;

    beachtet bitte das dass ganze Casesensitive ist.

    Nun noch auf dem Backupserver mit rsync –daemon den daemon starten und das skript sollte funktionieren.

    Damit kann man auch die Passwort abfrage umgehen die sonst erzwungenermaßen kommen würde wenn man nach extern sichert.

    Wer will kann in der crontab dann auch noch nen job reinmachen das dass ganze alle 5 minuten gesichert wird.

    Das würde dann zum Beispiel so aussehen:

    */5 * * * * root /home/backup/backup.pl

    Dieses Skript ruft alle fünf Minuten die Datei /home/backup/backup.pl auf.

    Patrick

    26 Feb 12 at 20:40

Leave a Reply