Comment convertir les Tables MySQL MyISAM en InnoDB sans phpMyAdmin ?
Comme vous le savez, pour pouvoir afficher vos contenus et vos données aux utilisateurs, votre application Web est connectée à une Base de données MySQL. Mais saviez-vous qu’il existe une infinité de configurations possibles pour MySQL. En effet, de nombreux paramètres peuvent être personnalisés en vue de modifier le fonctionnement interne du système de gestion de base de données.
Quelques exemples de configurations MySQL différentes :
- Le système MariaDB 10.6 vs MySQL 8
- Les charsets (collations) Latin1 vs UTF8
Ou d’autres paramètres tels que :
- Le type de moteur de stockage InnoDB vs MyISAM
Très bien, mais quelles sont les différences entre ces 2 célèbres Storage Engine ? Pour quelles raisons convertir ses Tables MySQL depuis MyISAM vers InnoDB et comment procéder ? C’est ce que nous allons voir dans la suite de ce billet.
MyISAM vs InnoDB
Pour vous aider à déterminer quel moteur, InnoDB ou MyISAM, est le plus optimal pour votre application Web, ci-dessous, retrouvez un comparatif de Fonctionnement et de Performance des deux Storage Engine MySQL.
Différences entre les moteurs MySQL : InnoDB vs MyISAM
Ce n’est pas parce que InnoDB est plus récent, et qu’il est celui choisi “par défaut” par MySQL (Oracle) et MariaDB, il n’est pas forcément meilleur que MyISAM. Pour faire le choix qui sera adapté à votre projet, il convient de vérifier d’abord les différentes caractéristiques de chaque moteur MySQL.
Voici donc les différences principales d’InnoDB par rapport au moteur MySQL original : MyISAM !
- InnoDB est un moteur relationnel.
Nativement, il comprend et gère les Relations MySQL, ce qui lui permet d’interrompre l’écriture de données MySQL (en cours de route, si nécessaire ou en cas de crash du serveur), sans “corrompre” la base de données, et ce, grâce à un processus complémentaire de Vérification des cohérences des Relations MySQL. En effet, par exemple dans phpMyAdmin, vous constaterez que l’option Repair Table (pour réparer une table MySQL corrompue) est disponible pour MyISAM, mais pas pour InnoDB. - InnoDB gère les sessions multiples.
MyISAM et InnoDB gèrent différemment le verrouillage des Tables, lors des tentatives d’écriture par des utilisateurs multiples. Sur MyISAM, seul un utilisateur à la fois peut modifier la table. C’est plus simple (donc plus rapide) pour les bases de données avec une majorité de requêtes Lecture et moins d’Écriture, ou carrément pour les bases en lecture seule à 100%, car dans ce cas, MyISAM consommera moins de mémoire RAM qu’InnoDB.
InnoDB, quant à lui, utilise un verrouillage au niveau des Entrées MySQL et non au niveau de la Table MySQL dans son ensemble. InnoDB dispose d’un mécanisme intelligent qui empêche 2 utilisateurs de modifier les mêmes données en même temps. Dans ce cas, il rend donc moins probable l’obtention d’un message d’erreur de type « La Table est occupée ». InnoDB est donc particulièrement utile pour les bases avec de multiples utilisateurs, mais il a comme inconvénient qu’il consomme plus de mémoire RAM, puisque son mécanisme effectue plus de contrôles. Cependant, si vous avez choisi un Hébergeur Web de qualité, consommer plus de mémoire RAM ne signifie pas que votre application Web sera plus lente. Il s’agit simplement du serveur Web qui aura besoin de mémoriser plus d’informations à court terme pour bien fonctionner. - InnoDB prend en compte les Clés étrangères.
Les tables MyISAM ne disposent pas de la fonction Clés étrangères permettant de forcer l’utilisation des relations entre les tables MySQL, alors que les tables InnoDB en ont une. Ce dernier dispose de clés étrangères MySQL qui lient les données entre elles de façon cohérente, et stricte. InnoDB s’assure donc qu’il n’est pas possible pour un utilisateur MySQL d’enregistrer des données qui “briseraient les liens” et la “cohérence” entre les Tables MySQL. - InnoDB améliore la fiabilité de l’intégrité des données.
MyISAM n’est pas la solution la plus fiable pour l’intégrité des données, car il n’utilise pas de journal transactionnel qui mémoriserait chaque opération, comme le fait déjà InnoDB. En cas d’erreur ou d’interruption inopinée, InnoDB peut récupérer les données perdues via ses journaux, en vue de reconstruire l’intégrité de la Table MySQL.
De son côté, MyISAM ne disposant pas d’un tel système sécurisant l’intégrité des données, une défaillance matérielle, un crash MySQL ou une opération annulée peuvent entraîner la corruption des données. Il sera alors nécessaire d’effectuer manuellement un Repair Table via phpMyAdmin par exemple.
Dans le pire des cas avec une corruption de base MyISAM, il sera nécessaire de rapatrier une sauvegarde antérieure. Si aucun backup n’est présent, les données peuvent être définitivement perdues sans restauration possible.
Assurez-vous de choisir un Hébergeur Web qui réalise des backups réguliers de vos données.
Vous souhaitez utiliser MyISAM en toute sécurité ? Chez EasyHoster est inclus un Auto Repair cPanel qui travaille pour vous et répare automatiquement vos Tables MyISAM corrompues. Contactez-nous pour plus d’infos. - InnoDB occupe plus d’espace sur le périphérique de stockage.
Les mécanismes complémentaires présents dans InnoDB prennent de la place sur le disque. Il n’est donc pas rare de voir une base de données MyISAM dont la taille est doublée après sa conversion depuis MyISAM vers InnoDB.
Comme vous l’avez compris, InnoDB est plus perfectionné et plus fiable, mais MyISAM se montrera plus léger pour certains projets. D’ailleurs, voyons tout de suite les différences entre InnoDB et MyISAM au niveau de leur performance.
Performances MySQL & MariaDB : MyISAM vs InnoDB
La différence principale entre ces 2 moteurs est que MyISAM sera plus rapide en lecture sur les tables MySQL de taille réduite. En revanche, les performances d’InnoDB seront meilleures que celles de MyISAM pour les grands volumes de données. C’est aussi le cas pour les applications Web et les sites qui effectuent des opérations régulières en base de données, ce qui peut être le cas d’un site WordPress “moyen”, en fonction des plugins installés.
Donc, si votre application repose sur des tables contenant beaucoup de données statiques (qui ne changent pas souvent), MyISAM pourrait vous offrir de meilleures performances qu’InnoDB. En effet, puisque MyISAM bloque toute la table MySQL lorsqu’une requête est en cours d’écriture, ce dernier est plus rapide pour récupérer les données des sites “non dynamiques” (nous pourrions presque parler de sites Web en “lecture seule”).
MyISAM conviendra bien, par exemple, pour un site vitrine WordPress avec un plugin de cache activé, et les CRONs de WordPress optimisées par cPanel.
De son côté, InnoDB offre une vitesse d’écriture des données plus rapide que la lecture. Ce moteur pourra donc mieux gérer de nombreuses requêtes envoyées dans les deux sens (lecture/écriture). Il sera meilleur pour les applications où les données changent fréquemment, en particulier pour les larges tables. En revanche, son fonctionnement plus complexe consomme plus d’espace disque et de Mémoire RAM. Cette dernière peut parfois être très limitée chez les hébergeurs bon marché.
InnoDB conviendra bien, par exemple, pour un forum de discussion où les membres échangent beaucoup de messages.
Faut-il utiliser InnoDB avec un CMS comme WordPress ou Prestashop ?
Bien sûr, dans la plupart des cas, vous devriez utiliser InnoDB lorsque vous utilisez WordPress, Prestashop, Joomla, Magento ou un autre CMS de ce type. Ne fut-ce que pour sa fonctionnalité d’Anti Corruption des données (et même si nous disposons de l’Auto Repair sur MyISAM, qui fonctionne pour ~ 90% des corruptions), InnoDB reste notre moteur MySQL favori !
De plus, le mécanisme d’InnoDB semble offrir une meilleure stabilité aux sites réalisés avec des CMS malgré tout assez complexes comme WordPress, Prestashop, Joomla et Magento.
D’après nos observations, InnoDB conviendra mieux aux sites WordPress un peu complexes, avec plus de ~ 20 plugins installés.
En effet, sur ce type de sites, de nombreuses requêtes MySQL sont effectuées dans les coulisses, en particulier lorsqu’un administrateur WordPress navigue sur son Tableau de bord (Heartbeat, WP Cron…).
InnoDB gère mieux les nombreuses requêtes simultanées.
Quelques questions fréquentes sur WordPress, MyISAM et InnoDB
Faut-il éviter MyISAM pour un site WordPress?
Non, pas nécessairement.
Tout d’abord, peut-être que vous avez une base de données de très petite taille (~ moins de 200 Mo).
Si vous avez de bonnes raisons de penser que votre site WordPress échange très peu de requêtes MySQL, en Front End (zone publique) comme en Back End (interface d’administration), car vous utilisez très peu de “plugins gourmands”, alors vous pourriez considérer de choisir MyISAM pour encore plus de performances.
Cependant, si vous choisissez MyISAM, n’oubliez pas de monitorer le bon fonctionnement du service MySQL de votre application Web pour pouvoir être averti immédiatement de toute corruption sur votre base de données si cela devait arriver.
N’hésitez pas à contacter le support technique EasyHoster pour toute question sur InnoDB vs MyISAM, ainsi que nos mesures de sécurité pour l’hébergement de données.
Les Tables MyISAM et InnoDB peuvent-elles coexister dans une même base de données MySQL ?
Souvent sur WordPress, les Tables qui sont configurées sur MyISAM sont plus anciennes que les Tables InnoDB. Les Tables MyISAM sont souvent issues d’installations de WordPress et de plugins WordPress antérieurs à ~ 2011.
Mais ce n’est pas toujours le cas…
MyISAM n’est pas forcément un “vieux” système
Ne nous faites pas dire ce que nous n’avons pas dit. Vous pouvez avoir installé WordPress il y a quelques jours et tout de même disposer de plusieurs tables MyISAM.
Par exemple, cela peut arriver lorsque certains plugins WordPress forcent le moteur sur MyISAM pendant leur installation. Peut-être que les développeurs du plugin ont jugé que leurs données étaient plutôt statiques ?
Il est aussi possible que votre hébergeur ait choisi de forcer le moteur par défaut sur MyISAM pour économiser de la mémoire RAM ou de l’espace disque. N’hésitez pas à vérifier avec lui quel est son Default Storage Engine.
Chez EasyHoster, nous préférons éviter d’écraser le comportement par défaut ayant été défini par MySQL (Oracle) et MariaDB, donc nos serveurs MySQL sont bien configurés sur InnoDB comme moteur de stockage par défaut 😉 Donc, par défaut, procéder à l’installation automatique de WordPress sur votre Hébergement EasyHoster vous permettra d’obtenir 100% de Tables InnoDB.
Dans la version 5.5.5 de MySQL ayant été déployée le 6 juillet 2010, le moteur de stockage par défaut est passé sur InnoDB, au lieu de MyISAM.
Sur la capture d’écran ci-dessous, vous pouvez observer à quoi ressemble la base de données d’un site WordPress fraîchement installé chez EasyHoster, sur un système à jour en 2022 (MariaDB 10.5).
Convertir MyISAM en InnoDB
Puisque vous avez lu attentivement le début de ce billet, vous avez pu faire un choix éclairé sur le bon moteur de stockage MySQL à utiliser, et votre choix s’est probablement tourné vers InnoDB, puisqu’il est le choix à privilégier dans la majorité des situations.
Il est donc venu le temps d’apprendre comment convertir des Tables MySQL MyISAM en InnoDB. Pour cela, il existe une méthode manuelle, que vous pouvez suivre en cliquant dans votre interface phpMyAdmin ; ou une méthode plus rapide, destinée aux utilisateurs expérimentés, car elle est basée sur quelques lignes de commande à exécuter dans votre Terminal cPanel ou votre Shell SSH.
Penchons-nous tout de suite sur ces 2 méthodes de conversion MyISAM vers InnoDB.
- Conversion manuelle des tables MySQL (une par une) via phpMyAdmin (pour les débutants)
- Conversion rapide des tables MyISAM en InnoDB, sans phpMyAdmin (pour les experts)
1. Convertir manuellement une table MySQL avec phpMyAdmin
Dans cette méthode, vous allez apprendre à convertir vos Tables MySQL, les unes après les autres, manuellement via votre interface phpMyAdmin. Cette méthode est recommandée si vous ne maîtrisez pas le Shell et les commandes Linux.
Pour commencer, rendez-vous dans votre compte cPanel > phpMyAdmin et sélectionnez votre base de données.
Sous la colonne Type de phpMyAdmin, vous pourrez facilement identifier quelles Tables MySQL sont configurées sur InnoDB ou MyISAM.
N’oubliez pas qu’il est impératif de créer une sauvegarde de votre base de données MySQL avant toute opération sensible comme la conversion de MyISAM vers InnoDB. Pour apprendre à créer de bons backups de vos bases de données, n’hésitez pas à consulter ce billet complémentaire :
Une fois connecté à votre base de données sur phpMyAdmin, cliquez sur le nom d’une table, par exemple wp_posts.
Ensuite, rendez-vous sous l’onglet Opérations > Storage Engine > Sélectionnez InnoDB > Go !
Vous devriez ensuite recevoir le message “Your SQL query has been executed successfully”, qui signifie que vous pouvez passer à la table suivante.
Par contre, il existe des sites WordPress complexes, où beaucoup de plugins ont été installés et qui peuvent contenir près d’une centaine de Tables MySQL !
Il est assez déconseillé de réaliser une si longue opération manuellement, car l’erreur humaine est très risquée lorsqu’il y a beaucoup de clics à effectuer sur une interface Web.
Client EasyHoster ? Si vous ne disposez pas des compétences techniques requises pour convertir les Tables MySQL de votre WordPress en ligne de commande, n’hésitez pas à contacter notre support technique afin de bénéficier de plus d’assistance.
2. Convertir les tables MyISAM en InnoDB, facilement et rapidement sans phpMyAdmin
Dans cet exemple, nous allons utiliser le Terminal de cPanel pour plus de simplicité, mais vous pouvez également réaliser ces opérations en vous connectant au Shell en SSH.
TERMINAL cPanel
Commencez par vous connecter à votre compte cPanel > section Terminal. Vous verrez ensuite une invite de commande comme celle-ci.
EXPORT du Fichier .sql
Nous commençons par réaliser un Dump MySQL grâce à la commande mysqldump, c’est-à-dire un Export de la base dans le répertoire racine du compte cPanel.
mysqldump -h localhost -u $USER -p $DB > mysql-table.sqlRemplacez $USER par le Nom d’utilisateur MySQL associé à la base de données, et $DB par le Nom de votre base de données.
Pour plus de simplicité, vous pouvez conserver le nom de fichier mysql-table.sql.
Sur un Hébergement cPanel EasyHoster, le serveur hôte -h reste localhost, mais celui-ci pourrait être différent chez un hébergeur alternatif. Si nécessaire, consultez la documentation de votre hébergeur actuel.
Voici un exemple de commande pour l’Utilisateur MySQL example_user et la base example_db :
mysqldump -h localhost -u example_user -p example_db > mysql-table.sqlEntrez votre mot de passe MySQL et n’oubliez pas que celui-ci restera invisible à l’écran (noir).
En fonction de la taille de votre Base de données, l’opération peut prendre quelques minutes sans pour autant afficher de barre de chargement.
CONTRÔLE du Fichier .sql (facultatif)
La commande suivante permet de contrôler la présence du fichier .sql et sa Taille en Octets.
ls -l mysql-table.sql
CHERCHER REMPLACER
Ensuite, nous allons exécuter la commande sed sur le fichier mysql-table.sql en vue d’effectuer un Rechercher Remplacer de ENGINE = MyISAM par ENGINE = InnoDB.
sed -i 's/ENGINE\=MyISAM/ENGINE\=InnoDB/g' ./mysql-table.sqlLes tables MySQL du fichier .sql sont maintenant configurées pour être importées en Storage Engine InnoDB.
IMPORT SQL InnoDB
Il ne reste plus qu’à réimporter la base de données MySQL fraîchement convertie en Innodb. Pour cela, nous utilisons cette fois-ci la commande mysql et non mysqldump, vous aviez suivi.
mysql -h localhost -u $USER -p $DB < mysql-table.sqlN’oubliez pas de remplacer $USER et $DB par vos identifiants MySQL.
NETTOYAGE du Fichier .sql
Enfin, pour faire le ménage derrière nous, il est possible de supprimer le Dump MySQL en ligne de commande avec la commande rm, à manier avec grande précaution bien sûr !
rm mysql-table.sql
En moins de 5 minutes, grâce au Terminal de notre Hébergement cPanel, nous avons pu convertir notre base de données MyISAM en InnoDB.
CHECK phpMyAdmin
Puisque vous avez bien suivi ces instructions, comme sur la Gif ci-dessous, vous pouvez admirer toutes ces Tables MyISAM qui sont devenues des Tables InnoDB.
CONCLUSION
Si vous êtes utilisateur de WordPress et que vous aviez beaucoup de Tables MyISAM, il est probable que vous sentiez une amélioration au niveau de la vitesse de chargement de votre Tableau de bord WordPress.
Vous maîtrisez maintenant l’art de l’InnoDB !
Mais puisque vous avez été attentif à ce guide, vous voudrez peut-être choisir MyISAM au lieu d’InnoDB pour certains projets spécifiques ?
Convertir les Tables InnoDB en MyISAM
Pour booster les performances d’un site léger et très statique en mode Lecture, c’est-à-dire, pour un site créé de préférence sans CMS complexe, ou avec très peu de plugins installés, vous pourriez jeter votre dévolu sur MyISAM.
Si vous faites ce choix, il vous sera nécessaire de Convertir vos Tables InnoDB en MyISAM.
Dans ce cas, rien de plus simple.
Il vous suffit de suivre les instructions données ci-dessus pour InnoDB, mais en inversant les instructions pour convertir vos Tables en MyISAM.
Par exemple, la commande sed pour le Recherche Remplacer réalisé dans le Terminal cPanel devrait ressemble à ceci :
sed -i 's/ENGINE\=InnoDB/ENGINE\=MyISAM/g' ./mysql-table.sqlMyISAM et InnoDB n’ont maintenant plus aucun secret pour vous ! 😃
Conclusion sur InnoDB & MyISAM
Vous savez maintenant s’il est judicieux ou non de passer votre projet en InnoDB et vous connaissez les meilleures méthodes pour y parvenir. Mais peut-être qu’il y a encore quelque chose qui coince ? Votre hébergeur actuel ne vous propose peut-être pas les mêmes outils que ceux décrits dans ce billet ? Vous avez peur de tout casser ?
Dans ce cas, gagnez en tranquillité en choisissant un Hébergement Web infogéré EasyHoster et bénéficiez d’une Assistance Premium 7j/7 et 365j/an, y compris pour vos questions sur WordPress. Sans compter que nous offrons la migration entrante à tous les nouveaux clients (aucune connaissance technique requise pour le changement d’hébergeur).
Pour tous vos besoins en matière d’Hébergement Web, n’hésitez pas à vous reposer sur EasyHoster en commençant par ouvrir un ticket auprès de nos services pour nous parler de vos besoins.
