MySQL 5.5: Master-Master-Replikation

Auf die Datenbanken von zwei MySQL-Server kann parallel schreibend und lesend zugegriffen werden - die Daten werden gleichzeitig synchronisiert.

Master-Master-Replikation - das Prinzip

Im Prinzip werden zwei Master-Slave-Replikationen eingerichtet. Jeder Master ist der Slave der anderen Installation.

Damit es zu keinem Konflikt bei Primärschlüsseln kommt, die automatisch hochgezählt werden, wird in der Konfigurationsdatei ein Startwert und ein Intervall angegeben.

Im folgenden soll der Startwert von Server 1 ebenfalls 1 betragen und der von Server 2 entsprechend 2. Das Intervall soll für beide 2 betragen. Für die Zählfolge ergibt sich damit:

Das bedeutet aber auch, dass sehr häufig Werte übersprungen werden, insbesondere wenn ein MySQL-Server nur als Ausfallsicherung mitläuft. Dies sollte bedacht werden, wenn Primärschlüssel z.B. für Rechnungsnummern benutzt werden und Sprünge unschön wären.

Ausgangssituation

Der MySQL-Server 1 läuft bereits und hat einen gewissen Datenbestand. MySQL-Server 2 wurde neu aufgesetzt und soll den kompletten Datenbestand synchronisieren, mit Ausnahme der Datenbank mysql mit Benutzerdaten.

Beide Server sind via VPN über die privaten IPs 192.168.5.2 und 192.168.5.4 miteinander verbunden, die in der Datei hosts als db1 und db2 definiert sind. Damit sind die Server unter ihren Namen im Netzwerk erreichbar.

Datenbestand sichern

Zunächst wird via mysqldump ein Backup des gesamten Datenbestandes von Master 1 inklusive der Master-Daten erstellt:

mysqldump -p --master-data --all-databases --result-file=dbdata1.sql

Diese Daten werden erstmal von Master 2 eingelesen - die Benutzerdatenbank wird nun überschrieben! Ein Vorschlag führt über die mysql-shell, die mit mysql aufgerufen wird. Ist der Server bisher nur gestartet worden, besitzt er Benutzer root noch kein Kennwort. Nach dem Aufruf wird das eben erstellte Backup eingelesen:

source dbdata1.sql;

Vorteile von diesem Schritt sind für mich 2 Punkte, die beide mit der Benutzerdatenbank zu tun haben:

• Passwort für root
  Ein neues Kennwort für den Benutzer root ist schnell erstellt.
  
• Host der anderen Benutzer ändern
  User, die vorher vom Server db2 auf db1 zugreifen, sollen dass nun von db2 dürfen.

Die folgenden drei Zeilen erledigen das schnell:

use mysql;
UPDATE user set Password=password('geh3im1') WHERE User='root';
UPDATE user SET Host='db1' WHERE Host='db2';
UPDATE db SET Host='db1' WHERE Host='db2';
FLUSH PRIVILEGES;

Für eine genauere Beschreibung siehe MySQL 5.5 Master-Slave-Replikation.

Benutzer für die Replikation

Auf beiden Servern wird ein MySQL-User benötigt, mit dem die andere Datenbank die zu replizierenden Daten abholen kann. Die jeweiligen SQL-Befehle dazu lauten:

Für Master1 (db1)

GRANT REPLICATION SLAVE ON *.* TO repl@'db2' IDENTIFIED BY 'geheim2';

und für Master 2 entsprechend

GRANT REPLICATION SLAVE ON *.* TO repl@'db1' IDENTIFIED BY 'geheim1';

Konfiguration von Master 2

Jeder Server benötigt seine eigene Server-ID, Binary-Logging und die erwähnten Einstellungen für automatisch inkrementelle Werte. Außerdem können nur bestimmte Datenbanken ausgewählt oder ignoriert werden. Für den neuen Master 2 sieht eine Konfiguration bei der alle Datenbanken mit Ausnahme von der Datenbank mysql repliziert werden sollen folgendermaßen aus:

# einzigartige ID für jeden Server
server-id       = 2
 
# Binary Log und Replizierung für DB mysql deaktivieren
binlog-ignore-db         = mysql
replicate-ignore-db    = mysql
 
# Eigene Befehle nicht erneut replizieren (standard)
replicate-same-server-id = 0
 
# Beim automatischen Hochzählen nicht 1, sondern 2 addieren
auto-increment-increment = 2
 
# Beginne automatisch inkrementelle Werte mit 1
auto-increment-offset    = 1
 
# Protokolldaten nach 21 Tagen löschen. Ein Slave könnte für bis zu 21
# Tage offline sein und dann immer noch den Datenbestand replizieren
expire_logs_days           = 21
 
# Bei einer Dateigröße über 500 MB wird in eine neue Datei geschrieben
max_binlog_size            = 500M

Slave auf Master 2 starten

Nach Änderung der Konfigurationsdatei und erfolgreichem Neustart wird in der mysql-shell der Slave gestartet. Zunächst muss der Master-User mitgeteilt werden:

CHANGE MASTER TO MASTER_HOST='db1', MASTER_USER='repl', MASTER_PASSWORD='geheim';

Ob alle Daten richtig sind - neben den eben eingegebenen Daten sind dies insbesondere die Bezeichnung und die Position des Master-Logfiles auf db1 - lässt sich mit dem Status des Slaves feststellen:

show slave status\G

In der Ausgabe sollte folgende Zeilen geprüft werden:

                  Master_Host: db1
                  Master_User: repl
                  Master_Port: 3306
              Master_Log_File: db1-bin.000007
               Relay_Log_File: hz01-relay-bin.000001
             Slave_IO_Running: No
            Slave_SQL_Running: No

Ist alles in Ordnung, kann der Slave gestartet werden:

start slave;

Mit der Status-Abfrage show slave status\G kann man an den Zeilen

             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

erkennen, dass der Slave läuft. Dies ist aber erst eine Master-Slave-Replikation.

Konfiguration von Master 1

Bis auf zwei kleine Änderungen für Server-ID und auto-increment-offset ist diese mit der oberen Konfiguration identisch:

server-id       = 1
binlog-ignore-db=mysql
replicate-ignore-db=mysql
replicate-same-server-id = 0
# Beginne automatisch inkrementelle Werte mit 2
auto-increment-increment = 2
auto-increment-offset = 2
expire_logs_days        = 21
max_binlog_size         = 500M

Damit die Konfiguration gültig ist, muss der MySQL-Server neu gestartet werden.

Slave auf Master 1 starten

Wenn die Zeile in der Statusanzeige von Slave2

Seconds_Behind_Master: 0

den Wert 0 ausgibt, haben beide Server den gleichen Datenbestand (bezogen auf die zu replizierenden Datenbanken). Mit dem Master-Status von Master2

SHOW MASTER STATUS\G

erhält man die letzten relevanten Informationen

            File: db2-bin.000003
        Position: 107
    Binlog_Do_DB: 
Binlog_Ignore_DB: mysql

um den Slave auf db1 zu starten. Hierzu die mysql-Shell aufrufen und entsprechend wie oben schon beschrieben die Daten von Master2 eingeben:

CHANGE MASTER TO MASTER_HOST='db2', MASTER_USER='repl', MASTER_PASSWORD='geheim2', MASTER_LOG_FILE='db2-bin.000003', MASTER_LOG_POS=107;

Nach einer Kontrolle mit show slave status\G kann auch der Slave 1 mit start slave; gestartet werden.

Alle SQL-Befehle, die nicht die Datenbank mysql betreffen, werden nun zeitnah auf dem anderen Server ausgeführt. Damit beide Server auch tatsächlich als Master - also für Schreibzugriffe - genutzt werden können, muss unbedingt der Slave-Status beider Server beobachtet werden.