Too Cool for Internet Explorer

Setting up Master-Slave replication using xtrabackup


In a previous blog entry i described a method of how to setup master-slave replication with mysql. In steps #4 and #5 i used mysqldump and mysql-client for creating a database dump on the master and importing it on the slave. The problem with this approach is, that the database tables are locked, as long as the dump is running. For small databases this might not be a problem, but as data grows, the time to create a dump takes longer and longer. @work we apparently reached some critical level -- mysqldump ran hours and hours and would probably still run, if i had not stopped it already.

Luckily there are more suitable tools for large databases available. Innodb hot backup and xtrabackup. I've decided to go with xtrabackup, because it's open-source, free and actively developed. Innodb hot backup is closed-source and not for free ;-).

The following steps are ment to replace steps #4 and #5 of my previous blog post.

1. building xtrabackup

For Linux i had to build xtrabackup from the source package, because there was no binary package available for my architecture -- it's very easy, though:

harald@master:~/xtrabackup-0.9.5rc$ automake -a -c
...
harald@master:~/xtrabackup-0.9.5rc$ ./configure
...
harald@master:~/xtrabackup-0.9.5rc$ make
...
harald@master:~/xtrabackup-0.9.5rc$ cd innobase/xtrabackup
harald@master:~/xtrabackup-0.9.5rc/innobase/xtrabackup$ make
...
harald@master:~/xtrabackup-0.9.5rc/innobase/xtrabackup$ sudo cp \
    innobackupex-1.5.1 /usr/local/bin
harald@master:~/xtrabackup-0.9.5rc/innobase/xtrabackup$ sudo cp \ 
    xtrabackup /usr/local/bin

Needless to say, that xtrabackup needs to be deployed on every database server.

2. creating a database dump

After successfully building and installing xtrabackup, taking a database dump is very easy:

root@master:~# innobackupex-1.5.1 --user=... --password=... \
    --defaults-file=... --databases="..." .

The command innobackupex-1.5.1 takes the following parameters:

--user
username to use for database connection
--password
password to use for database connection
--defaults-file
this parameter is required, if the my.cnf configuration file is not located at /etc/my.cnf
--dabases
space-separated list of databases to backup
.
destination directory to save dump to

Dumping the database with xtrabackup is incredible fast compared to mysqldump. With xtrabackup it's just a matter of minutes:

real    4m15.614s
user    0m11.710s
sys     0m14.960s

If xtrabackup was successful, it should have created a subdirectory which name is the current date/time, with all required files in it. The directory can now be copied to the slave:

root@master:~# scp -r 2010-03-02_15-02-24 root@xx.xx.xx.xx:~

3. Setting up the slave

The first thing to do on the slave is applying the binary log files to the database dump:

root@dbslave1:~# innobackupex-1.5.1 --apply-log 2010-03-02_15-02-24

...

100302 14:29:56  innobackupex: innobackup completed OK!

Innobackupex will show the message above, if everything was OK. Next task is to copy the database dump to it's new location on the slave. innobackupex is doing everything for you:

root@dbslave1:~# innobackupex-1.5.1 --copy-back 2010-03-02_15-02-24

...

100302 14:29:56  innobackupex: innobackup completed OK!

xtrabackup should now have copied the dump to the mysql data directory. It's a good idea to check the user and owner of the copied files and adjust them, when needed.

Last step is to start the replication. All information required to do so ist stored in the file xtrabackup_binlog_info:

root@dbslave1:~# cat 2010-03-02_15-02-24/xtrabackup_binlog_info
mysql-bin.000331	54249842

With this information available the replication can be set up as described in step #6 of my previous blog post.



MySQL master / slave replication


There are tons of tutorials about setting up master / slave replication for MySQL. Here are my own quick notes:

1. Master: /etc/mysql/my.cnf

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

2. Slave: /etc/mysql/my.cnf

[mysqld]
server-id = 2
log_bin   = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size  = 100M

3. Master: granting privileges for slave user on database master

GRANT REPLICATION SLAVE ON . 
TO '<slave_username>'@'<slave_ip>' 
IDENTIFIED BY '<slave_password>';

4. Master: creating database dump

Start mysql console as database root and enter the following command:

FLUSH TABLES WITH READ LOCK;

DON'T shut down the mysql client, otherwise the table lock is lost. Open a second shell to the database master and enter the following command on commandline:

mysqldump -u root -p... --databases ... --opt > masterdump.sql

Next, switch back to your mysql console and enter the following command:

SHOW MASTER STATUS;

The output will look something like:

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 40140874 |              |                  | 
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> 

Write down "File" and "Position" ... you will need it later for starting replication.

Now you can unlock the tables:

UNLOCK TABLES;

5. Slave: import database dump

Copy masterdump.sql to the slave server and import the database:

mysql -u root -p... < masterdump.sql

This may take quite some time ...

6. Slave: start replication

Start mysql client on slave and enter the following commands:

CHANGE MASTER TO 
MASTER_HOST='<master_host>', 
MASTER_USER='<slave_username>', 
MASTER_PASSWORD='<slave_password>', 
MASTER_LOG_FILE='<mysql-bin file name you've written down in step 4>', 
MASTER_LOG_POS=<master position you've written down in step 4>;

START SLAVE;


ORMs sind doof


ORM

Wikipedia sagt dazu:

Objektrelationale Abbildung (englisch object-relational mapping, ORM) ist eine Technik der Softwareentwicklung, mit der ein in einer objektorientierten Programmiersprache geschriebenes Anwendungsprogramm seine Objekte in einer relationalen Datenbank ablegen kann. Dem Programm erscheint die Datenbank dann als objektorientierte Datenbank, was die Programmierung erleichtert. [...]

Inzwischen bringt ja so ziemlich jedes PHP Framework seine eigene ORM Implementierung mit, es gibt aber auch einige Framework-unabhängige ORM Implementierungen. Ich habe mir in den letzten Jahren immer mal wieder verschiedenste ORM Implementierungen angesehen -- immer dann, wenn in mir der Wunsch nach einer objektorientierten Zugriffsweise auf meine Datenbanken aufkam. Leider jedoch konnte mich bisher keine ORM Implementierung überzeugen.

ORMs sind doof

Auch in mir kommt immer mal wieder der Wunsch auf objektorientiert auf meine Datenbanken zuzugreifen, da dies den Zugriff auf einzelne Datensätze -- Objekte -- erheblich vereinfacht. Jedoch -- zu welchem Preis wird diese Vereinfachung erkauft?

Modellierung

Ich modelliere meine Datenbanken schon seit Jahren mit dem ER Modeller dbWrench. Das ist meiner Meinung nach super komfortabel. Ich sehe auf einen Blick all meine Tabellen und die Abhängigkeiten bzw. Verknüpfungen zwischen einzelnen Tabellen. Über die Funktion "Forward Engineering" kann dbWrench mein Datenbankschema in der Datenbank immer aktualisieren. Da ich bei MySQL den Tabellentyp InnoDB verwende, sind auch in der Datenbank sämtliche Verknüpfungen festgehalten und liessen sich z.b. über die INFORMATION_SCHEMA Tabelle leicht auslesen.

Nun ist es leider so, dass offenbar so ziemlich jede ORM Implementierung die Datenbankdefinition auf Ihre Weise bekommen möchte. Da muss man entweder seitenweise XML oder YAML Konfiguration, oder gar ellenlangen PHP Code schreiben -- nur um der Anwendung eine Information bekannt zu geben, die eigentlich exakt so schon in der Datenbank vorhanden ist?

Abstraktion

Wie weit muss man die Datenbankzugriffe abstrahieren? Nun, es gibt da sicherlich die verschiedensten Anforderungen. Ich denke bei der Entwicklung von Unternehmenssoftware kann man die Anforderungen ziemlich genau spezifizieren. Man entscheidet sich zu einem gewissen Zeitpunkt für ein bestimmtes Datenbankprodukt. Normalerweise wird diese Entscheidung nicht nach wenigen Monaten oder Jahren über den Haufen geworfen -- es sei denn es gibt sehr triftige Gründe dafür.

Deshalb bin ich der Meinung, dass die Abstraktion nicht so weit gehen muss, dass sämtliche Datenbankzugriffe abstrahiert werden und für beliebige Datenbanksysteme geeignet sind. Im Gegenteil: ich entscheide mich ja nicht für eine bestimmte Datenbank nur aus Kostengründen, sondern auch, weil diese vielleicht Features mitbringt, die ein anderes Datenbanksystem nicht unterstützt.

So erweitert z.b. MySQL den SQL Standard um eigene spezifische Befehle, die es in anderen Datenbanken nicht gibt, die aber sehr praktisch sind. Das ist kein Alleinstellungsmerkmal von MySQL. Beispiel: Hätte ich mich für Oracle entschieden, wäre ich doch dumm, würde ich zum Abbilden / Abfragen von Hierarchischen Strukturen nicht CONNECT BY verwenden -- nur weil dies nicht Teil des SQL Standards ist und dies so mit keiner anderen Datenbank funktioniert.

Nur: keine ORM Implementierung kann auf diese einzelnen Datenbankfeatures eingehen -- womit ich beim nächsten Punkt angelangt wäre.

Abfrage

Das grösste Manko aller (PHP) ORM Implementierungen ist meiner Meinung nach die Abfrage einer Datenbank. Ich gebe zu: ich mag SQL -- es gibt mir das passende Werkzeug zum Abfragen einer relationalen Datenbank in die Hand -- es wurde zu diesem Zweck entwickelt! Ich schreibe gern SQL, da es strukturiert und übersichtlich ausschaut und mich schnell zum Ziel führt. Ich gebe weiterhin zu: Ich nutze auch gern MySQL spezifische SQL Features -- aus den oben genannten Gründen.

Nun ist es jedoch so, dass die ORM Implementierungen in der Regel den Zugriff soweit abstrahieren, dass -- normalerweise -- kein SQL mehr geschrieben wird. CONNECT BY und ähnliche Dinge wären damit Unmöglich. Heutzutage hat sich folgende Schreibweise zum Erstellen von Datenbankabfragen etabliert:

$dbo
    ->select(array(
        'media.media_id', 'media.media_name', 'member.member_name', ...
    ))
    ->from('media')
    ->join('member', 'member.member_id = media.member_id')
    ->where('media.category_id = ?')
    ->order('media.media_id')
...

Ich bin kein Fan einer solchen Schreibweise:

  1. Es ist kein SQL ;-)
  2. Es ist wesentlich mehr Aufwand als beim Schreiben von SQL erforderlich
  3. Ich kann keine Datenbankspezifischen SQL Erweiterungen verwenden
  4. Ich habe keine Kontrolle darüber, welchen SQL Code die ORM Implementierung daraus generiert
  5. Es liegt in der Natur der Sache, dass ein derartiges Konstrukt niemals auch nur annähernd so performant sein kann wie ein simples SQL Statement übergeben an die Datenbank
  6. Ich kann das Statement nicht per Copy / Paste zwischen meinem Datenbank Client und der Anwendung hin und her kopieren -- praktisch, wenn man das ganze erstmal testen will
  7. Wenn ich den Datenbanktreiber einer nicht-relationalen Datenbank hinterlege, weil ich mich z.b. entscheide statt MySQL MongoDB anzusprechen, wird diese Schreibweise ohnehin ad Absurdum geführt. (Nur als beispiel -- ich weiss nicht, ob irgendeine PHP ORM Implementation überhaupt nicht-relationale Datenbanken unterstützt)

Natürlich bietet so ziemlich jede ORM Implementierung einen Fallback zur Herkömmlichen Absetzung von SQL Anfragen ohne ein Objekt-Mapping. Nur, wenn ich damit an einer Stelle in meiner Anwendung anfange: warum dann überhaupt eine derartige Abstraktion nutzen?

Fazit

Meiner Ansicht nach erkauft man sich den konsequenten Einsatz eines ORM zu einem zu hohen Preis. Deshalb habe ich den Einsatz eines solchen für mich immer wieder verworfen. Mein Wunsch wäre ein SQL -> Objektmapper. D.h.: Ich schreibe SQL, zurück bekomme ich Objekte, mit denen ich weiterarbeiten kann ...



MongoDB + PHP + OSX


Ich spiele gerade ein wenig mit Mongo DB. Da ich das die Tage sicherlich noch mal gebrauchen kann, mache ich mir hier mal ein paar wichtige Notizen zur Installation:

  • Der Aktuelle PHP Treiber ist nicht kompatibel zum aktuellen stable Release (0.8.0) auf der Mongo DB Seite. Deshalb muss ein daily-build von Mongo DB heruntergeladen werden. Am besten lädt man hier das Paket inkl. aller Tools und Treiber (beinhaltet auch den Source der PHP Erweiterung). Mongo DB also herunterladen und irgendwohin installieren (z.b. /opt/mongo).
  • Zum Compilieren des PHP Treibers benötigt man die Boost C++ Libraries. Beim Compilieren der Boost Libraries wurden die Namen der Bibliotheken derart angelegt, dass sie beim Compilieren des PHP Treibers von Mongo DB nicht gefunden werden konnten. Deshalb habe ich nach dem ./configure --prefix=/opt/boost von Boost die Datei Makefile editiert und die Zeile BJAM_CONFIG= nach BJAM_CONFIG='--layout=system' geändert.
  • Nachdem Boost Installiert wurde, lässt sich nun der Mongo DB Treiber compilieren. Bei mir befindet sich der PHP Treiber unterhalb des Verzeichnisses /opt/mongo/drivers_and_tools/mongo-php-driver. Ein phpize, ./configure --with-mongodb=/opt/mongo/ --with-boost=/opt/boost/, make, make install und der Treiber ist gebaut und installiert.

Eigentlich ganz einfach ... aber ich vergesse es sonst garantiert wieder :-)



multiple ssh


Heute wollte ich mal den "slave status" unserer 5 Datenbank Server bei Pixelio ansehen. Natürlich ist es schrecklich umständlich sich auf allen Servern einzeln anzumelden um immer und immer wieder den gleichen Befehl auszuführen. Hierfür gibt es Tools, die ein Kommando auf mehreren Servern ausführen können (sollen). Ausprobiert habe ich pssh und dsh. Jedoch, mit beiden bin ich nicht klar gekommen. Da ich wie gesagt nur eben schnell mal einen Befehl auf den Slaves ausführen wollte, habe ich auch keine Lust gehabt, mich intensiv mit den Tools zu beschäftigen:

pssh ist python basiert, schon der erste Minuspunkt -- aber gut, da OSX eh python mitbringt, hab ich's halt installiert und konfiguriert. Allerdings wollte pssh keine Ausgabe zurück liefern und nach dem Ausführen hatte ich plötzlich jede menge SSH prozesse laufen, die ich manuell killen musste.

dsh hat imo eine mehr als rudimentäre Dokumentation. Nach Ausgiebigem googeln wusste ich dann auch, dass man offenbar eine Liste der Server benötigt, mit denen man sich Verbinden will und ausserdem noch Servergruppen anlegen kann. Klingt gut. Allerdings kann dsh offenbar meine "~/.ssh/config" nicht lesen und kennt daher auch meine hosts nicht. Da ich auf die Schnelle nicht in Erfahrung bringen konnte, wie ich in der Serverliste den Port spezifiziere (mit ":" ging es jedenfalls nicht), habe ich das also auch sein lassen.

In der Zwischenzeit hätte ich sicherlich auch 5x manuell den slave status ausführen können -- aber gut, man ist ja faul. Nachdem ich also pssh und dsh von meiner festplatte verbannt hatte, habe ich mir dann in ca. 1 Minute selbst ein kleines Shell-Script zusammengezimmert, das zwar bei weitem nicht die Features von pssh und dsh bietet, aber dafür für meine Zwecke problemlos funktioniert:

#!/usr/bin/env sh
SERVERS=`cat ~/.dsh/$1`

for i in $SERVERS
do
        echo $i
        ssh $i $2
done

Das Script habe ich unter "/usr/local/bin/dsh" abgelegt und ist damit in meinem Pfad erreichbar. In der Datei "~/.dsh/dbslaves" habe ich mir eine Liste meiner Slaves angelegt:

slave-1
slave-2
slave-3
slave-4
slave-5

Diese Hosts habe ich ohnehin schon in meiner "~/.ssh/config" drin. Ausserdem sind auf den Servern meine Keys für passwortloses Login installiert. Nun kann ich über:

dsh dbslaves "mysql -uroot -p... -e 'SHOW SLAVE STATUS\G'"

bequem meinen Slave Status abfragen oder andere Kommandos ausführen.



Neue Serverlandschaft bei pixelio.de


In den vergangenen fünf Jahren ist pixelio.de aus einem Hobbyprojekt eine kleine Firma geworden und aus ehemals einem Server ist eine Serverlandschaft mit 10 Servern und einem Loadbalancer entstanden. Die letzten Wochen haben wir endlich unser Netzwerk umstrukturiert und auf eine solidere Basis gestellt, sodass wir es zukünftig leichter haben weitere Server aufzunehmen. Dabei muss man mal ein Lob an HostEurope aussprechen, die im Service und Support eine wirklich gute Arbeit leisten -- es hat schon einen Vorteil, wenn man direkt mit Technikern am Telefon reden kann im Vergleich zu anderen Providern, wo das nur über einen Call-Center möglich ist und das ganze einem Stille-Post ähnelt (und die Ergebnisse entsprechend ausfallen).

Wir nutzen nachwievor eine alte Software, die eigentlich nicht für eine derart grosse Bilddatenbank ausgelegt ist und ausgesprochen schlecht skaliert. Um das Datenbankproblem in den Griff zu kriegen, setzen wir seit einiger Zeit schon den mysql-proxy von Jan Kneschke ein. Dieser Proxy kann transparent zwischen die Datenbank und eine Web-Anwendung gehängt werden und lässt sich über lua voll steuern. Über ein lua Script können wir so alle Queries parsen, der Proxy entscheidet dann welche Queries an den Master gehen müssen und welche an die Slaves. Das funktioniert sehr gut -- viel besser als eine reine PHP basierte Lösung, die wir ursprünglich mal in die Datenbankschicht der Bilddatenbank-Software gestrickt hatten.

Für nächstes Jahr ist endlich die Einführung einer von Grund auf neu entwickelten Software angedacht, die wir derzeit schon bei clipdealer.de im Einsatz haben. In diesem Zuge werden wir dann auch einen memcache-Cluster aufbauen, der die Datenbank massiv entlasten soll.

Ich bin gespannt, wie das Server-Diagramm in weiteren fünf Jahren aussehen wird ...



Bäume in MySQL


Das Abbilden von Baumstrukturen in Datenbanken stellt oft eine grosse Herausforderung dar. Wenn die Datenbank nicht gerade über eine Funktion zum effizienten Auslesen von hierarchischen Strukturen bietet, wie z.b. Oracles SQL über CONNECT BY, hat man ein Problem: Wie kann man einen Ast inkl. seiner Kinder auslesen?

Es gibt einige suboptimale Ansätze wie das Speichern des Pfades als String in einer Tabellenspalte oder die Referenzierung über eine Parent ID, bei der man gezwungen ist applikationsseitig eine Funktion zu schreiben, die rekursiv alle Kinder eines Astes ausliest und dabei in jeder Ebene eine neue Abfrage an die Datenbank stellt.

Nested Sets sind da schon sehr viel effizienter - beim Auslesen einer Datenbank. Das Einfügen und Löschen bzw. Verschieben von Ästen ist jedoch mit erheblichem Aufwand verbunden, da oft eine Vielzahl an Datensätzen aktualisiert werden müssen.

Jan Kneschke, Entwickler bei MySQL AB, hat nun eine weitere Möglichkeit vorgestellt, die von einem neuen Feature von MySQL 5 gebrauch macht, den 'Stored Procedures'. 'Stored Procedures' in MySQL können rekursiv ausgeführt werden, sodass das Rekursive Auslesen einer Datenbanktabelle über die Referenzierung der Parent ID komplett datenbankseitig erfolgen kann.

Auf seiner Projektseite kann man eine Bibliothek herunterladen, die die wichtigsten Funktionen zum Arbeiten mit Bäumen bereitstellt. Benötigt wird eine aktuelle Version von MySQL 5 z.b. die aktuell als stabil geltende Version 5.0.16. Die Bibliothek steht unter MIT Lizenz und kann unter Einhaltung der Regeln somit auch kommerziell verwendet werden.




Datenbankdesign mit DbWrench


Ich war längere Zeit auf der Suche nach einer Software, mit der man komfortabel Datenbankmodelle entwickeln kann. Meine Hauptanforderungen waren hier wieder: Schnelle und intuitive Arbeitsweise, keine Oberfläche, die mit viel Schnick-schnack überladen ist und natürlich plattformübergreifende Verfügbarkeit.

Mit DbWrench bin ich jetzt fündig geworden. Hierbei handelt es sich um eine in Java geschriebene Applikation, die problemlos unter Mac OS X, Linux und Windows - mit diesen drei Betriebssystemen habe ich es ausprobiert - verwendet werden kann.

Man kann ein Datenbankmodell komplett mit DbWrench entwickeln oder aber eine bestehende Datenbank einlesen. Das reverse Engineering funktioniert dabei normalerweise recht zuverlässig. Leider unterstützt DbWrench noch keine MySQL Spezialdatentypen wie ENUM - diese wandelt es in einen BLOB um. Auch das Anlegen der Datenbank aus einem Modell heraus ist für DbWrench kein Problem. Für MySQL wird z.b. der Tabellentyp InnoDB unterstützt. DbWrench erzeugt zunächst einen SQL Dump, der entweder gespeicher oder direkt an den Datenbankserver übermittelt werden kann.

Ausserdem kann die Anwendung auch eine HTML Dokumentation eines Datenbankmodells generieren. Es werden alle Informationen der Datenbank übersichtlich erfasst und sogar eine Bilddatei im Format JPEG oder PNG aus der grafischen Darstellung des Modells generiert.

Sehr sympathisch ist mir, dass DbWrench ein Datenbankmodell im XML Format speichert und nicht in einem eigenen binären Format, für das keine öffentlichen Spezifikationen erhältlich sind.

DbWrench ist Shareware - von der Webseite kann man eine vollfunktionsfähige 30 Tage lauffähige Version herunterladen. Es gibt keine preislichen Abstufungen, die Standard Edition kostet 150$ - das ist meines Erachtens nach nicht wenig oder doch zumindest so viel, dass ich mir den Kauf - für privat - zumindest doch zweimal überlegen werde.




MySQL V5.0.3 beta


Eine neue Beta Version von MySQL ist verfügbar. Mit MySQL 5 halten weitere aus professionellen Datenbanken bekannte Features Einzug in das freie Datenbanksystem. Endlich wird es möglich sein auch mit MySQL Views, Trigger und Stored Procedures zu verwenden.

MySQL hat für Langzeitbenutzer der Datenbank, die wissen möchten welche neuen Features die Version 5 für sie bringt, einige PDF Dokumente veröffentlicht: