Lors de la création d'une nouvelle base de données, il est crucial de reproduire exactement le jeu de caractères et le collationnement de la base d'origine afin d'éviter de nombresues erreurs lors de l'import.

• Sous MySQL 5.7, le collationnement par défaut est utf8mb4_general_ci.
• Sous MySQL 8.0, le collationnement par défaut devient utf8mb4_0900_ai_ci, introduit spécifiquement par cette version. Un export depuis MySQL 8.0 vers une version 5.6 ou 5.7 provoquera une erreur de reconnaissance du jeu de caractères.
• Recommandation : pour vos projets internes, privilégiez utf8_general_ci ou utf8mb4_general_ci. La compatibilité prime sur la performance.

2. Séparer l'import de la structure et celui des données

Il est fortement conseillé d'imoprter d'abord la structure des tables, puis les données dans un second temps. L'import de la structure est rapide mais génère davantaeg d'erreurs potentielles. Cette séparation facilite grandement le débogage.

Export de la structure via mysqldump

Si la commande mysqldump n'est pas accessible directement sous Linux, naviguez vers le répertoire d'installation de MySQL :

cd /usr/local/mysql-5.7.18/bin
./mysqldump --no-data -uroot -p mabase clients commandes > structure.sql

Le paramètre --no-data limite l'export à la structure uniquement. mabase correspond au nom de la base, suivi optionnellement de noms de tables. Sans table spécifiée, toute la base est exportée.

Export des données uniquement via mysqldump

./mysqldump --no-create-info -uroot -p mabase clients commandes > donnees.sql

Le paramètre --no-create-info (-t) permet d'exporter uniquement les enregistrements sans les instructions CREATE TABLE.

3. Problèmes liés à l'encodage

• Si l'import signale un collationnement inexistant, effectuez un remplacement global dans le fichier SQL pour substituer le collationnement manquant par un existant.
• Lors d'une conversion de utf8 vers utf8mb4, la longueur maximale des caractères passe de 3 à 4 octets. Cela peut provoquer un dépassement de la taille de stockage d'un champ, générant des erreurs inattendues.

4. Ordre d'exécution des objets

• Les contraintes de clés étrangères imposent un ordre de création précis : les tables référencées doivent exister avant celles qui les référencent.
• Les outils comme Navicat placent les vues avant les procédures stockées dans le fichier généré. Or certaines vues dépendent de procédures. Solution : déplacer manuellement la création des procédures avant celle des vues.

5. Volume et performance de la migration

Lors d'une migration de données de développement, l'export d'environ 5 millions d'enregistrements a pris environ 6 minutes, et l'import environ 25 minutes. Migrer l'intégralité des données d'un coup est envisageable si le volume reste gérable.

6. Problèmes fréquents lors de l'import de structure

Incompatibilité entre versions de Navicat et gestion des partitions

Différentes versions de Navicat peuvent produire des scripts SQL légèrement différents pour une même base, entraînant des erreurs lors de l'import. Voici un exemple de table partitionnée :

DROP TABLE IF EXISTS `t_exemple`;
CREATE TABLE `t_exemple`(
    `identifiant` varchar(42) NOT NULL COMMENT 'identifiant',
    `libelle` VARCHAR(64) NOT NULL COMMENT 'libellé',
    `departement_id` VARCHAR(64) NOT NULL COMMENT 'département'
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='table test'
/*!50500 PARTITION BY LIST COLUMNS(departement_id)
(PARTITION p01 VALUES IN ('01') ENGINE=INNODB,
 PARTITION p02 VALUES IN ('02') ENGINE=INNODB,
 PARTITION p03 VALUES IN ('03') ENGINE=INNODB) */;

Erreur de privilèges SUPER sur les vues et procédures

Message d'erreur :

Access denied; you need (at least one of) the SUPER privilege(s) for this operation

Cette erreur provient de la clause DEFINER qui contient les identifiants du compte d'origine. Solution : supprimer la clause DEFINER='root'@'%' ou remplacer le nom d'utilisateur par le compte courant.

Longueur d'index excessive due à une version MySQL ancienne

Message d'erreur :

Specified key was too long; max key length is 767 bytes

Activation des paramètres InnoDB nécessaires :

SET GLOBAL innodb_large_prefix = ON;
SET GLOBAL innodb_file_format = Barracuda;
SET GLOBAL innodb_file_per_table = ON;

Vérification :

SHOW VARIABLES LIKE 'innodb_large_prefix';

7. Astuces complémentaires

• Pendant l'import/export, désactivez la mise en veille et l'écran de veille de votre machine. Évitez d'interagir avec Navicat pour prévenir tout gel du processus.
• Utilisez la fonction de comparaison de structure de Navicat pour vérifier la cohérence entre deux bases.
• Dans Notepad++, remplacez les sauts de ligne par des espaces si nécessaire pour reformater vos scripts.

Export partiel d'une table

Pour n'exporter qu'une partie des données, créez d'abord une table dérivée :

CREATE TABLE employe_extrait AS
SELECT *
FROM employe
WHERE employe.id = '1';

Il suffit ensuite d'exporter la table employe_extrait.