MySQL-Datenbank-Replikation

Diese Anleitung stellt eine technisch besonders interessante Möglichkeit vor, wie GREYHOUND nahezu ausfallsicher betrieben werden kann und das ganze System stark in Richtung Hochverfügbarkeit trimmt.

Hochverfügbarkeit bedeutet in diesem Zusammenhang, dass Ausfälle des aktiven MySQL Datenbanksystems innerhalb von einer Minute kompensiert werden können und der Betriebszustand nicht erst aus einem (hoffentlich vorhandenen) Backup wiederhergestellt werden muss.

Um dieses Ziel zu erreichen, richtet man neben dem sogenannten Live-System (das Serversystem auf dem der MySQL-Server aktuell läuft) ein HotSpare-System ein, welches eine identische Kopie des Live-Systems darstellt und im Fehlerfall die Aufgabe von diesem übernehmen soll. Im Datenbankumfeld spricht man dann auch von einer Master-Master Replikation im sogenannten Aktiv-Passiv Modus. Hierbei stellen beide Datenbankserver eigentlich einen Master (also ein Live-System) dar, wobei einer passiv geschaltet ist und von keiner Anwendung genutzt wird und auch nicht genutzt werden darf, da es sonst zu Dateninkonsistenzen kommt.

Der hier verwendete Trick besteht darin, dass beide Systeme sich gegenseitig replizieren und somit immer beide Datenbanken auf exakt demselben Stand sind. Fällt nun ein Datenbankserver aus, kann einfach aus dem passiven HotSpare-System das aktuelle Live-System werden, indem im GREYHOUND Admin einfach die Datenbankverbindung auf den anderen Server geändert wird. Das System ist innerhalb von Sekunden wieder “Live” und es kann in Ruhe das ehemalige Master System repariert werden, was dann später wieder zum HotSpare oder Slave wird.

Voraussetzungen

  1. Das GREYHOUND System wird inhouse betrieben und befindet sich im lokalen Netzwerk.
  2. Das GREYHOUND System wird mit einer externen MySQL Datenbank betrieben, die sich auf einem separaten Server befindet.
  3. Ein zweiter Datenbank-Server mit ähnlichen Leistungsdaten und mindestens gleichem freien Speicherplatz wie das Live-System ist vorhanden.
  4. Auf beiden Datenbank-Servern ist exakt die gleiche MySQL Version installiert und die MySQL-Konfiguration ist bereits an GREYHOUND angepasst.
  5. Beide Datenbank-Server stehen nur im internen Netz und sollten von außen aus Sicherheitsgründen nicht erreichbar sein, da wir hier das Rechte-und Sicherheitsmodell von MySQL nicht weiter vertiefen und von einem einfachen Szenario ausgehen wollen.
  6. Beide Datenbank-Server können per TCP/IP auf Port 3306 miteinander kommunizieren und können ebenfalls beide vom GREYHOUND Server aus auf Port 3306 erreicht werden.

Vorbereitende Maßnahmen

Als erstes muss mit Hilfe des GREYHOUND Admins der laufende GREYHOUND Server Prozess beendet werden. Außerdem müssen alle zugehörigen GREYHOUND Programme/Tools/Instanzen beendet werden. Anschließend beenden wir auf beiden Datenbank-Servern den MySQL Dienst.

Man sollte sich unbedingt versichern, dass ein frisches Backup sowohl vom GREYHOUND Server System als auch von der aktuellen Live-Datenbank vorliegt. Sofern das nicht der Fall ist, ist zunächst dringend erst ein Backup zu erstellen.

Einrichtung der Replikation

Zunächst ist der aktuelle (Live) Master Datenbank-Server für die Replikation zu konfigurieren. Dafür öffnet man mit einem Texteditor die my.ini, die sich unterhalb des Installationsverzeichnisses von MySQL befindet. Hier fügt man folgenden Abschnitt ein:

#Konfiguration der Replikation
server_id=1
log_bin="C:/MySQL Server 5.1/Data/mysql-bin"
relay_log="C:/MySQL Server 5.1/Data/mysql-relay-bin"
log_slave_updates=1
#read_only=1
#sync_binlog=1

Ausschnitt der my.ini-Datei des Master Datenbank-Servers

Die Verzeichnisse müssen evtl. an die aktuellen Gegebenheiten angepasst werden. Im nächsten Schritt ist selbiges für den zweiten (Slave, HotSpare) Datenbank-Server zu wiederholen. Dabei ist aber unbedingt auf die unterschiedliche server_id zu achten:

Konfiguration der Replikation
server_id=2
log_bin="C:/MySQL Server 5.1/Data/mysql-bin"
relay_log="C:/MySQL Server 5.1/Data/mysql-relay-bin"
log_slave_updates=1
#read_only=1
#sync_binlog=1

Ausschnitt der my.ini-Datei des Slave Datenbank-Servers

Um die Sicherheit des Ganzen zu erhöhen, kann bei dem Slave oder HotSpare System noch die Option read_only=1 auskommentiert werden. Dadurch wird sichergestellt, dass keine Anwendung außer dem MySQL Replikationsthread schreibend auf die Datenbanken zugreifen kann. Es darf einzig und allein auf das aktuelle Live-System geschrieben werden, sonst gibt es Inkonsistenzen.

Bevor man die MySQL Systeme nun wieder startet, muss man sicherstellen, dass beide Systeme den gleichen konsistenten Datenbestand aufweisen. Dies kann man mit Hilfe einer sogenannten kalten Kopie erreichen, da dieses Verfahren hier die einfachste und effizienteste Variante darstellt. Kalte Kopie bedeutet: Beide System sind offline und die Datenverzeichnisse von MySQL werden einfach vom aktuellen Master auf das Slave System kopiert, um für beide Maschinen die gleichen Ausgangsbasis zu bekommen.

Je nach persönlichem Sicherheitsbedürfnis kann man die Replikation mit dem bereits voreingestellten root User von MySQL durchführen oder aber einen separaten Benutzer mit minimalen Rechten hierfür einrichten. Dieser User muss dann aber auf beiden Datenbank-Servern eingerichtet werden. Also startet man auf beiden Systemen zunächst wieder MySQL über die Windows Dienststeuerung und richtet optional den Replikationsuser über die MySQL Shell oder ein MySQL GUI Tool ein:

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl@'192.168.0.%' IDENTIFIED BY 'p4ssword';

Auch hier bitte wieder die IP-Adresse bzw. den Username “repl” und das Password “p4ssword” den eigenen Bedürfnissen anpassen.

Replikation aktivieren

Der nächste Schritt besteht darin, dem Slave mitzuteilen, wie er sich mit dem anderen Master-Server verbinden und seine Binärlogs abspielen soll. Dies macht man über die MySQL Anweisung “CHANGE MASTER TO”. Diese Anweisung setzt die entsprechenden Einstellungen in einer Datei namens master.info. Folgende Kommandos sind wieder auf beiden Datenbank-Servern in der MySQL Shell auszuführen:

CHANGE MASTER TO MASTER_HOST="192.168.0.100", MASTER_USER="repl", MASTER_PASSWORD="p4ssword", MASTER_LOG_FILE="mysql-bin.000001", MASTER_LOG_POS=0;

Dabei ist darauf zu achten, die korrekten Daten für die Benutzerauthentifizierung einzugeben und die IP-Adresse/Hostname des jeweils anderen Server-Systems zu verwenden. Führt man dieses Kommando also auf dem aktuellen Live-System aus, ist für den Parameter MASTER_HOST die IP-Adresse des Slave (HotSpare) Systems anzugeben.

Um die Replikation nun zu starten, ist auf beiden Datenbank-Servern den MySQL Befehl “START SLAVE” auszuführen:

START SLAVE;

Mit Hilfe der Befehle “SHOW SLAVE STATUS”, “SHOW MASTER STATUS”, “SHOW PROCESSLIST”, können wir nun schauen, was das System macht und ob die Replikation erfolgreich gestartet wurde. Als letzten Test sollten wir auf dem Live-Server mit der MySQL Shell oder einem GUI Tool einen neuen Datensatz in einer beliebigen Tabelle anlegen oder ändern und überprüfen, ob die Änderung zeitnah auf dem Slave-System ausgeführt wird. Ist dieses der Fall, können wir den GREYHOUND mittels des Admins wieder starten und in Betrieb nehmen.

Ausfall des Live-Systems bzw. Wechseln der Master

Sollte sich der aktuelle Live Datenbank-Server einmal verabschieden oder einen Defekt aufweisen, kann man nun relativ einfach das GREYHOUND System mit wenigen Schritten wieder online bringen und die Vorteile dieser Master-Master Replikations-Topologie nutzen.

Aufgrund der symmetrischen Konfiguration ist es relativ einfach, die Rollen der beiden Systeme zu vertauschen, ohne dass wir Gefahr laufen, widersprüchliche Aktualisierungen zu erhalten:

  1. Stoppen aller Schreiboperationen auf dem aktiven Server. Dafür beendet man den GREYHOUND Server über den Admin und stellt sicher, dass auch sonst niemand auf den MySQL Server zugreift.
  2. Nun führt man den MySQL Befehl “SHOW MASTER STATUS” auf dem aktiven Server aus und merkt sich die Binärlog-Koordinaten. Das geht natürlich nur noch, sofern der Master noch irgendwie funktioniert und nicht total abgestürzt ist.
  3. Auf dem passiven System führt man nun den Befehl “SELECT MASTER_POS_WAIT()” mit den Binärlog-Koordinaten des aktiven Servers aus. Dieser Befehl blockiert, bis die Slave Prozesse zum aktiven Server aufgeholt haben. Damit stellt man zu 100% sicher, dass beide Systeme nun konsistent sind und der Slave zum Master keinen Rückstand hat.
  4. Im GREYHOUND Admin ist die Datenbankverbindung auf den bisherigen passiven Server um zu konfigurieren und anschließend der GREYHOUND Server wieder neu zu starten.

Ausblick

Das Thema Replikation ist je nach Szenario recht umfangreich und wir empfehlen jedem, der ernsthaft über den Einsatz einer solchen Lösung im produktiven Umfeld nachdenkt, sich intensiv mit der Materie zu beschäftigen und vorab die verschiedenen “Notsituationen” durchzuspielen, um im Fall der Fälle auch wirklich den Vorteil einer solchen Topologie nutzen zu können.

Für das tiefergehende Verständnis und weitere Details empfehle wir dem Interessierten das Buch “High Performance MySQL, Optimierung, Datensicherung, Replikation & Lastverteilung” aus dem OReilly Verlag in der zweiten Auflage.

Variante 2 (empfohlen) – Einrichtung einer Master-Slave-Replikation

Voraussetzungen:

a) Beide Server können über Port 3306 miteinander kommunizieren, Test via Telnet über Kommandozeile: telnet [IP des Servers] 3306

Im Erfolgsfall sollte sich der gegnerische MySQL-Server melden, sollte der Telnet-Client nicht installiert sein, kann dieser über die Windows-Systemsteuerung nachinstalliert werden.

b) Mindestens doppelte, freie Speicher-Kapazität in Relation zur Datenbankgröße, Beispiel: Datenbankgröße: 10 GB –> benötigter freier Speicherplatz: 20 GB

c) Der Slave-Server kann via Share auf die GREYHOUND-Partition des Master-Servers zugreifen.

d) Der GREYHOUND Server Dienst ist während der Einrichtung auf beiden Servern gestoppt.

Schritt 1: Anlage eines Replikations-Users in MySQL

Auf dem Master muss ein sog. Replikations-User angelegt werden, hierzu verwendet man in einer MySQL-Konsole (beispielsweise mit HeidiSQL oder Queryworx) folgende Query:

INSERT INTO `mysql`.`user` SET `Host`="10.44.99.2",`User`="replicator",`Password`="",`Select_priv`="Y",`Insert_priv`="Y",`Update_priv`="Y",`Delete_priv`="Y",`Create_priv`="Y",`Drop_priv`="Y",`Reload_priv`="Y",`Shutdown_priv`="Y",`Process_priv`="Y",`File_priv`="Y",`Grant_priv`="Y",`References_priv`="Y",`Index_priv`="Y",`Alter_priv`="Y",`Show_db_priv`="Y",`Super_priv`="Y",`Create_tmp_table_priv`="Y",`Lock_tables_priv`="Y",`Execute_priv`="Y",`Repl_slave_priv`="Y",`Repl_client_priv`="Y",`Create_view_priv`="Y",`Show_view_priv`="Y",`Create_routine_priv`="Y",`Alter_routine_priv`="Y",`Create_user_priv`="Y",`Event_priv`="Y",`Trigger_priv`="Y",`Create_tablespace_priv`="Y",`ssl_type`="",`ssl_cipher`="",`x509_issuer`="",`x509_subject`="",`max_questions`=0,`max_updates`=0,`max_connections`=0,`max_user_connections`=0,`plugin`=NULL,`authentication_string`=NULL;

mySQL-Query

Anschließend wird der Replikations-User noch mit den entsprechenden Replikations-Rechten ausgestattet:

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO replicator@'192.168.1.170' IDENTIFIED BY '';"
  • replicator” = Replikationsuser
  • “192.168.1.170” = IP des Slave-Servers

Das Ergebnis sollte in der mysql.user Tabelle so aussehen:

Schritt 2: Anpassung der my.ini auf dem Master-Server

In der my.ini müssen folgende Einträge ergänzt werden (einfach am Ende der my.ini anhängen)

:#Konfiguration der Replikation
server_id = 1
log_bin = mysql-bin
expire_logs_days = 7

Schritt 3: Anpassung der my.ini auf dem Slave-Server

In der my.ini müssen folgende Einträge ergänzt werden (einfach am Ende der my.ini anhängen):

# Konfiguration der Replikation
log-bin = mysql-bin
server_id = 2
relay-log = mysql-relay-bin
log-slave-updates = 1
expire_logs_days = 7
# Wenn dieser Server Master wird, muss der untenstehende Eintrag auskommentiert werden! MySQL Neustart!
read-only = 1

Schritt 4: Kalte Kopie der Datenbank

  1. Der MySQL-Dienst ist sowohl auf dem Master als auch auf dem Slave zu stoppen.
  2. Die Datenbank ‘greyhound’ innerhalb des MySQL data Verzeichnisses des Master-Servers ist auf den Slave-Server in das dortige MySQL data Verzeichnis zu kopieren.

Schritt 5: Abfrage vom Master-Status

  1. Der MySQL-Dienst auf dem Master ist zu starten.
  2. Die MySQL-Konsole auf dem Master ist zu öffnen und die folgende Query auszuführen: show master status;
  3. Die Werte bei “file” und “Position” muss man sich merken (oder kurz notieren), Beispiel: file = mysql-bin.000001; Position = 100

Schritt 6: Start der Replikation auf dem Slave

  1. Der MySQL-Dienst auf dem Slave ist zu starten.
  2. Die MySQL-Konsole auf dem Master ist zu öffnen und folgende Query auszuführen:
    • change master to master_host=’192.168.1.160′,
    • master_user=’replicator’,
    • master_password=”,
    • master_log_file=’mysql-bin.000001′,
    • master_log_pos=100;
    • master_host = IP-Adresse des Master-Servers
    • master_user = Replikationsuser
    • master_log_file = Dateiname, welchen der Master als Status ausgibt
    • master_log_pos = Positon, welche der Master als Status ausgibt
  3. Zum Abschluss ist folgende Query auszuführen:
    • start slave;