Computer

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:

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