Schnelle Fehlerbehebung bei häufigen MySQL-Replikationsfehlern

MySQL-Replikationsfehler sind einfacher zu beheben, wenn Sie zwei Fragen trennen: Kann die Replik Ereignisse von der Quelle abrufen, und kann sie die bereits abgerufenen Ereignisse anwenden? Das sind unterschiedliche Fehler. Ein Netzwerkproblem, fehlendes Binärlog, falsches Passwort oder falsche Host-Berechtigung stoppt normalerweise den I/O-Thread. Ein doppelter Schlüssel, fehlende Zeile, DDL-Konflikt oder Datenabweichung stoppt normalerweise den SQL-Thread.

Beginnen Sie mit der Statusausgabe. Auf modernem MySQL:

SHOW REPLICA STATUS\G

Auf älteren Systemen:

SHOW SLAVE STATUS\G

Verwenden Sie den Befehl, den Ihr Server unterstützt. Neuere Ausgaben verwenden Namen wie Replica_IO_Running, Replica_SQL_Running und Seconds_Behind_Source. Ältere Ausgaben verwenden Slave_IO_Running, Slave_SQL_Running und Seconds_Behind_Master.

Die ersten nützlichen Werte sind:

Replica_IO_Running: ob die Replik verbunden ist und Quell-Binärlogs liest.
Replica_SQL_Running: ob die Replik Relay-Log-Ereignisse anwendet.
Last_IO_Errno und Last_IO_Error: warum das Abrufen fehlgeschlagen ist.
Last_SQL_Errno und Last_SQL_Error: warum das Anwenden fehlgeschlagen ist.
Relay_Master_Log_File, Exec_Master_Log_Pos oder neuere Quell-Positionsfelder: wo sich die Replik im Stream befindet.

Gehen Sie nicht direkt zu einer Lösung. Kopieren Sie zuerst die vollständige Statusausgabe in Ihre Incident-Notizen. Sobald Sie RESET REPLICA ausführen, eine Transaktion überspringen oder die Replik neu ausrichten, verschwinden einige der besten Beweise.

Wenn der I/O-Thread gestoppt ist

Wenn Replica_IO_Running No ist, liest die Replik nicht erfolgreich von der Quelle. Der SQL-Thread kann noch eine Weile ältere Relay-Log-Ereignisse anwenden, aber irgendwann werden sie ausgehen.

Häufige Ursachen sind:

Der Quell-Host oder -Port ist falsch.
Eine Firewall, Sicherheitsgruppe oder Routing-Regel blockiert die Verbindung.
Das Passwort des Replikationsbenutzers ist falsch.
Der Replikationsbenutzer ist von einem anderen Host erlaubt als dem, den die Replik tatsächlich verwendet.
Binäres Logging ist auf der Quelle deaktiviert.
Die Quelle hat die von der Replik angeforderte Binärlog-Datei gelöscht.
TLS-Einstellungen wurden geändert und die Replik kann sich nicht mehr authentifizieren.

Testen Sie vom Replik-Host aus:

mysql -h source-db.example.com -u repl_user -p

Wenn eine direkte Anmeldung fehlschlägt, wird auch die Replikation fehlschlagen. Überprüfen Sie das Konto auf der Quelle:

SHOW GRANTS FOR 'repl_user'@'replica_host_or_ip';

Das Konto benötigt die Berechtigung REPLICATION SLAVE. Der Berechtigungsname verwendet in MySQL-Grants immer noch "SLAVE".

Überprüfen Sie auch, ob binäres Logging aktiviert ist:

SHOW VARIABLES LIKE 'log_bin';
SHOW MASTER STATUS;

In neueren Versionen ist möglicherweise SHOW BINARY LOG STATUS verfügbar. Der Punkt ist derselbe: Die Quelle muss Binärlogs haben, und die angeforderte Datei muss noch existieren.

Fehler 1236: Fehlendes oder nicht lesbares Binärlog

Last_IO_Errno: 1236 ist einer der Fehler, die normalerweise bedeuten, dass die Replik nach einer Binärlog-Datei oder -Position fragt, die die Quelle nicht bereitstellen kann. Die genaue Meldung variiert. Es kann heißen, dass die erste Log-Datei nicht gefunden werden kann, ein Log-Ereignis nicht gelesen werden konnte oder die Quelle die Verbindung während des Lesens geschlossen hat.

Der häufigste operative Fall ist einfach: Die Replik war zu lange offline, und die Quelle hat die benötigten Binärlogs gelöscht.

Überprüfen Sie, welche Logs auf der Quelle verbleiben:

SHOW BINARY LOGS;

Vergleichen Sie dann diese Liste mit der im Replik-Status genannten Datei. Wenn die Replik mysql-bin.000120 benötigt und die Quelle jetzt bei mysql-bin.000140 beginnt, kann die Replik nicht von Binärlogs aufholen.

Sie haben drei realistische Optionen:

Stellen Sie die Replik aus einem frischen Backup von der Quelle wieder her oder bauen Sie sie neu auf.
Verwenden Sie eine andere Replik, die noch die benötigten Daten hat, als Klonquelle, falls Ihr Prozess dies unterstützt.
Wenn GTID verwendet wird und die fehlenden Transaktionen anderswo existieren, konfigurieren Sie von einer gültigen Quelle neu, die sie bereitstellen kann.

Raten Sie keine neuere Log-Position, nur um die Replikation zu starten. Das erstellt eine Replik mit fehlenden Transaktionen. Sie kann gesund aussehen, während sie stillschweigend falsche Daten zurückgibt.

Erhöhen Sie nach der Wiederherstellung die Aufbewahrungsdauer von Binärlogs, wenn die Festplattenkapazität dies zulässt:

[mysqld]
binlog_expire_logs_seconds=604800

Dieses Beispiel entspricht etwa 7 Tagen. Wählen Sie einen Wert basierend darauf, wie lange Replikas während Wartungsarbeiten oder Incidents offline sein können.

Wenn der SQL-Thread gestoppt ist

Wenn Replica_SQL_Running No ist, hat die Replik Ereignisse abgerufen, aber eines nicht anwenden können. Dies ist oft ein Datenkonsistenzproblem, kein Verbindungsproblem.

Lesen Sie den vollständigen Last_SQL_Error. Er sagt Ihnen normalerweise die Tabelle, den Schlüssel, die fehlgeschlagene Operation und manchmal die Quell-Log-Position. Überprüfen Sie dann die relevante Zeile sowohl auf der Quelle als auch auf der Replik, bevor Sie etwas ändern.

Für ein fehlgeschlagenes Ereignis um eine bekannte Binärlog-Position kann mysqlbinlog das Ereignis anzeigen:

mysqlbinlog --start-position=123456 --stop-position=124500 /var/lib/mysql/mysql-bin.000321

Wenn die Quell-Binärlogs nicht auf dem lokalen Host sind, verwenden Sie Remote-Optionen oder überprüfen Sie eine kopierte Log-Datei. Seien Sie vorsichtig bei zeilenbasierten Ereignissen: Sie benötigen möglicherweise Decodierungsoptionen und Tabellenmetadaten, um lesbar zu sein.

Fehler 1062: Doppelter Eintrag

Last_SQL_Errno: 1062 bedeutet, dass die Replik versucht hat, eine Zeile einzufügen oder zu aktualisieren und auf einen eindeutigen Schlüssel gestoßen ist, der bereits existiert.

Typische Ursachen sind:

Jemand hat direkt auf die Replik geschrieben.
Die Replik wurde aus dem falschen Snapshot initialisiert.
Ein vorheriger Replikationsfehler wurde übersprungen.
Auto-Increment-Einstellungen sind in einem Multi-Source- oder Active-Active-Design falsch.
Anwendungsschreibvorgänge gingen versehentlich an zwei beschreibbare Server.

Die verlockende Lösung ist:

STOP REPLICA;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START REPLICA;

Ältere Syntax verwendet STOP SLAVE und START SLAVE. Dies kann für eine wegwerfbare Berichtsreplik akzeptabel sein, nachdem Sie bestätigt haben, dass die Zeile keine Rolle spielt. Es ist gefährlich für eine Replik, die später hochgestuft werden könnte. Das Überspringen bedeutet, dass die Replik nicht mehr dieselbe Transaktionshistorie wie die Quelle hat.

Ein sichererer Prozess ist:

Identifizieren Sie die widersprüchliche Tabelle und den Schlüssel.
Vergleichen Sie die Zeile auf Quelle und Replik.
Entscheiden Sie, ob die Replik-Zeile gelöscht, aktualisiert oder die Replik neu aufgebaut werden soll.
Dokumentieren Sie die Entscheidung, da dies jetzt ein Datenkonsistenzereignis ist.

Wenn die Replik für Failover vorgesehen ist, ist ein Neubau oft sauberer als das manuelle Patchen mehrerer unbekannter Unterschiede.

Fehler 1032: Datensatz nicht gefunden

Last_SQL_Errno: 1032 bedeutet normalerweise, dass die Replik versucht hat, eine Zeile zu aktualisieren oder zu löschen, die lokal nicht existiert. Dies ist das Spiegelbild vieler doppelter Schlüsselprobleme. Die Quelle hatte eine Zeile; die Replik nicht.

Häufige Ursachen sind:

Eine Zeile wurde manuell auf der Replik gelöscht.
Eine vorherige Transaktion wurde übersprungen.
Der anfängliche Dump hat Daten verpasst.
Replikationsfilter haben frühere Schreibvorgänge ausgeschlossen.

Gehen Sie nicht davon aus, dass die fehlende Zeile harmlos ist. Wenn ein UPDATE keine Zeile finden kann, ist die Replik bereits anders als die Quelle. Vergleichen Sie Anzahlen und Beispieldaten um den betroffenen Schlüssel. Wenn die Tabelle klein ist, kann ein Tabellen-Neulad angemessen sein. Wenn sie groß oder kritisch ist, verwenden Sie ein Konsistenz-Tool oder bauen Sie die Replik neu auf.

Authentifizierungs- und Host-Berechtigungsprobleme

Ein sehr häufiger Fehler nach Passwortrotation oder Netzwerkänderungen ist ein I/O-Fehler, der wie Zugriff verweigert aussieht:

Access denied for user 'repl_user'@'10.0.2.15'

Der Host im Fehler ist der, den MySQL sieht. Er stimmt möglicherweise nicht mit dem erwarteten Hostnamen überein, insbesondere bei NAT, Proxys oder Container-Netzwerken.

Überprüfen Sie auf der Quelle die Benutzer:

SELECT user, host, plugin FROM mysql.user WHERE user = 'repl_user';

Wenn die Replik von 10.0.2.15 verbindet, kann eine Berechtigung für 'repl_user'@'replica.internal' möglicherweise nicht übereinstimmen, es sei denn, Namensauflösung und Berechtigungen sind abgestimmt. Bevorzugen Sie explizite Host-Muster, die Ihrem Netzwerkdesign entsprechen.

Wenn das Plugin abweicht, können ältere Clients gegen Konten mit neueren Authentifizierungs-Plugins fehlschlagen. Die Aktualisierung des Clients ist normalerweise besser als die Abschwächung der Authentifizierung, aber in gemischten Versionen-Umgebungen benötigen Sie möglicherweise eine geplante Kompatibilitätsänderung.

Relay-Log-Probleme

Manchmal ist die Quellverbindung in Ordnung, aber die Replik hat Relay-Log-Korruption oder lokale Festplattenprobleme. Der Fehler kann einen Relay-Log-Lesefehler, ein abgeschnittenes Ereignis oder eine Relay-Log-Position erwähnen.

Überprüfen Sie zuerst die Festplattenintegrität und den freien Speicherplatz. Eine volle Festplatte kann mehrere seltsame Replikationssymptome erzeugen:

df -h
iostat -xz 1

Wenn das Relay-Log korrupt ist, die Quelle aber noch die benötigten Binärlogs hat, können Sie oft die Relay-Logs zurücksetzen und die Replik erneut abrufen lassen. Der genaue Befehl hängt von Version und Topologie ab. Führen Sie Reset-Befehle nicht leichtfertig aus; bestätigen Sie, dass Sie die Quell-Log-Datei und -Position kennen, die bereits ausgeführt wurde.

In vielen Fällen ist diese Art von Problem ein Zeichen dafür, dass der Replik-Host ein zugrunde liegendes Speicherproblem hatte. Beheben Sie dies, bevor Sie der Replik wieder vertrauen.

Replikationsverzögerung ist nicht immer ein Fehler

Seconds_Behind_Source kann hoch sein, während beide Threads laufen. Das bedeutet, dass die Replikation lebt, aber zurückliegt. Behandeln Sie Verzögerung anders als einen gestoppten Thread.

Überprüfen Sie:

Ist die Festplatte der Replik gesättigt?
Erzeugt die Quelle einen Schreibburst?
Konkurrieren lange Lesevorgänge auf der Replik mit dem SQL-Thread?
Ist die Replik kleiner oder langsamer als die Quelle?
Hat ein Backup-Job oder Snapshot zur gleichen Zeit begonnen?

Wenn die Verzögerung fällt, holt die Replik auf. Wenn die Verzögerung wächst, entfernen Sie Last oder fügen Sie Kapazität hinzu. Ein Neustart einer zurückliegenden Replik behebt selten einen anhaltenden Ressourcen-Engpass.

Filter und Multi-Source-Replikation

Replikationsfilter können Fehler schwerer lesbar machen. Eine Replik kann absichtlich einige Datenbanken oder Tabellen ignorieren, aber die Anwendung erwartet möglicherweise dennoch, dass zugehörige Daten existieren. Wenn Sie Filter verwenden, überprüfen Sie sie, bevor Sie annehmen, dass die Replik korrupt ist:

SHOW REPLICA STATUS\G

Suchen Sie nach Feldern, die Replicate_Do_DB, Replicate_Ignore_DB, Replicate_Do_Table oder Umschreibungsregeln erwähnen. Ältere Ausgaben verwenden dieselben allgemeinen Namen unter SHOW SLAVE STATUS.

Filterung ist besonders riskant bei datenbankübergreifenden Schreibvorgängen. Wenn eine Transaktion app.orders und audit.order_events aktualisiert, aber die Replik audit herausfiltert, kann die resultierende Kopie technisch konsistent mit dem Filter sein und dennoch nutzlos für einen Workflow, der Audit-Zeilen erwartet. Anweisungsbasiertes Logging kann Datenbankfilter noch überraschender machen, da die ausgewählte Standarddatenbank beeinflussen kann, ob ein Ereignis repliziert wird.

Multi-Source-Replikation fügt eine weitere Ebene hinzu. Ein Kanal kann gesund sein, während ein anderer gestoppt ist. Überprüfen Sie in diesem Fall den Status für alle Kanäle, anstatt nur den ersten Ausgabeblock zu lesen:

SHOW REPLICA STATUS\G

Bei kanalbasierten Setups enthält die Statusausgabe einen Kanalnamen. Beheben Sie den fehlgeschlagenen Kanal, ohne gesunde Kanäle zurückzusetzen. Wenn zwei Quellen überlappende Schlüssel in dieselbe Tabelle schreiben können, sind doppelte Schlüsselfehler oft ein Designproblem und kein einmaliger Replikationsfehler.

Vermeiden Sie versteckte Datenabweichungen

Der schlimmste Replikationsfehler ist der, der Yes sagt und dennoch falsche Daten enthält. Abweichungen können nach übersprungenen Transaktionen, direkten Schreibvorgängen auf Replikas, fehlgeschlagenen Importen, schlechten Filtern oder manuellen Reparaturen auftreten.

Planen Sie für wichtige Replikas Konsistenzprüfungen. Percona Toolkits pt-table-checksum wird häufig dafür verwendet, und pt-table-sync kann helfen, Unterschiede in kontrollierten Situationen zu reparieren. Diese Tools können Last erzeugen, testen Sie sie daher zuerst und führen Sie sie mit Grenzen aus, die Ihrer Produktionsumgebung entsprechen.

Schützen Sie Replikas auch vor versehentlichen Schreibvorgängen:

[mysqld]
read_only=ON
super_read_only=ON

Verwenden Sie separate Anmeldeinformationen für Anwendungslesevorgänge. Lassen Sie Anwendungsbenutzer nicht "nur für den Fall" breite Schreibberechtigungen auf Replikas haben.

Eine schnelle Incident-Checkliste

Verwenden Sie diese Reihenfolge, wenn die Replikation ausfällt:

Speichern Sie die Ausgabe von SHOW REPLICA STATUS\G.
Überprüfen Sie, ob der I/O-Thread oder der SQL-Thread gestoppt ist.
Lesen Sie Last_IO_Error oder Last_SQL_Error; verlassen Sie sich nicht nur auf die Fehlernummer.
Überprüfen Sie das MySQL-Fehlerprotokoll auf passende Zeitstempel.
Testen Sie bei I/O-Fehlern Netzwerk, Anmeldeinformationen, Berechtigungen, TLS und Verfügbarkeit von Binärlogs.
Überprüfen Sie bei SQL-Fehlern die betroffene Zeile oder Tabelle sowohl auf der Quelle als auch auf der Replik.
Entscheiden Sie, ob Sie reparieren, mit dokumentiertem Risiko überspringen, eine Tabelle neu laden oder die Replik neu aufbauen.
Führen Sie nach der Wiederherstellung einen echten Schreibtests durch und überwachen Sie die Verzögerung.

Die meisten MySQL-Replikationsfehler werden nicht durch einen magischen Befehl gelöst. Sie werden gelöst, indem Beweise gesichert, identifiziert wird, welcher Thread fehlgeschlagen ist, und eine Lösung gewählt wird, die nicht zu einer Replik führt, die läuft, aber unzuverlässig ist.