MySQL Replikation

Aus Thomas-Krenn-Wiki
Wechseln zu: Navigation, Suche

Dieser Artikel erklärt wie die MySQL Replikation von einem Master zu einem Slave Server eingerichtet werden kann.

Einsatzbereiche

Vorweg muss klar sein, dass eine MySQL Replikation immer asynchron funktioniert, d.h. dass der Slave je nach Last immer eine gewisse Zeit hinterherhinkt. Somit können eine unbestimmte Anzahl an Transaktionen verloren gehen. Wenn dies keine Option ist, kann auf DRBD gesetzt werden, welches eine synchrone blockbasierte Replikation ermöglicht.

Datenverteilung/Disaster Recovery

Die Daten können von einem MySQL Server zu einem weit entferntem Server in einem anderem Rechenzentrum repliziert werden. Dies kann Teil einer Disaster Recovery Strategie sein.

Leseabfragen verteilen

Mit Hilfe der MySQL Replikation können Leseabfragen auf mehrere Server verteilt werden. Da bei vielen Applikationen (z.B. Web-Applikationen) die Leseabfragen bei weitem überwiegen, ist dies oft eine gute Methode für eine Skalierung.

MySQL Upgrades Testen

Da der MySQL Slave Server normalerweise abwärtskompatibel funktioniert, kann im Falle eines MySQL Upgrades zuerst der Slave aktualisiert werden. Man kann dann seine Applikation oder bestimmte Queries mit dem Slave testen.

Funktionsweise

Mysql-replication.png

In der einfachsten Variante gibt es einen Master und einen Slave. Der Master schreibt alle Änderungen an seinen Daten (UPDATE, DELETE, CREATE TABLE, ...) in das Binärlog. Bei der Statement-Based Replikation werden im Binary Log direkt die SQL Kommandos aufgezeichnet, welche Änderungen an den Daten vornehmen. Seit MySQL 5.1 gibt es zusätzlich noch die Row-Based Replication, welche keine SQL Kommandos aufzeichnet sondern direkt die Änderungen an den einzelnen Tabellen Zeilen. Es gibt auch die Möglichkeit eines "Mixed" Formats, d.h. Statement- und Row-Based werden gemischt verwendet. Von MySQL 5.1.12 bis MySQL 5.1.28, war das "Mixed" Format die Default Einstellung. Seit MySQL 5.1.29 ist Statement-based wieder der Standard-Wert. Seit MySQL 5.7.7 ist Row-Based die Standard-Einstellung. Vor- und Nachteile werden in [1] erläutert.

Der I/O-Thread am Slave Server verbindet sich zum Master Server und startet einen Binlog-Dump-Prozess und liest dadurch die Binlog-Events des Masters aus und schreibt diese in das Relay-Log am Slave Server. Der SQL Thread liest diese Binlogs ein und spielt das Relay-Log am Slave Server wieder ab. Dadurch werden die Daten am Slave nach und nach aktualisiert.

Einrichten der Replikation

Es wird beschrieben, wie die Replikation von einem bereits bestehenden MySQL Server eingerichtet werden kann. Details zu den einzelnen Parametern finden Sie auch in der Dokumentation von MySQL [2].

ACHTUNG: Die folgende Anleitung richtet eine unverschlüsselte Replikation ein. Diese darf nur innerhalb von vertrauenswürdigen Netzwerken, auf keinen Fall im Internet betrieben werden. Eine Anleitung dazu finden Sie hier: MySQL_Verbindungen_mit_SSL_verschlüsseln

Konfiguration am Master

  • Replikations-Account anlegen via MySQL Kommandozeile
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'192.168.0.%' IDENTIFIED BY 'password';
  • Binary-Logging aktivieren: Dazu in der MySQL Konfigurationsdatei (z.B. /etc/my.cnf) folgende Zeile eintragen:
log_bin = mysql-bin
  • Server ID festlegen: diese muss ebenfalls in der Konfigurationsdatei eingetragen werden
server_id = 10
  • Festplatten Sync des Binary Logs:
sync_binlog = 1
  • Interface auf dem Master so konfigurieren, dass sich der Slave über das Netzwerk verbinden kann:
bind-address = 192.168.0.1 
  • MySQL Server neu starten
  • Danach den Master Status überprüfen via MySQL Kommandozeile:
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |       98 |              |                  | 
+------------------+----------+--------------+------------------+

Konfiguration am Slave

  • Es muss ebenfalls eine eindeutige Server ID festgelegt werden:
server_id = 2
  • Zusätzlich sind noch folgende Parameter in der MySQL Konfigurationsdatei zu empfehlen:
relay_log = mysql-relay-bin
read_only = 1

Kopieren der Daten vom Master zum Slave

Mit Hilfe von Mysqldump kann ein MySQL Dump des Masters im laufenden Betrieb gemacht werden. Sollten Sie auch MyISAM Tabellen einsetzen, empfiehlt es sicht die Option "--lock-all-tables" zu setzen, damit der Dump konsistent ist.

mysqldump --all-databases --allow-keywords --single-transaction --flush-logs --master-data=2 -r /root/mysqldump.sql

Diesen Dump kopieren Sie danach auf den MySQL Slave. Dort löschen Sie alle vorhandenen Mysql Datenbanken.

mysql> show databases;
mysql> drop database test;
...

Danach spielen Sie den Mysqldump ein. Es empfiehlt sich unter Umständen die Binary-Logs am MySQL Slave zu deaktivieren während dem einspielen des MySQL Dumps, da ansonsten alle SQL Statements nochmal in den Binary-Logs aufgezeichnet werden.

cat /root/mysqldump.sql | mysql -u root -p

Starten Sie den Slave Server jetzt neu.

Starten der Replikation

Jetzt ist es soweit, die Replikation kann gestartet werden. Verbinden Sie sich dazu auf die MySQL Kommandozeile am Slave Server und führen Sie folgendes Statement aus:

mysql> change master to master_host='192.168.0.1', master_user='repl', 
  master_password='password', master_log_file='mysql-bin.000005', master_log_pos=98;

Beachten Sie, dass sie einen individuellen Wert bei master_log_file und master_log_pos eintragen müssen. Sie können diesen Wert aus dem Mysqldump entnehmen:

# head mysqldump.sql -n 25
-- MySQL dump 10.11
--
-- Host: localhost    Database: 
-- ------------------------------------------------------
-- Server version	5.0.33-log

...
...

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=98;

Überprüfen Sie ob das Kommando erfolgreich war:

mysql> show slave status\G

Wenn die Werte korrekt übernommen wurden, können Sie die Replikation starten:

mysql> start slave;

Jetzt können Sie den Status nochmal prüfen, Slave_IO_Running und Slave_SQL_Running sollten nun auf "Yes" sein.

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.0.1
                Master_User: repl
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.021831
        Read_Master_Log_Pos: 21716591
             Relay_Log_File: mysql-relay-bin.000229
              Relay_Log_Pos: 21716728
      Relay_Master_Log_File: mysql-bin.021831
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: 
        Replicate_Ignore_DB: 
         Replicate_Do_Table: 
     Replicate_Ignore_Table: 
    Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
                 Last_Errno: 0
                 Last_Error: 
               Skip_Counter: 0
        Exec_Master_Log_Pos: 21716591
            Relay_Log_Space: 21716728
            Until_Condition: None
             Until_Log_File: 
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File: 
         Master_SSL_CA_Path: 
            Master_SSL_Cert: 
          Master_SSL_Cipher: 
             Master_SSL_Key: 
      Seconds_Behind_Master: 0

Am Slave wird nach einem erfolgreichen "CHANGE MASTER" Kommando eine Datei namens "master.info" im MySQL Datenverzeichnis (z.B. /var/lib/mysql/ erstellt. Diese enthält unter anderem die IP vom Master, den Port, den Usernamen und das Passwort.

Verwaltung und Überwachung der Replikation

Sie sollten die Replikation unbedingt überwachen, da diese durch verschiedenste Umstände unterbrochen werden kann. Dies kann einen manuellen Eingriff erfordern um die Replikation wieder zu starten.

Außerdem sollten Sie von Zeit zu Zeit prüfen, ob die Daten am Master und am Slave ident sind. Dies kann z.B. mit Hilfe des Percona Tools "pt-table-checksum" [3] erfolgen.

Einzelnachweise

  1. https://dev.mysql.com/doc/refman/5.7/en/replication-sbr-rbr.html
  2. http://dev.mysql.com/doc/refman/5.1/en/replication.html
  3. http://www.percona.com/doc/percona-toolkit/2.0/pt-table-checksum.html


Foto Christoph Mitasch.jpg

Autor: Christoph Mitasch

Christoph Mitasch arbeitet in der Abteilung Web Operations & Knowledge Transfer bei Thomas-Krenn. Er ist für die Betreuung und Weiterentwicklung der Webshop Infrastruktur zuständig. Seit einem Studienprojekt zum Thema Hochverfügbarkeit und Daten Replikation unter Linux beschäftigt er sich intensiv mit diesem Themenbereich. Nach einem Praktikum bei IBM Linz schloss er sein Diplomstudium „Computer- und Mediensicherheit“ an der FH Hagenberg ab. Er wohnt in der Nähe von Linz und ist neben der Arbeit ein begeisterter Marathon-Läufer und Jongleur, wo er mehrere Weltrekorde in der Team-Jonglage hält.


Das könnte Sie auch interessieren

Linux Performance Analyse in 60 Sekunden
Linux Performance Messungen mit vmstat
MySQL Optimize Table