MySQL Backup mit mysqldump oder mysqlhotcopy

2009-10-13 - kostaki 9 Kommentare »

Morguefile by imelenchon

Backups sind toll, Backups braucht man, Backups sind wichtig! So hört man es meist schallen wenn es ums Thema geht. Spätestens wenn man Herr des eigenen Servers ist muss man sich mit dem erstellen von Backups auseinander setzen. Hoffentlich bevor die Platte versagt oder das Rechenzentrum abbrennt. In diesem Artikel möchte ich auf das automatische erstellen von MySQL Datenbankbackups als Serveradministrator eingehen. Man sollte also Zugriff auf die Konsole des Servers haben.

Mein „Backup-System“ besteht aus einem einfachen Shell Script das alle vorhandenen Datenbanken eines Servers ausliest, sie mit Hilfe von mysqlhotcopy oder mysqldump exportiert und dann gepackt archiviert. Einzelne Datenbanken können geblacklistet werden oder wenn man möchte kann man die Blacklist recht simpel in eine Whitelist umbauen, wobei ich das nicht mag, da man bei jeder neuen Datenbank ans aufnehmen ins Backup denken muss. Das Script ruft man nun regelmäßig auf und kann somit den kompletten MySQL Datenbanken Pool sichern.

Mein Testsystem ist ein Hetzner EQ4 auf dem Debian Lenny läuft, was auch sonst. Da ich ihn allein benutze brauche ich mir keine Gedanken um die rechtliche Lage beim sichern machen. Wer aber für Fremde Leute hostet sollte wenigstens sicher stellen das diese über das Backup informiert sind oder diese eben nicht mit sichern. Eine Kopie der Datenbank ist bei manchen Webseiten Gold Wert und nichts anderes ist ein Backup. Ich benutze für Datenbanken die ich nicht sichern möchte einen Prefix beim Namen und filtere diesen Prefix im Script (z.B. dev, tmp). Als erstes muss man sich für eine MySQL Backupmethode entscheiden. An dieser Stelle nur ein kurzer Vergleich, da die beiden Tools recht unterschiedlich arbeiten und ich hier nicht den Rahmen sprengen möchte. Vielleicht greife ich das in einem anderen Artikel noch mal auf.

Backups mit mysqldump

Mysqldump erstellt eine .sql Datei in der alle Befehle vorhanden sind um die komplette Datenbank wieder herstellen zu können. Dabei werden diese Befehle bei der Wiederherstellung einfach nach und nach ausgeführt. Es ist also keine Kopie der Datenbank, sondern eher ein Export aller Daten in einem für MySQL Server verständlichen Format. Zum sammeln der Daten führt mysqldump SQL Befehle aus und so sieht man zu den Zeiten des Backups eine recht massive Zunahme der Auslastung des MySQL Servers. Außerdem können bei großen Tabellen einzelne Befehle von mysqldump im Slowlog landen. Vorteilhaft ist das auch InnoDB Tabellen gesichert werden können, was bei mysqlhotcopy nicht der Fall ist. Wer also InnoDB und MyISAM Tabellen mischt, für den ist mysqldump geeignet. Das gleiche gilt natürlich bei reiner MyISAM Nutzung.

Anmerkung: Wer nur InnoDB Tabellen einsetzt sollte sich etwas anderes zulegen. Wenn man sich aktiv für InnoDB Tabellen entschieden hat, sollte man sich mit dem Thema Backup genauer beschäftigen, da man hier viel richtig oder falsch machen kann.

Backups mit mysqlhotcopy

Das zweite mitgelieferte Backup Tool ist mysqlhotcopy. Bei diesem Script werden die wirklichen Dateien im data Directory des MySQL Servers kopiert. Da die zeitraubenden SQL Befehle wegfallen ist dieses Tool schneller als mysqldump, was besonders bei großen Datenbanken auffällt. Der Hacken ist das es nur MyISAM und ARCHIVE Tabellen sichern kann. Wer aber nur diese Tabellen Typen einsetzt ist mit mysqlhotcopy gut bedient. Die Backups werden verglichen mit mysqldump größer. Wenn man die kompletten Indices mit sichert, sogar erheblich größer. Da man diese aber nach dem zurück spielen des Backups wieder herstellen kann, lasse ich sie weg. Eine weitere nette Eigenschaft von mysqlhotcopy ist die Fähigkeit Backups direkt mit SCP auf einem anderen Rechner zu erstellen.

Was nimmt man jetzt wenn man nur MyISAM Tabellen hat?

Wenn man wirklich nur MyISAM Tabellen hat ist es egal welche Methode man nimmt. Ab einer Datenbank Größe von 1 Gigabyte würde ich aber definitiv mysqlhotcopy einsetzen, da es einfach schneller ist. Die Tabellen der Datenbanken werden bei beiden Tools gelockt und damit ist der Zugriff auf diese in der Zeit des Backups nicht gegeben. Bei großen Datenbanken können da schon mal ein paar Minuten vergehen. Man sollte das Script also nicht zu Peekzeiten ausführen. Bei kleineren Datenbanken fällt das aber überhaupt nicht auf. Am besten selber testen.

Hier nun also das mysqldump Script. Die Unterschiede zum mysqlhotcopy Script sind recht marginal. Bis vor kurzem hatte ich sie auch noch zusammen in einem Script, aber da ich eh nur das eine oder das andere nehme habe ich sie getrennt. Beide Scripte in einer kommentierten (englisch) Version gibt es weiter unten zum Download.

#!/bin/sh
BACKUPDIR="/backups/db";
DBFILTER="(information_schema|dev|tmp)";
MYSQLDUMP="$(which mysqldump) --opt";
DIRNAME=`date +%Y-%m`;

MYSQL=$(which mysql);
TAR=$(which tar);
GZIP=$(which gzip);
NICE=$(which nice);
EGREP=$(which egrep);

mkdir -p "$BACKUPDIR/$DIRNAME";

DBS=$($MYSQL -Bse "show databases");
for db in $DBS
do
	if !(echo $db | $EGREP $DBFILTER > /dev/null);
	then
		FILENAME="$db-`date +%Y_%m_%d_%H_%M_%S`.sql.gz";
$NICE -n 20 $MYSQLDUMP $db | $NICE -n 20 $GZIP -c > "$BACKUPDIR/$DIRNAME/$FILENAME";
	fi
done

Erklärung:

2: Hier werden die Backups schlussendlich abgelegt
3: Das ist die Regex für egrep zum filtern von bestimmten Datenbank Namen. Wer sich mit Regulären Ausdrücken auskennt kann hier natürlich auch komplexere Filter bauen.
4: Hier wird der mysqldump Aufruf erstellt. An dieser Stelle kann man ihn an die eigenen Bedürfnissen anpassen.
5: Das Verzeichnis wird unterhalb von Backupdir angelegt und die Backups landen dann hier. Im Beispiel werden Backups also in /backups/db/2009-10/datenbank.sql.gz gepeichert.
7-11: Finden der benötigten Tools.
15: Auslesen der MySQL Datenbanken
18: Filtern der Datenbanknamen die in Zeile 3 definiert wurden. Wenn man den ! weg nimmt werden nur die im Filter angegebene Datenbanken gesichert.
20: Der Filename für das Backup.
21: Hier wird das eigentliche Backup ausgeführt. Durch Nice wird die Priorität herunter gesetzt. Es kann auch Sinn machen die Priorität hoch zu setzen um das Backup schneller fertig zu haben. Spielt aber alles nur eine Rolle wenn der Server wirklich ausgelastet ist und die Priorisierung überhaupt greift. Das Backup das mit mysqldump erstellt wird, wird sofort per gzip gepackt.

Die Authentifizierung beim MySQL Server wird aus der .my.cnf im Homedir des Backupusers genommen. Die Datei liegt also z.B. hier /root/.my.cnf wenn man die Backups als root ausführt. Man sollte beachten das diese Datei wirklich nur von dem Backupuser gelesen werden kann, in diesem Fall also 600 und root:root. Der Aufbau ist dann in etwa so:

[mysql]
user=mysqluser
password=blablubpw

[mysqldump]
user=mysqluser
password=blablubpw

[mysqlhotcopy]
user=mysqluser
password=blablubpw

Für jedes Dienstprogramm gibt es also einen eigenen Block. Man kann die Authentifizierung aber auch direkt an die MySQL Befehle in Zeile 3 und 7 anfügen (-umysqluser -pmysqlpw). Wenn man sie aber in der .my.cnf zu stehen hat, tauchen sie nicht z.B. bei ps in der Befehlsausgabe mit auf. Es ist auch praktischer die Daten nur an einer Stelle stehen zu haben, falls man sie mal ändern will/muss. Ist Geschmackssache. Der verwendete MySQL User muss natürlich die nötigen Rechte auf die Datenbanken haben um die Backups erstellen zu können.

Mögliche Optimierungen und Anpassungen

Es kann sein das es Sinn macht das erstellen des Backups vom packen zu trennen. Habe ich noch nicht getestet ob das Backup damit vielleicht schnell läuft.
Da mittlerweile die meisten Server mehr als einen CPU Kern besitzen, macht es Sinn gzip durch einen Multicore fähigen Packer zu ersetzen. (z.B. pbzip2) Welcher Packer benutzt wird hängt natürlich auch davon ab ob man lieber kleine Backups haben möchte oder möglichst schnelle. Theoretisch sollte man sogar komplett auf einen Packer verzichten, da er eine weitere Fehlerebene hinzufügt die das Backup beschädigen könnte. Pbzip2 ist derzeit in Debian Lenny leider nur mit Version 1.0.2 vertreten, aber in Squeeze (Testing) ist die aktuelle 1.0.5. Version enthalten.
Wenn ihr FTP Backupspace habt, bietet es sich natürlich an die Backups nach dem Erstellen sofort dort hin zu kopieren, da es nicht sehr sicher ist die Backups nur auf dem eigentlichen Server zu haben. Man sollte sich also auf jeden Fall eine Lösung dafür einfallen lassen. Persönlich benutze ich den Hetzner Backup FTP und einen eigenen RSYNC Server und das Monatsbackup lade ich zusätzlich noch auf meinen Lokalen Rechner herunter.

Hier das Script mit mysqlhotcopy. Bei mysqlhotcopy werden die Parameter --noindices und -q benutzt. Noindices kopiert die Indices nicht mit und -q unterdrückt die Debugging Ausgabe.

#!/bin/sh
BACKUPDIR="/backups/db";
DBFILTER="(information_schema|dev|tmp)";
MYSQLHOTCOPY="$(which mysqlhotcopy) --noindices -q";
DIRNAME=`date +%Y-%m`;
TEMPSAVEDIR="/tmp/`date +%Y-%m`";

MYSQL=$(which mysql);
TAR=$(which tar);
NICE=$(which nice);
EGREP=$(which egrep);

mkdir -p "$BACKUPDIR/$DIRNAME";

DBS=$($MYSQL -Bse "show databases");
for db in $DBS
do
	if !(echo $db | $EGREP $DBFILTER > /dev/null);
	then
		FILENAME="$db-`date +%Y_%m_%d_%H_%M_%S`.tar.gz";
		mkdir -p "$TEMPSAVEDIR";
		$NICE -n 20 $MYSQLHOTCOPY $db "$TEMPSAVEDIR";
		$NICE -n 20 $TAR -cPzf "$BACKUPDIR/$DIRNAME/$FILENAME" "$TEMPSAVEDIR";
		rm -rf "$TEMPSAVEDIR";
	fi
done

Unterschiede zur mysqldump Version:

6: Bei mysqlhotcopy wird ein Temp Verzeichnis gebraucht in das das Backup zuerst kopiert wird. Nachdem kopieren wird der Inhalt des Verzeichnisses gepackt. Bei größeren Datenbanken sollte man beachten das auf der Temp Partition genügend Platz vorhanden ist oder das Temp Verzeichnis wo anders hinlegen!

Wiederherstellen einer Datenbank

Nachdem man nun Backups hat sollte man auch wissen was man mit ihnen anfangen kann. Es empfiehlt sich außerdem die Backups regelmäßig zu testen um sicher zustellen das alles in Ordnung ist. Ein defektes Backup ist genauso gut wie kein Backup. Ich lade sie mir dazu herunter und teste sie lokal. Des weiteren brauche ich auch ab und an aktuelle Versionen für meinen Dev Server und das ist dann natürlich auch ein guter Test. Man sollte den Test auf jeden Fall nicht auf dem Live System durchführen. Nur zu schnell vergisst man einen anderen Datenbanknamen zu verwenden und schon ist die Live Version der Datenbank überschrieben. Wenigstens hat man ja nun ein Backup

Die Wiederherstellung des mysqldumps ist sehr einfach. Die Datei entpacken und die enthaltenen MySQL Befehle per mysql ausführen.

$ cd /backups/db/2009-10/
$ gzip -d backup.sql.gz
$ mysql datenbankname < /backups/db/2009-10/backup.sql

Auch hier gilt wenn man die Benutzerdaten in der .my.cnf zu stehen hat kann man sie weglassen, wenn nicht muss man sie angeben (-umysqluser -pmysqlpw). Das Leerzeichen zwischen -u/-p und dem jeweiligen Text kann man weglassen. Das ist also ausnahmsweise kein Schreibfehler.

Bei den mysqlhotcopy Backups hat man ja die Daten Dateien. Diese muss man nur an die richtige Stelle kopieren, die richtigen Zugriffsrechte setzen und dann die Indices wieder herstellen. Für diesen Ablauf sollte man den MySQL Server anhalten. Wenn es wirklich brennt ist das meist eh schon gegeben. Die MySQL Datenbanken liegen bei Debian Standardmäßig unter /var/lib/mysql/. Wenn ihr sie dort nicht findet, schaut in eurer Config (/etc/mysql/my.cnf => datadir) nach.

$ /etc/init.d/mysql stop
$ cp -R /entpacktes/backup/verzeichnis /var/lib/mysql/datenbank
$ chown -R mysql:mysql /var/lib/mysql/datenbank
$ find /var/lib/mysql/datenbank -type d -exec chmod 700 {} +
$ find /var/lib/mysql/datenbank -type f -exec chmod 660 {} +
$ cd /var/lib/mysql/datenbank
$ myisamchk -rq *.MYI
$ /etc/init.d/mysql start

Mit myisamchk -rq werden die Indices wieder hergestellt. Das ist nötig wenn beim erstellen der Backups --noindices benutzt wurde. -r übernimmt das reparieren, -q steht für Quick und überspringt die Datenfiles (es werden also nur die Indices repariert). Das funktioniert nur wenn die Daten okay sind. Bei manchen MySQL Versionen scheint das Reparieren der Indices mit myisamchk nicht zu funktionieren. Dann kann man sich aber mit REPAIR TABLE helfen.

Wichtig sind die richtigen Zugriffsrechte! Der Datenbankordner muss 700 haben und die Dateien im Ordner müssen 660 sein. Gehören muss alles mysql:mysql. Wenn ihr also einen „errno: 13“ Fehler bekommt, heißt das das irgend etwas daran falsch ist.

Backupstrategie

Ansprechen möchte ich noch das Backups mindestens täglich erstellt werden und das man sie an mindestens 2 physisch von einander getrennten Orten aufbewahren sollte. Also am besten auf 2 unterschiedlichen Rechnern. In diesem Beispiel werden alte Backups auch nicht gelöscht. Darum muss man sich also regelmäßig selbst kümmern oder das Script anpassen, sonst läuft die Backuppartition irgend wann voll. Im schlimmsten Fall stürzt dann der Server ab. Im besten Fall werden einfach keine Backups mehr angelegt. Beides ist nicht wünschenswert. Zu einer guten Backupstrategie gehört natürlich noch mehr. Sucht am besten mal nach dem Begriff Backupstrategie.

Das tägliche Backup kann man mit Hilfe diese Crontabeintrages erstellen. Es wird täglich um 6 Uhr 10 ausgeführt. Wie schon erwähnt sollte man die Zeit selbst nach dem eigenen Lastaufkommen bestimmen. Vielleicht ist genau dies die Zeit an der man die meisten User auf seiner Website hat, dann sollte man das Backup natürlich auf eine andere Zeit legen.

10 6 * * * /pfadzumscript/backup_mysql.sh

Wenn man Debug Informationen vom Script ausgeben lässt bekommt man diese per lokaler Mail zugestellt oder man leitet sie in eine Datei um. Die Datei Umleitung sieht dann wie folgt aus.

10 6 * * * /pfadzumscript/backup_mysql.sh &> /pfadzumlog/mysqlbackup.log

Debian Root