mysql

In den vergangenen Wochen ist mir dieser Fehler mehrfach bei einer sehr großen MySQL-Installation aufgetaucht. Es hat einige Zeit gedauert, bis ich diesen Fehler ausgemerzt habe, zumal die betreffende Tabelle über 20 Mio Einträge bei einer Tabellengröße über 14 GB besitzt. Da ist jeder Befehl, jeder Versuch, die Tabelle zu reparieren, nicht in einer Kaffeepause abgetan. Ein Repair dauert mehrere Stunden …

Da es sich um eine MyISAM-Tabelle handelt, ist der erste Versuch natürlich, die Tabelle per „myisamchk“ zu reparieren. Den genauen Ablauf habe ich im Artikel „MyISAM-Datenbank-Tabelle reparieren“ bereits ausführlich beschrieben. Je nach Tabellengröße ist es an der Zeit, in eine ausgedehnte Mittagspause oder gar ins Bett zu gehen. Dieser Befehl dauert seine Zeit und in meinem Fall hat die Reparatur zwar die Tabelle wieder lesbar gemacht, das defekte Key-File jedoch nicht repariert.

Methode 1: Das Keyfile löschen

In diversen Foren bin ich über die nachfolgend beschriebene Methode gestoßen, um das Keyfile wieder erneut zu stellen. Um es vorweg zu nehmen: Die Methode hat bei meiner Tabelle nicht funktioniert. Ich kann nicht behaupten, dass die Methode nie funktioniert. Aus diesem Grund beschreibe ich sie hier auch. Nur in meinem Fall führte sie nicht zum Erfolg.
Ziel der Methode ist, dass betreffende Keyfile zu löschen und neu erstellen zu lassen:

1. Den MySQL-Server stoppen („sudo service mysql stop“)
2. Das betreffende .myi-File im Dateisystem umbenennen (beispielsweise „name.old“)
3. Den MySQL-Server wieder starten („sudo service mysql start“)
4. Nun die Tabelle reparieren lassen ( http://dev.mysql.com/doc/refman/5.1/en/repair-table.html ). Hierbei soll das Keyfile neu erstellt werden, da es ja nicht mehr vorhanden ist.

In meinem Fall brach die Reparatur mit wilden Fehlermeldungen ab. Ich habe danach wieder das Keyfile der Tabelle hinzugefügt und die Tabelle wieder neu repariert („myisamchk“). Dieser Versuch hat mich dann insgesamt über 12 Stunden beschäftigt. In dieser Zeit war die Datenbank also offline.

Methode 2: Tabelle per Dump neu aufbauen

Bei dieser Methode wird von der betreffenden Tabelle ein Dump erzeugt. Dies geht relativ schnell mittels „mysqldump –h localhost –u USER –pPASSWORD DATENBANKNAME TABELLENNAME > mydump.sql“ Wir erzeugen also einen Dump der Tabelle mit dem defekten Keyfile. In meinem Fall einfach auf der lokalen Platte (sofern natürlich ausreichend Platz vorhanden ist). Zu beachten ist ebenfalls, dass die Tabelle nicht als defekt gekennzeichnet sein darf („marked as crashed“). Wenn dies der Fall ist, muss zuvor per myisamchk die Tabelle repariert werden (siehe oben).
Nun haben wir also auf der lokalen Platte den Dump „mydump.sql“ liegen. Dann wird es spannend: Wir löschen über mysql die Tabelle mit dem defekten Keyfile. Dann spielen wir den Dump wieder zurück. Dies geschieht mit dem Befehl „mysql –h localhost -u USER –pPASSWORD ZIELDATENBANK < dump.sql“. Wenn nach einem abschließenden Return nicht sofort ein Fehler in der Konsole auftaucht, kann sich unser Puls erst einmal wieder regulieren. Im Filesystem kann gesehen werden, dass die Tabelle angelegt wurde und die Größe der Tabelle stetig wächst. Irgendwann wird die Tabelle ihre vorherige Größe haben und das Keyfile wird neu erstellt. Dann meldet sich die Konsole (hoffentlich) ohne Fehlermeldung wieder zurück.
Diese Methode hat für mich großen Charme, denn einerseits hat der eigentliche Dump und das Zurückspielen – vergleichsweise – wenig Zeit gekostet, zum Anderen habe ich mir ein „Optimize Table“ gespart. Die Tabelle wurde ja durch das Rückspielen der Daten von jeglicher Fragmentierung befreit.

Es darf eigentlich nicht vorkommen, doch es passiert: Ein unbedachter Wackler an einem Stromkabel beim Ein- oder Umbau im Serverschrank oder gar eine Stromschwankung oder Stromausfall: Der Server wird ohne ordentliche Abmeldung einfach stromlos und das Chaos ist perfekt. Sofort werden der Techniker und der zuständige Admin kreideweiß im Gesicht. Wird der Server wieder anlaufen? Wird die Festplatte einen Schaden haben? Und wird die MySQL-Datenbank korrupt sein?
Gehen wir einmal davon aus, dass der Server wieder anläuft, das Betriebsystem zwar das nicht ordnungsgemäße Ausschalten in den Statusmeldungen moniert, aber ansonsten klaglos seinen Dienst verrichtet, wird spätestens MySQL zur Bewährungsprobe. Denn wenn im Moment des Stromausfalles schreibend auf die Datenbank zugegriffen wurde, kann man davon ausgehen, dass die Datenbank nicht ohne Schaden genommen wieder anlaufen wird.

Warning: Table ist maked as crashed and last repair failed
Wenn MySQL ohne Fehlermeldung anläuft bedeutet dies leider nicht, dass auch alle Tabellen ohne Schaden den Stromausfall überstanden haben. Gerade MyISAM-Tabellen werden, wenn während des Stromausfalles darauf zugegriffen wurde, Schaden genommen haben.
Man muss nicht gleich in Panik verfallen, es gilt Ruhe zu bewahren und den Schaden zu minimieren. Die Reparatur wird jedoch, je nach Tabellengröße, längere Zeit in Anspruch nehmen. Es ist daher zuerst zu prüfen, ob es nicht vielleicht sinnvoll ist, ein Backup zurückzuspielen. Je nach Tabelle und Sicherungs-Infrastruktur kann dies der schnellste Weg der Wiederherstellung sein.

MyISAM-Tabelle überprüfen
Loggen Sie sich auf der Konsole auf dem Server ein. Navigieren Sie zum MySQL-Server („var/lib/mysql“) und wechseln Sie in das Verzeichnis der Datenbank. Prüfen Sie nun die Tabellen mit

myisamchk tabellenname.MYI

Mit Spannung kann das Ende der Prüfung abgewartet werden. Wenn die Konsole „MyISAM-Table ‚TABLENAME’ is corruped“ meldet, ist einen Reparatur der MyISAM-Tabelle angesagt.

Die schnelle MyISAM-Reparatur
Die Konsolenausgabe schlägt vor, mit dem Schalter „-r“ die Reparatur durchzuführen. Mittels dieses „Wiederherstellungsmodus“ werden falsche und gelöschte Datensätze entfernt und die Indexdatei neu erstellt. Wer es schneller möchte, kann den Schalter „-q“ zusätzlich verwenden. Dann wird versucht, die Indexdatei zu reparieren, anstatt sie neu zu erstellen. Probieren Sie die Reparatur aus.
In jedem Fall wird die Reparatur Zeit in Anspruch nehmen. Da die Datenbank sowieso offline ist, können Sie auch über den Speicher des Datenbankservers verfügen. Man beschleunigt die Reparatur ungemein, wenn man zuvor die MySQL-Variablen „sort_buffer_size“ und „key_buffer_size“ jeweils auf 25 Prozent des verfügbaren Hauptspeichers setzt.

MyISAM-table ‚tablename’ is not fixed because of errors
Wenn die schnelle MyISAM-Reparatur fehlschlägt, müssen Sie den Modus „safe-recover“ („-0“) wählen:

myisamchk –r –o TABELLENNAME

Je nach Tabellengröße wird diese Wiederherstellung mehrere Stunden benötigen. Sie ist genauer, aber auch sehr langsam.
Bei allen bisher bei mir aufgetretenen MyISAM-Reparatur-Szenarien haben diese Schritte zum Erfolg geführt. Wenn trotzdem Fehler auftreten oder mysiamchk sogar abstürzt, müssen Sie folgende Schritte ausführen:

1.    Kopieren Sie die Datenbank per Filesystem in einen sicheren Bereich
2.    Führen Sie die folgenden Befehle aus:

shell> mysql DATENBANKNAME
mysql> SET AUTOCOMMIT=1;
mysql> TRUNCATE TABLE TABELLENNAME;
mysql> quit

3. Kopieren Sie die Datenbank-Datei aus dem sicheren Bereich wieder auf den Server.
4. Starten Sie „myisamchk -r -q“, also die schnelle Datenbankreparatur, erneut.

Hand aufs Herz: Wieviel Zeit invenstieren Sie vor dem Anlegen der ersten Datenbank-Tabelle in das optimale Tabellendesign? Ich hoffe viel, denn durch das richtige Design, die richtig gewählten Dateitypen und letztlich die richtigen Indizes lassen sich die Größe einer Datenbank minimieren und die Abfragen massiv beschleunigen.

Doch meistens ist das folgende Bild eher an der Tagesordnung. Man legt die erste Tabelle an, füllt die mit Testdaten und programmiert die Anwendung. Es kommen Spalten hinzu, manche werden gelöscht, umbenannt und viele schlichtweg vergessen. Später scheut man sich, das Datenbankdesign noch einmal zu überarbeiten, da man froh ist, dass die Anwendung läuft und man keine Zeit mehr investieren möchte.

Doch wenn die Größe der Datenbanktabelle wächst, wird man von der Realität eingeholt. Es rächt sich mit steigender Datenbankgröße, wenn man das Design vernachlässigt hat. Wer nicht ständig die programminternen Datenbankabfragen auf das Vorhandensein eines optimalen Index prüft, wird schnell Schiffbruch erleiden. Und wer zu großzügig mit Datentypen umgeht, wir schnell unnötigen Plattenplatz belegen und seine Datenbank aufgrund der Größe der Tabellen verlangsamen.

Gerade bei den numerischen Datentypen ist es unumgänglich, den „besten“ Datentyp für ein Feld zu wählen. Es gilt immer: Man wählen den kleinsten Datentyp, der für das Feld ausreicht. Wählt man einen zu großen Datentyp, belegt dieser nur unnötiger Weise Speicherplatz. Bremst unter Umständen sogar die Datenbank. Daher ist es wichtig zu wissen, welche maximale Werte ein numerischer Datentyp unter MySQL annehmen kann.

Unten aufgeführt habe ich die gängigen Datentypen (nicht vollständig!) aufgeführt und gebe Tipps für die Verwendung. Eine detaillierte Aufstellung kann in der MySQL-Doku nachgelesen werden.

• TINYINT: Nimmt Werte zwischen 0 bis255 oder -128 bis 127 auf. Optimal für die ID von kleinen Tabellen (maximal 255 Einträge) oder Status-Felder.
• SMALLINT: Nimmt Werte zwischen 0 bis 65.535 oder -32.768 bis 32.767 auf. Langt bereits als Datentyp für die ID-Spalte vieler Tabellen.
• MEDIUMINT: Nimmt Werte zwischen 0 bis 16.777.215 oder -8.388.608 bis 8.838.607 auf. Optimaler Wert für ID-Felder im „kleinen“ Millionenbereich.
• INT: Nimmt Werte zwischen 0 und 4.294.967.295 oder -2.147.438.648 und 2.147.483.647 auf. Im Gegensatz zu anderen Datenbanken ist der Integer bei MySQL schon sehr, sehr mächtig. Für viele Felder ist dieser bereits schon überdimensioniert und sollte durch einen kleineren Datentyp ersetzt werden.
• BIGINT: Nimmt Werte zwischen 0 und 18.446.744.073.709.551.615 oder -9.223.372.036.854.775.808 und 9.223.372.036.854.775.807 auf. Gerade Umsteiger von anderen Datenbanksystemen wählen gerne das „große INT“ für die ID einer stark wachsenden Tabelle. Doch unter MySQL ist der der Integer bereits groß genug für die meisten Tabellen.

Speicherplatzbedarf von nummerischen Daten-Typen
Warum wir nur den kleinsten Datensatztyp wählen sollten, der wirklich benötigt wird, zeigt die folgende Tabelle. In dieser ist der erforderliche Speicherplatz eines Datentyp aufgeführt. Wenn wir beispielsweise für eine Spalte großzügig BIGINT (8 Byte) deklariert haben, aber ein SMALLINT (2 Byte) als Spaltentyp reichen würde, verschenken wir 6 Bytes. Pro Datensatz, wohlgemerkt! Wer als Datensatz-ID BIGINT gewählt hat und nun den Spaltentyp auf INT reduziert, spart 4 Bytes (50 Prozent) an Speicherplatz, bezogen auf das ID-Feld. Es lohnt sich also, auch laufende Tabellen zu gegebener Zeit auf die Datentypen hin zu prüfen.

• TINYINT: 1 Byte
• SMALLINT: 2 Byte
• MEDIUMINT: 3 Byte
• INTEGER: 4 Byte
• BIGINT: 8 Byte

Update: Noch weniger Gedanken macht man sich um TEXT …

Schon mal Gedanken gemacht, wieviel Text in ein MySQL-Text-Feld passt? Viel. Sehr viel, sollte man meinen. Dem ist aber nicht so. Rund 65.000 Zeichen passen in ein Text-Feld. Nachfolgend der Speicherplatz und die maximale Textlänge von MySQL Text Feldern:

TINYTEXT	256 bytes
TEXT	65,535 bytes	~64kb
MEDIUMTEXT	16,777,215 bytes	~16MB
LONGTEXT	4,294,967,295 bytes	~4GB

Wir haben uns im Artikel „MySQL: Trigger einrichten – Beispiele zur Verwendung von Triggern“ mit der Anlage und Verwendung von Trigger beschäftigt. Bisher war die Grenze die eigene Datenbank. Also die Quell- und Zieltabelle, die wir mit einem Trigger beehrt haben, lagen innerhalb der eigenen Datenbank. Nun stellt sich die Frage, ob wir mit einem Trigger auch über eine Datenbankgrenze hinweg zugreifen können um Tabellen, die in unterschiedlichen Datenbanken liegen, so mit einem Trigge rzu verknüpfen.

Wir erstellen folgendes Testszenario:
Eine Datenbank „Datenbank1“ mit der Tabelle „testtabelle1“ sowie synchron die „Datenbank2“ mit der „testtabelle2“. Beide „Testtabellen“ bekommen der Einfachheit halber nur ein Feld „id“ mit dem Dateityp „integer“.

Die Tabellenstrukturen lauten wir folgt:
[Datenbank1]

CREATE TABLE IF NOT EXISTS `testtabelle1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

[Datenbank2]

CREATE TABLE IF NOT EXISTS `testtabelle2` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Wir legen nun den Trigger an. Dieser soll von datenbank1.testtabelle seine Werte in datenbank2.testtabelle2 schreiben. Daher wird der Trigger auch in der Quell-Datenbank „datenbank1“ angelegt. Der Einfachheit halber beschränken wir uns in diesem Beispiel nur auf einen INSERT-Trigger:

DROP TRIGGER IF EXISTS `insert_testtabelle2`;
DELIMITER //
CREATE TRIGGER `insert_testtabelle2` AFTER INSERT ON `testtabelle1`
FOR EACH ROW BEGIN
INSERT INTO datenbank2.testtabelle2 (id) VALUES (NEW.id);
END
//
DELIMITER ;

Wie wir sehen, sprechen wir die Ziel-Datenbank „datenbank2“ und die darin enthaltene Tabelle „testtabelle2“ einfach über „datenbank2.testtabelle2“ an.

Nun ist es Zeit, unseren datenbankübergreifenden Trigger einmal auszuprobieren. Wir fügen einen Wert in datenbank1.testtabelle1 ein:

INSERT INTO testtabelle1 (id) VALUES (1);

Wir schreiben also eine “1” in die erste Tabelle. Dieser Wert sollte nun auch in unserer datenbank2.testtabelle2 erscheinen. Wir kontrollieren es:

SELECT * FROM testtabelle2;

Und siehe da, unser Trigger über zwei Datenbanken hinweg hat funktioniert.

Nun stellt sich die Frage, ob wir mit einem Trigger auch Tabellen auf einem anderen Datenbankserver ansprechen können. Nun, leider habe ich hier keine gute Nachrichten zu berichten. Ich habe es bis heute noch nicht geschafft, eine Verbindung zwischen zwei unterschiedlichen Datenbankservern zu erreichen. Synonyme, wie es der Microsoft SQL-Server kennt, sind MySQL leider immer noch fremd.

Datenbankabfrage Join über unterschiedliche Datenbanken
Wie bereits oben gezeigt, funktioniert das Ansprechen einer Datenbank bzw. der darin enthaltenen Tabelle über
Datenbankname.tabelle
Somit ist auch ein Join kein Problem, die richtigen Rechte vorausgesetzt. Wie bei Trigger erwähnt, habe ich bisher noch keine Möglichkeit gefunden, über mehrere Datenbankserver hinweg auf Datenbanken zuzugreifen. Im Netz kursieren einige Anleitungen, wie dies über Tricks möglich ist. Aber eine saubere Lösung sieht – leider – anders aus.

Heute müssen wir auf einen MySQL-Server zugreifen, der in einem lokalen Netz hinter einer Firewall steht. In diesem Fall ist es relativ einfach, denn das lokale Netz ist einfach ein privates Heimnetzwerk mit DSL-Anschluß. Hier ist zu beachten, dass durch den Internet-Provider jeden Tag eine neue IP-Adresse vergeben wird. Also müssen wir einen Dienst wie dyndns.org (LINK) nutzen, damit wir eine Verbindung zum Heimnetzwerk erhalten können, auch wenn wir die aktuelle IP-Adresse nicht kennen.

In unserem Beispiel hängt ein Netgear Router mit integrierter Firewall vor dem Heimnetzwerk. Damit wir von „außen“ auf den MySQL-Server kommen können, müssen wir die Firewall konfigurieren.

Port 3306 an der Firewall freigeben
Wir wissen, dass der MySQL-Server auf den Port 3306 (Standard) lauscht. Man kann nächtelang mit Administratoren darüber diskutieren, ob es sinnvoll (und sicherer) ist, diesen Port freizugeben oder einen anderen Port für den Zugriff zu nutzen. Ich persönlich bin der Meinung, dass egal welcher Port offen ist, dieser natürlich eine Gefahr darstellt. Ob wir den Port 3306 oder 1234 nutzen: Ein Portsniffer wird auch diesen Port finden.
Ich lege dies in die persönliche Entscheidung, welchen Port man freigibt. In dieser Doku beschränke ich mich auf den Standard-Port von MySQL.

Bei den Netgear-Routern muss der Port über „Dienste“ zuerst angelegt werden. Wir tragen dort den Port „3306“ ein und geben ihm eine sinnvolle Bezeichnung wie beispielsweise „MySQL“.

Firewall: Port-Forwarding
Nun müssen wir der Firewall mitteilen, was mit Traffic am Port 3306 geschehen soll. Wir möchten, dass diese Kommunikation zum MySQL-Server geleitet wird. Hier ist wichtig, dass der MySQL-Server eine feste IP-Adresse im Netzwerk hat. Je nach Router kann hier auch eine dynamische Adresse eingegeben werden. Prinzipiell empfehle ich aber, einem Server immer eine statische Adresse zu geben.
Bei diesem Netgear-Router kann das Forwarding über „Regeln für die Firewall“ definiert werden. Mittels des Button „Hinzufügen“ fügen wir die neue Regel hinzu. Wir verknüpfen den zuvor angelegten „Dienst“ (Port 3306) mit der IP-Adresse des MySQL-Servers. Wie wir in der Hardcopy erkennen können, werden hier nur eingehende Dienste definiert. Ausgehende Dienste sind alle freigegeben. Auch hier kann man lange diskutieren, wie sinnvoll dies ist.
Es muss sichergestellt sein, dass der Port 3306 für eingehende wie ausgehende Dienste freigegeben wird.

Erster Test per Telnet
Nun könnte die Verbindung zum MySQL Server bereits funktionieren. Wir testen dies über Telnet. Bitte beachten, dass dieser Test nicht im lokalen Netzwerk, sondern von einem anderen Netzwerk aus gestartet werden muss (beispielsweise eine UMTS-Verbindung). Unter Windows startet man einem Command-Box über START/Ausführen/cmd.exe (oder Windows-R) und gibt dort „telnet“ ein. Dann „open meinhost 3306“, wobei „meinhost“ die DynDNS-Adresse ist.
Nach Bestätigen des Befehls startet der Verbindungsaufbau. Wenn eine Fehlermeldung wie „Es konnte keine Verbindung mit ….“ erscheint,  hat die Verbindung offensichtlich nicht funktioniert. Wir stattdessen ein wilder „Datenmüll“ (meist ist der Servername ersichtlich) angezeigt, hat die Verbindung funktioniert.

Freigabe („nach außen“) des MySQL-Server
Wenn die Verbindung nicht funktioniert hat, kann es daran liegen, dass der MySQL-Server noch keine Verbindungen außerhalb „localhost“ akzeptiert. Diese Einstellung macht aus Sicht der Sicherheit Sinn, muss jedoch hier geändert werden.

Über die Anwendung von Triggern in MySQL ist schon viel geschrieben worden. Auch die MySQL-Hilfe hilft wie gewohnt immer weiter. Dennoch möchte ich ein paar Worte über den Sinn und die praktische Anwendung mit Beispiele über die Trigger verlieren.

Warum Trigger?
Der Haupteinsatzpunkt von Triggern besteht, wenn man mehrere Tabellen hat, die einen Bezug miteinander haben. Wenn Tabellen normalisiert sind, verwendet man Datensätze über mehrere Tabellen hinweg. Wenn nun in der Haupttabelle beispielsweise ein Datensatz gelöscht wird, muss man immer daran denken, die Auslagerung in der Untertabelle ebenfalls zu „pflegen“. Man kann dies über die Anwendung direkt machen, was eher schlecht ist. Wer sich mit StoredProcedures auskennt, hat vielleicht eine solche geschrieben, die diese Arbeit übernimmt. Viel eleganter ist jedoch, solche Aufgaben direkt per Trigger zu erledigen.

Trigger Beispiel: Warenwirtschaft mit Produkte
In unserem Trigger Beispiel verwenden wir eine Hauptabelle „produkte“. Diese hat viele hier nicht relevante Felder. Wichtig ist das Feld „id“ und das Feld „preis“. Nun werden wir eine weitere Tabelle haben, eine sogenannte „Preishistorie“. In dieser Tabelle möchten wir später nachvollziehen können, wie sich der Preis eines bestimmten Produktes im Laufe der Zeit entwickelt hat.
In der Tabelle „preishistorie“ haben wir nur wenige Felder. Neben der Datensatz-ID („id“) haben wir den Bezug zu Tabelle produkte.id, die „produktid“. Dann den aktuellen Preis („preis“) sowie ein Datumfeld „aenderung“, in welchem wir das aktuelle Datum der Änderung speichern. Wir möchten bei einem INSERT in der Produkttabelle auch einen INSERT mit dem aktuellen (Start-)Preis in Preishistorie-Tabelle erhalten. Ferner soll bei einem DELETE auch die Tabelle Preishistorie von diesem Produkt bereinigt werden.

MySQL-Fehlermeldung 1064 bei Neuanlage Trigger
#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near “ at line X

Soviel vorweg: Diese Fehlermeldung bei der Neuanlage eines Trigger tritt auf, wenn der Trigger ohne “Delimiter” angelegt wird. Also diesen Syntax nicht vernachlässigen!

INSERT-Trigger
Wir legen zuerst einen Insert-Trigger an. Dieser wird ausgelöst, wenn ein INSERT auf unsere Tabelle produkte erfolgt IST (AFTER). Dies ist deshalb sehr wichtig, da wir die automatisch eingefügte id des Datensatzes benötigen, der hier per „NEW.id“ in die Spalte „productid“ übergeben wird.

Bei jedem INSERT wird nun der aktuelle Preis mit aktuellem Datum in die Tabelle Preishistorie geschrieben.

delimiter |

CREATE TRIGGER insert_preishistorie
      AFTER INSERT
      ON produkte
      FOR EACH ROW BEGIN
      INSERT INTO preishistorie (productid, preis,aenderung) VALUES (NEW.id, NEW.preis,NOW());
    END;
|
delimiter ;

DELETE-Trigger
Wenn ein Produkt in unserer Haupttabelle gelöscht werden soll, sollen auch alle Einträge in der Preishistorie gelöscht werden. Dies erreichen wir über den DELETE-Trigger:

delimiter |

CREATE TRIGGER delete_preishistorie
      AFTER DELETE
      ON produkte
      FOR EACH ROW BEGIN
      DELETE FROM preishistorie WHERE productid=OLD.id;
    END;
|
delimiter ;

UPDATE-Trigger

Dieser Trigger stellt uns, wenn man die Syntax verstanden hat, auch nicht weiter vor ein Problem. Hier ist jedoch anzumerken, dass dieser Trigger bei jedem Update ausgelöst wird. Dies würde bedeuten, dass wir den Preis auch in unsere Historie aufnehmen würden, wenn ein anderes Feld anstatt des Preises sich ändert. Aus diesem Grund müssen wir prüfen, ob sich der Preis auch wirklich geändert hat. Hier wird auch spätestens der Unterschied zwischen OLD.preis und NEW.preis deutlich.

delimiter |

CREATE TRIGGER update_preishistorie
      AFTER UPDATE
      ON produkte
      FOR EACH ROW BEGIN
            IF NEW.preis != OLD.preis THEN
                  INSERT INTO preishistorie (productid, preis,aenderung) VALUES (NEW.id, NEW.preis,NOW());
            END IF;
    END;
|
delimiter ;

Trigger löschen
Um einen Trigger zu löschen, benutzt man einfach untenstehende Anweisung:
DROP TRIGGER update_preishistorie

Jede Datenbank, die untertags durch viele INSERT, DELETE oder UPDATE-Statements drangsaliert wird, sollte in regelmäßigen Abstanden optimiert werden. Dies kann beispielsweise mittels PHPmyAdmin per unter dem Punkt „Operatoren“ (vorher eine Tabelle auswählen), „Optimiere Tabelle“ geschehen, oder per mysql-Konsole selbst.

Warum überhaupt eine Datenbanktabelle optimieren?
Doch zuerst müssen wir uns kurz mit der Frage befassen, warum eine MySQL-Datenbanktabelle überhaupt optimiert werden muss. Nun, dies ist keine Eigenschaft, die nur MySQL betrifft. Auch Datenbanken unter Microsoft SQL Server benötigen von Zeit zu Zeit den Optimierungsbefehl. Wobei „benötigen“ eigentlich nicht richtig ist. Man muss den Befehl nicht ausführen, wenn man in Kauf nehmen kann, dass die Serverlast von Tag zu Tag steigt und die Datenbank immer langsamer wird. Der Grund liegt in der Defragmentation der Daten. Nehmen wir mal folgendes Beispiel an. Wir haben ein Feld X vom Wert tinyint. Dies ist anfänglich 0; wir updaten es auf 1. Wird das Dateisystem defragmentiert? Nein, denn ob 0 oder 1 – die Speicherplatzgröße für beide Werte ist gleich. Nehmen wir jetzt aber ein Feld „Name“ vom Typ varchar(x). Das Feld hat den Wert „Müller“. Nun machen wir ein Update auf „Müller-Lüdenscheid“. Hätten wir das Feld per Char(max) festgelegt, hätte das Datenbanksystem die maximale Länge des Feldes blockiert. Der nun längere Name, der aber unter der Maximum-Länge des Feldes liegt, hätte immer noch in den vorbereiteten Platz gepasst. Der Nachteil ist gravierender: Die Datenbank wird mit jedem Datensatz so groß wie die Definition der maximalen Datensatzlänge angibt. Auch wenn nahezu leere Felder eingefügt werden, steigt der Datenplatz unaufhaltsam an. Ein Grund, warum varchar-Felder ein Segen sind, auch wenn sie andere Probleme mit sich bringen. Weiteres über Optimize Table kann direkt in der MySQL-Doku nachgelesen werden.

Ausführung von Optimize Table
Die Ausführung des Befehls ist einfach. In einer MySQL-Shell muss lediglich
OPTIMIZE TABLE Tabellenname;
Ausgeführt werden. Zu beachten ist jedoch, dass MySQL die Tabelle während der Laufzeit von OPTIMIZE TABLE sperrt!

Während man bei einem Microsoft SQL Server – zumindest ab der Standard-Version – Wartungspläne definieren kann, die automatisch solche „Wartungen“ wie ein Optimize Table vornehmen, muss man bei MySQL auf die Möglichkeiten des Host-Systemes zurückgreifen.

Tabelle optimieren oder reparieren mittels mysqlcheck
Die meisten MySQL-Installationen bringen bereits in einer Standardinstallation das Hilfsprogramm „mysqlcheck“ mit. Der Vorteil dieses Programmes ist, dass wir damit auch auf einen laufenden SQL-Server zugreifen und wir eben das Programm per Cronjob aufrufen können. Wir ersparen uns dadurch das Schreiben von Skripts.

Einrichten eines Cronjobs für das Optimieren einer MySQL-Tabelle

1. Wir loggen uns per SSH (putty) auf unserem Server ein
2. Je nach Linux-Distribution warden wir mit “su” zum Root.
3. Editieren der Crontab ( /etc/crontab) mit dem Befehl crontab -e
4. Wir fügen folgende neue Zeile per Editor in unserer Crontab ein:
   0 1 * * * mysqlcheck -Aao –auto-repair -u root -p[password] > /dev/null
5. Editor mit ESC, “:wq!” verlassen.

Beschreibung des Befehls:
„0 1 * * *“: Hiermit weisen wir den Cron an, jeden Tag um 1 Uhr und 0 Minuten den Befehl auszuführen. Nähere Informationen zur Steuerung eines Crons erhalten Sie im Artikel „Linux-Cronjob einrichten“

mysqlcheck -Aao –auto-repair: Wir rufen damit das Programm “mysqlcheck” auf. Die Parameter können direkt in der MySQL-Doku nachgelesen werden. Die Parameter hier bewirken:

-A: Alle Datenbanken (all-databases) werden überprüft/optimiert
-a: Analysiert die Tabellen (Analyse)
-o: Optimierung (optimize) der Tabellen
-auto-repair: Wenn eine Tabelle beschädigt ist, wird sie automatisch repariert.
Mit den Parametern „-Aao –auto-repair“ erledigen wir also mehr, als ein reines „Optimize“.

Parameter „-u root -p[password]”: Hier geben wir den Datenbankbenutzer (root) und sein Passwort an

Parameter „> /dev/null”: Wir schreiben die Ausgabe des Befehls ins digitale Nirwana. Hier könnte auch eine Ausgabe in ein Logfile oder eine Mail erfolgen.

UPDATE: mysqlcheck doesn’t support multiple contradicting commands

Diese Fehlermeldung tritt bei neueren Versionen von mysqlcheck auf. Hier ist es nicht mehr möglich, mehrere Kommandos aneinanderzuhängen. Um den Fehler zu umgehen, genügt der nachfolgende Aufruf:

mysqlcheck -u root -p [PASSWORT] --auto-repair --optimize --all-databases

Wenn ein MySQL-Datenbankserver per Replikation einige Tage in Betrieb ist, häufigen sich die Binär-Logs im MySQL-Verzeichnis. Zur Erinnerung: Ein Master-MySQL-Server speichert in seinem Binär-Log alle Transaktionen, die seine eigene Datenbank verändern. Der oder die Slave-Server greifen auf dieses Binär-Log zu und replizieren so anhand dieser Historie ihre eigene Datenbank. Ein Löschprozess findet per Standard nicht statt.

Auch wenn die Datenbank sehr klein ist, kann der Umfang der Binär-Logs einen stattlichen Umfang annehmen. Dies liegt weniger an der Größe der Datenbank – dies hat keinen primären Einfluss auf die Größe der Logfiles – sondern an der Anzahl der Insert und Updates, die eine Datenbank verändert haben. Jede einzelne Änderung findet sich im Binär-Log wieder. Wie wir in der Hardcopy hier sehen, schreibt der Beispiel-MySQL-Server seine Daten bereits in das 61. Logfile. Per Konsole erfahren wir, dass diese Logfiles bereits stolze 6,2 GB an Speicherplatz belegen, da in der mysql.cnf max_binlog_size  = 100M definiert ist; also jedes Logfile wird etwas größer als 100 MB.

Manuelle Löschung der MySQL-Binär-Logs
Prinzipiell ist das Löschen der Binär-Logs jedezeit möglich. Die Datenbank muss nicht angehalten werden und auch die Konsistenz des Master-Datenbankservers ist durch das Löschen in keinster Weise gefährdet. Warum auch? Der Master-Datenbankserver schreibt einfach fleißig sein Logfile. Er selbst stützt sich nicht auf die Logfiles. Wohl aber die Slave-Server. Wenn wir also Logfiles auf dem Master löschen, die ein Slave noch nicht verarbeitet hat, droht eine Inkonsistenz der Replikation. Also müssen wir jeden Slave fragen, mit welchem Logfile er gerade beschäftigt ist. Das älteste Logfile, das JEDER Slave bereits vollständig abgearbeitet hat, ist demnach das neueste Logfile, das gelöscht werden darf.

Ermittlung des aktiven Replikation-Logfiles
Wir loggen uns also auf jedem Slave-Server über die MySQL-Konsole ein und fragen nach dem aktiven Logfile:

# mysql –u root –p
mysql > SHOW SLAVE STATUS G

Über den Befehl SHOW SLAVE STATUS erfahren wir, welches das aktuelle Master-Logfile (Master_Log_File) und das Relay_Master_Log_File ist. Auf unserem Beispiel-Server würde im Statusbricht „mysql-bin.000061“ auftauchen. Wenn alle Slaves aktuell am 61. Logfile arbeiten, können die 60 Logfiles davor also gelöscht werden.

Entfernung der MySQL-Binär-Logfiles
Der folgende Befehl löscht die Binär-Files unwiderruflich. Ängstliche Administratoren können gerne Sicherheitskopien der zu löschenden Logfiles anfertigen. Sollte man sich einmal bei dem folgenden Befehl „verhauen“ und eine Inkonsistenz der Replikation erreichen, dann ist dies nicht der Untergang des Abendlandes. Man fertigt dann eine aktuelle Sicherung der Datenbank auf dem Master an, installiert diese auf dem Slave und startet dort die Replikation zu diesem Zeitpunkt neu.

Da wir keine Angst haben, löschen wir also die Logfiles. Zu beachten gilt: Die Angabe der Logfile-Nummer im folgenden Befehl ist exklusive; es werden also nur Logfiles BIS ZU DIESEM gelöscht. Wir können in unserem Fall alle 60 Logfiles löschen. Der Befehl dafür lautet also:

PURGE BINARY LOGS TO `mysql-bin.000061`

Automatisierte Löschung der Binär-Logs
Bei der routinemäßigen Überprüfung der Server kann der Administrator sicherlich die Binär-Files so löschen. Allerdings bietet MySQL auch die automatisierte Löschung an. Allerdings kann darüber nur ein Zeitraum in Tagen definiert werden. Ob der oder die Slave-Server auf dem aktuellen Stand sind, wird nicht überprüft. Aber auch hier gilt das gleiche wie bei der manuellen Löschung: Wenn einmal der Fall eintritt, dass die Inkonsistenz eintritt, muss die Datenbank auf dem Slave neu eingespielt werden. Ärgerlich, da mit Offline-Zeiten zu rechnen ist, aber kein Untergangs-Szenario.

In der MySQL-Konfigurationsdatei my.conf (/etc/mysql/my.cnf) finden wir den Eintrag “expire_logs_days=0“. Die „0“ setzt das Löschen außer Kraft. Wir setzten diesen Wert nun auf einen vernünftigen Wert. Ob

expire_logs_days=7

oder

expire_logs_days=30

die richtigen Werte sind, muss jeder MySQL-Datenbank-Administrator für sich selbst entscheiden. Da die Replikation der Slaves vom Administrator sowie ständig überwacht werden muss, dürfte ein so langes „Hinterherhinken“ eines Slaves sowieso nicht toleriert werden. In vielen Installationen sind heute bereits 10 Tage per default hiterlegt:

server-id        = 1
log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size         = 100M

Wer eine MySQL-Datenbank spiegelt (Master/Slave), muss die Replikation öfters kontrollieren, denn im laufenden Betrieb können sich Fehler einschleichen und die Replikation wird gestoppt. Wer denn beispielsweise eine Webseite per Load Balancer gespiegelt hat, wird seinen Kunden nicht auf jedem Frontend-Server die gleichen Daten präsentieren. Aus diesem Grund muss die Replikation überwacht werden. Heute möchten wir uns mit der prinzipiellen Überwachung auseinandersetzen. Denn nur wer weiß, was MySQL für Fehler erzeugen kann und wie man diese per Hand bereinigt, kann sich dann Gedanken um eine Automation oder Überwachung machen.
Dass ein Fehler bei der MySQL-Replikation vorliegt, kann beispielsweise über das Syslog („/var/lib/mysql/mysqld.log“) darauf stoßen. Oder die MySQL-Konsole meldet via SLAVE STATUS einen Fehler.

So ist prinzipiell bei einem MySQL-Replikations-Fehler vorzugehen:

1. Anmelden an der MySQL-Konsole mittels „mysql -u root –p“ [RETURN], Eingabe des Passwortes.
2. Abfrage des Replikation-Status mit „SHOW SLAVE STATUS G”. Das “G” steht für die formatierte Ausgabe.

Wenn der Slave einen Fehler meldet, ist dieser sowie das SQL-Statement, welches den Fehler verursacht hat, in der Statusmeldung ersichtlich. Nun ist zu prüfen, wie verfahren werden soll. Eventuell soll nur der fehlerverursachende Befehl übersprungen werden. Dann ist wie folgt zu verfahren:

1. Den Slave stoppen mit: STOP SLAVE;
2. Den Befehl überspringen mit SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
3. Den Slave wieder zur Replikation anfahren mit : START SLAVE;
4. Den Replikationsprozess überwachen mit SHOW SLAVE STATUS G
5. Die MySQL-Konsole beenden mit: quit;

Erkennen, dass die MySQL-Replikation fehlerhaft ist
Über die MySQL-Konsolen-Befehl „SHOW SLAVE STATUS G“ erfährt man alles wissenswerte über den Prozess. Neben den Parameter „Last_Error“ und „Last_Errno“ sollte auf die Parameter „Slave_IO_Running“ und „Slave_SQL_Running“ geachtet werden.. Die Parameter „Last_error“ enthalten eine Fehlernummer und gegebenenfalls ein SQL-Kommando, das einen Fehler verursacht. Aus den Parametern „Slave […] Running“ erfährt man, ob die Replikation noch läuft. Wenn einer der beiden Parameter auf „NO“ steht (oft nur Slave_SQL_Running), dann muss man sich um die Fehlerbehebung kümmern.

Gerne gesehene Fehler einer MySQL-Replikation
Wer eine Datenbank repliziert und nicht nur auf dem Master, sondern auch auf dem Slave mit den Tabellen arbeitet, kann sehr schnell zu folgendem INSERT-Problem kommen. Dann nämlich, wenn der Master Datensätze in eine Tabelle einfügt und ein oder die Slaves in diese (replizierte) Tabelle ebenfalls Datensätze hinzufügen. Selbst wenn die laufende IDs über die Tabelle per „AUTO_INCREMENT“ hinzufügt, tappt man sehr schnell in die Falle und erzeugt einen doppelten Schlüssel (Duplicated Key). Denn die INSERT-Befehl auf dem Master, der vielleicht ohne explizite Angabe einer Datensatz-ID ausgeführt wurde („AUTO_INCREMENT“), wird über das Binär-Log auf dem Slave explizit, also genau mit der ID ausgeführt, die auch auf dem Master eingefügt wurde. Fügt jetzt auch noch der Slave in diese Tabelle auf dem Slave Datensätze hinzu, kann die ID bereits vergeben sein und die MySQL-Replikation schlägt fehl. Das Verhalten ist klar, denn wenn die Replikation die Vergabe der ID dem Slave überlassen würde, könnte so der Spiegel auseinanderlaufen.

Im Artikel „MySQL Cluster oder Hot-Stand-By Lösung? Eine Frage der Hochverfügbarkeit“ haben wir die Vorbereitungen zu unserer MySQL-Replikation getroffen. Im Artikel „MySQL Replikation (Master/Slave) einrichten“ haben wir das der Replikation zugrundeliegende Logfile eingerichtet und ein Abbild der zu sichernden Datenbank erstellt. Dieses muss nun auf alle MySQL-Slave-Server kopiert werden. Wenn mehrere Slave-Server eingerichtet werden sollen, müssen die folgenden Schritte auf jedem Slave-Server durchgeführt werden.

Wir haben die Master-DB („mysql-snapshot.tar“) auf dem Slave-Server in den Ordner „/var/lib/mysql“ kopiert. Über eine Putty-Shell stoppen wir den MySQL-Server und entpacken die Master-Datenbank:

/etc/init.d/mysql stop
cd /var/lib/mysql
tar -xvf mysql-snapshot.tar

Die my.cnf auf dem Slave anpassen
Nun müssen wir die „my.cnf“ auf dem Slave anpassen. Wichtig ist auf jeden Fall, dem Slave eine eigene Server-ID zu geben. Jeder MySQL-Server in unserer Replikation benötigt eine eigene eindeutige ID. Wir öffnen die my.cnf mit einem Texteditor unserer Wahl:

nano /etc/mysql/my.cnf

server-id = 2
replicate-do-db = this_db [optional]

Das Logfile auf dem Master enthält alle Änderungen der Master-Datenbank. Wenn wir nur eine einzelne Datenbank replizieren möchten, können wir den Slave mittels „replicate-do-db“ anweisen, nur die Datenbank „this_db“ zu replizieren.
Ebenfalls optional ist die Angabe von Tabellen. Zu beachten gilt, wenn der Befehl „replicate-do-table“ einmal im Logfile verwendet wird, muss jede zu replizierende Tabelle aufgeführt werden (im untenstehenden Beispiel werden nur die drei benannten Tabellen repliziert).

#replicate-do-table beschränkt die Replikation auf einzelne Tabellen, falls nicht die gesamte Datenbank repliziert werden soll.
replicate-do-table = shopdb.customer
replicate-do-table = shopdb.order
replicate-do-table = shopdb.tracking

Den MySQL Slave wieder starten
/etc/init.d/mysql start

Dem MySQL-Slave mitteilen wer sein Master ist
Die Replikation geht von den einzelnen Slaves aus. Der Slave greift mit dem Replikationskonto (das wir im Artikel „MySQL Replikation (Master/Slave) einrichten“ angelegt haben) auf den Master zu.

Mittels MySQL-Konsole setzen wir folgenden Befehl ab:

CHANGE MASTER TO MASTER_HOST='<host>‘, MASTER_USER='<Replikations-User>‘, MASTER_PASSWORD='<passwd>‘, MASTER_LOG_FILE=’mysql-bin.<Hier die aktuelle Dateiangabe>, MASTER_LOG_POS=<Die Position vom Master>;

<host>: Name oder besser, die IP des Masters
<Replikations-User>: Name des Replikations-User auf dem Master (hier: „repl“)
<passwd>: Das Passwort des Replikations-User
MASTER_LOG_FILE: Beispielsweise „mysql-bin.000001“ (siehe notierte Angaben aus dem Start der Master-Replikation)
MASTER_LOG_POS: Beispielsweise „100“ (siehe notierte Angaben aus dem Start der Master-Replikation)

Wenn nach diesem Befehl keine Fehlermeldung erscheint, ist die Replikation schon fast perfekt.

Replikation anfahren
Mittels MySQL-Konsole starten wir Replikation:
START SLAVE;

Wenn alles ordnungsgemäß eingegeben wurde, führt der Slave ab sofort die Replikation durch. Mittels des MySQL-Konsolen-Befehls

show slave statusG

kann der Status der Replikation jederzeit abgerufen werden. Die Replikation stoppt bei einem Fehler.
Sobald ein Slave mit der Replikation begonnen hat, finden wir in seinem Datenverzeichnis unter „var/lib/mysql“ zwei Dateien namens master.info und relay-log.info. Der Slave verwendet diese beiden Dateien um zu vermerken, welcher Teil der Masterlogs bereits abgearbeitet wurde.

Über diese Schritte können so alle weiteren Slaves in die Replikation einbezogen werden. Nun benötigen wir noch das Wissen, wie wir mit Fehler der Replikation umgehen und wie wir diese überwachen. Über dieses Thema beschäftigen wir uns in einem weiteren Artikel.