Résolution des Problèmes Courants de Migration MySQL et des Erreurs de Transfert de Données

Les migrations MySQL échouent de quelques manières familières. L'importation s'arrête sur une clé étrangère. Les caractères se transforment en points d'interrogation. Un dump de MySQL 5.7 ne se charge pas proprement dans MySQL 8.0. Les données se chargent, mais l'application plante parce qu'une routine stockée, un déclencheur, un utilisateur ou un mode SQL n'a pas été transféré comme prévu. Aucun de ces problèmes n'est inhabituel, mais ils sont beaucoup plus faciles à gérer lorsque vous traitez la migration comme un processus reproductible plutôt qu'une copie unique.

La meilleure habitude de migration est de répéter. Prenez une vraie sauvegarde, restaurez-la dans une cible de préproduction, exécutez les mêmes commandes d'importation que vous prévoyez d'utiliser en production et notez chaque avertissement. Une répétition vous indique si votre dump est complet, si votre configuration cible est compatible et combien de temps le chargement prend réellement. Cela vous donne également un plan de restauration plus réaliste que "nous allons trouver une solution pendant la fenêtre de maintenance".

Commencez par Identifier le Type de Panne

Lorsqu'une migration échoue, ne commencez pas à modifier les variables du serveur au hasard. Classez l'erreur dans l'un de ces compartiments :

Compatibilité : différences de version, mots réservés, fonctionnalités supprimées, valeurs par défaut modifiées.
Encodage : inadéquations de jeu de caractères et de collation.
Contraintes : clés étrangères, clés uniques, contraintes de vérification, colonnes générées.
Couverture des objets : déclencheurs, routines, événements, vues, utilisateurs ou privilèges manquants.
Performance : importation trop lente, disque plein, journaux binaires en croissance, index trop longs.
Comportement de l'application : données importées, mais les requêtes ou les écritures se comportent différemment.

Cette classification vous donne la prochaine commande à exécuter. Une erreur de clé en double et un emoji corrompu sont tous deux des "problèmes de migration", mais ils ont des causes complètement différentes.

Inadéquations de Version : MySQL 5.7 vers 8.0 et Sauts Similaires

Les mises à niveau de version majeure sont là où de nombreuses surprises apparaissent. MySQL 8.0 a modifié les valeurs par défaut, les mots réservés, le comportement d'authentification, les internes du dictionnaire de données et le comportement de l'optimiseur par rapport à 5.7. Une ancienne syntaxe fonctionne encore ; d'autres non. MariaDB ajoute une autre couche de compatibilité car ce n'est pas un remplacement direct pour chaque fonctionnalité MySQL.

Avant la migration, capturez les paramètres source :

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

Sur la cible, exécutez les mêmes vérifications et comparez. sql_mode mérite une attention particulière. Un dump qui se charge sur une source permissive peut échouer sur une cible plus stricte avec des erreurs telles que des dates invalides, des valeurs par défaut manquantes pour les colonnes NOT NULL, ou des dates zéro qui ne sont plus acceptées sous le mode cible.

Si vous rencontrez une erreur comme :

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

ne relâchez pas immédiatement sql_mode pour toujours. Inspectez d'abord la définition de la table et les données. Vous devrez peut-être corriger les valeurs par défaut, convertir les dates zéro ou mettre à jour les hypothèses de l'application. Faire correspondre temporairement le sql_mode source pendant l'importation peut vous aider à terminer une restauration par étapes, mais la production devrait évoluer vers un mode explicite connu que votre application a testé.

Les mots réservés peuvent également casser les anciens schémas. Une colonne ou une table nommée rank, groups, ou un autre mot réservé plus récent peut nécessiter des guillemets ou un renommage. Si l'erreur apparaît dans le DDL, inspectez l'instruction exacte du dump et testez une version corrigée sur la cible.

Problèmes de Plugin d'Authentification

Une migration qui inclut un basculement d'application échoue souvent avant même l'exécution d'une requête car les clients ne peuvent pas s'authentifier. MySQL 8.0 utilise couramment caching_sha2_password par défaut, tandis que les clients plus anciens peuvent s'attendre à mysql_native_password.

Vérifiez les utilisateurs cibles :

SELECT user, host, plugin FROM mysql.user;

La meilleure correction est généralement de mettre à jour la bibliothèque cliente ou le pilote. Si ce n'est pas possible avant le basculement, vous pouvez avoir besoin d'un compte de compatibilité temporaire :

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

Traitez cela comme une décision de compatibilité, pas une meilleure pratique générique. Les paramètres d'authentification affectent la sécurité, et la bonne réponse dépend de vos versions client et de votre modèle de risque.

Problèmes de Jeu de Caractères et de Collation

Les problèmes de jeu de caractères sont pénibles car l'importation peut se terminer avec succès alors que les données sont déjà endommagées. Le symptôme classique est ?, du mojibake, des caractères accentués cassés, ou des insertions échouées impliquant des emoji.

Vérifiez les définitions de la base de données source et des tables :

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

Vérifiez également les colonnes :

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;

Pour la plupart des applications modernes, utf8mb4 est le bon jeu de caractères cible car il prend en charge toute la gamme Unicode, y compris les emoji. L'ancien nom utf8 de MySQL n'est pas identique à l'UTF-8 complet dans les anciennes versions ; c'est généralement un jeu de caractères sur 3 octets.

Lors du dump et de l'importation, soyez explicite :

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

Si les données source sont en fait latin1, ne les déclarez pas aveuglément utf8mb4 en espérant. Déterminez d'abord si les octets sont valides dans l'encodage source. Certains anciens systèmes contiennent des données "doublement encodées" où la colonne prétend avoir un jeu de caractères mais l'application a stocké des octets d'un autre. Cela nécessite une conversion testée, pas une recherche et remplacement global.

Les différences de collation peuvent également modifier le comportement. L'ordre de tri, les comparaisons d'unicité et la sensibilité à la casse peuvent différer entre les collations. Si un index unique échoue pendant la migration, vérifiez si la collation cible traite deux chaînes comme égales alors que la source ne le faisait pas.

Échecs de Clés Étrangères

Les erreurs de clés étrangères signifient généralement l'une des quatre choses suivantes :

Les tables enfants ont été importées avant les tables parents.
Le dump est partiel et il manque des lignes référencées.
Les données source avaient déjà des références incohérentes.
Le schéma cible diffère du schéma source.

La solution de contournement courante pour le chargement en masse est :

SET FOREIGN_KEY_CHECKS = 0;
-- importer les données
SET FOREIGN_KEY_CHECKS = 1;

Cela peut être approprié pour une restauration logique complète à partir d'un dump fiable. Ce n'est pas un outil de nettoyage. La réactivation de FOREIGN_KEY_CHECKS ne revalide pas complètement chaque ligne existante comme beaucoup de gens le supposent, vous pouvez donc importer de mauvaises relations et ne pas vous en apercevoir avant plus tard.

Si vous fusionnez des données ou importez seulement une partie d'un schéma, gardez les vérifications activées lorsque c'est possible et chargez d'abord les tables parents. Si vous devez désactiver les vérifications, exécutez des requêtes de validation par la suite. Par exemple :

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;

Utilisez des requêtes comme celle-ci pour vos relations réelles, en particulier pour les tables à haute valeur telles que les commandes, les paiements, les comptes et les permissions.

Erreurs de Clé en Double

Une erreur de clé en double signifie que la cible a déjà une valeur que les données entrantes veulent insérer :

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

Si la cible doit être une copie exacte, la correction propre est généralement de supprimer et recréer la base de données cible, puis d'importer à nouveau. Une cible à moitié chargée n'est pas un bon point de départ pour une deuxième tentative à moins que votre processus n'ait été conçu pour reprendre.

Si vous fusionnez des données, décidez de la politique de conflit avant l'importation. INSERT IGNORE cache les doublons en sautant les lignes. REPLACE INTO supprime la ligne existante et insère la nouvelle, ce qui peut déclencher des cascades et modifier les colonnes mises à jour automatiquement. ON DUPLICATE KEY UPDATE est plus explicite, mais nécessite toujours des règles minutieuses.

Pour les migrations, je préfère les tables de staging pour les fusions. Chargez les données entrantes dans des tables staging_*, inspectez les conflits, puis écrivez des instructions INSERT ... SELECT ou UPDATE ... JOIN délibérées. C'est plus lent à concevoir, mais cela évite de jeter silencieusement des données.

Déclencheurs, Routines, Événements et Vues Manquants

Une migration peut sembler réussie car les tables et les lignes existent, alors qu'une logique importante de la base de données est manquante. Les options de mysqldump sont importantes :

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

Les vues et les routines peuvent échouer lors de l'importation à cause des comptes définisseurs. Une vue peut référencer :

DEFINER=`old_user`@`old_host`

Si ce compte n'existe pas sur la cible, l'objet peut échouer à la création ou échouer lors de son utilisation. Vous pouvez soit créer le compte définisseur nécessaire avec les privilèges appropriés, soit ajuster les définisseurs pendant un processus de migration contrôlé. Ne supprimez pas aveuglément les définisseurs sans comprendre le modèle de sécurité de l'application.

Après l'importation, comparez les comptes d'objets :

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;

Vérifiez également les événements planifiés si votre application en dépend :

SHOW EVENTS FROM appdb;

Importations Lentes et Grandes Tables

Les grandes importations sont généralement limitées par les E/S disque, la maintenance des index, la journalisation binaire, les vérifications de clés étrangères ou la taille des transactions. Avant de régler, surveillez la cible :

iostat -xz 1
df -h
top

Pour les dumps logiques, utilisez des insertions étendues. mysqldump le fait par défaut dans la plupart des cas, mais confirmez que vous n'utilisez pas --skip-extended-insert sauf si vous avez besoin de diffs lisibles par l'homme plus que de vitesse.

Pour les importations InnoDB, un innodb_buffer_pool_size plus grand peut aider si la cible a de la mémoire disponible. Ne le réglez pas si haut que le système d'exploitation commence à swapper. Pendant un chargement unique, certaines équipes assouplissent temporairement les paramètres de durabilité tels que innodb_flush_log_at_trx_commit ou désactivent la journalisation binaire pour la session d'importation. Ces choix échangent la récupération après crash ou la récupération à un point dans le temps contre la vitesse, ils ne doivent donc être utilisés que lorsque vous pouvez vous permettre de redémarrer l'importation à partir d'une sauvegarde connue.

Si la cible est également une source de réplication, soyez prudent avec les journaux binaires. La désactivation de la journalisation binaire peut accélérer l'importation, mais les réplicas en aval ne recevront pas ces modifications. Dans une topologie avec des réplicas, décidez où l'importation doit avoir lieu et comment les modifications doivent circuler avant de désactiver les journaux.

Pour les très grandes tables, envisagez des outils de sauvegarde physique ou les utilitaires de dump et de chargement de MySQL Shell au lieu d'un simple mysqldump. Les dumps logiques sont portables et faciles à inspecter, mais ils ne sont pas toujours le chemin le plus rapide pour des ensembles de données de plusieurs centaines de gigaoctets.

Pannes d'Espace Disque

Les pannes de disque pendant la migration sont courantes et évitables. Vous avez besoin d'espace pour le fichier de dump, les données importées, les index, les fichiers temporaires, les journaux binaires et parfois le double stockage pendant que les tables sont reconstruites.

Vérifiez avant l'importation :

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

À l'intérieur de MySQL, vérifiez les tailles des tables :

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;

Si l'importation échoue parce que le disque se remplit, ne supprimez pas simplement des fichiers aléatoires du répertoire de données. Libérez de l'espace en toute sécurité, inspectez si la cible est partiellement chargée et décidez s'il faut recommencer depuis le début.

Validation Post-Migration

Une migration n'est pas terminée lorsque la commande d'importation se termine. Validez le résultat.

Commencez par les comptages de lignes pour les tables importantes :

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

Les comptages de lignes seuls ne suffisent pas. Comparez les sommes ou les vérifications pour les données critiques pour l'entreprise :

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

Exécutez les mêmes requêtes sur la source et la cible pendant la dernière période calme. Pour les tables qui continuent de changer pendant la migration, utilisez un gel planifié, un rattrapage de réplication ou une réconciliation au niveau de l'application.

Testez les workflows de l'application sur la cible avant le basculement :

Connexion et création de session.
Création et mise à jour des enregistrements principaux.
Recherches et rapports qui dépendent des collations ou des index.
Tâches d'arrière-plan, déclencheurs et événements planifiés.
Vérifications des permissions et actions d'administration.

Le test de l'application est important car la base de données peut être techniquement importée mais comportementalement erronée.

Une Liste de Vérification Pratique pour le Triage de Migration

Lorsqu'une erreur de migration apparaît, utilisez cette séquence :

Sauvegardez le message d'erreur exact et l'instruction SQL en échec si disponible.
Identifiez le compartiment : compatibilité, encodage, contrainte, couverture des objets, performance ou comportement de l'application.
Comparez les versions MySQL source et cible, sql_mode, les jeux de caractères et les collations.
Pour les erreurs de contrainte, inspectez les lignes parent et enfant spécifiques ou les clés en double.
Pour les problèmes d'encodage, arrêtez l'importation jusqu'à ce que vous sachiez si les octets source sont valides et comment la connexion client les interprète.
Pour les importations lentes, vérifiez le disque, la mémoire, les journaux binaires et la maintenance des index avant de modifier des variables aléatoires.
Après une correction, réexécutez la migration en préproduction avant de l'appliquer en production.

La migration MySQL la plus fiable est celle que vous pouvez jeter et répéter. Gardez les commandes dans un script, gardez les modifications de configuration documentées et faites de la validation une partie du plan plutôt qu'un dernier regard plein d'espoir.