Comment convertir les tables MyISAM vers INNODB d’une base MySQL ?

De façon curieuse, il existe plusieurs moteurs de stockage de disponible pour les bases de données du SGBDR MySQL. Le format installé par défaut jusqu’à la version 5.5 était le moteur MyISAM. Il est désormais remplacé par le moteur INNODB.

Jusqu’à présent, le choix du moteur et ce qui se passait sous le capot n’était pas mon souci. Sauf, que le moteur MyISAM est devenu obsolète et donc un facteur de risque pour les bases de données de mes sites web. Il est devenu important de migrer au plus vite.

Les avantages du moteur MyISAM :

• Rapide en lecture de table,
• Il occupe moins de place que le format INNODB (environ -40 %),
• Il dispose de l’indexation en plein texte qui permet des recherches précises et performantes sur des colonnes de type texte par des mots-clés.

Les avantages du moteur INNODB :

• Moteur de bases de données relationnelles et transactionnelles,
• Plus sécurisé et beaucoup moins sujet à la corruption des données.

De plus comme indiqué en introduction, le moteur MyISAM  n’est plus installé par défaut sur les tables wordpress. Il est donc condamné à disparaitre au profit d’INNODB.

Comment savoir quel moteur est utilisé par les tables de votre base de données?

Dans une base de données MySQL (ou son fork MariaDB), plusieurs moteurs peuvent cohabiter au sein de la même base. Certaines de vos tables utiliseront le moteur MyISAM, d’autres INNODB (ou un autre moteur).

Pour visualiser ses tables, le plus simple est d’utiliser l’outil PhpMyAdmin. Tout bon hébergeur y donne accès. Démarrez le programme et après avoir sélectionné votre base, vous pouvez visualiser la liste des tables :

Dans la colonne « Type », vous avez l’information sur le moteur utilisé. Dans l’exemple, ci-dessus, vous constatez que seules les tables natives de Wordpress sont au format MyISAM. Les autres tables associées à des plugins que j’utilise utilisent déjà le moteur INNODB.

Quelles précautions à prendre avant de changer de format ?

Une seule, faire une copie de sa base données. Pour cela utiliser « PhpMyAdmin », aller dans le menu « Exporter », puis cliquer sur le bouton « Exécuter » :

Par défaut, vous sauvegarder un fichier en local, sur votre ordinateur. Chez Gandi, il est possible de faire une copie sur le serveur. Cette dernière option est plus rapide aussi bien pour la sauvegarde que pour une éventuelle restauration.

Comment migrer les tables MyISAM  en INNODB ?

Pour cela nous allons utiliser quelques commandes SQL qui permettent d’afficher l’ensemble des tables au format MyISAM et les instructions à exécuter. Il n’y a alors en principe plus qu’à faire un simple « Copier & coller » du résultat de la requête pour effectuer la migration.

Lister toutes les tables utilisant le moteur MyISAM :

SELECT CONCAT(‘ALTER TABLE ‘,table_schema,’.’,table_name,’ ENGINE=InnoDB;’)

FROM information_schema.tables

WHERE 1=1

AND engine = ‘MyISAM’

AND table_schema NOT IN (‘information_schema’, ‘mysql’, ‘performance_schema’);

Lister les tables utilisant le moteur MyISAM d’une base de données nommée :

L’inconvénient de l’instruction SQL précédente est que vous obtenez toutes les tables de l’ensemble des bases. Je préfère cibler une base en particulier avec l’instruction suivante :

SELECT CONCAT(‘ALTER TABLE ‘,table_schema,’.’,table_name,’ ENGINE=InnoDB;’)

FROM information_schema.tables

WHERE 1=1

AND engine = ‘MyISAM’

AND TABLE_SCHEMA = ‘your_database_name’

AND table_schema NOT IN (‘information_schema’, ‘mysql’, ‘performance_schema’);

Lancer la conversion :

Il faut recopier le résultat de la requête qui se présente de la façon suivante :

ALTER TABLE Nom_de_la_base.wp_Nom_de_la_basecommentmeta ENGINE=InnoDB;

ALTER TABLE Nom_de_la_base.wp_Nom_de_la_basecomments ENGINE=InnoDB;

ALTER TABLE Nom_de_la_base.wp_Nom_de_la_baselinks ENGINE=InnoDB;

ALTER TABLE Nom_de_la_base.wp_Nom_de_la_baseoptions ENGINE=InnoDB;

ALTER TABLE Nom_de_la_base.wp_Nom_de_la_basepopularpostsdatacache ENGINE=InnoDB;

Attention, il est possible que les lignes de commande n’apparaissent pas entièrement. Il vous faut cliquer sur le bouton « Option » et demander l’affichage des résultats complets et re-exécuter la requête.

Lancer la conversion INODB sur les tables de Wordpress :

Si comme moi, vous avez que les tables natives de wordpress qui ont conservées le moteur MyISAM. Vous pouvez lancer directement l’instruction suivante :

ALTER TABLE wp_commentmeta ENGINE=InnoDB;

ALTER TABLE wp_comments ENGINE=InnoDB;

ALTER TABLE wp_links ENGINE=InnoDB;

ALTER TABLE wp_options ENGINE=InnoDB;

ALTER TABLE wp_postmeta ENGINE=InnoDB;

ALTER TABLE wp_posts ENGINE=InnoDB;

ALTER TABLE wp_term_relationships ENGINE=InnoDB;

ALTER TABLE wp_term_taxonomy ENGINE=InnoDB;

ALTER TABLE wp_terms ENGINE=InnoDB;

ALTER TABLE wp_usermeta ENGINE=InnoDB;

ALTER TABLE wp_users ENGINE=InnoDB;

N’oubliez pas de lancer une requête globale pour vérifier qu’il n’existe pas encore une table dans vos bases qui soit restée au format MyISAM.

Tests de vitesse :

J’avais quelques requêtes identifiées comme lentes dans ma base de données. J’ai comparé les vitesses d’exécution dans les deux formats via la fenêtre SQL de PhpMyAdmin :

Alors même que les requêtes  de lecture d’une base sont en principe à l’avantage du moteur MyISAM, INNODB obtient de meilleurs scores avec des temps d’exécution qui divisés par deux.

Les problèmes rencontrés lors de la migration :

Aucune table ne s’affiche dans le résultat de la requête de sélection des tables MyISAM

Il s’agit vraisemblablement d’une erreur sur le nom de la base dans la requête. Vérifier l’orthographe et respecter la casse (Majuscule / minuscule)

Fichier log de taille insuffisante :

Message d’erreur lors de la conversion de la table wp_options :

#1118 – The size of BLOB/TEXT data inserted in one transaction is greater than 10% of redo log size. Increase the redo log size using innodb_log_file_size.

Solution trouvée :

J’avais deux possibilités pour résoudre le problème :

• Augmenter la taille des logs en suivant la procédure décrite ici : Changer la taille de innodb_log_file_size mais opération compliquée à réaliser avec mon hébergement et mon site en production,
• Diminuer la taille de la table wp_options.

J’ai préféré l’option nettoyage de la table wp_options qui est passée 5 Mo à 1,5 Mo par un nettoyage des données présentes et inutiles.

Problème de champ date invalide :

Message d’erreur :

#1067 – Valeur par défaut invalide pour ‘comment_date’

Solution trouvée :

La correction consiste à ajouter devant les instructions « ALTER TABLE », l’instruction suivante :

SET SQL_MODE = « NO_AUTO_VALUE_ON_ZERO »;

Conclusions et tests de performances :

Avec plus de vingt bases à migrer, l’opération a été fastidieuse mais s’est bien déroulé. Je n’ai constaté aucune perte de données une fois la migration réalisée. Ce qui est l’essentiel.

La taille de la base est passée de 167 Mo à 235 Mo. Concernant les performances, rien de flagrant jusqu’à présent aussi bien dans l’exécution du site web que des requêtes à partir du back office de Wordpress.

En résumé :

Les différentes étapes pour une migration réussie de MyISAM vers INNODB :

1. Sauvegarder sa base de données,
2. Visualiser les tables à migrer de la base et les instructions SQL,
3. Lancer la commande de migration avec un « Copier & Coller » des instructions,
4. Vérifier et tester sa base de données et son site web à la recherche d’anomalies,
5. Partir en vacances :).