Fehlermeldung

  • User warning: The following module has moved within the file system: comment_notify. In order to fix this, clear caches or put the module back in its original location. For more information, see the documentation page. in _drupal_trigger_error_with_delayed_logging() (line 1156 of /local/www/drupal/core/7.79/includes/bootstrap.inc).
  • User warning: The following module has moved within the file system: piwik. In order to fix this, clear caches or put the module back in its original location. For more information, see the documentation page. in _drupal_trigger_error_with_delayed_logging() (line 1156 of /local/www/drupal/core/7.79/includes/bootstrap.inc).

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:

Server 1:13579[...]
Server 2:246810[...]

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:

  1. 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:

  1. 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:

  1. use mysql;
  2. UPDATE user set Password=password('geh3im1') WHERE User='root';
  3. UPDATE user SET Host='db1' WHERE Host='db2';
  4. UPDATE db SET Host='db1' WHERE Host='db2';
  5. 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)

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

und für Master 2 entsprechend

  1. 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:

  1. # einzigartige ID für jeden Server
  2. server-id = 2
  3.  
  4. # Binary Log und Replizierung für DB mysql deaktivieren
  5. binlog-ignore-db = mysql
  6. replicate-ignore-db = mysql
  7.  
  8. # Eigene Befehle nicht erneut replizieren (standard)
  9. replicate-same-server-id = 0
  10.  
  11. # Beim automatischen Hochzählen nicht 1, sondern 2 addieren
  12. auto-increment-increment = 2
  13.  
  14. # Beginne automatisch inkrementelle Werte mit 1
  15. auto-increment-offset = 1
  16.  
  17. # Protokolldaten nach 21 Tagen löschen. Ein Slave könnte für bis zu 21
  18. # Tage offline sein und dann immer noch den Datenbestand replizieren
  19. expire_logs_days = 21
  20.  
  21. # Bei einer Dateigröße über 500 MB wird in eine neue Datei geschrieben
  22. 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:

  1. 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:

  1. show slave status\G

In der Ausgabe sollte folgende Zeilen geprüft werden:

  1. Master_Host: db1
  2. Master_User: repl
  3. Master_Port: 3306
  4. Master_Log_File: db1-bin.000007
  5. Relay_Log_File: hz01-relay-bin.000001
  6. Slave_IO_Running: No
  7. Slave_SQL_Running: No

Ist alles in Ordnung, kann der Slave gestartet werden:

  1. start slave;

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

  1. Slave_IO_Running: Yes
  2. 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:

  1. server-id = 1
  2. binlog-ignore-db=mysql
  3. replicate-ignore-db=mysql
  4. replicate-same-server-id = 0
  5. # Beginne automatisch inkrementelle Werte mit 2
  6. auto-increment-increment = 2
  7. auto-increment-offset = 2
  8. expire_logs_days = 21
  9. 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

  1. 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

  1. SHOW MASTER STATUS\G

erhält man die letzten relevanten Informationen

  1. File: db2-bin.000003
  2. Position: 107
  3. Binlog_Do_DB:
  4. 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:

  1. 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.

Kommentare

Hallo,
Danke für Dein HowTo. Gibt auch eine Möglichkeit die Datenbank mit replizieren zu lassen, so dass ich nicht manuell auf beiden Servern mich um die Benutzerverwaltung kümmern muss?

Hallo,

wenn Du damit die komplette Datenbank "mysql" meinst: ja. Einfach in der my.cnf die beiden Zeilen

  1. binlog-ignore-db = mysql
  2. replicate-ignore-db = mysql

nicht in die my.cnf eintragen.

Danke, das habe ich probiert. Nach dem Einspielen des Dumps von Server A nach Server B sind zwar die User und Rechte von A auch auf B vorhanden, allerdings werden Änderungen/Ergänzungen in beide Richtungen nicht weitergegeben.

Merkwürdigerweise kann ich aber bespielsweise eine Tabelle in der db mysql anlegen, die ich dann auch dem anderen Server sehen.

Bei meiner lokalen Installation habe ich es eben ausprobiert: Zeilen auskommentiert und einen User angelegt; dieser wurde sofort auf dem anderen Server angezeigt. Auch laut der offiziellen MySQL-Dokumentation - 15.4.1.17. Replication and User Privileges sollte das möglich sein.

Kennst Du dort den Abschnitt 6.13. Replikation: Problemlösungen?

Btw. Protokollieren der Fehlermeldungen ist immer empfehlenswert. Dazu in der my.cnf so etwas wie

  1. 
  2. log-error = /var/log/mysql/error.log

eintragen. Es versteht sich von selbst, dass das entsprechende Verzeichnis dem Datenbank-User gehören sollte und dass das Logfile in die Rotation (logrotate, newsyslog) eingebunden werden sollte.

Herzlichen Dank, Dein HowTo und Deine Antworten waren sehr hilfreich.

Eine super Schnell-Anleitung MYSQL 5.5 MASTER-MASTER Replikation in deutlich weniger als 30 Minuten - nichts für Anfänger :)

Vielen Dank

mfg
buc

:wq

Guten Tag,
zu Beginn möchte ich mich für die Anleitung/das HowTo bedanken. Wirklich sehr gut gelungen.

Nun hätte ich aber eine Frage zu einem spezielleren Anwendungsfall.

Ausgangssituation:
Im kleinen Gruppenrahmen möchten wir eine Anwendung für Bogensportvereine programmieren. Hierbei sollte die Client-Version (Vereinsrechner) sowohl offline als auch online nutzbar sein. Des Weiteren soll die Möglichkeit für den einzelnen Schützen gegeben sein online eigene Trainingsabläufe unabhängig von den vereinsinternen Wettkämpfen einzupflegen.

Realisierung und Verwendung:
Die Client-Version wird in JAVA entwickelt. Jeder Verein bekommt eine Datenbank auf einem Online Server zur Verfügung gestellt. Um aber die offline Möglichkeit zu gewährleisten ist auch auf dem Client MySQL installiert. Wenn ich nun die Anleitung richtig verstehe ist dies im Grunde eine Master-Slave Verbindung. Master ist der Vereinsrechner, Slave der Server. Nun gibt aber der einzelne Schütze Trainingsabläufe online in dieselbe Datenbank ein. Dann wird ja aus dem ganzen eine Master-Master Verbindung. Da ja der Datenbestand auf beiden Seiten sich ändert und dieser erst beim "online gehen" des Vereinsrechners synchronisiert wird.

Nun meine Frage: Wie verhält sich die Replikation wenn nun mehrere Vereine die Anwendung nutzen? Da nun ja eine N Anzahl an Mastern versucht sich mit dem Server abzugleichen?! Wie zu Beginn erwähnt, hat aber jeder Verein seine eigene DB auf dem "Main"-Server und auch nur diese soll abgeglichen werden. Verkopfe ich mich hier gerade zu sehr oder funktioniert diese Überlegung gar nicht und wir müssen die Synchronisierung anwendungsbasiert realisieren?

Ich wäre über Antworten sehr dankbar.

Lieben Gruß und im Vorgriff vielen Dank,
Martin

Das Problem ist deswegen schon nicht via MySQL-Master-Master-Replikation zu lösen, da jede MySQL-Installation nur einen Master haben darf. Wenn ich das Problem richtig verstehe, müsste der zentrale Server aber Multi-Master fähig sein, da er sich mit jedem Client synchronisieren muss.

Multi-Master-Anwendungen sind nur möglich, wenn man einen Ring bildet, z.B. bilden die 4 Server A, B, C und D die Master-Slave-Paare AB, BC, CD und DA. Fällt ein Server aus, muss man sich einen guten Plan überlegen, wie die Synchronisierung aufrecht erhalten werden kann. Dagegen ist mit AB, AC und AD nur eine Master-Slave-Paarung möglich, wobei A der einzige Master wäre.

Der letzte Fall könnte ein Ansatz für Ihr Problem sein: Zumindest die Clients hätten, sobald sie Online gingen, relativ schnell einen synchronen Datenbestand. Es fehlt aber der Weg, wie Clients ihren Datenbestand beim Haupt-Server abliefern. Allerdings bleibt die Administration recht aufwendig: Die Datenübertragung Master-Slave sollte verschlüsselt stattfinden. Das ginge via VPN oder auf auf MySQL-Ebene.

Das größere Problem sehe ich in der Synchronisation: Was ist, wenn sich ein Client nicht mehr synchronisiert. Das kann schnell vorkommen: erhebliche Probleme entstehen z.B. beim Synchronisieren der Cache-Tabellen von Drupal 6. Hier wird regelmäßig versucht einen bereits existierenden Primärschlüssel anzulegen. 

Oder ein weiterer Client kommt hinzu: Entweder muss die gesamte MySQL-Replikation neu aufgezogen werden oder sie müssen im Vorfeld einen optimistischen Wert für auto-increment-increment angeben. Trotzdem ist das keine zukunftssichere Lösung.

Die Replikation ist eher gedacht, einen Datenbestand verfügbar zu halten und Ausfälle zu vermeiden. Da in Ihrem Fall nur Datenbestände abgeglichen werden, würde ich hier eher den Weg über einen Webservice gehen. Unter Java gibt es da schon ein paar schöne Lösungen, z.B. Webservice in Java. Ein weiterer Vorteil ist, dass beliebige viele Clients sehr einfach hinzugefügt werden können.

Noch viel Spass mit dem spannenden Projekt!

Viele Grüße Stefan

Guten Tag Stefan,
vielen Dank für deine schnelle Antwort. So etwas in der Art dachten wir uns schon. Aber ein Rat eines SQL Gurus, wie dieser von Dir, macht es deutlich einfacher die Zusammenhänge zu verstehen. Vielen Dank.

Wir lösen das ganze nun über eine Online DB und die Offline Clients arbeiten mittels Hibernate und einer Jobque. Wenn wir hier die Reihenfolgen sauber beachten sollte das die Lösung sein.

Nochmals vielen Dank und weiterhin viel Erfolg,
Martin

Wow, vielen Dank!

Auch von mir ein fettes Dankeschön für diese klasse Anleitung :-)

Interessante Anleitung, leider funktioniert diese nicht bei mir.
Beim Starten des Servers 2 mit "start slave;", steht bei mir "No" in der Zeile "Slave_IO_Running".

Beim Master-Server "SHOW MASTER STATUS\G" erscheint nur "Empty set (0.00 sec)".

Beim Slave gibt die Zeile
Last_IO_Error
normalerweise eindeutige Fehlermeldungen, warum eine Verbindung nicht zusstande kommt oder abgebrochen wurde.

Spontan tippe ich darauf, dass das binary_log nicht richtig aktiviert ist. Einfach mal beim master mit
show binary logs;
testen.