Convertir une base de données utf8 en utf8mb4

Une ancienne application peut exiger de convertir ses tables de utf8 en utf8mb4. Ne faites pas de remplacements globaux dans un dump et ne supprimez pas les tables de production : une substitution de texte peut modifier des données d'utilisateurs, des noms de collations non équivalents ou du contenu sérialisé.

1. Créer et vérifier une copie protégée

Travaillez depuis un dossier privé en dehors de public_html, avec un nouveau nom de dump. Arrêtez temporairement les écritures de l'application et générez un dump cohérent. -p demande le mot de passe de façon interactive et évite de le laisser dans l'historique ou dans la liste des processus :

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

Si mysqldump renvoie une erreur, ne continuez pas et ne considérez pas le fichier partiel comme valide. L'export des routines exige des privilèges ; s'il échoue, vérifiez de quels objets l'application a besoin avant de les omettre. Les événements, s'il y en a, doivent aussi être inventoriés et exportés avec les options et privilèges adéquats. La somme de contrôle détecte les modifications ultérieures du fichier, mais ne prouve pas que la copie est complète. Les permissions en lecture seule ne la rendent pas non plus immuable.

Téléchargez aussi les deux fichiers vers un stockage distinct de l'hébergement. Ne modifiez pas ce dump : ce sera le point de récupération.

2. Restaurer dans une nouvelle base

Créez une base vide, avec un autre utilisateur et un autre nom, et restaurez-y la copie :

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

Dans le clone, désactivez les e-mails, paiements, webhooks et tâches automatiques de production. Configurez un clone de l'application pour qu'il utilise BASE_NUEVA et vérifiez qu'il s'ouvre, que la connexion fonctionne et qu'il exécute ses opérations principales avant de convertir quoi que ce soit.

3. Prévisualiser et convertir le clone

Consultez d'abord les tables dont la valeur par défaut est encore utf8 ou utf8mb3 :

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

L'encodage d'une colonne peut différer de celui par défaut de sa table. Vérifiez aussi les colonnes ; une table dont la valeur par défaut est utf8mb4 pourrait encore contenir d'anciennes colonnes :

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;

Le générateur suivant est un point de départ pour les tables qu'affiche la première liste. CONVERT TO transforme toutes leurs colonnes de texte, y compris celles qui ont un encodage différent ; ne l'exécutez pas sans avoir examiné ces exceptions. S'il ne faut changer que certaines colonnes, préparez un ALTER TABLE ... MODIFY qui conserve leur définition complète.

Générez les instructions exactes dont vous auriez besoin, vérifiez-les une par une et exécutez-les uniquement sur BASE_NUEVA :

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 affiche normalement les collations de l'ancien utf8 sous le nom utf8mb3 ; le second motif maintient la compatibilité avec les versions qui affichent encore l'ancien alias. La collation choisie doit être compatible avec la version de MySQL et avec l'application. Vérifiez qu'elle existe sur la destination. Changer les règles de comparaison peut créer de nouvelles correspondances dans des index uniques ; la conversion peut aussi agrandir des types de texte ou entrer en conflit avec des clés étrangères. Vérifiez au préalable les index des colonnes de texte, car en augmentant la taille maximale par caractère, certains anciens index peuvent dépasser la limite admise.

Si vous voulez que les nouvelles tables héritent de utf8mb4, changez aussi la valeur par défaut de la base clonée :

ALTER DATABASE `BASE_NUEVA`
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Cela ne convertit pas les tables existantes. Configurez aussi utf8mb4 dans le connecteur de l'application ; changer uniquement le schéma ne garantit pas qu'elle puisse écrire des caractères de quatre octets. --single-transaction fournit un instantané cohérent pour les tables transactionnelles, mais ne protège ni les tables MyISAM ni les changements de schéma pendant le dump.

4. Valider et basculer l'application

Comparez entre la base d'origine et le clone :

• schéma, index, vues, triggers et routines ;
• nombre de lignes de chaque table ;
• échantillons avec accents, caractères de quatre octets et contenu sérialisé ;
• accès, recherches, modification et tâches planifiées de l'application.

Quand toutes les vérifications sont correctes, arrêtez de nouveau les écritures, reportez dans le clone les modifications survenues depuis le premier dump si nécessaire et changez les identifiants de l'application pour pointer vers la nouvelle base. Gardez la base d'origine intacte et sans écritures pendant la période d'observation.

Retour en arrière

Si la validation ultérieure échoue, arrêtez les écritures et identifiez les opérations reçues dans la nouvelle base. Décidez comment les conserver avant de revenir à l'originale : changer uniquement les identifiants ferait perdre ces modifications. Rétablissez ensuite les anciens identifiants et validez l'application. Ne supprimez ni la base d'origine ni le dump vérifié avant d'avoir explicitement clos la période de récupération.

Si vous avez des doutes sur la collation adéquate ou sur la façon de synchroniser les dernières écritures, contactez l'assistance avant le changement.