Dumping MySQL – Aber Richtig!

Posted by Jesco Freund at Aug. 9, 2010 9:21 p.m.

MySQL ist immer noch die im Web-Umfeld am häufigsten eingesetzte Datenbank. Damals™ war die MyISAM Storage Engine auch ne dolle Sache. Schnell (verglichen mit Konkurrenzprodukten), und als i-Tüpfelchen auch noch datenbankbeschleunigtes Wühlen in Texten. Doch oh weh, die Programmierer wurden anspruchsvoller, begannen, komplexere Anwendungen zu schreiben und verlangten auch von ihrer Datenbank immer mehr: Transaktionen muss sie beherrschen, und Constraints, bitteschön!

Die zwischenzeitlich eine Weile lang in Mode befindliche Berkeley-Engine konnte so etwas wie Transaktionen, aber mit Constraints war immer noch Essig. Die Rettung nahte in Gestalt von InnoDB. ACID-konform war das schuckelige Teilchen, Constraints, Stored Procedures und Hastenichgesehen sind mittlerweile auch für MySQL kein Thema mehr.

Doch wehe, wehe, wenn ich auf das nächste sehe: Bei aller Liebe zu InnoDB, die Werkzeugsammlung, die der Administration von MySQL dient, nimmt nicht immer Rücksicht auf den Fortschritt…

Immer noch sehr beliegt zur Erzeugung von Backups ist das Werkzeug mysqldump. Das Konzept ist einfach: Struktur und Inhalt einer Datenbank werden in Form von SQL-Statements ausgeworfen, deren Ausführung Struktur und Inhalte in ihrer aktuellen Form erzeugen würden. Diese Methode hat einige Nachteile, deren schlimmster die miserable Performance ist – nicht bei der Erstellung eines Backups, wohl aber bei einem Restore. Auch inkrementelle oder differenzielle Backups sind nur auf Umwegen möglich (aber machbar).

Trotzdem ist diese Methode nach wie vor sehr beliebt, wird in Foren und sogar im MySQL-Handbuch als Backup-Methode beschrieben. Das MySQL-Handbuch versteigt sich sogar zu folgender Aussage:

The mysqldump program and the mysqlhotcopy script can make backups. mysqldump is more general because it can back up all kinds of tables. mysqlhotcopy works only with some storage engines.

Auf gut deutsch: mysqldump ist die eierlegende Wollmilchsau, wenn es um Backups geht. mysqldump funktioniert zweifelsohne beim Erstellen von Dumps. Doch wie ist es um die Einspielbarkeit selbiger bestellt? Um das zu beantworten, stelle ich einmal genau das nach, was im im Katastrophenfall als braver Admin tut – macht nüscht, man hat ja Backup.

Zuerst braucht's natürlich eine Beispiel-Datenbank mit Beispiel-Daten drin. Das Beispiel macht mit Absicht Gebrauch von einer Foreign Key Constraint – im wahren Leben kommt sowas schließlich auch vor, andauernd sogar

CREATE DATABASE fktest;
USE fktest;

CREATE TABLE bb (
    id INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE aa (
    id INT,
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id) REFERENCES bb(id) ON DELETE CASCADE
) ENGINE=INNODB;

INSERT INTO bb (id) VALUES (1), (2), (3), (4), (5);
INSERT INTO aa (id, parent_id) VALUES (10,1), (20,2), (30,3), (40,4), (50,5);

Von diesem Konstrukt fertige man ein Backup mit mysqldump:

mysqldump -B fktest > fktest.sql

Um den Ernstfall zu simulieren, werfe man die Datenbank fktest über Bord:

DROP DATABASE fktest;

Anschließend bemühe man den erzeugten Dump, um zu retten, was zu retten ist:

mysql < fktest.sql

Soweit, so gut. Die böse Überraschung droht, wenn man den Dump mit phpMyAdmin erstellt hat, oder bei mysqldump die Option --compact mit angegeben hat. Versucht man nun, einen solchen Dump für ein Restore zu nutzen, endet die Aktion sehr schnell mit der ungnädigen Aussage:

ERROR 1005 (HY000) at line 7: Can't create table 'fktest.aa' (errno: 150)

Der Grund liegt einfach darin, dass alle Dump-Werkzeuge die Tabellen in alphabetischer Reihenfolge abarbeiten. In dem von mir konstruierten Beispiel ist es jedoch so, dass Tabelle aa erst angelegt werden kann, wenn Tabelle bb exisitiert – es sei denn, man veranlasst MySQL, vorübergehend Foreign Key Constraints zu ignorieren.

mysqldump fügt eine entsprechende Anweisung in den Dump ein, wenn man nicht gerade die Option --compact bemüht. Bei phpMyAdmin muss der Haken bei „Fremdschlüsselüberprüfung deaktivieren“ gesetzt sein, was in den Standard-Einstellungen nicht der Fall ist. Wenn man das alles beachtet, kann man auch weiter mit Dumps arbeiten, wenn Foreign Key Constraints im Spiel sind. Beachtet man diese Kleinigkeiten hingegen nicht, darf man – falls man im Eifer des Gefechts das Problem korrekt diagnostiziert – den Dump ein wenig bearbeiten.

SET FOREIGN_KEY_CHECKS=0;

an den Anfang des Dumps geschrieben, und schon klappt's auch mit dem Restore. Danach sollte man Foreign Key Checks allerdings sofort wieder aktivieren (normalerweise Bestandteil des Dump-Skripts).

Was bleibt zum Schluss? Nun ja, der Wunsch nach einem Werkzeug, dass es freilaufenden Deppen unmöglich macht, Tabellen mit Foreign Key Constraints zu dumpen und dabei die Abschaltung der Foreign Key Checks wegzulassen. Vornehm nennt sich das dann Poka Yoke, auf rustikaldeutsch „idiotensicher“.

No comments | Defined tags for this entry: MySQL, server, SQL

Comments

No comments