Datenbank von utf8 nach utf8mb4 konvertieren

Eine ältere Anwendung kann verlangen, ihre Tabellen von utf8 nach utf8mb4 zu konvertieren. Führen Sie keine globalen Ersetzungen in einem Dump durch und löschen Sie keine Produktionstabellen: Eine Textersetzung kann Benutzerdaten, Namen nicht gleichwertiger Collations oder serialisierte Inhalte verändern.

1. Eine geschützte Kopie erstellen und prüfen

Arbeiten Sie in einem privaten Ordner außerhalb von public_html und mit einem neuen Dump-Namen. Halten Sie die Schreibvorgänge der Anwendung vorübergehend an und erstellen Sie einen konsistenten Dump. -p fragt das Passwort interaktiv ab, sodass es weder im Verlauf noch in der Prozessliste erscheint:

umask 077
mysqldump --single-transaction --routines --triggers -u USUARIO_MYSQL -p BASE_DE_DATOS > base-antes-utf8mb4.sql &&
test -s base-antes-utf8mb4.sql &&
sha256sum base-antes-utf8mb4.sql > base-antes-utf8mb4.sql.sha256 &&
chmod 400 base-antes-utf8mb4.sql base-antes-utf8mb4.sql.sha256 &&
sha256sum -c base-antes-utf8mb4.sql.sha256

Gibt mysqldump einen Fehler zurück, machen Sie nicht weiter und betrachten Sie die unvollständige Datei nicht als gültig. Der Export von Routinen erfordert Berechtigungen; schlägt er fehl, prüfen Sie, welche Objekte die Anwendung braucht, bevor Sie sie weglassen. Auch Events, falls vorhanden, müssen erfasst und mit den passenden Optionen und Berechtigungen exportiert werden. Die Prüfsumme erkennt spätere Änderungen an der Datei, beweist aber nicht, dass die Kopie vollständig ist. Auch reine Leseberechtigungen machen sie nicht unveränderlich.

Laden Sie beide Dateien zusätzlich auf einen Speicher außerhalb des Hostings herunter. Bearbeiten Sie diesen Dump nicht: Er ist Ihr Wiederherstellungspunkt.

2. In einer neuen Datenbank wiederherstellen

Legen Sie eine leere Datenbank mit anderem Benutzer und Namen an und stellen Sie die Kopie dort wieder her:

mysql -u USUARIO_NUEVO -p BASE_NUEVA < base-antes-utf8mb4.sql

Deaktivieren Sie im Klon die E-Mails, Zahlungen, Webhooks und automatischen Aufgaben der Produktion. Richten Sie einen Klon der Anwendung ein, der BASE_NUEVA verwendet, und prüfen Sie, ob er sich öffnet, die Anmeldung funktioniert und die wichtigsten Vorgänge laufen, bevor Sie etwas konvertieren.

3. Den Klon prüfen und konvertieren

Fragen Sie zuerst die Tabellen ab, deren Standardwert noch utf8 oder utf8mb3 ist:

SELECT TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'BASE_NUEVA'
  AND (
      TABLE_COLLATION LIKE 'utf8mb3\_%'
      OR TABLE_COLLATION LIKE 'utf8\_%'
  );

Die Kodierung einer Spalte kann von der Standardkodierung ihrer Tabelle abweichen. Prüfen Sie auch die Spalten; eine Tabelle mit Standardwert utf8mb4 könnte noch alte Spalten enthalten:

SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'BASE_NUEVA'
  AND CHARACTER_SET_NAME IS NOT NULL
ORDER BY TABLE_NAME, ORDINAL_POSITION;

Der folgende Generator ist ein Ausgangspunkt für die Tabellen aus der ersten Liste. CONVERT TO wandelt alle Textspalten um, auch solche mit abweichender Kodierung; führen Sie es nicht aus, ohne diese Ausnahmen zu prüfen. Müssen nur einige Spalten geändert werden, bereiten Sie ein ALTER TABLE ... MODIFY vor, das ihre vollständige Definition beibehält.

Erzeugen Sie die genauen Anweisungen, die Sie brauchen würden, prüfen Sie sie einzeln und führen Sie sie nur auf BASE_NUEVA aus:

SELECT CONCAT(
    'ALTER TABLE `', REPLACE(TABLE_SCHEMA, '`', '``'),
    '`.`', REPLACE(TABLE_NAME, '`', '``'),
    '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'
) AS sentencia_a_revisar
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'BASE_NUEVA'
  AND TABLE_TYPE = 'BASE TABLE'
  AND (
      TABLE_COLLATION LIKE 'utf8mb3\_%'
      OR TABLE_COLLATION LIKE 'utf8\_%'
  );

MySQL 8 zeigt die Collations des alten utf8 normalerweise unter dem Namen utf8mb3; das zweite Muster sorgt für Kompatibilität mit Versionen, die noch den früheren Alias anzeigen. Die konkrete Collation muss mit der MySQL-Version und der Anwendung kompatibel sein. Prüfen Sie, ob die gewählte Collation im Ziel existiert. Geänderte Vergleichsregeln können in eindeutigen Indizes neue Übereinstimmungen erzeugen; die Konvertierung kann außerdem Texttypen vergrößern oder mit Fremdschlüsseln kollidieren. Prüfen Sie vorher die Indizes auf Textspalten, denn mit der größeren maximalen Länge pro Zeichen können manche alten Indizes das zulässige Limit überschreiten.

Sollen neue Tabellen utf8mb4 erben, ändern Sie außerdem den Standardwert der geklonten Datenbank:

ALTER DATABASE `BASE_NUEVA`
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Das konvertiert die vorhandenen Tabellen nicht. Konfigurieren Sie auch im Connector der Anwendung utf8mb4; nur das Schema zu ändern stellt nicht sicher, dass sie Vier-Byte-Zeichen schreiben kann. --single-transaction liefert einen konsistenten Snapshot für transaktionale Tabellen, schützt aber weder MyISAM-Tabellen noch Schemaänderungen während des Dumps.

4. Prüfen und die Anwendung umstellen

Vergleichen Sie zwischen Originaldatenbank und Klon:

• Schema, Indizes, Views, Trigger und Routinen;
• Zeilenanzahl jeder Tabelle;
• Stichproben mit Akzenten, Vier-Byte-Zeichen und serialisierten Inhalten;
• Anmeldung, Suche, Bearbeitung und geplante Aufgaben der Anwendung.

Wenn alle Prüfungen erfolgreich sind, halten Sie die Schreibvorgänge erneut an, übertragen Sie bei Bedarf die seit dem ersten Dump entstandenen Änderungen in den Klon und stellen Sie die Zugangsdaten der Anwendung auf die neue Datenbank um. Lassen Sie die Originaldatenbank während des Beobachtungszeitraums unverändert und ohne Schreibvorgänge.

Zurücksetzen

Schlägt die spätere Prüfung fehl, halten Sie die Schreibvorgänge an und ermitteln Sie die Vorgänge, die in der neuen Datenbank eingegangen sind. Legen Sie fest, wie Sie sie sichern, bevor Sie zum Original zurückkehren: Nur die Zugangsdaten umzustellen würde diese Änderungen verlieren. Stellen Sie danach die vorherigen Zugangsdaten wieder her und prüfen Sie die Anwendung. Löschen Sie weder die Originaldatenbank noch den geprüften Dump, bevor Sie den Wiederherstellungszeitraum ausdrücklich abgeschlossen haben.

Wenn Sie unsicher sind, welche Collation passt oder wie Sie die letzten Schreibvorgänge synchronisieren, wenden Sie sich vor der Umstellung an den Support.