Nachher ist man immer schlauer, oder: MySQL Replikation im Nachhinein

mssql-mysql-conv

Replikation?

MySQL kann ein Master-Slave System abbilden. Alle Aktionen auf dem Master System werden dabei ebenfalls auf dem Slave ausgeführt. Das ganze macht Sinn, wenn man bspw.

  • ein Backup benötigt
  • ein immer aktuelles Backup benötigt
  • Lesezugriffe auf eine andere Datenbank umleiten möchte (quasi Loadbalancing)

Es gibt natürlich noch weitere Anwendungsfälle, jedoch sollten hiermit die meisten abgefangen sein.

Recht viele Anleitungen zur Einrichtung sind im Netz zu finden. Allerdings beziehen die meisten der Anleitungen sich darauf, dass man auf einer grünen Wiese anfängt. Doch wie funktioniert das ganze – konsistent – wenn eine Datenbank schon eine Zeit in Betrieb ist? Das möchte ich hier kurz aufzeigen.

Doch vorher: Vorsicht!

Solltet Ihr nun frisch mit dem Thema Replikation unter MySQL anfangen, so solltet Ihr Euch vorher das ganze genauer anschauen. Ich möchte nur ein Beispiel bringen.

RAND() auf Master / Slave

Ist die Replikation zwischen zwei Systemen aktiv, wird jedes SQL-Statement auf dem Master und auf dem Slave ausgeführt. Doch was bedeutet das für manche Statements, bzw. deren Ergebnisse?


mysql> select rand();
+-------------------+
| rand() |
+-------------------+
| 0.784395840749513 |
+-------------------+
1 row in set (0.00 sec)

Dieses Statement liefert uns lediglich eine Zufallszahl zurück. Nichts besonderes. Joa. Aber liefert das Slave System das gleiche Ergebnis? Nö. Das Statement liefert also auf dem Master ein ganz anderes Ergebnis als auf dem Slave. Genauso kann es laufen mit AutoIncrement Feldern, Zeitstempeln etc….

Set Up

Folgende Schritte sind notwendig:

  1. Account anlegen
  2. Anpassen der my.cnf auf Master und Slave
  3. Restart der Dienste
  4. Check
  5. Replikation konfigurieren
  6. Backup der Master Datenbank
  7. Restore auf der Slave Datenbank
  8. Starten der Replikation

Account anlegen

Die Replikation benötigt einen speziellen Account mit entsprechenden Rechten (REPLICATION SLAVE, REPLICATION CLIENT (Monitoring Priv.)). Dieser Account wird auf Master & Slave angelegt.


mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO <account>@'192.168.0.%' IDENTIFIED BY '<PASSWORD>';

Anpassen der my.cnf (Master & Slave)

Jeweils auf Master und Slave muss das “Binary Log” (log_bin) eingeschaltet werden. Des Weiteren muss jedem Server eine eindeutige ID gegeben werden (server_id).


log_bin = mysql-bin

server_id = 10 (Slave z.B. +1)

Nach dem Einschalten der Binary Logs muss der Dienst neu gestartet werden (Master & Slave).

Prüfen ob das Binary Log eingeschaltet ist

Über “show master status” kann man prüfen ob die Änderungen erfolgreich waren.


mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 11267 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Wird ein File und eine Position angezeigt, so ist das Binary Log aktiv.

Replikation einrichten

Nun muss der Slave eingerichtet werden. Wer ist der Master? Wie lautet der Account mit den entsprechenden Rechten?


mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.10', MASTER_USER='<account>', MASTER_PASSWORD='<PASSWORD>';

Damit ist die Replikation noch nicht komplett eingerichtet und auch nicht aktiv.

Datentransfer

Wir benötigen nun ein Backup der Master DB aus zwei Gründen: Erstens sollte man ein Backup des Masters anfertigen um im Problemfall die Daten zu haben und zweitens benötigen wir einen Snapshot der Datenbank um diese als “Startpunkt” für den Slave zu nehmen.

Dieser Startpunkt muss unbedingt konsistent sein. Dazu erstellt man einen Dump mit Hilfe von mysqldump und diversen Parametern z.B. für das TableLocking. Dabei spielt der Parameter “–master-data=1″ eine besondere Rolle. Durch diesen Parameter bekommen wir folgende Zeile in den Dump eingefügt:


CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=11267;

Dieser Code wird beim Einspielen des Dumps auf dem Slave mit ausgeführt (Parameter gesetzt) und sorgt für die Vervollständigung der Slave Konfiguration. Dem Slave wird mitgeteilt welches Binlog aktiv ist und an welcher Position. Genau dort setzt der Slave mit der Replikation an. Sollte beispielsweise die Position von der des Masters abweichen, so werden Transaktionen fehlen oder ggf. doppelt ausgeführt.

Wir verwenden für den Dump also folgendes mysqldump Kommando:


# MyISAM Tabellen vorhanden?

mysqldump --single-transaction --all-databases --master-data=1 --lock-all-tables -u root -p > /tmp/masterdata.sql

# Nur Innodb?

mysqldump --single-transaction --all-databases --master-data=1 -u root -p > /tmp/masterdata.sql

Den Dump nun auf den Slave kopieren und dort einspielen:


mysql -u root -p < masterdata.sql

Mit dem Befehl (mysql cli) “show slave status;” kann man sich die Parameter für die Replikation nochmal ansehen. Die Parameter aus dem Dump werden dort nun auch angezeigt (MASTER_LOG_FILE, MASTER_LOG_POS).

Replikation starten

Ein simples “start slave;” startet nun die Replikation. Den Status solltet Ihr wieder mit “show slave status;” überprüfen – sollte laufen.

read-only?

Per Default verhält sich das Slave System wie ein “normaler” MySQL Server. Mit einem entsprechenden Account kann man auch in die Datenbank schreiben (INSERT, UPDATE, …). Damit riskiert man Inkonsistenzen. Eine solche Datenbank sollte lediglich für Lesezugriffe genutzt werden (Statistiken bspw.). Es gibt auch einen Parameter für die my.cnf um den Server als read-only zu konfigurieren -> “read_only = 1″.

2 Responses to Nachher ist man immer schlauer, oder: MySQL Replikation im Nachhinein

  1. Stefan

    Also eine Master/Slave Lösung als Backup zu verkaufen halte ich für sehr gewagt.
    Jede Inkonsistenz durch fehlerhafte SQL-Statements, jedes unbedachte Drop und Delete landen automatisch auch auf dem Slave. Das ist nicht sicher!

Hinterlasse eine Antwort

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind markiert *

Du kannst folgende HTML-Tags benutzen: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>