Aus dem Rechenzentrum

Heute lag eine Anfrage einer Kundin auf meinem Tisch, die eine Datenbank voller MySQL-Tabellen hatte. Der Zeichensatz der Datenbank und aller Tabellen und Spalten war latin1_german_ci. Dumm nur, dass die Applikation überall UTF8-kodierte Werte reingeschrieben hat – was hier und da so ein bisschen funktionierte, an anderen Stellen aber so gar nicht. In phpMyAdmin wurden alle Umlaute aber fehlerhaft dargestellt, und damit stand dann auch fest, dass da einfach Murks passiert war (denn wir wissen ja, phpMyAdmin hat immer recht, siehe hier).

Nun ist aber das Problem, dass die nachträgliche Änderungen der Spalten in phpMyAdmin erstens mühselig ist und zweitens auch gar nichts bringt, weil MySQL dabei die Inhalte der Spalten auch gleich (in seine Sinne) korrekt konvertiert. Die einzige Möglichkeit ist, die Spalte auf eine binäre Spalte vom Typ BLOB zu ändern, weil hierbei die Zeichensatzangabe verlorengeht, was wir hier auch explizit wollen – sie ist ja falsch. Das ist aber noch viel unglaublich aufwendiger.

Auf der Shell lässt sich das Problem aber schließlich trivial lösen. Zunächst dumpen wir die gesamte Datenbank, und dabei geben wir an, dass latin1 als Zeichensatz verwendet werden soll – auf diese Weise dumpt MySQL die Daten so (falsch), wie sie in den Spalten stehen; da sind sie ja als latin1 kodiert. Dann ändern wir alle Kollations- und alle Zeichensatzangaben im SQL-Dump von latin1 auf utf8 – wobei die Daten, genauer gesagt die Bytes aber ja unverändert bleiben. Anschließend wird alles wieder in MySQL gefüttert.

Und so sieht’s aus (es wird eine .my.cnf mit hinterlegten Zugangsdaten vorausgesetzt, damit keine Passworteingabe erfolgen muss):

mysqldump --default-character-set=latin1 mydatabase mydatabase.sql

cat mydatabase.sql |
  sed 's/latin1_german_ci/utf8_general_ci/g;' |
  sed 's/latin1/utf8/g;' |
  mysql mydatabase

Fertig! In phpMyAdmin sicherheitshalber kontrollieren, dass alle Spalten als utf8 kodiert sind und gleichzeitig auch die Umlaute korrekt angezeigt werden. Und da wir die Zeichensatzänderung nicht bei Dumpen, sondern beim Einspielen des Dumps „on the fly“ machen, liegt mit mydatabase.sql dann auch gleich noch ein Backup des Originalzustands vor, falls alles schiefgehen sollte.

Problematisch wird die Sache nur, wenn nur einige Tabellen oder – noch schlimmer – nur einige Spalten einiger Tabellen falsch kodiert sind. In diesem Fall wäre dann doch eher professionelle Hilfe gefragt – oder viel Handarbeit. ;-)

Wenn es um PHP und Unicode geht, am Besten auch noch in Zusammenhang mit MySQL, gibt es eine Vielzahl an Tipps und Howtos. Manche davon sind falsch, die meisten zum Glück zwar richtig, doch selbst die, die Richtiges vermitteln, tun das manchmal nur durch Zufall, und oftmals fehlt das, was für eine verlässliche Handhabung am wichtigsten ist: Eine Erklärung, warum man dieses und jenes so und nicht anders machen soll.

Dieser Artikel hat den hehren Anspruch, Erklärungen zu liefern, Verständnis für Zusammenhänge zu wecken und schließlich dabei unterstützen, Unicode mit PHP richtig zu machen – ohne dass Sie dafür ein dickes Handbuch lesen müssen. Natürlich bleiben dabei viele Details auf der Strecke, deshalb sehen Sie diesen Artikel pragmatisch: Er sollte für den gewöhnlichen Alltag reichen und Sie dabei nicht ganz dumm sterben lassen. Für tiefergehende Informationen – lesen Sie ein Buch.

Zunächst einmal gilt es eine Sache zu verstehen, nämlich:

„Zeichen“ und „Bytes“ sind etwas völlig Verschiedenes.

Das ist vor allem wichtig für diejenigen, die bei diesem Thema erstmal gleich die gute alte Zeichensatztabelle im Kopf haben. Da haben wir nämlich gelernt: Ein Zeichen ist ein Byte; mit einem Byte kann man 256 verschiedene Zeichen darstellen. Wenn man gut aufgepasst hat, weiß man vielleicht auch noch, dass die ersten 128 Zeichen immer gleich waren („US-ASCII“) und die nächsten 128 Zeichen darüber zeichensatzspezifisch waren – dass das Zeichen mit der Nummer 188 z.B. in ISO-8859-1 „¼“ bedeutet, während es in ISO-8859-2 „ź“ bedeutet.

Lektion Nummer 1: Diese Zeichensatztabellen bitte alle sofort vergessen. Zu einem späteren Zeitpunkt, wenn das mit Unicode klar geworden ist, kann man sich gerne wieder daran erinnern und sie sauber in einen technischen Zusammenhang bringen, aber für den Moment: Weg damit. Und damit auch gleich weg mit der Vorstellung, dass „1 Byte“ in irgendeiner Art und Weise „1 Zeichen“ bedeuten würde. In der gebräuchlichen UTF-8-Kodierung kann ein Zeichen nämlich durchaus bis zu vier Bytes benötigen.

Hier nun daher der große, grundsätzliche Pferdefuß von PHP in allen Versionen < 6, direkt aus der offiziellen Quelle:

A string is series of characters, therefore, a character is the same as a byte. That is, there are exactly 256 different characters possible. This also implies that PHP has no native support of Unicode.

Bitte hier kurz innehalten, durchatmen und sich noch einmal ganz deutlich vor Augen führen: PHP hat keine Ahnung von Unicode.

Um das ad hoc etwas greifbarer zu machen, hier ein konkretes Beispiel für ein PHP-Script, das ich mit einem Texteditor erstellt habe, der die Datei UTF-8-kodiert abspeichert (was heutzutage eigentlich alle tun):

$ cat test-strlen.php
<?php echo strlen("ä"); ?>

$ php test-strlen.php
2

Überrascht? Wichtig ist das vor allem für Leute, die sonst in anderen Programmiersprachen entwickeln, die von Haus aus UTF-8-tauglich sind. Nehmen wir zum Beispiel Perl: Dort werden intern Strings als Zeichen verarbeitet; ein interner UTF-8-Marker hilft Perl dabei, zu verstehen, ob die im RAM liegenden Bytes einer Zeichenkette als UTF-8 interpretiert werden müssen, um sinnvolle Zeichen zu ergeben, oder nicht. Mit PHP muss in diesem Punkt grundsätzlich anders gearbeitet werden.

Eines ist aber so oder so wichtig zu verstehen: Erst die Angabe eines Zeichensatzes kann eine Folge von Bytes sinnvoll in eine Folge von Zeichen überführen, oder griffiger für einen Klebezettel an der Kühlschranktür ausgedrückt:

1-4 Bytes + Zeichensatzangabe = Zeichen

Wann immer also auf einer Website oder in einer E-Mail „kaputte“ Umlaute zu beobachten sind, so liegt das immer daran, dass entweder eine Zeichensatzangabe vorhanden ist, die aber nicht stimmt, oder dass gar keine vorhanden ist, der Client den Zeichensatz rät und dabei den falschen rät. Das kann man ihm aber kaum zum Vorwurf machen – es ist Aufgabe des Erstellers des Inhalts, sich um die korrekte Zeichensatzangabe zu kümmern.

Nun findet auf Websites aber jene Menge Interaktion statt, und so gibt es gleich eine ganze Reihe von Bereichen, in denen Unicode eine Rolle spielt:

• ein PHP-Script sendet Daten an den Browser
• ein Browser sendet Daten an ein PHP-Script
• ein PHP-Script liest Daten aus einer Datenbank
• ein PHP-Script schreibt Daten in eine Datenbank

Jegliche Art von Kommunikation basiert aber auf Bytes und nicht auf Zeichen, was in der Konsequenz bedeutet: Bei jeglicher Art von Kommunikation muss eine Zeichensatzangabe mitgegeben werden, damit nichts schiefgeht. Das klingt auf den ersten Blick aufwendig, aber um es ganz deutlich zu sagen: Daran führt kein Weg vorbei.

Nehmen wir den einfachsten Fall: Ein PHP-Script sendet Daten an den Browser. Ein PHP-Script ist eine Textdatei, insofern wird der Zeichensatz dieser Textdatei durch den Editor festgelegt, der sie speichert. Dann ist wichtig, dass der Browser über den Zeichensatz informiert wird. Da gibt es viele Möglichkeiten; die wichtigsten wären:

• das PHP-Script führt header("Content-type: text/html; charset=utf-8"); aus
• im HTML-Header wird <meta http-equiv="Content-type" content="text/html; charset=utf-8"> angegeben
• in der php.ini wird default_charset = utf-8 angegeben
• in der Apache-Konfiguration wird AddDefaultCharset UTF-8 angegeben

Der umgekehrte Weg, dass ein Browser Daten an ein Script sendet, ist überraschend unklar: HTTP-Requests sehen nämlich keinen Zeichensatz-Header vor. Dabei wäre es doch schon durchaus wichtig zu wissen, ob ein vom Website-Besucher in einem Formular angegebenes „ä“ nun als ein oder als zwei Bytes übermittelt wird! In der Praxis wird ein Browser die Formulardaten im gleichen Zeichensatz kodieren wie der der Seite, die er abgerufen hatte, mit einem Fallback auf ISO-8859-1. Für den Alltag reicht das aus. Entwickler von HTTP-basierenden Schnittstellen, bei denen ein Client initiativ Formulardaten an den Webserver schickt, sind aber gut beraten, in der Schnittstellendefinition kurzerhand ausdrücklich festzulegen, was erwartet wird.

Nun haben wir also Formulardaten erhalten, und diese Formulardaten haben irgendeinen Zeichensatz, idealerweise UTF-8, wenn wir dem Browser schon das Formular als UTF-8 präsentiert haben. So weit, so gut.

Nun müssen die Daten in eine Datenbank, wobei ich hier als Beispiel einfach mal MySQL verwende. Und auch hier gibt es eine „Grundwahrheit“, die man sich am Besten gleich neben den Klebezettel von vorhin an den Kühlschrank heften sollte:

Bei MySQL sind Zeichensatzangaben für zwei Dinge wichtig:

1. für die Information, in welchem Zeichensatz Daten gespeichert werden sollen;
2. für die Information, in welchem Zeichensatz Daten übermittelt werden sollen.

(Ja, das ist schnöde vereinfacht, denn schließlich gibt es in MySQL ein client character set, ein connection character set, ein results character set, ein system character set, ein database character set und ein server character set. Wir wollen es aber für den Moment nicht zu kompliziert machen und uns auf die Dinge konzentrieren, bei denen es normalerweise Probleme gibt. Ganz pragmatisch, wissenschon.)

Über den ersten Punkt ist sicher fast jeder schon mal gestolpert, der mit einer halbwegs aktuellen phpMyAdmin-Version hantiert hat: Für jede Spalte, in die textliche Angaben hineinkommen (CHAR, VARCHAR, TEXT, …), muss ein Zeichensatz angegeben werden, wobei der Defaultwert von der Tabelle und der wiederum von der Datenbank und der wiederum vom Server geerbt wird. Und eines sei an dieser Stelle direkt gesagt: Man mag von phpMyAdmin halten, was man will, aber das mit den Zeichensätzen macht es richtig. Wenn Sie also durch die Daten blättern und über Umlaute stolpern, die kaputt sind: Suchen Sie den Fehler bei sich. phpMyAdmin hat immer recht. Wiederholen Sie das.

Bevor es weitergeht, wenden wir uns dem Herren weiter hinten im Publikum zu, der schon seit einer Minute mit dem Finger schnippst und behauptet, das könne nicht stimmen, denn seine Applikation würde prima funktionieren, aber phpMyAdmin würde die Umlaute falsch anzeigen. Nun: Lassen Sie sich nicht foppen. Suchen Sie den Fehler bei sich, und erinnern Sie sich an das Mantra: phpMyAdmin hat immer recht. Es wird reiner Zufall, um nicht zu sagen: schieres Glück sein, dass es trotzdem funktioniert. Typischerweise passiert das dann, wenn Sie UTF-8-kodierte Daten in einer als Latin1 markierten Spalte ablegen. Hier werden die Zeichen dann zweimal falsch verarbeitet: Wenn MySQL die Bytes aus dem Datensatz entsprechend dem Zeichensatz der Spalte interpretiert, und wenn es dann dieses fehlerhafte Ergebnis in den gewünschten Zeichensatz der Verbindung konvertiert, der vermutlich Latin1 und nicht UTF-8 ist und somit die erhaltenen Bytes weitestgehend unbehelligt lässt. Und zweimal falsch ergibt in diesem speziellen Zusammenhang ausnahmsweise sogar mal richtig: Sie geben faktisch eine Reihe von Bytes aus, die in Latin1 ein krummes „Büttelborn“ ergeben und schreiben dann für den Browser eiskalt drüber: So, das ist jetzt aber UTF-8. Und erst damit kommt das Zeichensatzproblem wie auf magische Weise in Ordnung, und der Browser rendert „Büttelborn“ aus den schnöden Bytes.

Wenn Sie dann aber Ihre selbstgeschriebene Applikation später mal auf einen anderen Server übertragen, oder wenn Sie MySQL updaten, oder wenn Sie PHP updaten, … und plötzlich die Umlaute nicht mehr funktionieren, dann wissen Sie, dass Sie etwas falsch gemacht haben, und zwar nicht mit dem Update, sondern mit Ihrem Code, schon vor langer Zeit. Sagen Sie nicht, ich hätte Sie nicht gewarnt.

Es wird somit Zeit für den nächsten Kühlschrankzettel:

SET NAMES utf8;

Damit stellen Sie ein, in welchem Zeichensatz Sie die Daten zwischen PHP und MySQL übermitteln. Das betrifft sowohl die Daten, die Sie in die Datenbank schreiben, als auch die, die Sie aus der Datenbank beziehen. Und jetzt der Clou: Die Information, in welchem Zeichensatz MySQL die Daten speichert, hat damit absolut nichts zu tun. Mit SET NAMES utf8 bekommen Sie immer UTF-8 raus, egal welchen Zeichensatz die Strings in der Tabelle faktisch haben. Und auch umgekehrt schicken Sie einfach Bytes in dem Zeichensatz rüber, den Sie bei SET NAMES angegeben haben, und MySQL kümmert sich darum, diese Folge von Bytes anhand des angegebenen Zeichensatzes als Zeichen zu interpretieren und dann diese Zeichen anhand des Zeichensatzes der Spaltendefinition in eine Folge von Bytes zu konvertieren, dies es dann fröhlich auf die Festplatte schreiben kann. Um so besser, wenn die beiden Zeichensätze identisch sind: Dann muss nämlich überhaupt nichts konvertiert werden.

Das Mantra für PHP-Entwickler muss also lauten:

Es spielt für Ihr Script überhaupt keine Rolle, welchen Zeichensatz die Spalten in der Datenbank haben.

Es spielt ausschließlich eine Rolle, in welchem Zeichensatz Sie mit MySQL kommunizieren.

(Na gut, erwischt. Das ist natürlich nicht die volle Wahrheit. Es spielt insofern doch eine Rolle, als dass es nicht jedes Zeichen in jedem Zeichensatz gibt. Übermitteln Sie also das Zeichen „✌“ UTF-8-kodiert an MySQL und verlangen vom Datenbankserver, dass er dieses Zeichen in eine als Latin1 formatierte Spalte schreibt, obwohl Latin1 das Zeichen gar nicht kennt, dann haben Sie schlechte Karten. Wunder kann MySQL nicht vollbringen. Insofern: Formatieren Sie Ihre Spalten lieber gleich als UTF-8.)

Gewöhnen Sie sich also einfach an, nach jedem mysql_connect(...) direkt ein mysql_query("SET NAMES utf8"); auszuführen. Wenn Sie zu den verantwortungsvollen Entwicklern gehören, die den Aufbau der Datenbankverbindung an einer zentralen Stelle untergebracht haben, sollte das ein Klacks sein.

Nun sind sie schon mal ziemlich weit: Sie kriegen die Daten aus Ihrer Datenbank als Bytes, die – wenn man UTF-8-Kodierung auf sie anwendet – hübsch korrekte Zeichen ergeben. Wenn Sie diese Zeichen mit echo ausgeben und dazu einen Content-type-Header mit „charset=utf-8“ (siehe oben), wird ein Browser die Zeichen richtig darstellen. Sie bekommen bei einem abgeschickten Formular Bytes rein, und wenn Sie diese Bytes an MySQL schicken, nachdem Sie ihm mit SET NAMES utf8; mitgeteilt haben, dass diese Bytes, als UTF-8 interpretiert, hübsche Zeichen ergeben, wird MySQL das auch verstehen und dann schließlich ganz ohne Ihr Zutun die Zeichenkette auf die Festplatte schreiben, in dem Zeichensatz, der in der Spaltendefinition steht.

Was noch fehlt, ist PHP selbst. Denken Sie an das obige Beispiel mit dem strlen("ä"), das 2 als – nun gar nicht mehr so – überraschendes Ergebnis liefert, weil ein „ä“ in UTF-8 eben zwei Bytes entspricht. Denken Sie an die unzähligen Vorkommen von strlen, strpos oder auch strtoupper: Letztere Funktion kann natürlich aus einem „u“ ein „U“ machen. Aber kann sie auch aus einem „ü“ ein „Ü“ machen? Wenn Sie bedenken, dass PHP einen String als eine Folge von Bytes ansieht, dann sollten Sie sich vor Augen halten, dass die zwei Bytes, die in der UTF-8-Kodierung ein „ü“ ergeben, im Latin1-Zeichensatz für zwei einzelne Zeichen stehen, nämlich für ein „Ó und ein „¼“. Diese Zeichen in Großbuchstaben umzuwandeln, dürfte schwierig sein. Das findet dann auch PHP:

$ cat test-strtoupper.php
<?php echo strtoupper("über"); ?>

$ php test-strtoupper.php
üBER

Ihnen wird schon ganz übel? Gut so, das ist der erste Schritt zur Heilung.

Hier kommt die mbstring-Erweiterung ins Spiel. Wenn Sie PHP nicht gerade selbst kompiliert haben (wo mbstring gerne vergessen wird), ist es typischerweise bei allen Distributionen mit einkompiliert. Schon mit phpMyAdmin werden Sie zeichensatzmäßig keine große Freude haben, wenn mbstring fehlt – so wenig, dass schon auf der Startseite deutlich gewarnt wird, dass Sie sich gar nicht einbilden müssen, irgendwie korrekt dargestellte Zeichen zu erwarten.

Mit der mbstring-Erweiterung bekommen Sie für nahezu alle stringverarbeitenden Funktionen Pendants mit dem Präfix mb_, der genau das gleiche tut, aber zeichensatzsensibel ist:

$ cat test-mb_strlen.php
<?php
echo strlen("ä") . "\n";
echo mb_strlen("ä", "utf8") . "\n";
?>

$ php test-mb_strlen.php
2
1

Ja, genau: Sie müssen den mb_-Funktionen den Zeichensatz mitteilen, in dem es den String aus Bytes interpretieren soll. Das sollte Sie inzwischen aber nicht mehr wundern. Sie können alternativ in der php.ini (oder mit ini_set()) auch mbstring.internal_encoding auf den gewünschten Zeichensatz setzen und sich die Angabe sparen.

Bleibt aber noch, überhaupt erstmal überall den mb_-Präfix einzustreuen.

Möglicherweise stöhnen Sie jetzt und lassen vor dem inneren Auge Tausende von Codezeilen vorbeiwandern, die anzupassen sind. Dafür hat sich das PHP-Team mbstring.func_overload ausgedacht. Das bedeutet nichts anderes, als dass die wichtigsten nicht multibytefähigen Stringfunktionen kurzerhand auf ihre multibytefähigen Pendants „umgebogen“ werden. Das kann allerdings nur in der php.ini, einer .htaccess-Datei oder in der httpd.conf gesetzt werden – nicht mit ini_set() im PHP-Script. Zu den nur verzeichnisspezifischen Einstellungen bemerkt die Dokumentation dann aber auch gleich:

It is not recommended to use the function overloading option in the per-directory context, because it’s not confirmed yet to be stable enough in a production environment and may lead to undefined behaviour.

Es bleibt also eigentlich nur der Einsatz in der php.ini – und genau dort ist er prädestiniert für „fix one, break another“: Vielleicht funktioniert Ihre Applikation danach problemlos; eine andere geht dafür kaputt. Sofern Sie den gesamten Server mit allen PHP-Scripts selbst kontrollieren, kann function overloading eine Option für Sie sein, unter Vorbehalt. Aber spätestens dann, wenn Sie Ihre Scripts z.B. auf eine andere Maschine portieren möchten oder gar auf einen Webspace, bei dem Sie gar keinen Zugriff auf die php.ini haben, stehen Sie dumm da und wünschen sich, doch lieber überall die Funktionen mit dem mb_-Präfix verwendet zu haben.

Bei PHP 6 wird dann übrigens alles anders. Dort wird es dann für Unicode-Strings und für Byte-Strings zwei verschiedene Typen geben, und die ganzen stringverarbeitenden Funktionen werden sich automatisch entsprechend des Typs des Strings korrekt verhalten. Soll heißen: Was in der guten alten Zeiten von Latin1 noch „einfach so“ funktionierte, wird mit PHP 6 dann vermutlich auch „einfach so“ funktionieren, mit schickem Unicode-Support.

Nur heute – heute ist es ein K(r)ampf. Nehmen Sie lieber Perl.

Update: Christian hat die Aufforderungen des Artikels erfreulich ernst genommen, bemerkt aber auch zerknirscht, dass seine Frau die Zettel wieder vom Kühlschrank weg haben möchte. Aber der Gedanke zählt!

Wenn es um Sicherheitsprobleme geht, werfen die entsprechenden Security-Advisories gerne mit Begriffen wie „cross-site scripting“, „cross-site request forgery“ oder „sql injection“ um sich. Aus aktuellem Anlass können wir mal ein praktisches Beispiel einer real existierenden Software nehmen (deren Name lieber ungenannt bleibt), mit der man mandantenfähig Rechnungen erstellen kann, die sich ein Kunde auf seinem Webspace installiert hat. Sie lief anfangs nicht, und die Basis seiner Anfrage war dann eher erstmal der Wunsch nach „Könnt ihr das zum Laufen bringen“-Support. Nachdem ich mir dazu kurz einige der fraglichen PHP-Dateien angeschaut hatte, wurde daraus dann aber eher ein kleines Lehrstück, das ich hier gerne (bis auf die Usernamen) unverändert weitergebe.

[...]

Es sieht mir auf den ersten Blick so aus, als könne man da sehr viel
verbessern, wenn ich das mal vorsichtig so sagen darf. ;-)  Ich sehe
schon auf den ersten Blick Angriffspunkte für SQL Injection sowie eine
Abhängigkeit von register_globals = On. Das erste ist ein handfester
Fehler; das zweite eröffnet eventuelle Angriffspunkte, die nicht sein
müssten. Freundlich gesagt sieht man der Software an, das sie von 2006
ist. :)

Willst du mal sehen, wie das mit SQL Injection funktioniert?

Gib mal bei der Anmeldung als Benutzername deinen Zugang "max" ein
und dann als Passwort dieses hier:

        '||ID='1

Schwupps, bist du drin. Das klappt mit "moritz" genauso. Und mit jedem
anderen auch.

Hintergrund ist, dass in der mod/authent.php ausgeführt wird:

        $result=$dbverbindung->run_sql("SELECT ID,User,Pwd FROM Admin WHERE User='"
          .$user."' AND Pwd='".$pwd."'");

Das heißt, wenn du "max" und "geheim" angibst, steht da:

        SELECT ID,User,Pwd FROM Admin WHERE User='max' AND Pwd='geheim'

Genauso steht dann aber auch da, wenn du mein Trick-Passwort anwendest:

        SELECT ID,User,Pwd FROM Admin WHERE User='max' AND Pwd=''||ID='1'

Also "selektiere alle User, deren Name 'max' ist und bei denen das
Passwort leer ist (was nie vorkommt), oder alle User, deren ID = 1 ist".

Das "Schöne" ist: Es wird so einfach der User 1 selektiert. Dass du da
"max" angibst, tut überhaupt nichts zur Sache. Siehe hier:

        mysql> SELECT ID,User,Pwd FROM Admin WHERE User='moritz' AND Pwd=''||ID='1';
        +----+--------+---------+
        | ID | User   | Pwd     |
        +----+--------+---------+
        |  1 | max    | geheim  | 
        +----+--------+---------+
        1 row in set (0.00 sec)
        
Aber: Da mod/authent.php immer den Namen aus dem _Formular_ in die
Session als angemeldeten User übernimmt, musst du nicht mal die numerische
ID kennen. Es reicht, dass das SQL-Statement _irgendeinen_ Datensatz
zurückliefert; dann schreibt es den Namen aus dem Formular in die
Session. Sprich, beim vorigen SQL-Statement wäre ich dann als "moritz"
angemeldet, obwohl das SQL-Statement deinen Account geliefert hat. Ich
brauche also nur einen beliebigen Benutzernamen sowie '||ID='1, dann
komme ich rein.

Richtig WÄRE im Programmcode übrigens:

        $result=$dbverbindung->run_sql("SELECT ID,User,Pwd FROM Admin WHERE User='"
          .mysql_real_escape_string($user)."' AND Pwd='".mysql_real_escape_string($pwd)."'");

Allerdings müsste man solche Fehler an ...

        $ grep -r ">run_sql" . | wc -l
        92

... Stellen in Ordnung bringen. Da kann man's auch gleich selber
schreiben. (Dass das Problem nicht nur beim Login vorliegt, sondern bei
jedem SQL-Statement, zeigt mir auch, dass das kein "Bug" ist, den der
Autor dort versehentlich eingebaut hat, sondern dass ihm grundlegendes
Verständnis vom Design von Webapplikationen fehlt. Ist ja nicht so, dass
die PHP-Dokumentation das nicht alles haarklein erklären würde..! Da
findet sich Code wie der von ihm fast 1:1 in der Doku - als Beispiel
dafür, wie man's NICHT macht.)

[...]

Folge: Der Kunde verpackt das installierte PHP-Script noch hinter einer .htaccess-Datei zum „übergeordneten“ Passwortschutz. Da es sich um eine Anwendung handelt, die er ausschließlich selbst einsetzt und die keinen Dritten zur Verfügung stehen wird, ist dies hier ein – vom Sicherheitsaspekt her – akzeptabler Kompromiss. Dass man üblicherweise lieber Software von Leuten einsetzen sollte, die ihr Handwerk verstehen, versteht sich hoffentlich von selbst.

Dieser Artikel ist der dritte Teil einer kleinen Gruppe von Artikeln über MySQL-Backups. Die weiteren Artikel werden hier verlinkt, sobald sie erscheinen.

Hier ist der erste Artikel: MySQL-Backups, aber wie?

Hier ist der zweite Artikel: MySQL Replikation

Hier ist der vierte und letzte Artikel: MySQL mit daemontools

Nachdem ich hier schon vor einer Weile beschrieben habe wie man eine MySQL-Replikation einrichtet und dabei am Rande erwähnt habe, daß man die durch die Replikation anfallenden Binary Logs beizeiten wegrotieren sollte, will ich heute darauf eingehen, wie man das bewerkstelligen kann.

Wer unbedingt möchte, kann sich sein eigenes Skript dazu schreiben, das in allen Slaves nachschaut, welche Binary Log Datei sie gerade lesen und dann auf dem Master alle Binary Log Dateien die älter sind als die älteste von einem Slave genutze entfernt. Diese Vorgehensweise funktioniert, ist aber ein bißchen aufwendiger. Vor allem gibt es ein paar Stolperfallen. So ist es mir zum Beispiel schonmal passiert, daß MySQL sich nach einem Update veranlaßt sah, den üblichen Dateinamen der Binary Logs zu verändern und diese sogar gleich in einem ganz anderen Ordner zu speichern (der Speicherort richtet sich anscheined danach, ob und wenn ja wo PID-Dateien abgelegt werden) — nach solchen Aktionen läuft so ein Skript dann natürlich gegen die Wand. Wobei man dieser Art von Veränderungen vorbeugen kann, indem man in seiner my.cnf explizit einträgt, wo Binary Logs abzulegen sind und wie sie zu heißen haben (mit --log-bin[=base_name]). Wichtig ist bei dieser Vorgehensweise vor allem, daß man keinen Fehler macht und keinem Slave ein Binary Log wegnimmt das er noch benötigt.

Und es gibt noch eine weitere Stolperfalle: Normalerweise heißen Binary Log Dateien z.B. mysql-bin.000008 oder nach dem Hostnamen (es sei denn, man hat mit --log-bin[=base_name] etwas anderes angeordnet). Wer den üblichen Ärger mit Software gewohnt ist, ahnt jetzt schon, was kommt. Die Dateien tragen eine Nummer im Namen die hochgezählt wird — und selbstverständlich auch irgendwann überlaufen wird, später oder eben auch mal früher. Nun könnte man meinen, man hätte ja die alten Binary Logs wegrotiert, da könnte MySQL ja die alten Namen quasi recyceln. Das wird es allerdings nicht tun, denn es pflegt in der Datei mysql-bin.index einen Index der bereits verwendeten Dateinamen (den Namen kann man wieder um mit --log-bin-index[=file_name] festschreiben). Das allerbeste an diesem Index ist, daß man ihn laut der Dokumentation von MySQL nicht editieren sollte während MySQL läuft. Und man sollte ihn editieren, denn wenn sein Inhalt nicht mit dem Zustand auf der Festplatte übereinstimmt, kann das bestimmte MySQL-Versionen verwirren, besonders wenn der unten beschriebene Befehl PURGE BINARY LOGS verwendet wird — neuere Versionen geben dann wenigstens noch eine Fehlermeldung aus.

Es geht aber auch einfacher: MySQL bietet für solche Fälle die Funktion PURGE BINARY LOGS, die hier dokumentiert ist. Sie entfernt nicht nur alte Binary Logs, sie aktualisiert auch den Index.

Hier hat man zwei Möglichkeiten, man kann entweder den Namen der ältesten Datei, die erhalten bleiben soll verwenden und z.B. befehlen PURGE BINARY LOG TO 'mysql-bin.001024'; oder man orientiert sich an einem Datum und befiehlt PURGE BINARY LOG BEFORE '2010-03-26 00:00:00';. Beides hat letztlich den gleichen Effekt, es ist eher eine Geschmacksfrage, welche Variante man nutzen möchte. Die erste hat den Vorteil, daß man das älteste noch benötigte Binary Log relativ bequem von einem Slave abfragen kann. Bei der zweiten Variante müßte man vom Slave abfragen wie viele Sekunden er hinter dem Master zurückliegt und dann den passenden Zeitpunkt berechnen.

Ein Vorteil von PURGE BINARY LOG ist in jedem Fall, daß es keinem aktiven Slave ein Binary Log wegnehmen wird. Sollte man das versuchen oder aus Versehen machen, dann wird MySQL den Fehler abfangen. Moneyquote: „This statement is safe to run while slaves are replicating. You need not stop them. If you have an active slave that currently is reading one of the log files you are trying to delete, this statement does nothing and fails with an error.“ Aber das gilt nur für Slaves, die zu diesem Zeitpunkt eine aktive Verbindung zum Master haben: „However, if a slave is not connected and you happen to purge one of the log files it has yet to read, the slave will be unable to replicate after it reconnects.“

Wer darauf vertraut, daß seine Slaves nicht allzuweit hinterherhinken, kann sogar die Variable expire_log_days in MySQL setzen, dann kümmert sich MySQL automatisch darum, daß die Binary Logs nach der angegebenen Anzahl von Tagen gelöscht werden. Sowas sollte man ggf. mit genügend Abstand einrichten und sich mit Nagios oder etwas ähnlichem einen Check bauen, der einen warnt wenn ein Slave dieser Grenze näher kommt.

Wir verwenden für diese Aufgabe hier ein einfaches Skript, das ich als Referenz hier verlinke.

Mein Kollege Christopher hatte sich in seinem Artikel über MySQL-Replikation ja bereits über die etwas gewöhnungsbedürfte Behandlung von localhost vs. 127.0.0.1 bei MySQL ausgelassen. Wir konfigurieren bei uns laufende MySQL-Server in der Regel mit skip-networking oder, wenn z.B. (lokale) Replikation benötigt wird, zumindest mit bind-address = 127.0.0.1.

Auf einem Server, den wir nur während einer Übergangsphase ausnahmsweise betreuen, musste ich nun dennoch erstmal Rätselraten: Die Verbindung über 127.0.0.1 klappte nämlich nicht, obwohl MySQL sich durchaus an das Loopback-Interface gebunden hatte und keinerlei iptables-Regeln vorlagen, die einen Connect auf dieser IP verhindert hätten. Die wenig aussagekräftige Fehlermeldung:

$ mysql --host=127.0.0.1 -p
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0

Insbesondere ein system error: 0 erschien mir ziemlich verwirrend, ist die 0 doch typischerweise der Exitcode für „alles klar, kein Problem“.

Nach einem Suchen war die Lösung überraschend einfach: Auf dem fraglichen System existierte eine /etc/hosts.deny mit ALL : ALL darin, und es war explizites Whitelisting mittels mysqld : 127.0.0.1 in der /etc/hosts.allow vonnöten. Für jemanden wie mich, der eigentlich grundsätzlich IP-basierte Zugriffe per iptables regelt und nicht per /etc/hosts.{allow,deny} durchaus eine Stolperfalle … und MySQL-typisch verwirrend, denn mit localhost funktioniert’s ja. (Wer Christophers Artikel nicht gelesen hat: Es funktioniert deshalb, weil MySQL bei der Angabe von localhost grundsätzlich eine socketbasierte Verbindung benutzt, weshalb nebenbei dann auch sämtliche Portangaben ignoriert werden – und eben auch die /etc/hosts.{allow,deny}.)

Dieser Artikel ist der zweite Teil einer kleinen Gruppe von Artikeln über MySQL-Backups. Die weiteren Artikel werden hier verlinkt, sobald sie erscheinen:

Hier ist der erste Artikel: MySQL-Backups, aber wie?

Hier ist der dritte Artikel: Hinter der MySQL-Replikation aufräumen

Hier ist der vierte und letzte Artikel: MySQL mit daemontools

Wie im ersten Teil erläutert, nutzen wir gerne eine zweite MySQL-Instanz mit Replikation (einen sogenannten Slave), um MySQL-Backups zu realisieren. In diesem Teil geht es um die Einrichtung dieser Replikation.

Vorab ein paar Worte zu unserem MySQL-Setup:

• Wir verwenden fast immer die Einstellungsoption skip-networking mit der die gesamte Erreichbarkeit des MySQL-Datenbanksystems über das Netzwerk abgeschaltet wird, inklusive der Möglichkeit das Datenbanksystem über das Loopback-Interface des Localhost anzusprechen.
• Wenn die Verbindung über das Loopback-Interface aber benötigt wird, verwenden wir meist bind-address = 127.0.0.1, womit die Netzwerk-Erreichbarkeit auf die Loopback-Verbindung beschränkt wird.
• Wir betreiben den Slave häufig auf demselben Server auf welchem auch der Master läuft. Die zusätzliche Last hält sich in Grenzen und es gibt weniger potentielle Fehlerquellen. Prinzipiell kann ein Slave aber natürlich auch auf einer anderen Maschine laufen.
• Egal wo der Slave nun läuft, in jedem Fall muß man leider auf die Option skip-networking verzichten. Replikation kann leider nicht über Sockets erfolgen, sondern muß zwingend durch den TCP/IP Stack gehen. Bei Slaves auf demselben Server wie dem Master nutzen wir daher gezwungenermaßen bind-address = 127.0.0.1.

Nun zur Umsetzung:

Zunächst wird die /etc/my.cnf einfach kopiert und als /etc/my-slave.cnf gespeichert. In dieser Datei müssen dann einige Optionen angepaßt werden, die dafür sorgen daß die neue MySQL-Instanz (im weiteren Slave genannt) auf einen anderen Datenbestand zugreift und ein anderes Socket bereitstellt. Das wesentliche in Auszügen:

[mysqld]
port = 3307
socket = /var/lib/mysql-slave/mysql.sock
datadir = /var/lib/mysql-slave
server-id = 2


Auf den folgenden Teil kann man verzichten, wenn man MySQL nicht per init-Skript startet sondern mit den daemontools (so machen wir es fast immer):


[mysqld_safe]
err-log=/var/log/mysql/mysql-slave.log
pid-file=/var/run/mysql/mysq-slave.pid


Verzichten heißt in diesem Fall ggf. auskommentieren. Die ursprüngliche Form sollte man auf keinen Fall stehen lassen.

In einzelnen Punkten kann das Setup mal etwas abweichen. Wir versuchen uns meist an der Default-my.cnf des System zu orientieren, dementsprechend können sich die Pfade leicht ändern.

Zum Port ist zu sagen, daß er recht beliebig ist. Prinzipiell kann jeder Port genommen werden, den die bereits vorhandene MySQL-Instanz (der Master) nicht nutzt (bzw. den kein anderer Dienst nutzt).

Die Server-ID ist ebenfalls recht beliebig, es muß lediglich eine andere sein als beim Master: „For each server, you should pick a unique positive integer in the range from 1 to 232 – 1, and each ID must be different from every other ID.“ Quelle: MySQL Doku

Die /etc/my.cnf benötigt weniger Anpassungen:


[mysqld]
log-bin=mysql-bin
server-id = 1


Das binary log benötigt man bei Replikation immer, man kommt nicht drum herum. Hier bei sei angemerkt, daß man es unter Umständen später mit Logrotation oder ähnlichen Mechanismen wegräumen kann. Das ist aber nicht ganz einfach, einerseits muß man darauf achten dem Master nicht das aktive Log wegzurotieren, andererseits könnte man die alten binary logs unter Umständen benötigen, falls man in der Zukunft weitere Slaves anlegt. (Diese kann man aber auch von einem späteren Stand des binary logs beginnen.)

Nun muß man dafür sorgen, daß Master und Slave auch beide gestartet werden. Wie man hier vorgeht hängt sehr davon ab, wie man seine MySQL-Instanzen startet, also ob man z.B. selber was skriptet, init verwendet oder lieber auf daemontools oder runit setzt. Wir bevorzugen daemontools.

Bei daemontools muß man lediglich ein run-Skript schreiben, mit dem der Slave mit der /etc/my-slave.cnf gestartet wird. Im einfachsten Fall sieht das so aus:


#!/bin/sh
exec /usr/bin/mysqld_safe --defaults-file=/etc/my-slave.cnf


Wer auf init setzt, muß entweder ein neues init-Skript nur für den Slave schreiben oder das vorhandene so anpassen, daß es beide Instanzen startet. Je nach verwendeter Distribution kann das unterschiedlich aufwendig werden.

An dieser Stelle sollte man den Master schonmal neustarten, damit er die Änderungen in der /etc/my.cnf übernimmt und vor allem anfängt, das binary log zu führen.

Jetzt muß man noch dafür sorgen, daß der Slave auch einen eigenen Datenbestand hat. Wie man dabei vorgeht hängt stark davon ab, wie viel einmalige Downtime man sich beim Einrichten des Slaves erlauben kann und wie groß der Datenbestand ist.

Falls der Datenbestand in der MySQL-Instanz bereits groß ist und eine lange Downtime vermieden werden soll, kann zunächst mit rsync eine Kopie des MySQL-Verzeichnisses eingefertigt werden. Diese Kopie ist zwar nicht gut genug um davon die neue Instanz zu starten, aber da meist ein Großteil des Datenbestands selten oder gar nicht verändert wird, kann man hier schon viel Vorarbeit leisten und so später Zeit sparen.

# mkdir /var/lib/mysql-slave
# rsync -vaH /var/lib/mysql/ /var/lib/mysql-slave/

Wie immer bei rsync ist der Slash am Ende des Pfades jeweils wichtig. Hier wird rsync befohlen, nicht den Ordner /var/lib/mysql/ selbst, sondern seinen Inhalt in den Ordner /var/lib/mysql-slave/ zu kopieren.

Wenn eine spürbare Verlangsamung des Systems vermieden werden soll, kann man das übrigens auch mit nice oder bei neueren Kerneln ionice machen:

# nice -n19 rsync -vaH /var/lib/mysql/ /var/lib/mysql-slave/

bzw.:

# ionice -c3 rsync -vaH /var/lib/mysql/ /var/lib/mysql-slave/

Wie gesagt ist der so duplizierte Datenbestand nicht konsistent! Man kann den Slave noch nicht damit starten. Um dies nun möglich zu machen muß man den Master dazu bringen alle Daten konsistent auf die Platte zu schreiben und ihn kurzzeitig mit einem READ LOCK sperren und dann während die Sperre besteht erneut rsync verwenden. Bei der Gelegenheit kann man gleich die Zugriffsrechte für den Slave einräumen.

Man verbindet sich also mit dem Master und befiehlt:

mysql> GRANT REPLICATION SLAVE ON *.* TO slave@'localhost' IDENTIFIED BY 'PASSWORD';
mysql>FLUSH TABLES WITH READ LOCK;
mysql>SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 2730869 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)


(Das Passwort kann man selbstverständlich selbst wählen.)

Die Ausgabe von SHOW MASTER STATUS sollte man sich notieren, wir benötigen sie gleich, genau genommen den Dateinamen und die Position (den Offset) im binary log, in unserem Bespiel ist das die 2730869. (Eventuell benötigt man sie später nochmal, wenn man weitere Slaves einrichten sollte, die auch ab dieser Position die Replikation beginnen sollen. In diesem Fall darf man später auch das binary log nicht wegrotieren. Insgesamt scheint dieser Anwendungsfall aber eher selten zu sein.)

Nun muß man den Master kurz anhalten und die Datenverzeichnisse synchronisieren. Hierbei sollte man ausnahmsweise mysqladmin benutzen, damit die Sperre erhalten bleibt. (Normalerweise werden Sperren beim Beenden aufgehoben.)

Update:

# mysqladmin shutdown
rsync -vaH /var/lib/mysql/ /var/lib/mysql-slave/

Es muß mysqladmin shutdown verwendet werden, mysqladmin stop war ein Tippfehler. /Update.

Sobald das erledigt ist, kann man den Master wieder starten und die Sperre aufheben, letzteres mit: mysql>UNLOCK TABLES;

(Wer später weitere Slaves aufsetzen möchte, die an dieser Stelle im Log mit der Replikation beginnen, sollte sich jetzt ein Backup von /var/lib/mysql-slave ziehen, z.B. einen Tarball.)

Jetzt kann der Slave gefahrlos gestartet werden, denn sein Datenverzeichnis enthält konsistente Daten. Wenn er sauber startet, kann man sich auch gleich mit ihm verbinden.

Achtung: Wenn man Master- und Slave-Instanz auf dem selben Computer am Laufen hat, gilt es bei der Verbindung zur Slave-Instanz folgendes zu beachten: Man kann nicht wie gewohnt einfach mysql auf der Kommandozeile ausführen, da das eine Verbindung zur Master-Instanz öffnen würde. Auch die Angabe des Ports mit mysql -P 3307 hilft nicht weiter, da MySQL in diesem Fall, trotz der expliziten Portangabe eine Verbindung zur Master-Instanz öffnen würde (dafür mal ein verdientes #fail an die Adresse der Programmierer). Das gleiche ärgerliche Verhalten würde auch bei mysql -P 3307 -H localhost auftreten.
Damit das funktioniert muß man mysql -P 3307 -H 127.0.0.1 (IPv4) oder mysql -P -H ::1 (IPv6) benutzen. Eine wie ich finde noch elegantere Lösung ist mysql -S /var/lib/mysql-slave/mysql.sock. Wozu den Networkstack bemühen, wenn man schon eine Shell offen hat?

Nun muß dem Slave noch mitgeteilt werden, daß er Änderungen an seinem Datenbestand vom Master replizieren sollt.

mysql> CHANGE MASTER TO
-> MASTER_HOST='localhost',
-> MASTER_USER='slave',
-> MASTER_PASSWORD='PASSWORD.',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS='2730869';
mysql> START SLAVE;

Der Slave beginnt jetzt ausgehend von der angegebenen Position (2730869) im binary log seinen Datenbestand zu synchronisieren. Den Fortschritt kann man mit mysql> SHOW SLAVE STATUS\G am besten einsehen. Die letzte Position ist dabei das entscheidende. Seconds_Behind_Master gibt an, ob der Slave mit dem Master synchron ist oder nicht. Steht hier der Wert NULL, ist der Slave angehalten und synchronisiert sich nicht mit dem Master, steht hier ingegen eine Zahl größer 0, gibt sie an wie viele Sekunden der Slave hinter dem Master zurückliegt (diese Zahl sollte schnell kleiner werden), steht hier genau 0 dann ist der Slave synchron zum Master.

Für ein Projekt benötigte ich eine Möglichkeit, eine Duplikatsprüfung durchzuführen. Anforderung war, nicht nur identische, sondern auch „ähnliche“ Datenbank-Einträge wiederfinden können. Dabei stieß ich auf den Algorithmus der Levenshtein-Distanz. MySQL kennt von Haus aus keine Funktion hierfür. Nach einigem Suchen bin ich allerdings auf ein kleines Plugin gestoßen, dass diesen Algorithmus als user-defined function nachrüstet.

Um es kurz zu machen – für die Installation auf einem CentOS 5 auf einer 64-Bit-Plattform habe ich folgende Schritte benötigt:

mkdir /usr/local/src/mysql_levenshtein_udf-1.0
cd /usr/local/src/mysql_levenshtein_udf-1.0
wget http://joshdrew.com/mysql_levenshtein_udf-1.0.tar.gz
tar -xzvf mysql_levenshtein_udf-1.0.tar.gz
gcc -I/usr/include/mysql -fPIC -O -pipe -o mysqllevenshtein.so -shared \
  -L/usr/lib64/mysql -lmysqlclient mysqllevenshtein.cc
cp mysqllevenshtein.so /usr/lib64/libmysqllevenshtein.so

Das -fPIC steht nicht in der README, wird aber für die 64-Bit-Plattform benötigt. Interessanterweise sucht mysqld die .so-Datei in /usr/lib64 und nicht in /usr/lib64/mysql, wie ich eigentlich erwartet hätte. Aber sei’s drum.

Auf der mysql-Shell schließlich noch als root:

CREATE FUNCTION levenshtein RETURNS INT SONAME 'libmysqllevenshtein.so';

Und das war’s auch schon. MySQL merkt sich solche vom Benutzer eingefügten Funktionen in der Tabelle mysql.func, so dass sie problemlos einen Restart überdauern. Sehr schick!

Und so wird’s benutzt, hier zum Beispiel vor dem Hinzufügen eines neuen Datensatzes, um zu schauen, ob es schon jemanden gibt, der möglicherweise so in etwa ebenfalls „Jonas Pasche“ heißt – nur dass eben auch „Ionas Paschke“ gefunden würde:

SELECT *
  FROM contact
 WHERE LEVENSHTEIN(n_family, "Pasche") < 2
       AND LEVENSHTEIN(n_given, "Jonas") < 2;

Okay, diese Stolperfalle habe ich mir letztlich selbst gebaut – woran man nicht alles denken muss.

Ein Kunde betreibt auf seinem Server einige Dienste, die eine Art „Lebenszeichen“ in Form eines Unix-Timestamps (für Unwissende: Die Zahl der Sekunden seit dem 1. Januar 1970) in einer MySQL-Tabelle hinterlassen. Da diese Dienste besonders wichtig für ihn sind, haben wir die Nagios-Überwachung seines Servers um einen Check für diese Dienste erweitert, der sich die Differenz zwischen der dort gespeicherten Zeit und der aktuellen zeit in Sekunden heraussucht. Hierbei darf ein gewisser Schwellenwert nicht überschritten werden, sonst gibt es einen Alarm. Und so sieht’s aus:

SELECT name,
       text,
       UNIX_TIMESTAMP(NOW())-text AS diff
  FROM setup
 WHERE name LIKE "%\_time"

Ich rechne hier die aktuelle Zeit mittels UNIX_TIMESTAMP() in einen Unix-Timestamp um, damit ich Sekunden mit Sekunden vergleiche – daraus ergibt sich dann einfach eine Differenz. Das klappte auch hervorragend.

Bis zur Zeitumstellung am Sonntag. Denn hier zeigt sich letztlich, dass die Zeit „2009-10-25 02:17:00“ durchaus nicht eindeutig ist: Es gibt sie nämlich um 2:17 Uhr vor der Zeitumstellung, und es gibt sie um 2:17 nach der Zeitumstellung, also um „gefühlt 3:17 Uhr“. Die Unix-Zeit, die ja einfach nur stupide Sekunden zählt, läuft aber eben einfach weiter. Das bringt einen zur konsequenterweise logischen, aber trotzdem erstaunlichen Situation, dass zwei Unix-Timestamps die gleiche lokale Zeit ergeben können. Konkret:

1256429820 => 2009-10-25 02:17:00 (vor der Zeitumstellung)
1256437020 => 2009-10-25 02:17:00 (nach der Zeitumstellung)

Daraus ergibt sich wiederum, dass sich aus der lokalen Zeit „2009-10-25 02:17:00“ nicht auf einen Unix-Timestamp schließen lässt, sondern gleich auf zwei, weil die lokale Zeit eben keine Angabe enthält, ob man sich gerade vor oder nach der Zeitumstellung befindet.

Nun befindet sich MySQL in meinem Script in genau der Situation: Es muss einen Unix-Timestamp aus „2009-10-25 02:17:00“ bilden, und das kann es nicht verlässlich. Der springende Punkt liegt aber schon beim NOW(): An sich weiß MySQL nämlich durchaus, ob es sich vor oder nach der Zeitumstellung befindet. Aber in dem Moment, wo NOW() diese aktuelle Zeit in einen String konvertiert hat, ist diese Information faktisch verloren – man sieht sie ja dem String nicht mehr an. UNIX_TIMESTAMP() kann also letztlich nur raten – und rät, die Zeitumstellung sei schon gelaufen.

Das führt zu der ärgerlichen Situation, dass das obige SQL-Statement in der fraglichen Stunde zwischen 2 und 3 Uhr plötzlich der Meinung ist, die zu checkenden Dienste seien bereits eine Stunde überfällig. Ab Punkt 2 Uhr liefert UNIX_TIMESTAMP(NOW()) nämlich bereits den Timestamp für 2 Uhr nach Zeitumstellung.

Um so ärgerlicher, weil ich viel einfacher auch einfach nur UNIX_TIMESTAMP() ohne Argument hätte schreiben können. Das liefert dann nämlich den aktuellen Timestamp, und dann sogar den richtigen, weil hier ja MySQL „die lokale Zeit“ (von der es weiß, ob sie vor oder nach der Umstellung liegt) in einen Timestamp konvertiert und keinen String, dem diese Information fehlt.

Aber ich denke, ich habe meine Strafe bereits bekommen: Am Sonntag um kurz nach 2 Uhr nachts von der Überwachung aus dem Schlaf geklingelt zu werden, dürfte hoffentlich ausreichend gewesen sein. Das Nagios-Check-Script ist dann inzwischen auch angepasst.

Dieser Artikel ist der erste Teil einer kleinen Gruppe von Artikeln über MySQL-Backups. Die weiteren Artikel werden hier verlinkt, sobald sie erscheinen.

Hier ist der zweite Artikel: MySQL Replikation

Hier ist der dritte Artikel: Hinter der MySQL-Replikation aufräumen

Hier ist der vierte und letzte Artikel: MySQL mit daemontools

Wenn es um Backups geht, sind Datenbanken immer nochmal gesondert zu beachten. Zwar kann man die vom Datenbanksystem auf die Platte geschriebenen Daten mit den üblichen Backup-Tools sichern, aber in den meisten Fällen wird man so kein konsistentes Backup der im Datenbanksystem gespeicherten Daten erhalten. Dabei ist es auch relativ egal, ob man nun ein Backup aller im Datenbanksystem gehaltenen Daten haben möchte, oder nur von einzelnen Datenbanken oder Tabellen. In jedem Fall hat mein einige Hürden zu nehmen.

Man braucht also ein Extra-Tool und nicht selten muß man sich die Lösung dann noch zusammenskripten. Im Planet MySQL Blog findet sich eine schöne Auflistung der gängigsten Lösungsmöglichkeiten:

There are a couple of options available to get consistent backups from MySQL.

1. Use mysqldump with FLUSH TABLES WITH READ LOCK
2. Use a slave with STOP SLAVE and your favourite backup tool.
3. For innodb, use the commerical backup tool ibbackup
4. Use LVM (Logical Volume Manager) snapshots with FLUSH TABLES WITH READ LOCK
5. Shutdown the database.

Man hat also immerhin verschiedene Optionen, unter denen man wählen kann. (Diese Liste erhebt keinen Anspruch auf Vollständigkeit.) Nebenbei wird aber offensichtlich, daß die Frage nach dem Backup bei der Konzeptionierung und Entwicklung von MySQL keine große Rolle gespielt hat – wie bei leider allzuvielen anderen Softwareprojekten auch, darunter viele andere Datenbanken.

Die Wahl die wir für unsere Anwendungsfälle treffen wird von folgenden Überlegungen geleitet:

• #1 bedeutet dem Datenbanksystem wird befohlen, alle Tabellen konsistent auf die Platte zu schrieben und sich dann zu sperren. Dann zieht man mit mysqldump alle Daten aus dem Datenbanksystem und hebt zuletzt die Sperre wieder auf. Solange die Sperre besteht ist kein schreibender Zugriff auf die Daten möglich. Soetwas kann man folglich nur dann machen, wenn man ein regelmäßiges Wartungsfenster hat, in dem die Applikation die auf die Datenbank zugreift nicht (oder nur-lesend) benutzt wird. Bei einem Buchhaltungssystem mag das noch praktikabel sein, aber bei Internet-Diensten, die keine Ladenöffnungszeiten kennen kommt diese Vorgehensweise eher selten in Frage. Unabhängig davon tritt hier das Problem auf, daß das Sichern der Daten mit mysqldump Zeit beansprucht. Hier müßte also zusätzlich darauf geachtet werden, daß das Wartungsfenster eingehalten wird.
• Für #5 gilt im Grunde das gleiche. Ein Anhalten des Datenbanksystems hat für die es benutzenden Applikationen sogar noch gravierendere Konsequenzen als eine Sperre, sie können nichtmal mehr lesend auf die Daten zugreifen. Hier würde das Backup nicht mit mysqldump geschrieben, sondern mit einem anderen Tool welches auf Dateisystemebene arbeitet. Auf Unix-basierten Systemen gibt es da eine sehr große Auswahl, die von tar über dump bis zu amanda reicht, um nur ein paar zu nennen.
• #3 kommt für uns nicht in Frage, weil wir keine kommerziellen Tools benutzen wollen. Das ist sowohl eine philosophische Frage als auch eine praktische, denn zu einem kommerziellen Tool könnten wir unseren Kunden nicht den Support bieten, den wir bei freier und quelloffener Software leisten können.
• #4 bedeutet auch, daß die Datenbank in einen konsistenten Zustand gebracht und dann gesperrt wird, allerdings nur solange bis ein LVM-Snapshot erstellt wurde. Das kann zwar auch etwas dauern (aber nur wenn die Hardware oder Software an ihre Grenzen stoßen, LVM-Snapshots gehen in der Regel schnell), geht aber in jedem Fall schneller als Methode #1. Hier gibt es aus unserer Sicht neben der (wenngleich kürzeren) Sperrzeit noch zwei Nachteile:
  1. Zum einen benötigt man bereits eine LVM-Umgebung. Sicherlich kann man auch unter einem laufenden System eine einrichten, aber das ist knifflig, kann schief gehen, dauert und geht nicht gänzlich ohne Downtime mindestens des Datenbanksystems. Nun mag man sich angewöhnt haben unter jedem System LVM einzurichten, um genau solchen Eventualitäten vorzubeugen. Es mag aber Situationen geben in denen man darauf verzichtet, weil die zusätzliche Komplexität unnötig ist oder gar störend wäre, z.B. wenn man ein System von ISCSI bootet und man statt mit LVM zu arbeiten einfach weitere ISCSI-Targets einbinden könnte.
  2. Die Wiederherstellung aus dem Backup ist zum Teil sehr aufwendig. Häufig wird man einen separaten MySQL-Dienst starten wollen oder müssen, über den man auf den Datenbestand im Snapshot zugreift. Wenn man innodb verwendet bleibt einem im Grunde nichts anderes übrig, da innodb einen großen Container für alle gemeinsam verwalteten Datenbanken verwendet, aus dem sich einzelne Tabellen nicht ohne weiteres extrahieren lassen.

Bleibt somit noch Methode #2. Hier wird ein zweiter MySQL-Dienst eingerichtet und als Slave konfiguriert, so daß er über die Replikationsfunktion von MySQL den Datenbestand des ersten spiegelt und sich aktuell hält. Für Backups wird dann auf dem Slave die Replikation vorübergehend angehalten. Ab diesem Punkt kann im Grunde jede beliebige Backup-Methode verwendet werden, man kann den Slave sogar für das Backup runterfahren. Uns genügt es aber, bei angehaltener Replikation alle Datenbanken und Tabellen des Slave einzeln mit mysqldump auszulesen und an einen Ort zu schreiben, an dem unser normales Backup sie findet und sichert.

Ein Slave der nur für Backups verwendet wird, erzeugt nicht sonderlich viel Last auf einem System und kann daher prinzipiell auch auf demselben Rechner laufen wie der Master, wenn man das denn möchte. Wenn man so vorgeht, sollte man jedoch bedenken, daß man einen zweiten MySQL-Server betreibt, der auch entsprechend zu sichern ist. Wenn der Slave nur für Backups dienen soll, muß man Nutzerzugriff darauf unbedingt verhindern, weil vom Nutzer gemachte Änderungen sonst durch die Replikation überschrieben werden können usw. Damit das möglich ist, kann die mysql-Datenbank mit den Nutzerdaten auf dem Slave vom Master abweichen. In so einem Fall empfiehlt es sich, diese eine Datenbank separat vom Master zu sichern (hierbei kann man aber getrost mit einem READ LOCK arbeiten, da diese Datenbank relativ klein ist und sich schnell sichern läßt.)

Wie man die Replikation einstellt, wird im nächsten Artikel beschrieben.