MySQL-Datenbanken sichern
Backup-Strategien

Data & StorageNetzwerk-ManagementNetzwerkeStorage

Eine MySQL-Datenbank lässt sich auf viele Arten sichern. Nutzer kleiner Datenbanken verwenden einfache kurze Befehle. In großen Umgebungen konfiguriert der Administrator einen MySQL-Server für ein Live-Backup, das bei einem Ausfall einspringt.

Datensicherungen

MySQL-Datenbanken sichern

Eine MySQL-Datenbank nimmt alle möglichen Daten auf. Darin lassen sich Artikel genauso verwalten wie Daten für Werbeeinblendungen. Mit regelmäßigen Datensicherungen sind Sie bei einem Rechnerausfall auf der sicheren Seite. MySQL (www.mysql.com) enthält für die Datensicherung eine ganze Reihe an Werkzeugen, die sich für verschiedene Anwendungsbereiche eignen. Der Anwender kann zwischen schnellen und sicheren Möglichkeiten wählen. MySQL erlaubt es auch, die Tabellen ohne direkten Zugriff auf den MySQL-Daemon zu sichern. Zudem lassen sich solche Datenbankserver redundant als Failover- Lösung konfigurieren. Dabei findet ein Live-Update statt, das der primäre Datenbankserver an die Slaves verteilt.

Tabelle in Datei kopieren
Wenn die Datenbank aus wenigen Tabellen besteht und zudem ein Shell-Zugang zum MySQL-Server vorhanden ist, sind die Daten mit der Option »select into outfile« einfach zu sichern. Mit »select into outfile« lässt sich eine Tabelle respektive ein Teil einer Tabelle auf die Schnelle in eine Datei schreiben. Das select-Statement ist dabei in vollem Umfang nutzbar, um dem entsprechend die sicherungsbedürftigen Dateien in die Auswahl einzubeziehen.

Select-Befehl
Eine ganze Tabelle speichert man ganz einfach mit dem Aufruf »select *«. Der komplette Aufruf von »select into outfile« zeigt folgendes Listing: select * into outfile ‘NameDerDatei.txt’ fields terminated by ‘,’ enclosed by ‘”‘ lines terminated by ‘n’ from NameDerTabelle; »NameDerDatei.txt« steht für die Datei, in die Sie die Tabelle sicheren. Die Anweisung »fields terminated by« enthält dasjenige Zeichen, mit dem die einzelnen Spaltenwerte in der Datei getrennt sind. Ein gängiges Zeichen dafür ist ein Komma. Im Beispiel kommt eine per Komma separierte Liste heraus, die einzelne Werte in Hochkommata einschließt.


Sicherheitsmechanismus

MySQL-Datenbanken sichern

Zu beachten ist, dass die Ausgabedatei nicht vorhanden sein darf. Dies nutzt MySQL als einfachen Sicherheitsmechanismus, der das irrtümliche Überschreiben einer Sicherungskopie verhindert. Zudem muss der Account, der »select into outfile« auf MySQL anwendet, Filerechte auf dem MySQL-Server-Host besitzen. Der größte Vorteil der Option »select into outfile« ist die hohe Geschwindigkeit beim Sichern einzelner Tabellen. Denselben Eifer legt auch das Gegenstück von »select into outfile« an den Tag: »load date infile«. Mit dieser Option lässt sich eine Datei laden, die mit »select into outfile « gesichert wurde. Das Kommando »load data infile« erhält unter anderem die einzulesende Datei als Parameter:

load data info ‘NameDerDatei’
[replace | ignore] into table NameDerTabelle

Im Wesentlichen lädt es den Inhalt der Datei in die Tabelle mit dem angegebenen Namen. Wenn die Kombination aus »select into outfile« und »load data info« für ein Tabellen-Backup zum Einsatz kommt, muss vor dem »load data« die Zieltabelle geleert sein. Alternativ funktioniert auch der ignore-Parameter. Dieser verwirft Zeilen mit doppelten Primary- Keys, so dass selbst dann keine Fehler auftreten, wenn die Zieltabelle bereits gleiche Daten enthält. Allerdings bringen »select into outfile« und »load data infile« auch einen Nachteil mit, der es für die meisten Anwender zu sehr beschränkt. Denn es lassen sich immer nur einzelne Tabelle laden oder speichern. Dadurch muss der Anwender bei einer neu angelegten Tabelle immer auch die Sicherungs- Skripte prüfen, damit die neue Tabelle auch ins Backup einfließt. Ideal ist »select into outfile« hingegen, wenn nur eine einzelne Tabelle zu sichern ist.


Mehrere Tabellen sichern

MySQL-Datenbanken sichern

Der große Vorteil von »backup table« verglichen mit »select into outfile« ist der Umstand, dass es mehrere Tabellen gleichzeitig sichert. Es verlangt jedoch wie die select-Funktion den Shell-Zugriff auf den MySQL-Server. »Backup table« funktioniert ähnlich wie »select into outfile«, ist aber vom Befehl her klarer strukturiert: Der Anwender gibt direkt im SQL-Statement an, was er sichern möchte. »Backup table» erhält als Parameter eine oder mehrere Tabellen sowie den Namen einer Datei, in die der Anwender die Tabellen sichern möchte: backup table Tabelle1 [Weitere] to ‘NameDerDatei’ Um mehrere Tabellen gleichzeitig zu sichern, geben Sie diese mit Kommata getrennt von einander an.

Datenbankbackup komplett
Das Programm »Mysqldump» vereinfacht Sicherungskopien, da es eine komplette Datenbank oder auch mehrere Datenbanken gleichzeitig vom MySQL-Server auf Festplatte schreibt. Praktischerweise braucht der Anwender dazu keinen Shell-Account. Allerdings muss der Port für MySQL frei zugänglich sein, sonst kann sich Mysqldump nicht mit dem Server verbinden. Der kurze und einfache Aufruf von Mysqldump lautet wie folgt:

mysqldump [OPTIONEN] database [Tabellenliste]

»[Tabellenliste]« entspricht den Tabellen der Datenbank, die in die Datensicherung gehören. Falls dies alle Tabellen sind, lässt der Nutzer die Angabe weg. Der obige Befehl gibt alle Daten auf den Bildschirm aus. Um die Datenbank in eine Datei zu speichern, reicht die Umleitung auf eine nicht existierende Datei: mysqldump database> sicherung.sql Die Sicherungskopie enthält sowohl Tabellenstruktur als auch die dazu gehörigen Daten. Um diese wieder aus der Sicherungskopie zu laden, verwenden Sie: mysql

Sicherung ohne lokalen Zugriff
Wenn auf dem MySQL-Server kein lokaler Zugriff möglich ist, lassen Sie Mysqldump die Sicherungsdaten von dem entfernten Server abholen. Dazu existiert die Option »-host=RemoteHost«. Allerdings erzeugt diese besonders bei großen Datenbanken jede Menge Traffic ? das größte Problem bei Sicherungen mit Mysqldump übers Netz. Für eine lokale Sicherung ist das Programm hingegen das optimale Werkzeug. Es bietet einen gehörigen Optionsumfang, mit dem sich das Programm optimal auf die lokalen Bedürfnisse einstellen lässt. Die Liste finden Sie auf der Projekt-Website www.mysql.com/doc/de/mysqldump.html.


Backup manuell durchführen

MySQL-Datenbanken sichern

Wer den MySQL-Server für die Zeit des Backups anhalten kann, sichert mit einer extrem einfache Methode. Dazu stoppen Sie den MySQL-Daemon, kopieren alle Datenbankdateien und fahren den Daemon wieder hoch. Die kopierten Dateien lassen sich dann sicher per SSH auf einen anderen Rechner transferieren. Damit dies funktioniert, müssen Sie alle FRM-, MYD- und MYI-Dateien aus dem Datenbankverzeichnis kopieren. Um die Daten zu restaurieren, folgt die Prozedur dem umgekehrten Weg: Daemon anhalten, Datenbankdateien zurück kopieren, Server wieder starten. Dieser Weg ist jedem Einsteiger klar und zudem schön handlich. Da Sie dabei jedoch den MySQL-Daemon anhalten, ist die Methode nur bei kleinen Datenbanken, für Websites mit wenig Traffic oder Intranet-Sites mit klaren Wartungszeiten praktikabel. Ein Vorteil: Die ganze Prozedur ist bereits in das komfortable Perl-Skript »mysqlhotcopy« verpackt (dev.mysql.com/doc/mysql/de/mysqlhotcopy.html). Mysqlhotcopy hat aber seinerseits eine Einschränkung, denn das Skript funktioniert nur mit ISAM- und My- ISAM-Datenbanken.


Sicherung per PHP-Skript

MySQL-Datenbanken sichern

MySQL-Daten ganz nach persönlichen Vorlieben zu sichern, erleichtert eine Skriptsprache wie PHP. Die nachfolgende Lösung mit PHP Syncscript erwartet, dass bereits zwei funktionstüchtige MySQLServer bereitstehen und zudem einer der Rechner als Apache-Webserver mit PHPUnterstützung fungiert. Für PHP muss auch der MySQL-Support installier
t sein, was im Rahmen der PHP-Distribution standardmäßig der Fall ist. Weiterhin muss der Sicherungsrechner Zugriff auf die MySQL-Ports haben. Normalerweise verwendet MySQL die Ports 3306 und 6000 für UDP und TCP. Gelingt der Zugriff später nicht, blockt die Firewall wahrscheinlich die Requests ab. Wichtig für die korrekte Datensicherung mit dem Beispielskript ist vor allem, wie die Daten auf Server organisiert sind. Wenn sich vorhandene Datensätze ständig ändern, sind naturgemäß mehr Daten zu sichern, als wenn nur selten neue Daten hinzukommen. Im ersten Fall müssen sowohl die neuen ? seit der letzten Sicherung hinzugekommenen Daten ? als auch die während dieses Zeitraums geänderten Daten in die Sicherung einfließen. Im zweiten Fall reicht es hingegen vollkommen aus, einfach nur die jeweils neuen Datensätze zu sichern. Da sich das Skript per Browser oder »WGET« starten lassen soll, muss es sich um eine normale PHP-Seite mit allen für HTML benötigten Anweisungen handeln. Das ist relativ einfach zu erreichen:



Diese HTML-Minimalseite reicht vollkommen aus. Das PHP-Statement ruft zunächst die Funktion »Update« auf, die dann nacheinander alle weiteren Update- Funktionen startet.

function UpdateNew()
{
print “Begin Update New
“;
}

Zunächst gibt die Funktion eine kurze Meldung aus. Das ist vor allem dann sehr praktisch, wenn der Administrator das Update-Skript per Webbrowser von einem anderen Rechner aufruft. Dadurch lässt sich sofort erkennen, welche Schritte bereits durchgelaufen sind. Nun soll das Skript eine Verbindung zum entfernten Datenbankserver aufbauen. Der entsprechende Befehl hierfür lautet:

$cR =mysql_connect(“remote_server_here”, “read_username_here”, “password_here”);

Dazu ist als »remote_server_here« der Name im Netzwerk oder die entsprechende IP-Adresse des Servers anzugeben. Normalerweise sind solche Rechner respektive MySQL selbst auf solchen Rechnern so konfiguriert, dass es unterschiedliche Usernamen und Passwörter für Lese- und Schreibzugriffe gibt. Da das Skript lesend auf diesen Rechner zugreift, muss bei »mysql_connect« der Account-Name des leseberechtigten Users und das zugehörige Passwort (»read_username_here« beziehungsweise »password_here)« stehen.

if( ! $cR)
{
print ‘No remote connection’;
return;
}

Da zumindest zu Beginn der Implementierung gerne mal der Verbindungsaufbau scheitert, testet das Skript den Return- Wert der connect-Funktion. Sollte die Verbindung nicht zustande kommen, beendet sich das Skript an dieser Stelle. Nun wird ebenfalls mit einer Fehlerbehandlung die Verbindung zum lokalen Rechner aufgebaut:

@LI:$cL = mysql_connect( “local_server_here”,”write_username_here”,”password_here”);
if( ! $cL)
{
print ‘no local connection.’;
return;
}

Der lokale Rechner speichert die kopierten Daten. Auch dieser muss mit Rechnername oder IP-Adresse sowie Usernamen und dazugehörigem Passwort im Skript stehen. Hier muss es allerdings ein Benutzer mit Schreibrechten sein.


Neue Datensätze ermitteln

MySQL-Datenbanken sichern

Die beiden Verbindungen zu den beteiligten Datenbankservern sind aktiv; jetzt ist der trickreiche Teil an der Reihe. Da das erste Beispielskript nur neue Datensätze kopiert, muss es im ersten Schritt ermitteln, welche Datensätze denn überhaupt neu sind. Dazu nutzt das Skript die fortlaufende Nummer oder eindeutige ID der Tabellenzeilen innerhalb der Tabelle. Es wird davon ausgegangen, dass die höchste ID der höchsten Nummer der Datensätze entspricht:

$qs = “select id from table_name_here order by id desc limit 1”;
$rLocal = mysql_db_query ( “database_name_here”, $qs, $cL);
if( ! $rLocal)
{
print “query error: $qs
“;
return;
}

Im Beispiel erfragt das SQL-Query zunächst die höchste ID aus der Tabelle. Der User muss nur »table_name_here« und »database_name_here» anpassen. Die Variable »table_name_here« steht dabei für die Tabelle innerhalb der Datenbank und »database_name_here« ist der Name der Datenbank, auf die innerhalb des MySQL-Servers zugegriffen wird. Auch bei dieser Abfrage empfiehlt sich eine Fehlerbehandlung, die darauf achtet, ob der Query tatsächlich beantwortet wurde respektive überhaupt beantwortet werden konnte. Kommt keine Antwort, gibt das Skript den verwendeten Query-String aus. So lässt sich leicht im Browser prüfen, wo diese spezielle Abfrage das Problem verursacht hat.

$row = mysql_fetch_row( $rLocal);
$num = $row[0];

Nun wird die mit Hilfe des Queries ermittelte Nummer per PHP ausgelesen. Da hier nur ein einzelner Wert interessant ist, reicht »mysql_fetch_row()« völlig aus. Die Nummer steht dann an der ersten Zelle im gelieferten Array und wird mit »[0]« indiziert und ausgelesen. Die höchste lokale Datensatznummer ist somit bekannt und es folgt eine ähnliche Abfrage für den entfernten Server.

$qsr = “select * from table_name_here where id>$num order by id”;
$rRemote = mysql_db_query( “database_name_here”, $qsr, $cR);
if( ! $rRemote)
{
print “query error: $qsr
“;
return;
}

Bei dieser zweiten Abfrage interessieren nur noch die Nummern, die größer als die zuvor ermittelte sind. Dementsprechend ist die Abfrage auch leicht verändert. Die Bedingung ist dabei hinter dem where-Statement formuliert. Die gelieferte Nummer steht dann wieder im Query- Resultat, das sich mit PHP auslesen lässt: $count = mysql_num_rows( $rRemote); Nun steht also fest, wie viele Datensätze kopiert werden müssen. Das Kopieren erfolgt dann im nächsten Schritt:

for( $i=0; $i<$count; $i++)
{
print “Updating: $i …
“;
$row = mysql_fetch_row( $rRemote);
$qsLocal = “insert into table_name_here(fields_here) values(values_here)”;
$rLocal = mysql_db_query(“database_name_here”, $qsLocal,$cL);
if( ! $rLocal)
{
print “query error: $qsLocal
“;
return;
}
}

Es wird einfach so oft iteriert, wie es für die Komplettkopie der neuen Datensätze nötig ist. Für jeden Datensatz ruft das Skript einmal »mysql_fetch_row()« auf; die Daten liegen dann in einem Array vor. Nun müssen diese Daten in die Tabelle eingefügt werden. Dazu ist »table_ name_here« wieder durch den Namen der eigentlichen Tabelle zu ersetzen. Anstelle von »fields_here« ist eine durch Kommata getrennte Liste der Felder einzutragen. Schließlich kommt eine durch Kommata getrennte Liste der einzelnen Array-Zellen an »values_here«. Die eingefügten Werte sind also »$row[0]«, »$row[1]« und so weiter. Schließlich wird mit dem auf diese Art zusammengesetzten Query-String noch das abschließende MySQL-Query abgesetzt. Dieses kümmert sich darum, dass die Daten wirklich in der Tabelle eingetragen werden.


Vollständiges Update

MySQL-Datenbanken sichern

Schließlich noch ein paar Worte zum Skript für das vollständige Update. Dies unterscheidet sich nicht sehr vom bisher vorgestellten Inhalt. Allerdings sind doch ein paar Dinge zu beachten.

$strDel = “delete from table_name_here”;
$r = mysql_db_query(“database_name_here”, $strDel, $cL);

Zunächst muss der Anwender die zu kopierende Tabelle auf dem Zielrechner zurücksetzen. Dadurch sind in dieser Tabelle keinerlei Daten mehr vorhanden und man kann beim späteren Einfügen ein einfaches insert-into-Statement verwenden. Das Löschen der Tabelle erfolgt einfach mit »delete from« . Hier ist beim Anpassen des Skriptes zu beachten, dass die Daten danach unwiderruflich gelöscht sind. Es ist unbedingt sicherzustellen, dass Sie die richtige Tabelle aus der richtigen Datenbank entfernen, bevor das Skript läuft.

$qs = “select * from table_name_here”;
$r = mysql_db_query(“database_name_here”, $qs, $cR);
num = mysql_num_rows( $r);
for( $i=0; $i$num; $i++)

Danach lassen sich alle Werte aus der Quelltabelle selektieren und in de
r Zieltabelle einfügen.


Master-Slave-Modus

MySQL-Datenbanken sichern

In einer MySQL-Umgebung mit Master und Slaves lässt sich bei der Verfügbarkeit der Daten ein hoher Wert erreichen. Laufen die Datenbanken im Master- Slave-Modus, holen sich die Slaves alle aktuellen Daten selbst vom Master ab. Dadurch erhält der Administrator ein Live- Backup, das ruckzuck die eigentliche Datenbank ersetzt. Fällt beispielsweise der Master-Rechner plötzlich mit einem Hardwaredefekt aus, springt der Slave-Rechner einfach ein. Dass sich der Datenbestand geändert hat, teilt der Master allen Slaves automatisch mit. Dazu gibt er seinen aktuellen Status bekannt, den er laufend in einer binären Datei (Binary-Log) protokolliert. Erhalten die Slaves eine entsprechende Nachricht, übertragen sie die geänderten Daten in ihre lokale Datenbank.

Elegant aber gefährlich
Trotz aller Eleganz dieser Datenreplikation ? Bedienungsfehler wirken sich verheerend aus. Eine SQL-Instruktion wie »Delete from mitglieder« lässt die Mitgliedertabelle auch auf den Slaves sofort verschwinden. Ein »echtes« Backup erspart der Master-Slave-Modus dem Administrator nicht. Als Grundausstattung benötigt der Slave den gleichen Datensatz, den der Server bei der Aktivierung des Binary-Log hatte. Halten Sie dazu den Server an und kopieren Sie alle Daten auf den Slave. Anschließend aktivieren Sie das Binary- Log und starten den Server. Der Slave kann nun flexibel in die Replikation eingebunden werden. Er muss beispielsweise nicht ständig laufen und lässt sich jederzeit anhalten. Sobald der Slave wieder hochfährt, nimmt er Kontakt mit dem Master auf und kümmert sich völlig selbstständig darum, alle Updates abzuholen. Obwohl der Master die Slaves nicht verwaltet (Housekeeping), protokolliert er, welche und wie viele Slaves momentan auf Updates warten. Das Kommando »show processlist« am MySQL-Prompt eingegeben zeigt die Informationen an.


Master-Slave konfigurieren

MySQL-Datenbanken sichern

Falls Sie unterschiedliche MySQL-Versionen einsetzen, prüfen Sie zuerst, ob die Replikation in dieser Kombination funktioniert. Unter www.mysql.com/doc/en/Replication_Compatibility.html gibt es eine Kompatibilitätstabelle. Als erstes legen Sie auf dem Master einen eigenen MySQL-Account mit dem Privileg »Replication Slave« an: mysql> grant replication slave on *.* to slave@’%.ihredomain.de’ identified by ‘ihrPasswort’; Danach tragen Sie zwei Optionen im »my.cnf« des Masters ein:

[mysqld]
log-bin
server-id=1

Die Anweisung »log-bin« aktiviert das binäre Log. Die ID ist frei wählbar und dient als einfache Kennung für einen Server. Nachdem Sie die Änderungen an »my.cnf« vorgenommen haben, starten Sie den MySQL-Server neu. Dann flushen Sie die Tabellen und blockieren außerdem alle Schreibzugriffe.

mysql> flush tables with read lock;

Jetzt ist die Datenbank bereit für das Backup und Sie fertigen den Snapshot der Masterdaten an. Dazu packen Sie das komplette Datenverzeichnis des Masters in ein Tar-Archiv. Wechseln Sie in das Datenverzeichnis der MySQL-Installation und verwenden Sie dort das Kommando:

tar -cvf /tmp/master-snapshot.tar

Die Tar-Datei kopieren Sie in das Datenverzeichnis des Slaves und brennen diese zusätzlich auf einen CD- oder DVDRohling. Mit dem so gesicherten Archiv konfigurieren Sie zu einem späteren Zeitpunkt ganz einfach weitere Slaves. Im Datenverzeichnis des Slave packen Sie das Archiv aus:

tar -xvf master-snapshot.tar

Neben dem Archiv brauchen Sie auch die aktuelle Position des Binary-Logs auf dem Master. Diese Information sichern Sie am besten auch auf der Backup-CD, denn nur zusammen mit dieser Information können Sie später die Slaves starten. Die Position des Binary-Logs erhalten Sie mit

mysql> show master status;

Haben Sie dies erledigt, gestatten Sie wieder Schreibzugriffe auf den Master. Das Kommando geben Sie im noch laufenden MySQL-Client ein:

mysql> unlock tables;

Nun geht es zurück zum Slave-Rechner, auf dem Sie die Konfigurationsdatei my.cnf bearbeiten. Hier brauchen Sie nur eine Server-ID angeben:

[mysqld]
server-id=2

Achten Sie darauf, dass sich die Slave- ID von der des Masters unterscheidet. Setzen Sie mehrere Slaves ein, müssen Sie allen verschiedene IDs geben. Nach der Änderung der my.cnf starten sie den Slave neu. Danach versorgen Sie ihn mit dem Hostnamen des Masters und dem Passwort.

mysql> change master to
-> master_host=’NameDesMasters’,
-> master_user=’slave’,
-> master_password=’ihrPasswort’,
-> master_log_file=’LogFile’,
-> master_log_pos=’offset’;

Der Wert »offset« steht für die Position im Binary-Log aus dem Snapshot. Damit sind Sie fast fertig und müssen anschließend lediglich noch den Slave- Thread auf dem Slave starten.

mysql> start slave;

Wichtig: Bei einigen Versionen von MySQL lautet das Kommando nicht »start slave«, sondern »slave start«.


Misslungene Replikation

MySQL-Datenbanken sichern

Wenn die Replikation nicht gelingen will, finden Sie entsprechende Fehlermeldungen im Error-Log. Meist handelt es sich um einen Vertipper beim Account- Namen oder Passwort. Dies korrigieren Sie mit einem neuen change-master-to- Befehl. Falls Sie in Synchronisationsprobleme geraten, zum Beispiel wenn Sie bereits das Binary-Logging verwenden, aber keinen Master aufgesetzt haben, müssen Sie das Binary-Log zurücksetzen. Um ein bereits laufendes Binary-Log in den gleichen Zustand zu versetzen, als wäre es gerade erst eingeschaltet worden, verwenden Sie das Kommando »reset master; « auf dem Master-Server. Um nach der Inbetriebnahme eines neuen Slaves sicherzustellen, dass der Master erst wieder in Betrieb geht, wenn der Slave exakt die gleichen Daten vorliegen hat, gehen Sie wie folgt vor.
Zunächst versetzen Sie den Master in einen Zustand mit Schreibsperre und ermitteln die aktuellen Daten des Binary Logs: mysql> flush tables with read lock; mysql> show master status; Danach wechseln Sie auf den Slave und aktualisieren ihn. Dies funktioniert mit einem select-Statement

: mysql> select master_pos_wait( arg1,arg2);

Dabei entspricht »arg1« dem Namen der Log-Datei und »arg2« der Position. Beides liefert der Befehl »master status;« zurück. Der Slave aktualisiert sich daraufhin mit dem Master. Erst wenn das Live- Backup vollständig ist, kommt das »select «-Statement zurück und Sie können den Master wieder freigeben. Das entsprechende Kommando lautet:

mysql>unlock tables;

Mit dem Kommando »show master status« finden Sie die Position des neuen Datenbankeintrags im Binary-Log heraus.