Aus dem Rechenzentrum

Dieser Artikel ist der dritte Teil einer kleinen Gruppe von Artikeln über MySQL-Backups. Die weiteren Artikel werden hier verlinkt, sobald sie erscheinen.

Hier ist der erste Artikel: MySQL-Backups, aber wie?

Hier ist der zweite Artikel: MySQL Replikation

Hier ist der vierte und letzte Artikel: MySQL mit daemontools

Nachdem ich hier schon vor einer Weile beschrieben habe wie man eine MySQL-Replikation einrichtet und dabei am Rande erwähnt habe, daß man die durch die Replikation anfallenden Binary Logs beizeiten wegrotieren sollte, will ich heute darauf eingehen, wie man das bewerkstelligen kann.

Wer unbedingt möchte, kann sich sein eigenes Skript dazu schreiben, das in allen Slaves nachschaut, welche Binary Log Datei sie gerade lesen und dann auf dem Master alle Binary Log Dateien die älter sind als die älteste von einem Slave genutze entfernt. Diese Vorgehensweise funktioniert, ist aber ein bißchen aufwendiger. Vor allem gibt es ein paar Stolperfallen. So ist es mir zum Beispiel schonmal passiert, daß MySQL sich nach einem Update veranlaßt sah, den üblichen Dateinamen der Binary Logs zu verändern und diese sogar gleich in einem ganz anderen Ordner zu speichern (der Speicherort richtet sich anscheined danach, ob und wenn ja wo PID-Dateien abgelegt werden) — nach solchen Aktionen läuft so ein Skript dann natürlich gegen die Wand. Wobei man dieser Art von Veränderungen vorbeugen kann, indem man in seiner my.cnf explizit einträgt, wo Binary Logs abzulegen sind und wie sie zu heißen haben (mit --log-bin[=base_name]). Wichtig ist bei dieser Vorgehensweise vor allem, daß man keinen Fehler macht und keinem Slave ein Binary Log wegnimmt das er noch benötigt.

Und es gibt noch eine weitere Stolperfalle: Normalerweise heißen Binary Log Dateien z.B. mysql-bin.000008 oder nach dem Hostnamen (es sei denn, man hat mit --log-bin[=base_name] etwas anderes angeordnet). Wer den üblichen Ärger mit Software gewohnt ist, ahnt jetzt schon, was kommt. Die Dateien tragen eine Nummer im Namen die hochgezählt wird — und selbstverständlich auch irgendwann überlaufen wird, später oder eben auch mal früher. Nun könnte man meinen, man hätte ja die alten Binary Logs wegrotiert, da könnte MySQL ja die alten Namen quasi recyceln. Das wird es allerdings nicht tun, denn es pflegt in der Datei mysql-bin.index einen Index der bereits verwendeten Dateinamen (den Namen kann man wieder um mit --log-bin-index[=file_name] festschreiben). Das allerbeste an diesem Index ist, daß man ihn laut der Dokumentation von MySQL nicht editieren sollte während MySQL läuft. Und man sollte ihn editieren, denn wenn sein Inhalt nicht mit dem Zustand auf der Festplatte übereinstimmt, kann das bestimmte MySQL-Versionen verwirren, besonders wenn der unten beschriebene Befehl PURGE BINARY LOGS verwendet wird — neuere Versionen geben dann wenigstens noch eine Fehlermeldung aus.

Es geht aber auch einfacher: MySQL bietet für solche Fälle die Funktion PURGE BINARY LOGS, die hier dokumentiert ist. Sie entfernt nicht nur alte Binary Logs, sie aktualisiert auch den Index.

Hier hat man zwei Möglichkeiten, man kann entweder den Namen der ältesten Datei, die erhalten bleiben soll verwenden und z.B. befehlen PURGE BINARY LOG TO 'mysql-bin.001024'; oder man orientiert sich an einem Datum und befiehlt PURGE BINARY LOG BEFORE '2010-03-26 00:00:00';. Beides hat letztlich den gleichen Effekt, es ist eher eine Geschmacksfrage, welche Variante man nutzen möchte. Die erste hat den Vorteil, daß man das älteste noch benötigte Binary Log relativ bequem von einem Slave abfragen kann. Bei der zweiten Variante müßte man vom Slave abfragen wie viele Sekunden er hinter dem Master zurückliegt und dann den passenden Zeitpunkt berechnen.

Ein Vorteil von PURGE BINARY LOG ist in jedem Fall, daß es keinem aktiven Slave ein Binary Log wegnehmen wird. Sollte man das versuchen oder aus Versehen machen, dann wird MySQL den Fehler abfangen. Moneyquote: „This statement is safe to run while slaves are replicating. You need not stop them. If you have an active slave that currently is reading one of the log files you are trying to delete, this statement does nothing and fails with an error.“ Aber das gilt nur für Slaves, die zu diesem Zeitpunkt eine aktive Verbindung zum Master haben: „However, if a slave is not connected and you happen to purge one of the log files it has yet to read, the slave will be unable to replicate after it reconnects.“

Wer darauf vertraut, daß seine Slaves nicht allzuweit hinterherhinken, kann sogar die Variable expire_log_days in MySQL setzen, dann kümmert sich MySQL automatisch darum, daß die Binary Logs nach der angegebenen Anzahl von Tagen gelöscht werden. Sowas sollte man ggf. mit genügend Abstand einrichten und sich mit Nagios oder etwas ähnlichem einen Check bauen, der einen warnt wenn ein Slave dieser Grenze näher kommt.

Wir verwenden für diese Aufgabe hier ein einfaches Skript, das ich als Referenz hier verlinke.

Dieser Artikel ist der zweite Teil einer kleinen Gruppe von Artikeln über MySQL-Backups. Die weiteren Artikel werden hier verlinkt, sobald sie erscheinen:

Hier ist der erste Artikel: MySQL-Backups, aber wie?

Hier ist der dritte Artikel: Hinter der MySQL-Replikation aufräumen

Hier ist der vierte und letzte Artikel: MySQL mit daemontools

Wie im ersten Teil erläutert, nutzen wir gerne eine zweite MySQL-Instanz mit Replikation (einen sogenannten Slave), um MySQL-Backups zu realisieren. In diesem Teil geht es um die Einrichtung dieser Replikation.

Vorab ein paar Worte zu unserem MySQL-Setup:

• Wir verwenden fast immer die Einstellungsoption skip-networking mit der die gesamte Erreichbarkeit des MySQL-Datenbanksystems über das Netzwerk abgeschaltet wird, inklusive der Möglichkeit das Datenbanksystem über das Loopback-Interface des Localhost anzusprechen.
• Wenn die Verbindung über das Loopback-Interface aber benötigt wird, verwenden wir meist bind-address = 127.0.0.1, womit die Netzwerk-Erreichbarkeit auf die Loopback-Verbindung beschränkt wird.
• Wir betreiben den Slave häufig auf demselben Server auf welchem auch der Master läuft. Die zusätzliche Last hält sich in Grenzen und es gibt weniger potentielle Fehlerquellen. Prinzipiell kann ein Slave aber natürlich auch auf einer anderen Maschine laufen.
• Egal wo der Slave nun läuft, in jedem Fall muß man leider auf die Option skip-networking verzichten. Replikation kann leider nicht über Sockets erfolgen, sondern muß zwingend durch den TCP/IP Stack gehen. Bei Slaves auf demselben Server wie dem Master nutzen wir daher gezwungenermaßen bind-address = 127.0.0.1.

Nun zur Umsetzung:

Zunächst wird die /etc/my.cnf einfach kopiert und als /etc/my-slave.cnf gespeichert. In dieser Datei müssen dann einige Optionen angepaßt werden, die dafür sorgen daß die neue MySQL-Instanz (im weiteren Slave genannt) auf einen anderen Datenbestand zugreift und ein anderes Socket bereitstellt. Das wesentliche in Auszügen:

[mysqld]
port = 3307
socket = /var/lib/mysql-slave/mysql.sock
datadir = /var/lib/mysql-slave
server-id = 2


Auf den folgenden Teil kann man verzichten, wenn man MySQL nicht per init-Skript startet sondern mit den daemontools (so machen wir es fast immer):


[mysqld_safe]
err-log=/var/log/mysql/mysql-slave.log
pid-file=/var/run/mysql/mysq-slave.pid


Verzichten heißt in diesem Fall ggf. auskommentieren. Die ursprüngliche Form sollte man auf keinen Fall stehen lassen.

In einzelnen Punkten kann das Setup mal etwas abweichen. Wir versuchen uns meist an der Default-my.cnf des System zu orientieren, dementsprechend können sich die Pfade leicht ändern.

Zum Port ist zu sagen, daß er recht beliebig ist. Prinzipiell kann jeder Port genommen werden, den die bereits vorhandene MySQL-Instanz (der Master) nicht nutzt (bzw. den kein anderer Dienst nutzt).

Die Server-ID ist ebenfalls recht beliebig, es muß lediglich eine andere sein als beim Master: „For each server, you should pick a unique positive integer in the range from 1 to 232 – 1, and each ID must be different from every other ID.“ Quelle: MySQL Doku

Die /etc/my.cnf benötigt weniger Anpassungen:


[mysqld]
log-bin=mysql-bin
server-id = 1


Das binary log benötigt man bei Replikation immer, man kommt nicht drum herum. Hier bei sei angemerkt, daß man es unter Umständen später mit Logrotation oder ähnlichen Mechanismen wegräumen kann. Das ist aber nicht ganz einfach, einerseits muß man darauf achten dem Master nicht das aktive Log wegzurotieren, andererseits könnte man die alten binary logs unter Umständen benötigen, falls man in der Zukunft weitere Slaves anlegt. (Diese kann man aber auch von einem späteren Stand des binary logs beginnen.)

Nun muß man dafür sorgen, daß Master und Slave auch beide gestartet werden. Wie man hier vorgeht hängt sehr davon ab, wie man seine MySQL-Instanzen startet, also ob man z.B. selber was skriptet, init verwendet oder lieber auf daemontools oder runit setzt. Wir bevorzugen daemontools.

Bei daemontools muß man lediglich ein run-Skript schreiben, mit dem der Slave mit der /etc/my-slave.cnf gestartet wird. Im einfachsten Fall sieht das so aus:


#!/bin/sh
exec /usr/bin/mysqld_safe --defaults-file=/etc/my-slave.cnf


Wer auf init setzt, muß entweder ein neues init-Skript nur für den Slave schreiben oder das vorhandene so anpassen, daß es beide Instanzen startet. Je nach verwendeter Distribution kann das unterschiedlich aufwendig werden.

An dieser Stelle sollte man den Master schonmal neustarten, damit er die Änderungen in der /etc/my.cnf übernimmt und vor allem anfängt, das binary log zu führen.

Jetzt muß man noch dafür sorgen, daß der Slave auch einen eigenen Datenbestand hat. Wie man dabei vorgeht hängt stark davon ab, wie viel einmalige Downtime man sich beim Einrichten des Slaves erlauben kann und wie groß der Datenbestand ist.

Falls der Datenbestand in der MySQL-Instanz bereits groß ist und eine lange Downtime vermieden werden soll, kann zunächst mit rsync eine Kopie des MySQL-Verzeichnisses eingefertigt werden. Diese Kopie ist zwar nicht gut genug um davon die neue Instanz zu starten, aber da meist ein Großteil des Datenbestands selten oder gar nicht verändert wird, kann man hier schon viel Vorarbeit leisten und so später Zeit sparen.

# mkdir /var/lib/mysql-slave
# rsync -vaH /var/lib/mysql/ /var/lib/mysql-slave/

Wie immer bei rsync ist der Slash am Ende des Pfades jeweils wichtig. Hier wird rsync befohlen, nicht den Ordner /var/lib/mysql/ selbst, sondern seinen Inhalt in den Ordner /var/lib/mysql-slave/ zu kopieren.

Wenn eine spürbare Verlangsamung des Systems vermieden werden soll, kann man das übrigens auch mit nice oder bei neueren Kerneln ionice machen:

# nice -n19 rsync -vaH /var/lib/mysql/ /var/lib/mysql-slave/

bzw.:

# ionice -c3 rsync -vaH /var/lib/mysql/ /var/lib/mysql-slave/

Wie gesagt ist der so duplizierte Datenbestand nicht konsistent! Man kann den Slave noch nicht damit starten. Um dies nun möglich zu machen muß man den Master dazu bringen alle Daten konsistent auf die Platte zu schreiben und ihn kurzzeitig mit einem READ LOCK sperren und dann während die Sperre besteht erneut rsync verwenden. Bei der Gelegenheit kann man gleich die Zugriffsrechte für den Slave einräumen.

Man verbindet sich also mit dem Master und befiehlt:

mysql> GRANT REPLICATION SLAVE ON *.* TO slave@'localhost' IDENTIFIED BY 'PASSWORD';
mysql>FLUSH TABLES WITH READ LOCK;
mysql>SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 2730869 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)


(Das Passwort kann man selbstverständlich selbst wählen.)

Die Ausgabe von SHOW MASTER STATUS sollte man sich notieren, wir benötigen sie gleich, genau genommen den Dateinamen und die Position (den Offset) im binary log, in unserem Bespiel ist das die 2730869. (Eventuell benötigt man sie später nochmal, wenn man weitere Slaves einrichten sollte, die auch ab dieser Position die Replikation beginnen sollen. In diesem Fall darf man später auch das binary log nicht wegrotieren. Insgesamt scheint dieser Anwendungsfall aber eher selten zu sein.)

Nun muß man den Master kurz anhalten und die Datenverzeichnisse synchronisieren. Hierbei sollte man ausnahmsweise mysqladmin benutzen, damit die Sperre erhalten bleibt. (Normalerweise werden Sperren beim Beenden aufgehoben.)

Update:

# mysqladmin shutdown
rsync -vaH /var/lib/mysql/ /var/lib/mysql-slave/

Es muß mysqladmin shutdown verwendet werden, mysqladmin stop war ein Tippfehler. /Update.

Sobald das erledigt ist, kann man den Master wieder starten und die Sperre aufheben, letzteres mit: mysql>UNLOCK TABLES;

(Wer später weitere Slaves aufsetzen möchte, die an dieser Stelle im Log mit der Replikation beginnen, sollte sich jetzt ein Backup von /var/lib/mysql-slave ziehen, z.B. einen Tarball.)

Jetzt kann der Slave gefahrlos gestartet werden, denn sein Datenverzeichnis enthält konsistente Daten. Wenn er sauber startet, kann man sich auch gleich mit ihm verbinden.

Achtung: Wenn man Master- und Slave-Instanz auf dem selben Computer am Laufen hat, gilt es bei der Verbindung zur Slave-Instanz folgendes zu beachten: Man kann nicht wie gewohnt einfach mysql auf der Kommandozeile ausführen, da das eine Verbindung zur Master-Instanz öffnen würde. Auch die Angabe des Ports mit mysql -P 3307 hilft nicht weiter, da MySQL in diesem Fall, trotz der expliziten Portangabe eine Verbindung zur Master-Instanz öffnen würde (dafür mal ein verdientes #fail an die Adresse der Programmierer). Das gleiche ärgerliche Verhalten würde auch bei mysql -P 3307 -H localhost auftreten.
Damit das funktioniert muß man mysql -P 3307 -H 127.0.0.1 (IPv4) oder mysql -P -H ::1 (IPv6) benutzen. Eine wie ich finde noch elegantere Lösung ist mysql -S /var/lib/mysql-slave/mysql.sock. Wozu den Networkstack bemühen, wenn man schon eine Shell offen hat?

Nun muß dem Slave noch mitgeteilt werden, daß er Änderungen an seinem Datenbestand vom Master replizieren sollt.

mysql> CHANGE MASTER TO
-> MASTER_HOST='localhost',
-> MASTER_USER='slave',
-> MASTER_PASSWORD='PASSWORD.',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS='2730869';
mysql> START SLAVE;

Der Slave beginnt jetzt ausgehend von der angegebenen Position (2730869) im binary log seinen Datenbestand zu synchronisieren. Den Fortschritt kann man mit mysql> SHOW SLAVE STATUS\G am besten einsehen. Die letzte Position ist dabei das entscheidende. Seconds_Behind_Master gibt an, ob der Slave mit dem Master synchron ist oder nicht. Steht hier der Wert NULL, ist der Slave angehalten und synchronisiert sich nicht mit dem Master, steht hier ingegen eine Zahl größer 0, gibt sie an wie viele Sekunden der Slave hinter dem Master zurückliegt (diese Zahl sollte schnell kleiner werden), steht hier genau 0 dann ist der Slave synchron zum Master.

Dieser Artikel ist der erste Teil einer kleinen Gruppe von Artikeln über MySQL-Backups. Die weiteren Artikel werden hier verlinkt, sobald sie erscheinen.

Hier ist der zweite Artikel: MySQL Replikation

Hier ist der dritte Artikel: Hinter der MySQL-Replikation aufräumen

Hier ist der vierte und letzte Artikel: MySQL mit daemontools

Wenn es um Backups geht, sind Datenbanken immer nochmal gesondert zu beachten. Zwar kann man die vom Datenbanksystem auf die Platte geschriebenen Daten mit den üblichen Backup-Tools sichern, aber in den meisten Fällen wird man so kein konsistentes Backup der im Datenbanksystem gespeicherten Daten erhalten. Dabei ist es auch relativ egal, ob man nun ein Backup aller im Datenbanksystem gehaltenen Daten haben möchte, oder nur von einzelnen Datenbanken oder Tabellen. In jedem Fall hat mein einige Hürden zu nehmen.

Man braucht also ein Extra-Tool und nicht selten muß man sich die Lösung dann noch zusammenskripten. Im Planet MySQL Blog findet sich eine schöne Auflistung der gängigsten Lösungsmöglichkeiten:

There are a couple of options available to get consistent backups from MySQL.

1. Use mysqldump with FLUSH TABLES WITH READ LOCK
2. Use a slave with STOP SLAVE and your favourite backup tool.
3. For innodb, use the commerical backup tool ibbackup
4. Use LVM (Logical Volume Manager) snapshots with FLUSH TABLES WITH READ LOCK
5. Shutdown the database.

Man hat also immerhin verschiedene Optionen, unter denen man wählen kann. (Diese Liste erhebt keinen Anspruch auf Vollständigkeit.) Nebenbei wird aber offensichtlich, daß die Frage nach dem Backup bei der Konzeptionierung und Entwicklung von MySQL keine große Rolle gespielt hat – wie bei leider allzuvielen anderen Softwareprojekten auch, darunter viele andere Datenbanken.

Die Wahl die wir für unsere Anwendungsfälle treffen wird von folgenden Überlegungen geleitet:

• #1 bedeutet dem Datenbanksystem wird befohlen, alle Tabellen konsistent auf die Platte zu schrieben und sich dann zu sperren. Dann zieht man mit mysqldump alle Daten aus dem Datenbanksystem und hebt zuletzt die Sperre wieder auf. Solange die Sperre besteht ist kein schreibender Zugriff auf die Daten möglich. Soetwas kann man folglich nur dann machen, wenn man ein regelmäßiges Wartungsfenster hat, in dem die Applikation die auf die Datenbank zugreift nicht (oder nur-lesend) benutzt wird. Bei einem Buchhaltungssystem mag das noch praktikabel sein, aber bei Internet-Diensten, die keine Ladenöffnungszeiten kennen kommt diese Vorgehensweise eher selten in Frage. Unabhängig davon tritt hier das Problem auf, daß das Sichern der Daten mit mysqldump Zeit beansprucht. Hier müßte also zusätzlich darauf geachtet werden, daß das Wartungsfenster eingehalten wird.
• Für #5 gilt im Grunde das gleiche. Ein Anhalten des Datenbanksystems hat für die es benutzenden Applikationen sogar noch gravierendere Konsequenzen als eine Sperre, sie können nichtmal mehr lesend auf die Daten zugreifen. Hier würde das Backup nicht mit mysqldump geschrieben, sondern mit einem anderen Tool welches auf Dateisystemebene arbeitet. Auf Unix-basierten Systemen gibt es da eine sehr große Auswahl, die von tar über dump bis zu amanda reicht, um nur ein paar zu nennen.
• #3 kommt für uns nicht in Frage, weil wir keine kommerziellen Tools benutzen wollen. Das ist sowohl eine philosophische Frage als auch eine praktische, denn zu einem kommerziellen Tool könnten wir unseren Kunden nicht den Support bieten, den wir bei freier und quelloffener Software leisten können.
• #4 bedeutet auch, daß die Datenbank in einen konsistenten Zustand gebracht und dann gesperrt wird, allerdings nur solange bis ein LVM-Snapshot erstellt wurde. Das kann zwar auch etwas dauern (aber nur wenn die Hardware oder Software an ihre Grenzen stoßen, LVM-Snapshots gehen in der Regel schnell), geht aber in jedem Fall schneller als Methode #1. Hier gibt es aus unserer Sicht neben der (wenngleich kürzeren) Sperrzeit noch zwei Nachteile:
  1. Zum einen benötigt man bereits eine LVM-Umgebung. Sicherlich kann man auch unter einem laufenden System eine einrichten, aber das ist knifflig, kann schief gehen, dauert und geht nicht gänzlich ohne Downtime mindestens des Datenbanksystems. Nun mag man sich angewöhnt haben unter jedem System LVM einzurichten, um genau solchen Eventualitäten vorzubeugen. Es mag aber Situationen geben in denen man darauf verzichtet, weil die zusätzliche Komplexität unnötig ist oder gar störend wäre, z.B. wenn man ein System von ISCSI bootet und man statt mit LVM zu arbeiten einfach weitere ISCSI-Targets einbinden könnte.
  2. Die Wiederherstellung aus dem Backup ist zum Teil sehr aufwendig. Häufig wird man einen separaten MySQL-Dienst starten wollen oder müssen, über den man auf den Datenbestand im Snapshot zugreift. Wenn man innodb verwendet bleibt einem im Grunde nichts anderes übrig, da innodb einen großen Container für alle gemeinsam verwalteten Datenbanken verwendet, aus dem sich einzelne Tabellen nicht ohne weiteres extrahieren lassen.

Bleibt somit noch Methode #2. Hier wird ein zweiter MySQL-Dienst eingerichtet und als Slave konfiguriert, so daß er über die Replikationsfunktion von MySQL den Datenbestand des ersten spiegelt und sich aktuell hält. Für Backups wird dann auf dem Slave die Replikation vorübergehend angehalten. Ab diesem Punkt kann im Grunde jede beliebige Backup-Methode verwendet werden, man kann den Slave sogar für das Backup runterfahren. Uns genügt es aber, bei angehaltener Replikation alle Datenbanken und Tabellen des Slave einzeln mit mysqldump auszulesen und an einen Ort zu schreiben, an dem unser normales Backup sie findet und sichert.

Ein Slave der nur für Backups verwendet wird, erzeugt nicht sonderlich viel Last auf einem System und kann daher prinzipiell auch auf demselben Rechner laufen wie der Master, wenn man das denn möchte. Wenn man so vorgeht, sollte man jedoch bedenken, daß man einen zweiten MySQL-Server betreibt, der auch entsprechend zu sichern ist. Wenn der Slave nur für Backups dienen soll, muß man Nutzerzugriff darauf unbedingt verhindern, weil vom Nutzer gemachte Änderungen sonst durch die Replikation überschrieben werden können usw. Damit das möglich ist, kann die mysql-Datenbank mit den Nutzerdaten auf dem Slave vom Master abweichen. In so einem Fall empfiehlt es sich, diese eine Datenbank separat vom Master zu sichern (hierbei kann man aber getrost mit einem READ LOCK arbeiten, da diese Datenbank relativ klein ist und sich schnell sichern läßt.)

Wie man die Replikation einstellt, wird im nächsten Artikel beschrieben.