Behebung häufiger MySQL-Migrationsprobleme und Datenübertragungsfehler

MySQL-Migrationen scheitern auf einige bekannte Arten. Der Import stoppt an einem Fremdschlüssel. Zeichen verwandeln sich in Fragezeichen. Ein Dump von MySQL 5.7 lässt sich nicht sauber in MySQL 8.0 laden. Die Daten werden geladen, aber die Anwendung bricht, weil eine gespeicherte Routine, ein Trigger, ein Benutzer oder ein SQL-Modus nicht wie erwartet übertragen wurde. Keines dieser Probleme ist ungewöhnlich, aber sie sind viel einfacher zu handhaben, wenn Sie die Migration als wiederholbaren Prozess und nicht als einmalige Kopie betrachten.

Die beste Migrationsgewohnheit ist das Proben. Nehmen Sie ein echtes Backup, stellen Sie es in einem Staging-Ziel wieder her, führen Sie dieselben Importbefehle aus, die Sie in der Produktion verwenden möchten, und notieren Sie jede Warnung. Eine Probe sagt Ihnen, ob Ihr Dump vollständig ist, ob Ihre Zielkonfiguration kompatibel ist und wie lange der Ladevorgang tatsächlich dauert. Sie gibt Ihnen auch einen Rollback-Plan, der realistischer ist als "wir werden das während des Wartungsfensters herausfinden".

Beginnen Sie mit der Identifizierung der Fehlerart

Wenn eine Migration scheitert, ändern Sie nicht wahllos Servervariablen. Ordnen Sie den Fehler einem dieser Bereiche zu:

Kompatibilität: Versionsunterschiede, reservierte Wörter, entfernte Funktionen, geänderte Standardwerte.
Kodierung: Zeichensatz- und Sortierungsinkonsistenzen.
Constraints: Fremdschlüssel, Unique Keys, Check-Constraints, generierte Spalten.
Objektabdeckung: Fehlende Trigger, Routinen, Events, Views, Benutzer oder Grants.
Leistung: Import ist zu langsam, Festplatte voll, Binärlogs wachsen, Indizes brauchen zu lange.
Anwendungsverhalten: Daten importiert, aber Abfragen oder Schreibvorgänge verhalten sich anders.

Diese Klassifizierung gibt Ihnen den nächsten Befehl, den Sie ausführen müssen. Ein Duplicate-Key-Fehler und ein korrupter Emoji sind beide "Migrationsprobleme", haben aber völlig unterschiedliche Ursachen.

Versionsunterschiede: MySQL 5.7 zu 8.0 und ähnliche Sprünge

Bei Major-Versions-Upgrades treten viele Überraschungen auf. MySQL 8.0 hat im Vergleich zu 5.7 Standardwerte, reservierte Wörter, das Authentifizierungsverhalten, das Data Dictionary und das Optimiererverhalten geändert. Manche alte Syntax funktioniert noch, manche nicht. MariaDB fügt eine weitere Kompatibilitätsschicht hinzu, da es kein Drop-in-Ersatz für jede MySQL-Funktion ist.

Vor der Migration erfassen Sie die Quell-Einstellungen:

SHOW VARIABLES LIKE 'version';
SHOW VARIABLES LIKE 'sql_mode';
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

Führen Sie auf dem Ziel dieselben Prüfungen durch und vergleichen Sie. sql_mode verdient besondere Aufmerksamkeit. Ein Dump, der auf einer permissiven Quelle geladen wird, kann auf einem strengeren Ziel mit Fehlern wie ungültigen Daten, fehlenden Standardwerten für NOT NULL-Spalten oder Nulldaten, die im Zielmodus nicht mehr akzeptiert werden, fehlschlagen.

Wenn Sie auf einen Fehler wie diesen stoßen:

ERROR 1067 (42000): Invalid default value for 'created_at'

lockern Sie nicht sofort dauerhaft sql_mode. Überprüfen Sie zuerst die Tabellendefinition und die Daten. Möglicherweise müssen Sie Standardwerte korrigieren, Nulldaten konvertieren oder Anwendungsannahmen aktualisieren. Ein vorübergehendes Anpassen des Quell-sql_mode während des Imports kann Ihnen helfen, eine stufenweise Wiederherstellung abzuschließen, aber die Produktion sollte sich in Richtung eines bekannten, expliziten Modus bewegen, gegen den Ihre Anwendung getestet wurde.

Reservierte Wörter können auch alte Schemas zerstören. Eine Spalte oder Tabelle namens rank, groups oder ein anderes neueres reserviertes Wort muss möglicherweise in Anführungszeichen gesetzt oder umbenannt werden. Wenn der Fehler in DDL auftritt, überprüfen Sie die genaue Anweisung aus dem Dump und testen Sie eine korrigierte Version auf dem Ziel.

Authentifizierungs-Plugin-Probleme

Eine Migration, die einen Anwendungswechsel beinhaltet, scheitert oft, bevor eine Abfrage ausgeführt wird, weil Clients sich nicht authentifizieren können. MySQL 8.0 verwendet standardmäßig caching_sha2_password, während ältere Clients möglicherweise mysql_native_password erwarten.

Überprüfen Sie die Zielbenutzer:

SELECT user, host, plugin FROM mysql.user;

Die bessere Lösung ist in der Regel, die Client-Bibliothek oder den Treiber zu aktualisieren. Wenn das vor dem Wechsel nicht möglich ist, benötigen Sie möglicherweise ein temporäres Kompatibilitätskonto:

ALTER USER 'app_user'@'%' IDENTIFIED WITH mysql_native_password BY 'new_secret';

Behandeln Sie dies als Kompatibilitätsentscheidung, nicht als allgemeine Best Practice. Authentifizierungseinstellungen beeinflussen die Sicherheit, und die richtige Antwort hängt von Ihren Client-Versionen und Ihrem Risikomodell ab.

Zeichensatz- und Sortierungsprobleme

Zeichensatzprobleme sind schmerzhaft, weil der Import erfolgreich abgeschlossen werden kann, während die Daten bereits beschädigt sind. Das klassische Symptom sind ?, Mojibake, defekte Akzentzeichen oder fehlgeschlagene Einfügungen mit Emojis.

Überprüfen Sie die Quell-Datenbank und Tabellendefinitionen:

SELECT schema_name, default_character_set_name, default_collation_name
FROM information_schema.SCHEMATA
WHERE schema_name = 'appdb';

Überprüfen Sie auch Spalten:

SELECT table_name, column_name, character_set_name, collation_name
FROM information_schema.COLUMNS
WHERE table_schema = 'appdb'
  AND character_set_name IS NOT NULL;

Für die meisten modernen Anwendungen ist utf8mb4 der richtige Zielzeichensatz, da er den gesamten Unicode-Bereich einschließlich Emojis unterstützt. Der ältere MySQL-Name utf8 ist in älteren Versionen nicht dasselbe wie volles UTF-8; es ist normalerweise ein 3-Byte-Zeichensatz.

Beim Dumpen und Importieren seien Sie explizit:

mysqldump --default-character-set=utf8mb4 -u user -p appdb > appdb.sql
mysql --default-character-set=utf8mb4 -u user -p appdb < appdb.sql

Wenn die Quelldaten tatsächlich latin1 sind, deklarieren Sie sie nicht blind als utf8mb4 und hoffen Sie das Beste. Stellen Sie zuerst fest, ob die Bytes in der Quellkodierung gültig sind. Manche alten Systeme enthalten "doppelt kodierte" Daten, bei denen die Spalte einen Zeichensatz angibt, die Anwendung aber Bytes aus einem anderen gespeichert hat. Das erfordert eine getestete Konvertierung, keine globale Suche und Ersetzung.

Sortierungsunterschiede können ebenfalls das Verhalten ändern. Sortierreihenfolge, Eindeutigkeitsvergleiche und Groß-/Kleinschreibung können zwischen Sortierungen variieren. Wenn ein Unique-Index während der Migration fehlschlägt, überprüfen Sie, ob die Zielsortierung zwei Zeichenfolgen als gleich behandelt, wenn die Quelle dies nicht tat.

Fremdschlüsselfehler

Fremdschlüsselfehler bedeuten normalerweise eines von vier Dingen:

Kindtabellen wurden vor Elterntabellen importiert.
Der Dump ist unvollständig und referenzierte Zeilen fehlen.
Quelldaten hatten bereits inkonsistente Referenzen.
Das Zielschema unterscheidet sich von der Quelle.

Der übliche Bulk-Load-Workaround ist:

SET FOREIGN_KEY_CHECKS = 0;
-- Daten importieren
SET FOREIGN_KEY_CHECKS = 1;

Dies kann für eine vollständige logische Wiederherstellung aus einem vertrauenswürdigen Dump geeignet sein. Es ist kein Bereinigungswerkzeug. Das erneute Aktivieren von FOREIGN_KEY_CHECKS validiert nicht jede vorhandene Zeile vollständig, wie viele Leute annehmen, sodass Sie schlechte Beziehungen importieren können und erst später bemerken.

Wenn Sie Daten zusammenführen oder nur einen Teil eines Schemas importieren, lassen Sie Prüfungen nach Möglichkeit aktiviert und laden Sie zuerst Elterntabellen. Wenn Sie Prüfungen deaktivieren müssen, führen Sie anschließend Validierungsabfragen durch. Zum Beispiel:

SELECT c.*
FROM orders c
LEFT JOIN customers p ON p.id = c.customer_id
WHERE c.customer_id IS NOT NULL
  AND p.id IS NULL
LIMIT 20;

Verwenden Sie solche Abfragen für Ihre tatsächlichen Beziehungen, insbesondere für wertvolle Tabellen wie Bestellungen, Zahlungen, Konten und Berechtigungen.

Duplicate-Key-Fehler

Ein Duplicate-Key-Fehler bedeutet, dass das Ziel bereits einen Wert hat, den die eingehenden Daten einfügen möchten:

ERROR 1062 (23000): Duplicate entry '123' for key 'PRIMARY'

Wenn das Ziel eine exakte Kopie sein soll, besteht die saubere Lösung normalerweise darin, die Zieldatenbank zu löschen und neu zu erstellen und dann erneut zu importieren. Ein halb geladenes Ziel ist kein guter Ausgangspunkt für einen zweiten Versuch, es sei denn, Ihr Prozess wurde für die Wiederaufnahme entwickelt.

Wenn Sie Daten zusammenführen, legen Sie die Konfliktstrategie vor dem Import fest. INSERT IGNORE verbirgt Duplikate, indem es Zeilen überspringt. REPLACE INTO löscht die vorhandene Zeile und fügt die neue ein, was Kaskaden auslösen und automatisch aktualisierte Spalten ändern kann. ON DUPLICATE KEY UPDATE ist expliziter, erfordert aber dennoch sorgfältige Regeln.

Für Migrationen bevorzuge ich Staging-Tabellen für Zusammenführungen. Laden Sie eingehende Daten in staging_*-Tabellen, überprüfen Sie Konflikte und schreiben Sie dann bewusste INSERT ... SELECT- oder UPDATE ... JOIN-Anweisungen. Es ist langsamer zu entwerfen, vermeidet aber das stille Verwerfen von Daten.

Fehlende Trigger, Routinen, Events und Views

Eine Migration kann erfolgreich aussehen, weil Tabellen und Zeilen vorhanden sind, während wichtige Datenbanklogik fehlt. mysqldump-Optionen sind wichtig:

mysqldump -u user -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  appdb > appdb.sql

Views und Routinen können beim Import aufgrund von Definer-Konten fehlschlagen. Eine View kann referenzieren:

DEFINER=`old_user`@`old_host`

Wenn dieses Konto auf dem Ziel nicht existiert, kann das Objekt möglicherweise nicht erstellt werden oder bei Verwendung fehlschlagen. Sie können entweder das benötigte Definer-Konto mit entsprechenden Berechtigungen erstellen oder die Definer während eines kontrollierten Migrationsprozesses anpassen. Entfernen Sie Definer nicht blind, ohne das Sicherheitsmodell der Anwendung zu verstehen.

Vergleichen Sie nach dem Import die Objektanzahlen:

SELECT ROUTINE_TYPE, COUNT(*)
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'appdb'
GROUP BY ROUTINE_TYPE;

SELECT TRIGGER_SCHEMA, COUNT(*)
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'appdb'
GROUP BY TRIGGER_SCHEMA;

Überprüfen Sie auch geplante Events, wenn Ihre Anwendung darauf angewiesen ist:

SHOW EVENTS FROM appdb;

Langsame Importe und große Tabellen

Große Importe werden normalerweise durch Festplatten-I/O, Indexwartung, Binärlogging, Fremdschlüsselprüfungen oder Transaktionsgröße begrenzt. Bevor Sie optimieren, beobachten Sie das Ziel:

iostat -xz 1
df -h
top

Verwenden Sie für logische Dumps erweiterte Einfügungen. mysqldump macht dies standardmäßig in den meisten Fällen, aber bestätigen Sie, dass Sie nicht --skip-extended-insert verwenden, es sei denn, Sie benötigen mehr menschenlesbare Diffs als Geschwindigkeit.

Für InnoDB-Importe kann ein größerer innodb_buffer_pool_size helfen, wenn das Ziel über verfügbaren Arbeitsspeicher verfügt. Stellen Sie ihn nicht so hoch ein, dass das Betriebssystem zu swappen beginnt. Während eines einmaligen Ladevorgangs lockern einige Teams vorübergehend die Haltbarkeitseinstellungen wie innodb_flush_log_at_trx_commit oder deaktivieren das Binärlogging für die Importsitzung. Diese Entscheidungen tauschen Crash-Recovery oder Point-in-Time-Recovery gegen Geschwindigkeit ein, daher sollten sie nur verwendet werden, wenn Sie es sich leisten können, den Import aus einem bekannten Backup neu zu starten.

Wenn das Ziel auch eine Replikationsquelle ist, seien Sie vorsichtig mit Binärlogs. Das Deaktivieren des Binärloggings kann den Import beschleunigen, aber nachgelagerte Replikate erhalten diese Änderungen nicht. Entscheiden Sie in einer Topologie mit Replikaten, wo der Import stattfinden soll und wie Änderungen fließen sollen, bevor Sie die Logs ausschalten.

Erwägen Sie für sehr große Tabellen physische Backup-Tools oder MySQL Shell Dump- und Lade-Dienstprogramme anstelle von einfachem mysqldump. Logische Dumps sind portabel und leicht zu überprüfen, aber sie sind nicht immer der schnellste Weg für Datensätze mit mehreren hundert Gigabyte.

Festplattenfehler

Festplattenfehler während der Migration sind häufig und vermeidbar. Sie benötigen Speicherplatz für die Dump-Datei, die importierten Daten, Indizes, temporäre Dateien, Binärlogs und manchmal doppelten Speicherplatz, während Tabellen neu aufgebaut werden.

Überprüfen Sie vor dem Import:

df -h
du -sh /var/lib/mysql

Innerhalb von MySQL überprüfen Sie die Tabellengrößen:

SELECT table_schema,
       ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS size_gb
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY size_gb DESC;

Wenn der Import aufgrund einer vollen Festplatte fehlschlägt, löschen Sie nicht einfach zufällige Dateien aus dem Datenverzeichnis. Geben Sie Speicherplatz sicher frei, überprüfen Sie, ob das Ziel teilweise geladen ist, und entscheiden Sie, ob Sie von vorne beginnen müssen.

Validierung nach der Migration

Eine Migration ist nicht abgeschlossen, wenn der Importbefehl beendet ist. Validieren Sie das Ergebnis.

Beginnen Sie mit Zeilenanzahlen für wichtige Tabellen:

SELECT COUNT(*) FROM customers;
SELECT COUNT(*) FROM orders;
SELECT COUNT(*) FROM payments;

Zeilenanzahlen allein reichen nicht aus. Vergleichen Sie Summen oder Prüfsummen für geschäftskritische Daten:

SELECT COUNT(*), SUM(total_amount), MIN(created_at), MAX(created_at)
FROM orders;

Führen Sie dieselben Abfragen auf Quelle und Ziel während der letzten ruhigen Phase aus. Verwenden Sie für Tabellen, die sich während der Migration weiter ändern, eine geplante Einfrierung, Replikationsaufholung oder Anwendungsabgleich.

Testen Sie Anwendungsworkflows vor dem Wechsel gegen das Ziel:

Login und Sitzungserstellung.
Erstellen und Aktualisieren von Kerndatensätzen.
Suchen und Berichte, die auf Sortierungen oder Indizes angewiesen sind.
Hintergrundjobs, Trigger und geplante Events.
Berechtigungsprüfungen und Admin-Aktionen.

Der Anwendungstest ist wichtig, weil die Datenbank technisch importiert, aber verhaltensmäßig falsch sein kann.

Eine praktische Migrations-Triage-Checkliste

Wenn ein Migrationsfehler auftritt, verwenden Sie diese Reihenfolge:

Speichern Sie die genaue Fehlermeldung und die fehlgeschlagene SQL-Anweisung, falls verfügbar.
Identifizieren Sie den Bereich: Kompatibilität, Kodierung, Constraint, Objektabdeckung, Leistung oder Anwendungsverhalten.
Vergleichen Sie Quell- und Ziel-MySQL-Versionen, sql_mode, Zeichensätze und Sortierungen.
Überprüfen Sie bei Constraint-Fehlern die spezifischen Eltern- und Kindzeilen oder doppelten Schlüssel.
Stoppen Sie bei Kodierungsproblemen den Import, bis Sie wissen, ob die Quellbytes gültig sind und wie die Client-Verbindung sie interpretiert.
Überprüfen Sie bei langsamen Importen Festplatte, Arbeitsspeicher, Binärlogs und Indexwartung, bevor Sie zufällige Variablen ändern.
Führen Sie die Migration nach einer Korrektur im Staging erneut durch, bevor Sie sie in der Produktion anwenden.

Die zuverlässigste MySQL-Migration ist die, die Sie verwerfen und wiederholen können. Halten Sie Befehle in einem Skript fest, dokumentieren Sie Konfigurationsänderungen und machen Sie die Validierung zu einem Teil des Plans, nicht zu einem hoffnungsvollen letzten Blick.