MySQL-Replikation ist ein Verfahren, durch das Sie die Ausfallzeiten bei einem Serverausfall oder einer geplanten Wartung reduzieren können, mit dem Sie Datenbankabfragen auf mehrere Server verteilen (Skalierung) oder Anwendungen gefahrlos testen können. Webseiten mit extrem hohen Besucheraufkommen wie z.B. Facebook, Twitter, YouTube und viele andere nutzen die MySQL- Replication, um ihre Kapazitäten zu verteilen und damit eine zuverlässig gute Erreichbarkeit für eine exponentiell wachsende Anzahl von Benutzern zu schaffen, ohne Gefahr zu laufen, dass Verfügbarkeitsausfälle zu Nichterreichbarkeiten führen.

 

Wie funktioniert die MySQL-Replikation?

Bei der MySQL-Replikation werden Daten von einer einzigen Quelle zu anderen Orten dupliziert. Die Daten auf Ihrem MySQL-Server (Master) werden dabei auf einen oder mehrere andere MySQL-Server (Slave/Slaves) repliziert. Auf diese Weise können alle Änderungen, die in der Master-Datenbank vorgenommen werden, automatisch von den anderen MySQL-Datenbanken übernommen werden. Die Übernahme erfolgt vom Master zum Slave.

Über die Konfiguration können Sie auch festlegen, dass Backups oder Anfragen von allen oder ausgewählten SQL-Datenbanken von einem Slave-Server durchgeführt werden. Dies wird auch als „Offloading from the master server“ bezeichnet und hilft Ihnen, Leistungsprobleme zu verhindern, wie sie z.B. häufig von Backup-Verfahren verursacht werden, bei denen der Master-Server durch die Backup-Erstellung ausgelastet ist.

Hinweis:
Bitte beachten Sie, dass eine Kapazitätsverteilung über MySQL-Replikation nur in den Fällen problemlos möglich ist, in denen der Slave Datenbanken nur liest. MySQL-Replikation ist problematisch, wenn der Slave auch Schreibzugriff benötigt, wie z.B. bei Shop-Systemen.

 

So richten Sie eine MySQL-Replikation ein

Idealerweise sollten Master- und Slave-Server die gleichen MySQL-Datenbank-Versionen nutzen. Konfigurieren Sie nun den Master- als auch den Slave-Server.

Konfiguration Master-Server

  1. Einrichtung eines Replikations-Accounts auf dem Master-Server
    Legen Sie zunächst auf dem Master-Server einen Replikations-Account für jeden Slave-Server an und vergeben Sie die Rechte für die MySQL-Replikation
    mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'IP-ADRESSE DES SLAVE-SERVERS' IDENTIFIED BY 'password';
  2. Passen Sie die my.cnf-Datei auf Ihrem Master-Server
    • Vergeben Sie eine eindeutige Server-ID
      server-id = EINDEUTIGE NUMMER
    • Konfigurieren Sie eine Verbindung zum Slave-Server
      bind-address = IP-ADRESSE DES MASTER-SERVERS
    • Aktivieren Sie das MySQL Binar-Log
      log_bin=mysql-bin
    • Definieren Sie den Sync-Binlog-Parameter
      sync_binlog = 1
  3. Starten Sie mit mysqld den MySQL-SERVER neu
  4. Überprüfen Sie nun den Masterstatus
    mysql> show master status\G

Konfiguration Slave-Server

Ändern Sie die my.cnf-Datei auf Ihrem Slave-Server wie folgt:

  1. Vergeben Sie eine eindeutige Server-IDserver-id = EINDEUTIGE NUMMER
  2. Passen Sie die Parameter reley-log und read_only der MySQL-Konfigurationsdatei an.relay_log = mysql-relay-bin
    read_only = 1

 

Bringen Sie die Datenbanken von Master und Slave auf den gleichen Stand

  1. Erstellen Sie auf dem Master-Server jetzt einen Dump der Masterdatenbank. Sperren Sie für die Zeit der Erstellung des Dumps den Schreibzugriff auf die Datenbank mit dem folgenden Kommando:mysql> FLUSH TABLES WITH READ LOCK;
  2. Starten Sie den Datenbank-Dumpmysqldump --all-databases --allow-keywords --single-transaction --flush-logs --master-data=2 -r /root/mysqldump.sql
  3. Nach Ausführung des Datenbank-Dump-Befehls, sollte der Sperrung des Schreibzugriffs auf die Datenbank des Master-Servers wieder aufgehoben sein. Zur Sicherheit, können Sie den Schreibzugriff mit folgendem Befehl entsperren:mysql> FLUSH UNLOCK TABLES;
  4. Lesen Sie die Parameter des MASTER-LOG-FILE und die MASTER-LOG-POS aus
    Öffnen Sie dazu den Header der Datei mysqldump.sql
    Notieren Sie sich den Parameter der mysql-bin-Datei (mysql-bin.xxxxxx), sowie die Master-Log-Position XX. Sie benötigen beide Angaben für die Aktivierung der Replikation.

Wechseln Sie nun zum Slave-Server

  1. Löschen Sie zunächst die Datenbanken auf dem Slave-Server.drop database DB-NAMEMit dem Befehl show databases können Sie sich die vorhanden Datenbanken anzeigen lassen.
  2. Spielen Sie nun den Datenbank-Dump auf dem Slave-Server ein.cat /root/mysqldump.sql | mysql -u root -pSicherheitstipp:
    Die Verbindung zwischen den beiden Servern sollte aus Sicherheitsgründen über eine verschlüsselte Verbindung erfolgen. Nutzen Sie unverschlüsselte Verbindungen ausschließlich in Netzwerken, denen Sie vertrauen können z.B., wenn Sie ein „Private Network“ verwenden.</lí>
  3. Starten Sie den MySQL-SERVER neu

 

Aktivieren Sie die MySQL-Replikation

  1. Verbinden Sie den Slave-Server mit der Datenbank des Master-Servers. Geben Sie über die Kommandozeile des Slave-Servers folgenden Befehl ein:mysql> change master to master_host='IP-ADRESSE DES MASTER-SERVERS', master_user='repl', master_password='password', master_log_file='mysql-bin.XXXXX', master_log_pos=XX;Für die Platzhalter („XX“) tragen Sie den Parameter der mysql-bin-Datei (mysql-bin.xxxxxx), sowie die Master-Log-Position ein, die Sie sich bei der Abfrage des Masterstatus notiert haben.
  2. Starten Sie nun die MySQL-Replikation.
    Geben Sie auf dem Slave-Server folgenden Befehl ein:
    mysql> start slave;
    ­Mit dem Kommando mysql> stop slave können Sie die MySQL-Replikation jederzeit wieder stoppen.

Checken Sie regelmäßig die Integrität Ihrer Daten

Überprüfen Sie regelmäßig, ob die Replikation vollständig und fehlerfrei läuft. Deshalb sollten Sie regelmäßig:

  1. die Verbindung überprüfen – die Werte für Slave_IO_Running und Slave_SQL_Running sollten auf „Yes“ stehen.
    Geben Sie dazu auf dem Slave-Server folgenden Befehl ein:mysql> show slave status\G
  2. die Konsistenz Ihrer Daten checken. Es gibt aber auch Tools, wie z.B. mit dem PERCONA Toolkit, mit denen Sie die Integrität der MySQL-Replikation sehr komfortabel überprüfen können.

Was tun, wenn die MySQL-Replikation nicht mehr funktioniert

Sie erhalten vermehrt Fehlermeldungen, die sich auf MySQL-Anfragen beziehen? Dann gibt es wahrscheinlich ein Problem mit der MySQL-Replikation.

Checken Sie in diesem Fall zunächst den Status Ihres Slave-Servers, denn eine häufige Ursache ist, dass der Slave-Server gestoppt hat. Geben Sie folgenden Befehl ein:

mysql> show slave status\G

Prüfen Sie, ob die Werte für Slave_IO_Running und Slave_SQL_Running auf „Yes“ stehen.  Sollte einer dieser Werte auf „no“ stehen, ist die MySQL-Replikation unterbrochen.

Rufen Sie nun die Logfiles auf. Dort finden Sie weitere Details zur Störung, z.B. wann der Fehler aufgetreten ist: [ERROR] Slave: sowie den Parameter der mysql-bin.XXXX sowie die Position XXXXX, an der die MySQL-Replikation unterbrochen wurde.

Mit folgenden Workaround sollten Sie die MySQL-Replikation wieder ans Laufen bringen:

  1. Stoppen Sie den Slave-Server mit:mysql> stop slave;
  2. Überspringen Sie einfach die MySQL-Abfrage, die den Fehler verursacht hat. Geben Sie dazu folgende Befehl ein:mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
  3. Starten Sie den Slave-Server neu:mysql> start slave;
  4. Überprüfen Sie nun erneut den Status des Slave-Servers mit dem Befehl:mysql> show slave status;

Die Werte für Slave_IO_Running und Slave_SQL_Running sollten nun wieder auf „Yes“ stehen.
Sollte das Problem weiterbestehen, probieren Sie es noch einmal, indem Sie zwei oder mehr MySQL-Abfrage überspringen:

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 2;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 3;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 4;

Replikationsmodi

Bei der MySQL-Replikation werden verschiedene Modi unterschieden, die man als asynchron, halbsynchron oder synchron bezeichnet.

Die asynchrone Replikation

MySQL ist standardmäßig asynchron. Dieser Modus (wie oben beschrieben) ist der Favorit unter den Entwicklern. Der Master schreibt in ein Binärprotokoll (binlog), das, wenn verfügbar, an den/die Slave/s gesendet wird. Der Master wartet nicht auf eine Bestätigung vom Slave, sondern arbeitet direkt weiter, deshalb spricht man auch von niedriger „Schreiblatenz“. In diesem Modus bestimmt der Slave, was aus dem Binärprotokoll gelesen wird. Es kann in diesem Modus ist nicht vollständig garantiert werden, bzw. es gibt keine Bestätigung dafür, dass der Slave wirklich alle Ereignisse / Updates erhalten hat, die der Master in das Binlog geschrieben hat.

Die Semi-Synchrone Replikation

Die Semi-Synchrone Replikation ist eine praktische Lösung für eine verbesserte Konsistenz zwischen Master und Slave. Sie reduziert das Risiko von Verzögerung oder Datenverlust durch ein Failover (Fehler / Ausfall) am Master. Die verbesserte Datenintegrität bewirkt allerdings eine Leistungseinschränkung, da der Master solange keine Schreibereignisse bestätigt, bis der Slave die Änderung / Aktualisierung implementiert hat. Auch dieser Modus ist keine Garantie für ein zuverlässiges Failover. Sobald jedoch eine Bestätigung (ein Commit) von Slave erfolgreich zurückgegeben wird, ist bekannt, dass es zwei Orte gibt, an denen die Daten existieren (der Master und mindestens ein Slave).

Die synchrone Replikation

Für hohe Datenkonsistenz können MySQL-Slaves in einer Kombination von asynchroner und halbsynchroner Replikation konfiguriert werden. Diese Replikationsmodi können auch vollständig synchron sein, wenn die Datenübertragung durch die Verwendung des „Zwei-Phasen-Commit“ -Verfahrens bestätigt und erfolgreich (ein Commit) an mehr als nur eine Instanz zur gleichen Zeit zurückgegeben wird. Das garantiert hohe Zuverlässigkeit und verbessert die Ausfallreaktionszeiten über auch mehrere Plattformen hinweg.

 

Hochverfügbarkeit durch die Einführung von GTIDs

Replikation mit Global Transaction Identifiers (GTIDs) ermöglich Ihnen Failover und Swichover-Szenarien, mit wenige manuellem Aufwand und Störungen. GTIDs sind unverwechselbar und beinhalten universell eindeutige Identifikatoren (UUIDs), die sich aus einer zweiteiligen Transaktionsnummer zusammensetzen. GTIDs wurden mit MySQL 5.6. eingeführt und werden als Header erstellt und in das binlog geschrieben. Mittels GTIDs können replizierte Transaktionen einfach zwischen Master und Slave (s) einfach nachverfolgt werden und ermöglichen einfache Wiederherstellungen auch beim Ausfall des Master-Servers.

Hier finden Sie weitere Informationen zur Replication Global Transaction Identifiers (GTIDs)

Wolf-Dieter Fiege
Letzte Artikel von Wolf-Dieter Fiege (Alle anzeigen)

Große Auswahl an günstigen Domain-Endungen – schon ab 0,08 € /Monat
Jetzt Domain-Check starten