MySQL 5.5: Master-Slave-Replikation

Allgemeines

Das Duplizieren oder Vervielfältigen der Daten eines MySQL-Servers wird replizieren genannt. Es ist möglich einen kompletten Server, ausgewählte Datenbanken oder sogar nur ausgewählte Tabellen einer Datenbank zu replizieren.

In jedem Fall wird mindestens eine weitere MySQL-Installation auf einem anderen Rechner benötigt. Nicht zwingend erforderlich, aber aus Gründen der Kompatibilität sollten die MySQL-Versionen beider Installationen identisch sein.

Im folgenden soll zwischen den zwei Varianten der Replikation vollständiger Datenbanken unterschieden werden:

1. Vollständige Replikation aller Datenbanken des Masters
2. Replikation aller Datenbanken, aber einzelne Datenbanken ausnehmen

Die Replikation ausgewählter Datenbanken ist zwar auch möglich, birgt aber Risiken. Soll z.B. nur die die Daten DB1 repliziert werden, würde der folgende Befehl ignoriert werden:

USE DB2;
UPDATE db1.foo SET bar=bar+1 WHERE id = 1;

Vorbereiten der Master-Instanz

Es müssen einige Einstellungen in der Datei my.cnf angepasst werden.

[mysqld]
# Jede MySQL-Instanz benötigt eine eigene einzigartige Server-ID
server-id       = 1 
# Binäres protokollieren aktivieren
log-bin
# Nach 21 Tagen sollen alte Einträge gelöscht werden
expire_logs_days        = 21
# Maximale Größe eines Logfiles
max_binlog_size         = 500M

Es kann sinnvoll sein, die Datenbanken mysql und test nicht zu replizieren. Die my.cnf wird dann um folgende Zeilen ergänzt:

binlog-ignore-db=mysql
binlog-ignore-db=test

Außerdem wird noch ein Replikations-User benötigt. Der entsprechende SQL-Befehl dazu lautet:

GRANT REPLICATION SLAVE ON *.* TO repl@'slave' IDENTIFIED BY 'geheim';

• repl: Der Benutzername zur Replikation
• slave: Der Name oder die IP des Slaves
• geheim: Das Passwort des Benutzers repl

Anschließend den MySQL-Master-Server neu starten.

Erstellen eines Backups

Ein Weg zur Sicherung der Daten führt über mysqldump. Zunächst sollte der schreibende Zugriff auf alle Tabellen blockiert werden:

mysql -u root -p -Bse 'FLUSH TABLES WITH READ LOCK'

Mit mysqldump (gehört zu jeder mysql-Installation) lässt sich der aktuelle Master-Stand schnell sichern:

mysqldump -u root -p --master-data --all-databases --result-file=./master-data_all.sql

Da es für mysqldump noch keine Option für den Ausschluss ganzer Datenbanken hat, habe ich dieses kleine Shell-Script geschrieben.

Danach die Schreibblockade wieder lösen mit:

mysql -u root -p -Bse 'unlock tables'

Vorbereiten des Slave-Instanz

Zunächst sollten in der my.cnf vom Slave einige Zeilen ergänzt werden:

[mysqld]
# Jede MySQL-Instanz benötigt eine eigene einzigartige Server-ID
server-id       = 2
# Folgende Datenbanken sollen ignoriert werden 
replicate-ignore-db=test
replicate-ignore-db=mysql

Den Slave-Server neu starten.

Slave starten

Zuletzt werden nun noch drei SQL-Befehle auf dem Slave benötigt:

1. Master-Informationen

MASTER_LOG_FILE und MASTER_LOG_POS sollte bereits durch die Option "--master-data" im Dumpfile übermittelt worden sein. Es fehlen noch die Angaben zum Replikations-Benutzers auf dem Master, die vorhin angelegt worden sind:

mysql> CHANGE MASTER TO MASTER_HOST='master', MASTER_USER='repl', MASTER_PASSWORD='geheim';

2. Backup einspielen

von der mysql-Shell mit:

mysql> source $DUMPFILE.sql;

oder von der System-Shell auch so:

mysql -p < $DUMPFILE.sql

Dann kann die Replikation gestartet werden:

mysql> start slave;

Ob die Replikation ordnungsgemäß ausgeführt wird, erfährt man mit:

show slave status\G

Wichtig sind die Zeilen

[...]
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[...]
Seconds_Behind_Master: 0

Die ersten beiden Zeilen zeigen an, ob die Replikation überhaupt läuft, die letzte, wie erfolgreich die Replikation ist. Die oberen Zeilen zeigen das optimale Ergebnis an.

Mögliche Fehler

Error Duplicate entry

Wie die Bezeichnung schon sagt, ein Eintrag ist bereits vorhanden. Wahrscheinlich ist etwas mit dem Dump nicht in Ordnung. Man sollte ein neues Backup machen. Wenn man weiß, was man tut, kann man die Fehlermeldung überspringen:

set global sql_slave_skip_counter=1;start slave;

Informationen zur Master-Slave-Konfiguration

Eine Slave ist kein Backup

Ähnlich wie ein RAID 1 zur Spiegelung von Festplatten kann eine Datenbank-Replikation nicht als Backup dienen. Alle SQL-Befehle auf dem Master werden zeitnah auf dem Slave ausgeführt. Ein unachtsamer drop, truncate oder delete Befehl vernichtet Daten sofort auf beiden Instanzen. Eine Replikation hilft nur bei Ausfall des Masters und beim Störungsfreien Backup.