MySQL Backup Strategien: LVM-Snapshots
Master – Slave mit LVM
Vorteile
- Restorezeit ist wesentlich geringer bei großen Datenbanken und vielen Indexen (abhängig von Lese- und Netzwerggeschwindigkeit).
- Geringe Ausfallzeit des Servers – keine Ausfallzeit des Arbeitsbetrieb
- Der Master kann unbeeinträchtigt seine Arbeit vortsetzen.
Nachteile
- “Eben mal” eine Tabelle oder Daten zurückspielen geht nicht, hierzu müssen wir ein kompletten Restore auf ein weiteres System durchführen, um dort mittels mysqldump die Daten zu extrahieren.
Szenario:
Wir benötigen mindestens 2 MySQL Instanzen die miteinander replizieren, damit verringern wir die Ausfallzeit auf 0. Also jedes beliebige Master – Slave oder Master – Master Szenaria, wobei wir am besten einen Slave für das Backup einrichten. Die Slave Instanz kann sich durchaus, wenn es Plattenplatz, CPU, RAM sowie die IO Werte zulassen, auf dem selben Host befinden. Eine Anleitung wie man eine Replikation aufsetzt findet man hier. Natürlich funktioniert das Ganze auch ohne Replikation, allerdings hat man dann, je nach Datenbank, eine Ausfallzeit von 10s – x Minuten. Wichtig, die my.cnf muss so angepasst werden, dass sich die Daten auf einem Logical Volumen (LVM) befinden. Darüber hinaus benötigen wir in der Volumengroup genügend Restplatz, damit uns der Snapshot nicht während des Backups vollläuft. Hier gibt es keine Faustformel das muss man einfach ausrechnen oder probieren.
In meinen Augen hat es sich relativ nützlich erwiesen die Konfiguration der MySQL Instanz wie folgt aufzugliedern:
- Das LV wird in ein Verzeichnis gemountet, beispielsweise /data.
- Unterhalb dieses Verzeichnisses werden unterschiedliche Verzeichnisse für binlogs, relaylogs, tmp, data sowie error und slowlog angelegt.
Das vereinfacht das Sichern insofern, dass wir hier nur bestimmte Verzeichnisse wegsichern müssen. Es ist auch keine schlechte Idee auf dem Slave das Binlog zu aktivieren und diese ebenfalls zu sichern (z.B. stündlich), damit wir ein sogenanntes Point in Time Recovery machen können (wird an einem späteren Zeitpunkt abgehandelt). Unsere my.cnf sieht dann an den entsprechenden Punkten wie folgt aus:
relay-log = /data/mysql-relaylog/relay.log relay-log-info-file = /data/mysql-relaylog/relay-log.info relay-log-index = /data/mysql-relaylog/relay-log.index log_error = /data/mysql-log/mysql.err log-slow-queries = /data/mysql-log/slow_queries.log log_bin = /data/mysql-binlog/mysql-bin.log innodb_log_group_home_dir = /data/mysql-data innodb_log_arch_dir = /data/mysql-data innodb_data_home_dir = /data/mysql-data datadir = /data/mysql-data
Für den Fall, dass wir einen weiteren Slave oder Master wieder herstellen müssen, benötigen wir noch die Postion und das Binlogfile, damit wir die Replikation an diesem Punkt wieder aufsetzen können. Im Prinzip gehen wir im weiteren wie folgt vor: wir stoppen den MySQL Dienst, dann ermitteln wir Binlog File und Position des Masters, legen einen Snapshot an, starten den MySQL Dienst und dann können wir das Backup vom Snapshot starten. Das Vorabscript für den Backup Dienst könnte wie folgt aussehen – bitte beachten, die Pfade und Variablen an die eigene Umgebung anpassen (Volumen und Volumengroupbezeichnungen zum Beispiel).
#!/bin/sh
# Groesse des Snapshots
GROESSE=20G
# Name des Snapshot
NAME=mysql-backup
# Geraetedateiname
# hier das LVS anpassen
GERAET=/dev/db/data
# Mount-Punkt
MOUNT=/backup
# Spiegelgeraet
SPIEGEL=/dev/db/mysql-backup
# Stoppe Datenbank
echo
echo "Shutting down lively database `date`"
echo
/etc/init.d/mysql stop
#gegebenen Fall diesen Wert anpassen, wenn die Datenbank länger braucht
sleep 3
# Erzeuge Restore Daten für Master - Master
name=$(tail -1 /data/mysql-binlog/mysql-bin.index)
size=$(stat -c '%s' ${name})
echo "binlog Position $name $size" > /data/mysql-data/db_restore.info
# Erzeuge Spiegel
lvcreate --size $GROESSE --snapshot --name $NAME $GERAET
ERGEBNIS1=`echo $?`
if [ $ERGEBNIS1 -ne 0 ]; then
echo
echo
echo " -- F E H L E R -- "
echo
echo " Starte Datenbank ohne Backup `date`"
echo
echo
/etc/init.d/mysql start
exit 22
fi
sleep 3
# starte Mysql wieder
echo
echo "Restarting lively database `date`"
echo
/etc/init.d/mysql start
# pruefe auf mount und mounte Spiegel oder exit
mount | grep /backup
ERGEBNIS2=`echo $?`
if [ $ERGEBNIS2 -eq 0 ]; then
echo
echo
echo " -- F E H L E R -- "
echo
echo " Der Mount besteht bereits"
echo
echo
exit 23
else
mount -o ro $SPIEGEL $MOUNT
fi
exit 0
Das Resultat an dieser Stelle sollte ein Read only eingemounteter Snapshot unter /backup sein. Dem Backup Programm weisen wir jetzt nur noch an, die Verzeichnisse:
/backup/mysql-data /backup/mysql-relaylog
zu sichern.
mehrmysql – sichern von Prozeduren, Funktionen und Triggern
MySQL 5 kommt mit vielen netten neuen Features, wie z.B. stored procedures oder triggers.
Kurz soll erläutert werden, wie man mittels mysqldump Prozeduren, Trigger etc. sichern kann.
mysqldump sichert via default alle trigger aber KEINE Prozeduren/Funktionen. Für das Verhalten von mysqldump sind 2 Optionen verantwortlich:
- –routines – Defaultwert ist FALSE
- –triggers – Defaultwert ist TRUE
Also wenn zusätzlich zu den Triggern, die Prozeduren mit gesichert werden sollen, muss die Option –routines dem Script etc. mit übergeben werden.
mysqldump <mysqldump optionen> --routines > outputfile.sql
Nehmen wir an, wir wollen nur die gespeicherte Prozeduren und Trigger sichern und nicht die MySQL-Tabellen und Daten (dies kann nützlich sein, wenn diese z.B. geändert werden oder diese auf einer anderen Datenbank importiert werden soll, der bereits die Daten, nicht aber die gespeicherten Prozeduren und / oder Trigger enthält), dann sollten wir folgendes tun:
mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt <database> > outputfile.sql
Dies sichert allerdings nur die Prozeduren,Trigger und Funktionen der angegebenen datenbank <database>. Um diese zu importieren benutzen wir folgenden Befehl:
mysql <database> < outputfile.sqlmehr
This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary
Als ich heute ein Backup einspielen wollte, um eine Replikation erneut aufzusetzen, stolperte ich über folgenden Fehler:
ERROR HY000: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
Wenn wir eine gespeicherte Funktion erstellen, müssen diese entweder als deterministisch deklariert oder es muss festgelegt werden, dass sie keine Daten modifizieren. Andernfalls kann sie für die Datenwiederherstellung oder Replikation Unsicherheiten bergen … so dass MySQL Handbuch. Zur Lockerung der Bedingungen für die Erstellung einer Funktion setzen wir die globale Systemvariable log_bin_trust_function_creators auf 1. Diese Variable hat den Standardwert 0.
> SET GLOBAL log_bin_trust_function_creators = 1;
Darüber hinaus können wir diese Variable genaus als Option beim Start des MySQL Servers übergeben –log-bin-trust-function-creators=1. Mehr Informationen gibt es [hier]. Und dann klappt es auch mit dem Einspielen des Backup 
…
Letzte Kommentare