MySQL Multi-Master-Replikation

Diesen Beitrag schrieb ich 9 Jahre und 1 Monat zuvor; die nachfolgenden Ausführungen müssen heute weder genau so nach wie vor funktionieren, noch meiner heutigen Meinung entsprechen. Behalte das beim Lesen (und vor allem: beim Nachmachen!) bitte stets im Hinterkopf.

Geschätzte Lesezeit: 3 Minuten

Die Multi-Master-Replikation kommt zum Einsatz, wenn Ausfallsicherheit oder Lastverteilung gewünscht sind: egal, auf welchem MySQL-Host Änderungen vorgenommen werden – sie werden auf allen angeschlossenen Hosts übernommen. Die grundsätzliche Einrichtung möchte ich dir hier zeigen.

Vorarbeiten auf allen Systemen

Im ersten Schritt müssen natürlich auf allen Hosts die benötigten Pakete installiert werden.

$ apt-get -y install mysql-client mysql-server

Jetzt können auf mysql1 die für die Replikation benötigten User angelegt werden: es sind drei an der Zahl, für jeden Host einer.

mysql1> GRANT REPLICATION SLAVE ON *.* 
    -> TO 'SlaveOnMysql1'@'%'
    -> IDENTIFIED BY 'qwertz'
    -> REQUIRE SUBJECT '/CN=mysql1';
Query OK, 0 rows affected (0.00 sec)
 
mysql1> GRANT REPLICATION SLAVE ON *.*
    -> TO 'SlaveOnMysql2'@'%'
    -> IDENTIFIED BY 'qwertz'
    -> REQUIRE SUBJECT '/CN=mysql2';
Query OK, 0 rows affected (0.00 sec)
 
mysql1> GRANT REPLICATION SLAVE ON *.*
    -> TO 'SlaveOnMysql3'@'%'
    -> IDENTIFIED BY 'qwertz'
    -> REQUIRE SUBJECT '/CN=mysql3';
Query OK, 0 rows affected (0.00 sec)

Sobald das passiert ist, wird der Dienst auf allen Hosts gestoppt.

$ service mysql stop

Die Konfiguration

Anschließend wird auf allen Maschinen die /etc/mysql/my.cnf angepasst: da es sich hier um eine standortübergreifende Replikation handelt genügt es nicht, wenn der Dienst ausschließlich auf localhost lauscht – er muss auf allen Interfaces präsent sein:

## file: "/etc/mysql/my.cnf"
...
bind-address = 0.0.0.0
...

Die Replikation erfolgt per SSL-Verschlüsselung! Ich habe bereits ausführlich gezeigt, wie sich die Replikation über SSL absichern lässt – selbstredend soll das auch hier gemacht werden. Für Details verweise ich auf den Artikel zum Thema und halte mich hier kurz: auf jedem Host müssen die benötigten Keys erstellt werden.

$ cd /etc/mysql
$ openssl req -x509 -newkey rsa:4096 \
> -keyout mysql_-private.pem \
> -out mysql_-public.pem \
> -subj '/CN=mysql_' \
> -nodes \
> -days 3650
Generating a 4096 bit RSA private key
....................................................................................................++
.........++
writing new private key to 'mysql_-private.pem'
-----
openssl rsa -in mysql_-private.pem -out mysql_-private-compat.pem
writing RSA key
chmod 0600 *.pem
chown mysql:mysql *.pem

mysql1

Die /etc/mysql/my.cnf auf mysql1 wird überarbeitet und erweitert; es ist wichtig, hier ersteinmal skip-slave-start einzusetzen! Sobald alles läuft, wird diese Zeile dann auskommentiert. Besondere Wichtigkeit hat in unserem Setup die Zeile log-slave-updates – lässt du diese aus, würden neue Daten, die auf mysql1 einlaufen sich zwar auf mysql2 replizieren, nicht aber auf mysql3. Daten, die auf mysql3 einlaufen, würden sich zwar auf mysql1 replizieren – nicht aber auf mysql2. Das ganze System würde einen in sich inkonsistenten Zustand erreichen – und das im schlimmsten Falle ohne entsprechende Warnungen auszulösen, denn jede MySQL verhält sich ja folgerichtig und lediglich so, wie sie konfiguriert wurde.

## file: "/etc/mysql/my.cnf
## @mysql1
...
[mysqld]
...
##---------------------------------------------------------------------
# SSL for replication
ssl-ca                   = /etc/mysql/ca-cert.pem
ssl-cert                 = /etc/mysql/mysql1-public.pem
ssl-key                  = /etc/mysql/mysql1-private-compat.pem
...
#
# * Logging and Replication
#
log_error                = /var/log/mysql/error.log
server-id                = 1
replicate-same-server-id = 0
auto-increment-increment = 3
auto-increment-offset    = 1
expire_logs_days         = 10
max_binlog_size          = 500M
log_bin                  = /var/lib/mysql/mysql-bin.log
relay-log                = mysqld-relay-bin
binlog_format            = MIXED
log-slave-updates
skip-slave-start
...

Erst den Dienst beenden:

$ service mysql stop

Jetzt kann der Datenbestand von mysql1 auf mysql2 und mysql3 transportiert werden; dazu gehören praktischerweise dann auch die eben angelegte neuen Nutzer :-) Ich habe mir einen SSH-Key angelegt, der auf den Hosts entsprechend in den authorized_keys hinterlegt ist und den Zugriff auf rsync einschränkt, aber das ist Geschmackssache: auf welchem Wege du die Daten überträgst, soll dir überlassen sein (so lange der gewählte Übertragungsweg hinreichend sicher ist).

$ rsync -avz --delete -e "ssh -i /root/rsync/rsync-mirror" /var/lib/mysql/* root@mysql_:/var/lib/mysql/
$ rsync -avz --delete -e "ssh -i /root/rsync/rsync-mirror" /etc/mysql/debian.cnf root@mysql_:/etc/mysql/debian.cnf

Nun kannst du den Dienst auf mysql1 final in Betrieb nehmen und dir die für die Replikation benötigten Daten abgreifen; zu diesem Zeitpunkt ist mysql1 vollständig bereit, der Dienst muss nun auf den übrigen Hosts eingerichtet werden.

mysql1> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.00000x |      ccc |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql2

Auch hier wird die /etc/mysql/my.cnf angepasst bzw. erweitert - wiederum aufpassen, dass keine Statements doppelt erscheinen!

## file: "/etc/mysql/my.cnf"
## @mysql2
...
[mysqld]
...
##---------------------------------------------------------------------
# SSL for replication
ssl-ca                   = /etc/mysql/ca-cert.pem
ssl-cert                 = /etc/mysql/mysql2-public.pem
ssl-key                  = /etc/mysql/mysql2-private-compat.pem
...
#
# * Logging and Replication
#
log_error                = /var/log/mysql/error.log
server-id                = 2
replicate-same-server-id = 0
auto-increment-increment = 3
auto-increment-offset    = 2
expire_logs_days         = 10
max_binlog_size          = 500M
log_bin                  = /var/lib/mysql/mysql-bin.log
relay-log                = mysqld-relay-bin
binlog_format            = MIXED
log-slave-updates
skip-slave-start
...

Der Datenbestand liegt ja schon in /var/lib/mysql – also kann der Dienst direkt in Betrieb genommen werden:

$ service mysql start

Und dann auch hier die benötigten Informationen für die Replikation abgreifen:

mysql2> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.00000y |      bbb |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql3

## file: "/etc/mysql/my.cnf"
## @mysql3
...
[mysqld]
...
##---------------------------------------------------------------------
# SSL for replication
ssl-ca                   = /etc/mysql/ca-cert.pem
ssl-cert                 = /etc/mysql/mysql3-public.pem
ssl-key                  = /etc/mysql/mysql3-private-compat.pem
...
#
# * Logging and Replication
#
log_error                = /var/log/mysql/error.log
server-id                = 3
replicate-same-server-id = 0
auto-increment-increment = 3
auto-increment-offset    = 3
expire_logs_days         = 10
max_binlog_size          = 500M
log_bin                  = /var/lib/mysql/mysql-bin.log
relay-log                = mysqld-relay-bin
binlog_format            = MIXED
log-slave-updates
skip-slave-start
...

Auch hier liegt der Datenbestand bereits in /var/lib/mysql bereit – also auch auf dem dritten Host den Dienst starten und die für die Replikation benötigten Daten abgreifen:

mysql3> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.00000z |      aaa |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Multi-Master-Replikation starten

mysql1 ist Master für den Slave mysql2

mysql2> CHANGE MASTER TO
  -> MASTER_HOST='mysql1',
  -> MASTER_USER='SlaveOnMysql2',
  -> MASTER_PASSWORD='qwertz',
  -> MASTER_LOG_FILE='mysql-bin.00000x',
  -> MASTER_LOG_POS=ccc,
  -> MASTER_SSL=1,
  -> MASTER_SSL_CA='/etc/mysql/ca-cert.pem',
  -> MASTER_SSL_CERT='/etc/mysql/ispc2-public.pem',
  -> MASTER_SSL_KEY='/etc/mysql/ispc2-private-compat.pem',
  -> Master_SSL_Verify_Server_Cert = 1;
Query OK, 0 rows affected (0.00 sec)
 
mysql2> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

mysql2 ist Master für den Slave mysql3

mysql3> CHANGE MASTER TO
  -> MASTER_HOST='mysql2',
  -> MASTER_USER='SlaveOnMysql3',
  -> MASTER_PASSWORD='qwertz',
  -> MASTER_LOG_FILE='mysql-bin.00000y',
  -> MASTER_LOG_POS=bbb,
  -> MASTER_SSL=1,
  -> MASTER_SSL_CA='/etc/mysql/ca-cert.pem',
  -> MASTER_SSL_CERT='/etc/mysql/mysql3-public.pem',
  -> MASTER_SSL_KEY='/etc/mysql/mysql3-private-compat.pem',
  -> Master_SSL_Verify_Server_Cert = 1;
Query OK, 0 rows affected (0.00 sec)
 
mysql3> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

mysql3 ist Master für den Slave mysql1

mysql1> CHANGE MASTER TO
  -> MASTER_HOST='mysql3',
  -> MASTER_USER='SlaveOnMysql1',
  -> MASTER_PASSWORD='qwertz',
  -> MASTER_LOG_FILE='mysql-bin.00000z',
  -> MASTER_LOG_POS=aaa,
  -> MASTER_SSL=1,
  -> MASTER_SSL_CA='/etc/mysql/ca-cert.pem',
  -> MASTER_SSL_CERT='/etc/mysql/mysql1-public.pem',
  -> MASTER_SSL_KEY='/etc/mysql/mysql1-private-compat.pem',
  -> Master_SSL_Verify_Server_Cert = 1;
Query OK, 0 rows affected (0.00 sec)
 
mysql1> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

Die Replikation prüfen und Abschlussarbeiten

Die funktionierende Replikation äußert sich darin, dass sich auf jedem Host der Slave-Status fehlerfrei abgreifen lässt; ich zeige das hier mal am Beispiel von mysql2:

mysql2> show slave status\G
  *************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql1
                  Master_User: SlaveOnMysql2
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.00000x
          Read_Master_Log_Pos: ccc
               Relay_Log_File: mysqld-relay-bin.000004
                Relay_Log_Pos: 1535400
        Relay_Master_Log_File: mysql-bin.000097
             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: ccc
              Relay_Log_Space: 1535557
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /etc/mysql/ca-cert.pem
           Master_SSL_CA_Path:
              Master_SSL_Cert: /etc/mysql/mysql2-public.pem
            Master_SSL_Cipher:
               Master_SSL_Key: /etc/mysql/mysql2-private-compat.pem
        Seconds_Behind_Master: 0
 Master_SSL_Verify_Server_Cert: Yes
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
  1 row in set (0.00 sec)

Replikation läuft, sie läuft über SSL und Master und Slave sind auf Gleichstand – das ist genau das, was wir wollten. Überprüfe anschließend auch auf den beiden anderen Hosts, dass die Replikation fehlerfrei läuft – Passwort-Vertipper sind so berühmte Fehler, oder die Benutzung des verkehrten SSL-Keys. Wenn jedoch alles überall fehlerfrei läuft ist die Zeit für einen ersten Testlauf gekommen: lege auf mysql1 eine Test-Datenbank an. Innerhalb kürzester Zeit muss sie sowohl auf mysql2 als auch auf mysql3 erscheinen, wobei sich die Angaben zu log_pos entsprechend ändern.

mysql1> CREATE DATABASE mytestdb;

Nun kannst du beginnen, das System zu stressen, zu testen, Ausfälle zu provozieren, kurz: auf Herz und Nieren zu testen. Auch solltest du sicherstellen, dass die Dienste ins Monitoring aufgenommen werden – idealerweise prüfst du nicht nur die Funktionalität der einzelnen Services ab, sondern auch die Integrität der Daten auf allen Hosts. Und vergiss nicht, abschließend auf allen Hosts in /etc/mysql/my.cnf die Zeile skip-slave-start auszukommentieren! Nur so stellst du sicher, dass bei einem Restart des Dienstes der Slave überhaupt gestartet wird ;)

Alle Bilder dieser Seite: © Marianne Spiller – Alle Rechte vorbehalten
Hintergrundbild: 1500x 1000px, Bild genauer anschauen – © Marianne Spiller – Alle Rechte vorbehalten

Eure Gedanken zu „MySQL Multi-Master-Replikation“

Ich freue mich über jeden Kommentar, es sei denn, er ist blöd. Deshalb behalte ich mir auch vor, die richtig blöden kurzerhand wieder zu löschen. Die Kommentarfunktion ist über GitHub realisiert, weshalb ihr euch zunächst dort einloggen und „utterances“ bestätigen müsst. Die Kommentare selbst werden im Issue-Tracker und mit dem Label „✨💬✨ comment“ erfasst – jeder Blogartikel ist ein eigenes Issue. Über GitHub könnt ihr eure Kommentare somit jederzeit bearbeiten oder löschen.