Einrichtung der asynchronen MySQL-Replikation: Eine Schritt-für-Schritt-Anleitung

Asynchrone MySQL-Replikation ist immer noch einer der nützlichsten Bausteine in einem Datenbank-Setup. Sie können sie für Lese-Replikate, sicherere Backups, Berichtskopien, Migrations-Staging und Disaster Recovery verwenden. Das wichtige Wort ist "asynchron": Die Quelle führt eine Transaktion aus, ohne darauf zu warten, dass das Replikat sie anwendet. Das verhindert, dass die Quelle bei jedem Replikat-Schreibvorgang blockiert, bedeutet aber auch, dass das Replikat nachhinken kann.

Die alte Terminologie in vielen MySQL-Befehlen verwendet "Master" und "Slave". Neuere MySQL-Versionen verwenden "source" und "replica" in Befehlen wie SHOW REPLICA STATUS und CHANGE REPLICATION SOURCE TO. In älteren Systemen, Beispielen und Skripten können Sie weiterhin die älteren Befehle sehen. Diese Anleitung verwendet Quelle und Replikat in der Erklärung und zeigt zuerst die neuere Befehlsform mit Hinweisen, wo sich die ältere Syntax unterscheidet.

Das Beispiel verwendet zwei Server:

• Quelle: 192.168.1.100
• Replikat: 192.168.1.101
• Replikationsbenutzer: repl_user
• Umfang: alle Datenbanken, es sei denn, Sie filtern absichtlich

Führen Sie dies zuerst in einer Testumgebung durch, wenn Sie die Prozedur noch nicht ausgeführt haben. Die Einrichtung der Replikation ist einfach, wenn alles sauber ist. Sie wird stressig, wenn die Quelle ausgelastet ist, der Dump inkonsistent ist oder das Replikat bereits alte Daten enthält.

Bevor Sie die Konfiguration ändern

Bestätigen Sie die Grundlagen:

• Beide Server laufen mit kompatiblen MySQL-Versionen.
• Das Replikat kann die Quelle über den MySQL-Port, normalerweise 3306, erreichen.
• Sie haben administrativen Zugriff auf beide MySQL-Instanzen.
• Sie können die MySQL-Konfigurationsdatei bearbeiten und MySQL bei Bedarf neu starten.
• Das Replikat ist entweder leer oder Sie wissen genau, welche vorhandenen Daten ersetzt werden müssen.
• Die Quelle hat genügend Festplattenspeicher für Binärlogs.

Testen Sie auf dem Replikats-Host den grundlegenden Netzwerkzugriff:

nc -vz 192.168.1.100 3306

Wenn nc nicht verfügbar ist, verwenden Sie telnet oder die Konnektivitätstools Ihres Cloud-Anbieters. Beheben Sie Firewalls, Sicherheitsgruppen, Bind-Adressen und Routing, bevor Sie die Replikation konfigurieren. Ein Replikationsbenutzer hilft nicht, wenn der TCP-Pfad blockiert ist.

Konfigurieren Sie den Quellserver

Die Quelle muss Änderungen in Binärlogs schreiben. Das Replikat liest diese Ereignisse und speichert sie in Relay-Logs, bevor es sie anwendet.

Bearbeiten Sie die MySQL-Konfigurationsdatei auf der Quelle. Übliche Speicherorte sind /etc/mysql/mysql.conf.d/mysqld.cnf, /etc/my.cnf oder eine von diesen Pfaden eingebundene Datei. Fügen Sie unter [mysqld] die folgenden Einstellungen hinzu oder überprüfen Sie sie:

[mysqld]
server-id=1
log-bin=mysql-bin
binlog_format=ROW

server-id muss in der gesamten Replikationstopologie auf jedem Server eindeutig sein. log-bin aktiviert die Binärprotokollierung. binlog_format=ROW ist eine praktische Standardeinstellung für die meisten modernen Replikations-Setups, da es Zeilenänderungen aufzeichnet, anstatt sich auf das Verhalten der erneuten Ausführung von Anweisungen zu verlassen.

Seien Sie vorsichtig mit binlog-do-db und binlog-ignore-db. Filtern klingt praktisch, kann aber überraschen, da das anweisungsbasierte Verhalten von der Standarddatenbank abhängt, die von der Sitzung ausgewählt wird. Wenn Sie eine gefilterte Replikation benötigen, entwerfen und testen Sie sie bewusst. Für ein erstes zuverlässiges Setup replizieren Sie alles.

Starten Sie MySQL auf der Quelle neu:

sudo systemctl restart mysql
# oder, auf einigen Systemen
sudo systemctl restart mysqld

Überprüfen Sie die Einstellungen:

SHOW VARIABLES LIKE 'server_id';
SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'binlog_format';

log_bin sollte ON sein. Die server_id sollte nicht null und eindeutig sein.

Erstellen Sie einen Replikationsbenutzer

Erstellen Sie auf der Quelle ein dediziertes Konto, das das Replikat verwenden wird. Beschränken Sie den Host auf die Replikatsadresse, wenn es Ihr Netzwerkdesign zulässt:

CREATE USER 'repl_user'@'192.168.1.101' IDENTIFIED BY 'use_a_real_secret_here';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'192.168.1.101';

Der Berechtigungsname lautet in MySQL-Grants immer noch REPLICATION SLAVE, auch wenn die neuere Dokumentation an anderer Stelle oft "replica" sagt.

Testen Sie die Anmeldung vom Replikats-Host aus:

mysql -h 192.168.1.100 -u repl_user -p

Wenn dies fehlschlägt, beheben Sie jetzt die Authentifizierung und das Netzwerk. Häufige Ursachen sind bind-address auf der Quelle, Firewall-Regeln, ein nicht übereinstimmender Benutzer-Host, DNS-Auflösung zu einer anderen Adresse und eine Inkompatibilität des Authentifizierungs-Plugins mit einem älteren Client.

Konfigurieren Sie den Replikats-Server

Konfigurieren Sie auf dem Replikat eine andere server-id. Relay-Logs werden normalerweise automatisch für die Replikation aktiviert, aber eine explizite Benennung kann die Vorgänge klarer machen:

[mysqld]
server-id=2
relay_log=mysql-relay-bin
read_only=ON

Für einen stärkeren Schutz sollten Sie nach der Einrichtung super_read_only=ON in Betracht ziehen. read_only hindert nicht jedes privilegierte Konto am Schreiben. super_read_only ist sicherer für Replikate, die niemals Anwendungsschreibvorgänge akzeptieren sollten, aber Sie müssen es möglicherweise vorübergehend für bestimmte administrative Aufgaben deaktivieren.

Starten Sie MySQL auf dem Replikat neu und überprüfen Sie:

sudo systemctl restart mysql

SHOW VARIABLES LIKE 'server_id';
SHOW VARIABLES LIKE 'read_only';

Erstellen Sie einen konsistenten initialen Snapshot

Das Replikat muss mit einer Datenkopie beginnen, die einer bestimmten Binärlog-Position entspricht. Wenn Snapshot und Position nicht übereinstimmen, kann die Replikation zwar starten, aber dennoch falsch sein.

Für InnoDB-lastige Datenbanken ist mysqldump --single-transaction normalerweise die einfachste konsistente Methode. Sie vermeidet eine lange globale Lesesperre für transaktionale Tabellen. Fügen Sie --source-data=2 in neueren MySQL-Versionen hinzu, damit der Dump die Binärlog-Datei und -Position der Quelle als auskommentierte Zeile aufzeichnet. Ältere Versionen verwenden --master-data=2.

Führen Sie dies auf einem vertrauenswürdigen Host aus, der eine Verbindung zur Quelle herstellen kann:

mysqldump -h 192.168.1.100 -u root -p \
  --all-databases \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --source-data=2 \
  > source_dump.sql

Wenn Ihre MySQL-Version --source-data nicht unterstützt, verwenden Sie:

mysqldump -h 192.168.1.100 -u root -p \
  --all-databases \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --master-data=2 \
  > source_dump.sql

--single-transaction ist für die InnoDB-Konsistenz sicher, macht aber nicht-transaktionale MyISAM-Tabellen nicht auf die gleiche Weise konsistent. Wenn Sie noch MyISAM-Tabellen haben, planen Sie ein Wartungsfenster oder verwenden Sie eine andere Backup-Methode, die Ihnen einen konsistenten Snapshot liefert.

Überprüfen Sie den Dump auf die aufgezeichneten Replikationskoordinaten:

grep -m 1 -E "CHANGE (MASTER|REPLICATION SOURCE)" source_dump.sql

Sie sollten eine auskommentierte Zeile mit einer Binärlog-Datei und -Position sehen. Behalten Sie sie. Sie werden sie verwenden, wenn Sie das Replikat auf die Quelle ausrichten, es sei denn, Sie entscheiden sich für eine GTID-basierte Replikation.

Importieren Sie den Snapshot auf dem Replikat

Übertragen Sie den Dump mit Ihrer üblichen sicheren Methode auf das Replikat:

scp source_dump.sql db-replica:/tmp/source_dump.sql

Stellen Sie auf dem Replikat sicher, dass Sie nicht versehentlich in einen vorhandenen produktionsähnlichen Datensatz schreiben. Wenn dieses Replikat eine saubere Kopie sein soll, löschen Sie nur das, was Ihr Migrationsplan zu ersetzen vorsieht, und erstellen Sie es neu. Importieren Sie dann:

mysql -u root -p < /tmp/source_dump.sql

Führen Sie den Import bei großen Dumps in screen oder tmux aus und beobachten Sie den Festplattenspeicher. Ein fehlgeschlagener Import, weil /var/lib/mysql oder /tmp vollgelaufen ist, kostet Zeit und kann dazu führen, dass Sie ein halb geladenes Replikat haben.

Richten Sie das Replikat auf die Quelle aus

Verwenden Sie unter MySQL 8.0.23 und neuer CHANGE REPLICATION SOURCE TO:

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='192.168.1.100',
  SOURCE_USER='repl_user',
  SOURCE_PASSWORD='use_a_real_secret_here',
  SOURCE_LOG_FILE='mysql-bin.000001',
  SOURCE_LOG_POS=1234;

Ersetzen Sie die Datei und Position durch die Werte aus dem Dump. Wenn Sie die ältere Syntax verwenden, lautet das Äquivalent:

CHANGE MASTER TO
  MASTER_HOST='192.168.1.100',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='use_a_real_secret_here',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=1234;

Wenn Ihre Umgebung GTIDs verwendet, ist die Einrichtung anders. Sie konfigurieren normalerweise den GTID-Modus auf beiden Servern, stellen einen Dump wieder her, der den GTID-Status bewahrt, und verwenden SOURCE_AUTO_POSITION=1 anstelle einer Datei und Position. Mischen Sie GTID- und Datei-Positionsanweisungen nicht leichtfertig; wählen Sie einen Ansatz und testen Sie ihn.

Eine kurze Anmerkung zur GTID-Replikation

Die GTID-Replikation ist nach der Einrichtung oft einfacher zu bedienen, da MySQL Transaktionen anhand globaler Transaktions-IDs verfolgt, anstatt dass Sie eine Binärlog-Datei und -Position manuell verwalten müssen. Sie ist besonders hilfreich bei Failover, Quellenänderungen und Replikats-Neuaufbauten.

Das bedeutet nicht, dass Sie sie mitten in einer Migration einfach einschalten sollten. Beide Server benötigen kompatible GTID-Einstellungen, und Ihr Backup- oder Dump-Prozess muss den korrekten GTID-Status bewahren. Ein gängiges Muster ist die Konfiguration:

[mysqld]
gtid_mode=ON
enforce_gtid_consistency=ON

Konfigurieren Sie dann nach der Wiederherstellung eines GTID-fähigen Dumps das Replikat mit:

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='192.168.1.100',
  SOURCE_USER='repl_user',
  SOURCE_PASSWORD='use_a_real_secret_here',
  SOURCE_AUTO_POSITION=1;

Verwenden Sie dies nur, wenn der GTID-Verlauf von Quelle und Replikat sauber und verstanden ist. Wenn Sie sich nicht sicher sind, ist die Datei-und-Positions-Replikation für ein erstes Setup einfacher zu durchschauen. Die schlechteste Wahl ist das Mischen von Beispielen beider Ansätze, bis die Replikation startet, die Transaktionshistorie aber nicht das ist, was Sie denken.

Starten Sie die Replikation:

START REPLICA;

Ältere Syntax:

START SLAVE;

Überprüfen Sie den Status:

SHOW REPLICA STATUS\G

Ältere Syntax:

SHOW SLAVE STATUS\G

Die wichtigsten Felder sind:

Feld	Gesunder Wert	Was es Ihnen sagt
Replica_IO_Running	Yes	Das Replikat kann eine Verbindung herstellen und Binärlog-Ereignisse abrufen.
Replica_SQL_Running	Yes	Das Replikat kann Relay-Log-Ereignisse anwenden.
Last_IO_Error	leer	Netzwerk-, Anmeldeinformations- oder Quelllog-Probleme werden hier angezeigt.
Last_SQL_Error	leer	Datenkonflikte und Anwendungsfehler werden hier angezeigt.
Seconds_Behind_Source	niedrig oder fallend	Ein grober Verzögerungsindikator.

Ältere Ausgaben verwenden Slave_IO_Running, Slave_SQL_Running und Seconds_Behind_Master.

Testen Sie mit einem kleinen Schreibvorgang

Erklären Sie nicht den Sieg, nachdem die Threads Yes sagen. Erstellen Sie eine kleine Testtabelle oder fügen Sie eine harmlose Zeile in ein vorhandenes Testschema auf der Quelle ein und überprüfen Sie dann, ob sie auf dem Replikat erscheint.

Beispiel auf der Quelle:

CREATE DATABASE IF NOT EXISTS repl_check;
CREATE TABLE IF NOT EXISTS repl_check.heartbeat (
  id INT PRIMARY KEY,
  checked_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
REPLACE INTO repl_check.heartbeat (id) VALUES (1);

Auf dem Replikat:

SELECT * FROM repl_check.heartbeat;

Dies fängt einfache Fehler wie das Ausrichten auf die falsche Quelle, das Herausfiltern der Datenbank oder die Verwendung veralteter Koordinaten ab.

Sichern Sie den Replikationskanal

Wenn der Replikationsverkehr ein nicht vertrauenswürdiges Netzwerk durchquert, ist TLS erforderlich. Selbst innerhalb eines privaten Netzwerks bevorzugen viele Teams jetzt verschlüsselten Datenbankverkehr, da sich Netzwerkgrenzen im Laufe der Zeit ändern.

Erstellen Sie mindestens den Replikationsbenutzer und -kanal, sodass Anmeldeinformationen nicht mit Anwendungskonten geteilt werden. Konfigurieren Sie für TLS-basierte Kanäle Zertifikate gemäß Ihrer MySQL-Version und fügen Sie dann SSL-Optionen in die Replikationsquellenkonfiguration ein. Die genauen Optionen variieren je nach Version und Zertifikatsrichtlinie, aber die Absicht ist dieselbe: Das Replikat sollte überprüfen, dass es eine Verbindung zur erwarteten Quelle herstellt, und Anmeldeinformationen sowie Zeilenänderungen während der Übertragung schützen.

Halten Sie auch die Berechtigung des Replikationsbenutzers eng. Es benötigt keine breiten DDL- oder DML-Zugriffe. Wenn jemand dieses Passwort erhält, sollte der Schaden auf das Lesen von Replikationslogs beschränkt sein, nicht auf das Schreiben von Anwendungsdaten.

Häufige Einrichtungsprobleme

Wenn Replica_IO_Running No ist, kann das Replikat keine Ereignisse abrufen. Überprüfen Sie:

• SOURCE_HOST ist korrekt.
• Die Quelle lauscht auf der erwarteten Adresse und dem erwarteten Port.
• Firewalls und Sicherheitsgruppen erlauben Verkehr.
• Der Host des Replikationsbenutzers stimmt mit der Quell-IP des Replikats überein.
• Das Passwort und das Authentifizierungs-Plugin funktionieren mit der Client-/Server-Version des Replikats.
• Die angeforderte Binärlog-Datei ist auf der Quelle noch vorhanden.

Wenn Replica_SQL_Running No ist, hat das Replikat Ereignisse abgerufen, konnte sie aber nicht anwenden. Überprüfen Sie Last_SQL_Error. Doppelte Schlüssel bedeuten oft, dass das Replikat nicht aus dem exakt passenden Snapshot initialisiert wurde oder jemand direkt in das Replikat geschrieben hat. Fehlende Zeilen bedeuten oft eine Datenabweichung. Das Überspringen einer Transaktion mit SQL_SLAVE_SKIP_COUNTER kann den Thread wieder in Gang bringen, kann aber auch das Replikat falsch machen. Verwenden Sie es nur, wenn Sie die fehlgeschlagene Transaktion verstehen und das Divergenzrisiko akzeptieren.

Wenn die Verzögerung direkt nach der Einrichtung hoch ist, lassen Sie das Replikat aufholen und beobachten Sie, ob Seconds_Behind_Source fällt. Ein großer Dump-Import, gefolgt vom Start der Replikation, kann einen Rückstand hinterlassen. Wenn die Verzögerung wächst statt schrumpft, überprüfen Sie die Festplatten-E/A auf dem Replikat und das Schreibvolumen auf der Quelle.

Halten Sie das Replikat nach der Einrichtung gesund

Konfigurieren Sie die Aufbewahrung von Binärlogs auf der Quelle, damit Replikate Wartungs- und Ausfallzeiten überstehen können. Modernes MySQL verwendet binlog_expire_logs_seconds:

[mysqld]
binlog_expire_logs_seconds=604800

Dieses Beispiel behält Logs etwa 7 Tage lang. Wählen Sie einen Wert basierend auf Ihren Wiederherstellungsanforderungen und der Festplattenkapazität. Ältere Systeme verwenden möglicherweise expire_logs_days.

Überwachen Sie den Replikationsstatus und die Verzögerung. Alarmieren Sie mindestens, wenn einer der Replikationsthreads stoppt, wenn die Verzögerung Ihre Toleranz überschreitet und wenn die Festplattennutzung der Quelle wächst, weil Binärlogs nicht gelöscht werden. Für Datenkonsistenzprüfungen verwenden viele Teams Tools von Percona Toolkit wie pt-table-checksum und pt-table-sync, aber testen Sie sie sorgfältig, bevor Sie sie auf produktionsgroßen Daten ausführen.

Halten Sie schließlich den Anwendungsverkehr vom Replikat fern, bis Sie sicher sind, dass es schreibgeschützt, auf dem neuesten Stand und überwacht ist. Ein Replikat, das versehentliche Schreibvorgänge akzeptiert, ist schlimmer als kein Replikat, da der Schaden bis zum Failover oder der Wiederherstellung verborgen bleiben kann.

Asynchrone Replikation funktioniert gut, wenn der Start-Snapshot, die Binärlog-Koordinaten, die Berechtigungen und die Überwachung alle übereinstimmen. Die meisten fehlgeschlagenen Setups sind darauf zurückzuführen, dass einer dieser Punkte angenommen statt überprüft wurde.