Datenbanken sind beim Backup besonders kritisch. W\u00e4hrend 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\u00e4ren.<\/p>\n
Die Bin\u00e4rdateien der Datenbank sollte man im laufenden Betrieb nicht wegsichern, da hier aufgrund des Caches der Datenbank und st\u00e4ndigen Schreibzugriffen Datenverlust und Inkonsistenz drohen.<\/p>\n
Viele Serveradmins bevorzugen daher den SQL-Dump: Man erzeugt eine Textdatei, die alle SQL-Kommandos enth\u00e4lt, mit der man die Datenbank komplett wieder herstellen kann.<\/p>\n
Ich pers\u00f6nlich 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\u00fctzt oder die Zeichenkodierung hat sich ge\u00e4ndert und zerst\u00f6rt dadurch alle Umlaute und Sonderzeichen.<\/p>\n
Abgesehen von diesen Problemen st\u00f6rt aber auch die relativ lange Zeit der Wiederherstellung: Das L\u00f6sen der \u00fcblichen Probleme kostet dabei die meiste Zeit, doch auch das Einspielen von 160 GByte SQL-Kommandos ben\u00f6tigt sein Zeit.<\/p>\n
Ich pers\u00f6nlich bevorzuge daher ein Backup der Bin\u00e4rdaten der Datenbank. Solange mein MySQL-Server auf aktuellem Stand ist (Upgrade erledigt ich ja regelm\u00e4\u00dfig), kann ich nach einem Crash jederzeit ein Neusystem aufsetzen, die Bin\u00e4rdateien zur\u00fcckspielen, Datenbank starten und schon l\u00e4uft\u00a0die Datenbank wieder – inklusive aller Benutzer und Berechtigungen.<\/p>\n
Ein Problem ergibt sich hier nur: Wie schon gesagt unterliegen die Bin\u00e4rdateien der Kontrolle des Datenbankservers und stehen st\u00e4ndig unter Schreibzugriff. Ohne die Hilfe der Datenbank ist eine Sicherung also nicht m\u00f6glich. Aber solange man als Storage Engine MyISAM einsetzt, l\u00e4\u00dft sich ganz einfach das l\u00f6sen:<\/p>\n
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.<\/p>\n
Doch auch beim lokalen Kopieren sto\u00dfen wir an die oben genannten Probleme. Doch lokal dauert das Kopieren nur sehr kurze Zeit, daher l\u00e4\u00dft sich das in den Griff kriegen<\/p>\n
Sendet man MySQL das Kommando FLUSH TABLES; so werden alle Caches geleert, also alle noch ausstehenden Daten wirklich in die Bin\u00e4rdateien geschrieben. Jedoch w\u00fcrde MySQL w\u00e4hrend dem Kopieren schon weiter zwischenspeichern und damit den Effekt zunichte machen.<\/p>\n
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\u00fchren kann, bis das Kommando UNLOCK TABLES; gesendet wird. Alle anderen m\u00fcssen also mit dem Schreiben abwarten, bis die Backupsoftware fertig mit den Lesen der Dateien ist.<\/p>\n
Soweit klappt das schonmal, nun m\u00fcssen wir nur sehen, dass der Kopiervorgang so schnell wie m\u00f6glich geht, denn wenn die Datenbank 2 Minuten vollst\u00e4ndig gelockt ist, ist das f\u00fcr den Betrieb eine starke Beeintr\u00e4chtigung.<\/p>\n
RSync erlaubt das Kopieren (besser: synchronisieren) von Daten. Dabei wird vor dem Kopiervorgang gepr\u00fcft, welche Dateien sich seit der letzten Ausf\u00fchrung ge\u00e4ndert haben und nur diese werden \u00fcbertragen.<\/p>\n
Beim ersten Aufruf wird nat\u00fcrlich alles \u00fcbertragen, da am Zielort ja noch keine Daten sind. Ruft man RSync danach nochmal mit den gleichen Parametern auf, werden nur noch die \u00c4nderungen seit der letzten Kopie \u00fcbertragen.<\/p>\n
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.<\/p>\n
Zuerst m\u00fcssen wir sehen, dass wir eine initiale Kopie der Datenbank erhalten:<\/p>\n
Da das kopieren vermutlich l\u00e4nger dauert, wiederholen wir diesen Vorgang erneut. Dabei werden dann nur noch die wenigen ge\u00e4nderten Daten \u00fcbertragen und das RSync-Kommando ist schon nach einem Sekundenbruchteil fertig.<\/p>\n
Nun liegt die Aktualit\u00e4t unsere Kopie nur noch einen Sekundenbruchteil hinter der originalen Datenbank. Da ein erneutes RSync nun nur noch minimale Daten \u00fcbertragen w\u00fcrde, k\u00f6nnen wie hierbei die Datenbank locken und damit daf\u00fcr sorgen, dass keine neuen Daten w\u00e4hrend dem letzten (finalen) Durchlauf geschrieben werden k\u00f6nnen.<\/p>\n
Damit haben wir unsere 1:1 Kopie der Datenbank ohne dass w\u00e4hremd des Kopierens ein Schreibzugriff die Daten h\u00e4tte vernichten k\u00f6nnen und dabei war die Datenbank nur f\u00fcr einen sehr kurzen Moment gelockt. Dies d\u00fcrfte keine Applikation \u00fcberhaupt registriert haben. Wobei das nat\u00fcrlich nur zutrifft, wenn wir die Tabellen nun auch gleich wieder freigeben:<\/p>\n
In Kommandos sieht das ganze dann so aus:<\/p>\n
MySQL: FLUSH TABLES;\r\n Shell: rsync -av \/var\/lib\/mysql\/ \/var\/backups\/mysql\/\r\n\r\n MySQL: FLUSH TABLES;\r\n Shell: rsync -av \/var\/lib\/mysql\/ \/var\/backups\/mysql\/\r\n\r\n MySQL: FLUSH TABLES WITH READ LOCK;\r\n Shell: rsync -av \/var\/lib\/mysql\/ \/var\/backups\/mysql\/\r\n MySQL: UNLOCK TABLES;<\/pre>\nEin Problem muss dabei aber noch gel\u00f6st werden:<\/p>\n
Per Shell ist das schwer zu l\u00f6sen, 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\u00f6nnen wir hier also nicht arbeiten.<\/p>\n
Backupscript mit Perl<\/h2>\n
Perl kann mit DBI (aus dem cpan Archiv) MySQL-Verbindungen aufbauen und sie \u00fcber mehrere externe Shell-Kommandos hinweg offen halten. Also \u00f6ffnen wir zuerst die Verbindung:<\/p>\n
#!\/usr\/bin\/perl\r\n use DBI;\r\n my @DSN = ('DBI:mysql:host=localhost', 'backup', 'XXXXXXXX');\r\n my $DB = DBI->connect(@DSN) || exit 1;<\/pre>\nNun kommt die oben genannte Logik zum Zug: Flush, Rsync<\/p>\n
$RESULT = $DB->prepare('FLUSH TABLES;') || exit 2;\r\n $RESULT->execute() || exit 3;\r\n system('rsync -a \/var\/lib\/mysql\/ \/var\/backups\/mysql');<\/pre>\nDiesen Part nochmals ausf\u00fchren, um beim zweiten Lauf alle \u00c4nderungen seit der (langsamen) ersten Kopie nachzuziehen, Dann geht es weiter mit Flush & Lock und RSync:<\/p>\n
$RESULT = $DB->prepare('FLUSH TABLES WITH READ LOCK;') || exit 2;\r\n $RESULT->execute() || exit 3;\r\n system('rsync -a \/var\/lib\/mysql\/ \/var\/backups\/mysql');<\/pre>\nAm Ende das Freigeben des Locks und damit dann auch schon die Beendigung der MySQL-Verbindung:<\/p>\n
$RESULT = $DB->prepare('UNLOCK TABLES;');\r\n $RESULT->execute();\r\n $DB->disconnect();<\/pre>\nMehr ist nicht wirklich n\u00f6tig f\u00fcr ein sauberes, schnelles Backup ohne viel Beeintr\u00e4chtigung der Dienste und ohne hohe Last auf dem Server, wie es ein MySQL-Dump gerne mal erzeugt.<\/p>\n
Das fertige Script<\/h2>\n
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\u00e4lt man das Endergebnis:<\/p>\n
#!\/usr\/bin\/perl\r\nuse DBI;\r\n\r\n##\r\n## CONFIGURATION\r\n##\r\n\r\n## WARNING: USE ONLY WITH MyISAM!\r\n\r\nmy $SRC_DIR = \"\/var\/lib\/mysql\/\";\r\nmy $DST_DIR = \"\/var\/backups\/mysql\/\";\r\n\r\nmy $MYSQL_HOST = \"localhost\";\r\nmy $MYSQL_USER = \"backup\";\r\nmy $MYSQL_PASS = \"XXXXXXXX\";\r\n\r\n##\r\n## END OF CONFIGURATION\r\n##\r\n\r\nmy $RSYNC = \"\/usr\/bin\/rsync -av --exclude=datenbank\/nichtgesicherte_tabellen.*\";\r\n\r\n# Open Database\r\nmy @DSN = ('DBI:mysql:host='.$MYSQL_HOST, $MYSQL_USER , $MYSQL_PASS);\r\nmy $DB = DBI->connect(@DSN) || exit 1;\r\n\r\n# Output\r\nprint stdout \"n\";\r\nprint stdout \"===============================================================================n\";\r\nprint stdout \"MYSQL BINARY BACKUPn\";\r\nprint stdout \"===============================================================================n\";\r\nprint stdout \"n\";\r\nprint stdout \"-------------------------------------------------------------------------------n\";\r\nprint stdout \"Initial RSyncn\";\r\nprint stdout \"-------------------------------------------------------------------------------n\";\r\nprint stdout \"n\";\r\n\r\n# Flush & RSync\r\n$RESULT = $DB->prepare('FLUSH TABLES;') || exit 2;\r\n$RESULT->execute() || exit 3;\r\nsystem($RSYNC.' '.$SRC_DIR.' '.$DST_DIR);\r\n\r\n# Output\r\nprint stdout \"n\";\r\nprint stdout \"-------------------------------------------------------------------------------n\";\r\nprint stdout \"Second RSyncn\";\r\nprint stdout \"-------------------------------------------------------------------------------n\";\r\nprint stdout \"n\";\r\n\r\n# Flush & RSync\r\n$RESULT = $DB->prepare('FLUSH TABLES;');\r\n$RESULT->execute();\r\nsystem($RSYNC.' '.$SRC_DIR.' '.$DST_DIR);\r\n\r\n# Output\r\nprint stdout \"n\";\r\nprint stdout \"-------------------------------------------------------------------------------n\";\r\nprint stdout \"Final RSync with locked tablesn\";\r\nprint stdout \"-------------------------------------------------------------------------------n\";\r\nprint stdout \"n\";\r\n\r\n# Flush, Lock, RSync, Unlock\r\n$RESULT = $DB->prepare('FLUSH TABLES WITH READ LOCK;');\r\n$RESULT->execute();\r\nsystem($RSYNC.' '.$SRC_DIR.' '.$DST_DIR);\r\n$RESULT = $DB->prepare('UNLOCK TABLES;');\r\n$RESULT->execute();\r\n\r\n# Output\r\nprint stdout \"n\";\r\nprint stdout \"-------------------------------------------------------------------------------n\";\r\nprint stdout \"MySQL Backup done.n\";\r\nprint stdout \"-------------------------------------------------------------------------------n\";\r\n\r\n# End of script\r\n$DB->disconnect();\r\nexit 0;<\/pre>\nFertig ist das MySQL Backup Script. Nat\u00fcrlich l\u00e4\u00dft sich das Script noch beliebig erweitern, so w\u00e4re z.B. die \u00dcbergabe der Daten per Kommandozeile sch\u00f6n und eine sch\u00f6nere Ausgabe w\u00e4re auch was feines. Aber das Script wurde bewu\u00dft klein gehalten, denn so ist es noch einfach lesbar und damit schnell angepa\u00dft, wenn neue Aufgaben anstehen.<\/p>\n
Wie immer nat\u00fcrlich der klare Hinweis: Weder f\u00fcr Konzept noch f\u00fcr Software wird eine Garantie \u00fcbernommen.<\/p>\n","protected":false},"excerpt":{"rendered":"
Einleitung Datenbanken sind beim Backup besonders kritisch. W\u00e4hrend 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\u00e4ren. Die Bin\u00e4rdateien der Datenbank sollte man im laufenden Betrieb nicht wegsichern, da hier aufgrund des Caches […]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":252,"menu_order":0,"comment_status":"open","ping_status":"open","template":"","meta":[],"_links":{"self":[{"href":"https:\/\/www.butschek.de\/wp-json\/wp\/v2\/pages\/228"}],"collection":[{"href":"https:\/\/www.butschek.de\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.butschek.de\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.butschek.de\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.butschek.de\/wp-json\/wp\/v2\/comments?post=228"}],"version-history":[{"count":1,"href":"https:\/\/www.butschek.de\/wp-json\/wp\/v2\/pages\/228\/revisions"}],"predecessor-version":[{"id":989,"href":"https:\/\/www.butschek.de\/wp-json\/wp\/v2\/pages\/228\/revisions\/989"}],"up":[{"embeddable":true,"href":"https:\/\/www.butschek.de\/wp-json\/wp\/v2\/pages\/252"}],"wp:attachment":[{"href":"https:\/\/www.butschek.de\/wp-json\/wp\/v2\/media?parent=228"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}