MySQL 5.5: Master-Slave-Replikation
Allgemeines
Das Duplizieren oder Vervielfältigen der Daten eines MySQL-Servers wird replizieren genannt. Es ist möglich einen kompletten Server, ausgewählte Datenbanken oder sogar nur ausgewählte Tabellen einer Datenbank zu replizieren.
In jedem Fall wird mindestens eine weitere MySQL-Installation auf einem anderen Rechner benötigt. Nicht zwingend erforderlich, aber aus Gründen der Kompatibilität sollten die MySQL-Versionen beider Installationen identisch sein.
Im folgenden soll zwischen den zwei Varianten der Replikation vollständiger Datenbanken unterschieden werden:
- Vollständige Replikation aller Datenbanken des Masters
- Replikation aller Datenbanken, aber einzelne Datenbanken ausnehmen
Die Replikation ausgewählter Datenbanken ist zwar auch möglich, birgt aber Risiken. Soll z.B. nur die die Daten DB1 repliziert werden, würde der folgende Befehl ignoriert werden:
USE DB2; UPDATE db1.foo SET bar=bar+1 WHERE id = 1;
Vorbereiten der Master-Instanz
Es müssen einige Einstellungen in der Datei my.cnf angepasst werden.
[mysqld] # Jede MySQL-Instanz benötigt eine eigene einzigartige Server-ID server-id = 1 # Binäres protokollieren aktivieren log-bin # Nach 21 Tagen sollen alte Einträge gelöscht werden expire_logs_days = 21 # Maximale Größe eines Logfiles max_binlog_size = 500M
Es kann sinnvoll sein, die Datenbanken mysql und test nicht zu replizieren. Die my.cnf wird dann um folgende Zeilen ergänzt:
binlog-ignore-db=mysql binlog-ignore-db=test
Außerdem wird noch ein Replikations-User benötigt. Der entsprechende SQL-Befehl dazu lautet:
GRANT REPLICATION SLAVE ON *.* TO repl@'slave' IDENTIFIED BY 'geheim';
- repl: Der Benutzername zur Replikation
- slave: Der Name oder die IP des Slaves
- geheim: Das Passwort des Benutzers repl
Anschließend den MySQL-Master-Server neu starten.
Erstellen eines Backups
Ein Weg zur Sicherung der Daten führt über mysqldump. Zunächst sollte der schreibende Zugriff auf alle Tabellen blockiert werden:
mysql -u root -p -Bse 'FLUSH TABLES WITH READ LOCK'
Mit mysqldump (gehört zu jeder mysql-Installation) lässt sich der aktuelle Master-Stand schnell sichern:
mysqldump -u root -p --master-data --all-databases --result-file=./master-data_all.sql
Da es für mysqldump noch keine Option für den Ausschluss ganzer Datenbanken hat, habe ich dieses kleine Shell-Script geschrieben.
Danach die Schreibblockade wieder lösen mit:
mysql -u root -p -Bse 'unlock tables'
Vorbereiten des Slave-Instanz
Zunächst sollten in der my.cnf vom Slave einige Zeilen ergänzt werden:
[mysqld] # Jede MySQL-Instanz benötigt eine eigene einzigartige Server-ID server-id = 2 # Folgende Datenbanken sollen ignoriert werden replicate-ignore-db=test replicate-ignore-db=mysql
Den Slave-Server neu starten.
Slave starten
Zuletzt werden nun noch drei SQL-Befehle auf dem Slave benötigt:
1. Master-Informationen
MASTER_LOG_FILE und MASTER_LOG_POS sollte bereits durch die Option "--master-data" im Dumpfile übermittelt worden sein. Es fehlen noch die Angaben zum Replikations-Benutzers auf dem Master, die vorhin angelegt worden sind:
mysql> CHANGE MASTER TO MASTER_HOST='master', MASTER_USER='repl', MASTER_PASSWORD='geheim';
2. Backup einspielen
von der mysql-Shell mit:
mysql> source $DUMPFILE.sql;
oder von der System-Shell auch so:
mysql -p < $DUMPFILE.sql
Dann kann die Replikation gestartet werden:
mysql> start slave;
Ob die Replikation ordnungsgemäß ausgeführt wird, erfährt man mit:
show slave status\G
Wichtig sind die Zeilen
[...] Slave_IO_Running: Yes Slave_SQL_Running: Yes [...] Seconds_Behind_Master: 0
Die ersten beiden Zeilen zeigen an, ob die Replikation überhaupt läuft, die letzte, wie erfolgreich die Replikation ist. Die oberen Zeilen zeigen das optimale Ergebnis an.
Mögliche Fehler
Error Duplicate entry
Wie die Bezeichnung schon sagt, ein Eintrag ist bereits vorhanden. Wahrscheinlich ist etwas mit dem Dump nicht in Ordnung. Man sollte ein neues Backup machen. Wenn man weiß, was man tut, kann man die Fehlermeldung überspringen:
set global sql_slave_skip_counter=1;start slave;
Informationen zur Master-Slave-Konfiguration
Eine Slave ist kein Backup
Ähnlich wie ein RAID 1 zur Spiegelung von Festplatten kann eine Datenbank-Replikation nicht als Backup dienen. Alle SQL-Befehle auf dem Master werden zeitnah auf dem Slave ausgeführt. Ein unachtsamer drop, truncate oder delete Befehl vernichtet Daten sofort auf beiden Instanzen. Eine Replikation hilft nur bei Ausfall des Masters und beim Störungsfreien Backup.
Kommentare
Martin Keckeis (nicht überprüft)
18. Oktober 2012 - 14:35
Permanenter Link
"MASTER_LOG_FILE und
"MASTER_LOG_FILE und MASTER_LOG_POS sollte bereits durch die Option "--master-data" im Dumpfile übermittelt worden sein. "
war bei mir nicht der fall, habe beim mysqldump aber auch nur eine datenbank genommen...
Lösung dann:
show master status (beim Master)
und diese Werte übernehmen:
CHANGE MASTER TO MASTER_LOG_FILE = 'log-bin.xxxxxx, MASTER_LOG_POS = 107;
stefan
18. Oktober 2012 - 22:58
Permanenter Link
Stimmt. Daneben kann man auch