Einrichten von Master-Master Replikation mit MySQL 5 auf Debian Etch

Version 1.0
Author: Falko Timme


Seit Version 5 ist bei MySQL eine eingebaute Unterstützung für eine Master-Master Replikation dabei, womit das Problem mit selbst-erzeugten Schlüsseln gelöst wird. In früheren MySQL Versionen bestand das Problem mit der Master-Master Replikation darin, dass Konflikte sofort auftraten, wenn beide, Node A und Node B, einen auto-incrementing key in die gleiche Tabelle eingefügt haben. Die Vorteile einer Master-Master Replikation gegenüber der traditionellen Master-Slave Replikation bestehen darin, dass Du Deine Programme nicht ändern musst, damit nur der Master Schreibzugriff erhält und dass es einfacher ist, eine hohe Verfügbarkeit zu gewährleisten, denn wenn der Master ausfällt, hast Du immer noch den anderen Master.

Ich übernehme keine Garantie, dass dies auch bei Dir funktioniert!

1 Vorbemerkung

In dieser Anleitung werde ich veranschaulichen, wie man die Datenbank exampledb des Servers server1.example.com mit der IP Adresse 192.168.0.100 auf den Server server2.example.com mit der IP Adresse 192.168.0.101 repliziert und umgekehrt. Jedes einzelne System ist zur gleichen Zeit Master und Slave. Beide Systeme laufen auf Debian Etch; jedoch sollte die Konfiguration mit geringfügigen oder keinen Änderungen für fast jede Distribution gelten.

2 Installation von MySQL 5.0

Falls MySQL 5.0 noch nicht auf server1 und server2 installiert ist, dann installiere es jetzt:

server1/server2:

apt-get install mysql-server-5.0 mysql-client-5.0

Um sicher zu stellen, dass die Replikation funkioniert, müssen wir MySQL veranlassen, auf allen Interfaces zu hören. Daher kommentieren wir die Zeile bind-address = 127.0.0.1 in /etc/mysql/my.cnf aus:

server1/server2:

vi /etc/mysql/my.cnf


[...]
# Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. #bind-address = 127.0.0.1 [...]
Starte danach MySQL neu:

server1/server2:

/etc/init.d/mysql restart

Überprüfe dann mit

server1/server2:

netstat -tap | grep mysql

ob MySQL wirklich auf allen Interfaces hört:

server1:~# netstat -tap | grep mysql
tcp 0 0 *:mysql *:* LISTEN 2671/mysqld
server1:~#

Richte danach ein MySQL Passwort für den Benutzer root@localhost ein:

server1/server2:

mysqladmin -u root password yourrootsqlpassword

Als Nächstes erstellen wir ein MySQL Passwort für root@server1.example.com:

server1:

mysqladmin -h server1.example.com -u root password yourrootsqlpassword

Nun richten wir einen Replikations-Benutzer slave2_user ein, der von server2 verwendet werden kann, um auf die MySQL Datenbank auf server1 zugreifen zu können:

server1:

mysql -u root -p

Führe in der MySQL Kommandozeile folgende Befehle aus:

server1:

GRANT REPLICATION SLAVE ON *.* TO 'slave2_user'@'%' IDENTIFIED BY 'slave2_password';
FLUSH PRIVILEGES;
quit;

Nun führen wir die letzten beiden Schritte noch einmal auf server2 aus:

server2:

mysqladmin -h server2.example.com -u root password yourrootsqlpassword

mysql -u root -p


GRANT REPLICATION SLAVE ON *.* TO 'slave1_user'@'%' IDENTIFIED BY 'slave1_password';
FLUSH PRIVILEGES;
quit;

3 Einige Notizen

Im Folgenden gehe ich davon aus, dass die Datenbank exampledb auf server1 bereits existiert und dass Tabellen mit Einträgen vorhanden sind. Nun setzen wir die Replikation exampledb zu server2 auf und danach setzen wir die Replikation von exampledb von server2 zu server1 auf.

Bevor wir anfangen die Replikation aufzusetzen, erstellen wir eine leere Datenbank exampledb auf server2:

server2:

mysql -u root -p


CREATE DATABASE exampledb;
quit;

4 Replikation aufsetzen

Nun setzen wir eine Master-Master Replikation in /etc/mysql/my.cnf auf. Entscheidende Konfigurationsoptionen für die Master-Master Replikation sind auto_increment_increment und auto_increment_offset:
  • auto_increment_increment kontrolliert das Inkrement zwischen aufeinanderfolgenden AUTO_INCREMENT Werten.
  • auto_increment_offset legt den Startpunkt für AUTO_INCREMENT Spaltenwerte fest.
Lass uns davon ausgehen, dass wir N MySQL Nodes (N=2 in diesem Beispiel) haben, dann hat auto_increment_increment den Wert N auf allen Nodes und jede Node muss einen anderen Wert für auto_increment_offset (1, 2, ..., N) haben.

Lass uns nun unsere zwei MySQL Nodes konfigurieren:

server1:

vi /etc/mysql/my.cnf

Suche den Abschnitt, der mit [mysqld] beginnt und setze folgende Optionen ein (indem Du alle vorhandenen widersprüchlichen Optionen auskommentierst):
[...]
[mysqld] server-id = 1 replicate-same-server-id = 0 auto-increment-increment = 2 auto-increment-offset = 1 master-host = 192.168.0.101 master-user = slave1_user master-password = slave1_password master-connect-retry = 60 replicate-do-db = exampledb log-bin = /var/log/mysql/mysql-bin.log binlog-do-db = exampledb relay-log = /var/lib/mysql/slave-relay.log relay-log-index = /var/lib/mysql/slave-relay-log.index expire_logs_days = 10 max_binlog_size = 500M [...]
Dann starte MySQL neu:

server1:

/etc/init.d/mysql restart

Führe nun das Gleiche auf server2 aus:

server2:

vi /etc/mysql/my.cnf


[...]
server-id = 2 replicate-same-server-id = 0 auto-increment-increment = 2 auto-increment-offset = 2 master-host = 192.168.0.100 master-user = slave2_user master-password = slave2_password master-connect-retry = 60 replicate-do-db = exampledb log-bin= /var/log/mysql/mysql-bin.log binlog-do-db = exampledb relay-log = /var/lib/mysql/slave-relay.log relay-log-index = /var/lib/mysql/slave-relay-log.index expire_logs_days = 10 max_binlog_size = 500M [...]
server2:

/etc/init.d/mysql restart


Als Nächstes sperren wir die exampledb Datenbank auf server1, finden den Master Status von server1 heraus, erstellen einen SQL dump von exampledb (den wir in exampledb auf server2 importieren, sodass beide Datenbanken die gleichen Daten beinhalten) und schalten die Datenbank wieder frei, damit sie wieder verwendet werden kann:

server1:

mysql -u root -p

Führe in der MySQL Kommandozeile folgende Befehle aus:

server1:

USE exampledb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Der letzte Befehl sollte etwas wie das anzeigen (schreib es bitte auf, wir brauchen es später noch):

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

mysql>

Verlasse nicht die MySQL Kommandozeile. Denn wenn Du dies tust, wird die gesperrte Datenbank wieder frei geschaltet und das wollen wir gerade nicht, da wir jetzt einen Datenbank dump erstellen müssen. Während die MySQL Kommandozeile immer noch geöffnet ist, öffnen wir ein zweites Kommandozeilenfenster, in dem wir den SQL dump snapshot.sql erstellen und ihn auf server2 transferieren (mittels scp):

server1:

cd /tmp
mysqldump -u root -pyourrootsqlpassword --opt exampledb > snapshot.sql
scp snapshot.sql root@192.168.0.101:/tmp

Danach kannst Du das zweite Kommandozeilenfenster schließen. Nun können wir in dem ersten Kommandozeilenfenster die Datenbank freischalten und die MySQL Kommandozeile verlassen:

server1:

UNLOCK TABLES;
quit;

Nun können wir auf server2 den SQL dump snapshot.sql wie folgt importieren:

server2:

/usr/bin/mysqladmin --user=root --password=yourrootsqlpassword stop-slave
cd /tmp
mysql -u root -pyourrootsqlpassword exampledb < snapshot.sql

Danach müssen wir auch den Master Status von server2 ermitteln und aufschreiben:

server2:

mysql -u root -p


USE exampledb;
FLUSH TABLES WITH READ LOCK;

SHOW MASTER STATUS;


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

mysql>

Schalte dann die Tabellen frei:

server2:

UNLOCK TABLES;

und führe folgenden Befehl aus um aus server2 einen Slave von server1 zu machen (es ist wichtig, dass Du die Werte im folgenden Befehl mit den Werten, die Du vom SHOW MASTER STATUS; Befehl erhalten hast, den wir auf server1 ausgeführt haben, ersetzt!):

CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER='slave2_user', MASTER_PASSWORD='slave2_password', MASTER_LOG_FILE='mysql-bin.000009', MASTER_LOG_POS=98;

Starte schließlich den Slave:

server2:

START SLAVE;

Überprüfe dann den Slave Status:

server2:

SHOW SLAVE STATUS;

Es ist wichtig, dass beide, Slave_IO_Running und Slave_SQL_Running den Wert Yes in der Ausgabe aufweisen (sonst ist etwas schief gelaufen und Du solltest Dein Setup nochmal überprüfen und Dir /var/log/syslog ansehen, um Fehler festzustellen):
mysql> SHOW SLAVE STATUS;
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+ | Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | +----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+ | Waiting for master to send event | 192.168.0.100 | slave2_user | 3306 | 60 | mysql-bin.000009 | 98 | slave-relay.000002 | 235 | mysql-bin.000009 | Yes | Yes | exampledb | | | | | | 0 | | 0 | 98 | 235 | None | | 0 | No | | | | | | 0 | +----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+ 1 row in set (0.00 sec) mysql>
Danach kannst Du die MySQL Kommandozeile auf server2 verlassen:

server2:

quit

Nun ist die Replikation von server1 auf server2 eingerichtet. Als Nächstes müssen wir die Replikation von server2 auf server1 konfigurieren.

Dafür beenden wir den Slave auf server1 und machen aus ihm einen Slave von server2:

server1:

mysql -u root -p

STOP SLAVE;

Vergewissere Dich, dass Du in folgendem Befehl die Werte des SHOW MASTER STATUS; Befehls verwendest, den Du auf server2 ausgeführt hast:

server1:

CHANGE MASTER TO MASTER_HOST='192.168.0.101', MASTER_USER='slave1_user', MASTER_PASSWORD='slave1_password', MASTER_LOG_FILE='mysql-bin.000009', MASTER_LOG_POS=783;

Starte dann den Slave auf server1:

server1:

START SLAVE;

Überprüfe dann den Slave Status:

server1:

SHOW SLAVE STATUS;

Es ist wichtig, dass beide, Slave_IO_Running und Slave_SQL_Running den Wert Yes in der Ausgabe aufweisen (sonst ist etwas schief gelaufen und Du solltest Dein Setup nochmal überprüfen und Dir /var/log/syslog ansehen um mögliche Fehler festzustellen):
mysql> SHOW SLAVE STATUS;
+----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+ | Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | +----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+ | Waiting for master to send event | 192.168.0.101 | slave1_user | 3306 | 60 | mysql-bin.000009 | 783 | slave-relay.000002 | 235 | mysql-bin.000009 | Yes | Yes | exampledb | | | | | | 0 | | 0 | 783 | 235 | None | | 0 | No | | | | | | 0 | +----------------------------------+---------------+-------------+-------------+---------------+------------------+---------------------+--------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+ 1 row in set (0.00 sec) mysql>
Danach kannst Du die MySQL Kommandozeile verlassen:

quit

Wenn nichts schief gegangen ist, sollte die MySQL Master-Master Replikation nun funktionieren. Wenn nicht, überprüfe bitte /var/log/syslog wegen MySQL Fehlern auf server1 und server2.

5 Links