Comment réparer les tables corrompues dans MySQL

Introduction

MySQL est l’un des systèmes de gestion de bases de données relationnelles open source les plus populaires. Il alimente beaucoup des applications Web et des sites que nous utilisons quotidiennement. Cependant, comme tout système de base de données, MySQL peut parfois rencontrer des erreurs qui conduisent à la corruption des données.

Les tables MySQL corrompues se produisent lorsque les données d’un tableau deviennent illisibles ou non valides. Cela se produit généralement en raison d’arrêts inattendus, de défaillances matérielles, de bogues logiciels ou de modifications non autorisées des données de table. Les tables corrompues entraînent des crashs lorsque MySQL tente de lire ou d’écrire dans les données non valides.

Heureusement, MySQL fournit des mécanismes pour détecter la corruption et reconstruire les tables endommagées. Les méthodes exactes dépendent de l’utilisation ou non par les tables corrompues du moteur de stockage MyISAM ou InnoDB.

Dans ce guide, nous couvrirons :

• Causes courantes de corruption des tables MySQL
• Comment vérifier et réparer la corruption des tables MyISAM
• Récupération des tables InnoDB à l’aide de dump et restore
• Activation du mode de récupération forcée InnoDB

Comprendre ces techniques de récupération peut vous aider à minimiser les temps d’arrêt et la perte de données lorsque vous êtes confronté à des tables MySQL corrompues.

Causes de corruption des tables MySQL

Certains événements courants pouvant conduire à la corruption des tables sont :

• Arrêts inattendus – Si le serveur de base de données plante ou perd l’alimentation pendant une opération d’écriture, les écritures incomplètes peuvent corrompre les données.
• Défaillances matérielles – Une RAM défectueuse, des disques durs et d’autres matériels peuvent introduire des erreurs de données.
• Bogues logiciels – Les bogues dans le code MySQL ou d’autres logiciels interagissant avec la base de données peuvent entraîner une corruption.
• Modifications non autorisées des données – Les utilisateurs ou les applications modifiant directement les fichiers de données MySQL au lieu d’utiliser l’API de base de données risquent la corruption.
• Problèmes de système d’exploitation – Les bogues, les crashs et les problèmes d’autorisation avec le système d’exploitation sous-jacent peuvent également corrompre les tables.
• Surcharge du serveur de base de données – Une charge élevée et l’épuisement des ressources peuvent amener MySQL à ne pas écrire correctement les données.

Une bonne administration de base de données, du matériel fiable et des logiciels robustes réduisent les risques de corruption. Mais des problèmes d’intégrité des données peuvent encore se produire occasionnellement malgré les meilleures pratiques.

Vérification et réparation de la corruption des tables MyISAM

Le moteur de stockage MyISAM était le moteur par défaut dans les anciennes versions de MySQL. MyISAM utilise le verrouillage au niveau des tables et stocke les données et les index dans des fichiers séparés.

Les tables MyISAM prennent en charge la vérification et la réparation directes des corruptions. Lorsque vous soupçonnez une corruption, confirmez-la en exécutant CHECK TABLE :

mysql> CHECK TABLE tablename;

Cela analysera la table et révélera les problèmes de corruption. S’il existe une corruption, utilisez REPAIR TABLE pour reconstruire les tables MyISAM :

mysql> REPAIR TABLE tablename;

MySQL reconstruira les données et les index de la table pour restaurer la cohérence.

En supposant que la réparation a réussi, vous verrez un message comme celui-ci dans votre sortie :

+--------------------------+--------+----------+----------+ 
| Table                    | Op     | Msg_type | Msg_text |
+--------------------------+--------+----------+----------+
| database_name.table_name | repair | status   | OK       |
+--------------------------+--------+----------+----------+

Cependant, parfois la corruption est trop grave pour que MySQL répare la table. Dans ces cas, la documentation MySQL suggère :

• Essayez la commande REPAIR TABLE une deuxième fois. Les tentatives de réparation ultérieures peuvent fonctionner.
• Utilisez myisamchk depuis la ligne de commande pour reconstruire les tables. Cet outil peut corriger une corruption plus étendue.
• Extrayez les données de la table avec des requêtes SELECT dans une autre table, puis recréez la table d’origine à partir des données extraites.
• Utilisez mysqldump pour exporter le schéma et les données dans un fichier de dump SQL. Supprimez ensuite la table, recréez-la et importez le fichier dump. Le processus de dump et restore peut reconstruire des tables corrompues inaccessibles à une réparation directe.
• Restaurez l’intégralité du répertoire de données MySQL à partir de fichiers de sauvegarde pris avant que la corruption ne se produise. Cela permet de redémarrer avec des données bonnes connues.

Récupération des tables InnoDB corrompues

InnoDB est le moteur de stockage MySQL moderne par défaut. Contrairement à MyISAM, InnoDB stocke les données et les index ensemble dans des fichiers de tablespace partagés.

InnoDB dispose également de capacités avancées de récupération après incident. Il effectue des sommes de contrôle pour détecter la corruption et peut annuler automatiquement les transactions incomplètes au redémarrage après un crash.

En général, le redémarrage de MySQL suffit pour qu’InnoDB restaure la cohérence des données après une défaillance. Mais occasionnellement, les tables sont toujours corrompues et nécessitent une reconstruction.

Une fonctionnalité InnoDB appelée innodb_force_recovery permet un accès en lecture seule aux tables corrompues pour en extraire les données. Pour activer :

• Arrêtez le service MySQL.
• Modifiez my.cnf et ajoutez :

innodb_force_recovery=1

• Redémarrez MySQL.

Avec la récupération forcée activée, vous pouvez accéder aux tables corrompues pour en extraire les données. La technique recommandée est :

• Utilisez SELECT INTO OUTFILE pour exporter toutes les données de table dans un fichier texte.
• Supprimez la table corrompue d’origine.
• Recréez la table à partir du schéma.
• Utilisez LOAD DATA INFILE pour importer le fichier de données extrait pour remplir la table reconstruite.

Ce processus de récréation fait revivre les tables InnoDB inaccessibles à une réparation normale.

Des valeurs innodb_force_recovery plus élevées tentent la récupération après incident sur des données plus gravement corrompues. Mais cela peut aggraver la perte de données si la corruption est grave. Utilisez le niveau de récupération forcée le plus bas qui permet l’accès pour l’extraction.

Bonnes pratiques pour éviter la corruption

Bien que MySQL inclue des options de réparation pour la récupération, il est idéal d’éviter la corruption en premier lieu. Les étapes pour aider à prévenir la corruption des tables sont :

• Utilisez une alimentation sans interruption pour éviter les crashs dus à des coupures de courant.
• Assurez-vous que les écritures MySQL sont vidées de la mémoire vers le disque lors de la validation des transactions pour éviter toute perte de données.
• Isolez MySQL sur un stockage fiable et performant sans charges d’E/S concurrentes.
• Validez et testez tout le code d’application et d’outils de gestion qui interagit avec la base de données.
• Limitez l’accès direct aux fichiers de données depuis l’extérieur de MySQL et évitez la manipulation manuelle.
• Effectuez régulièrement des sauvegardes logiques via mysqldump ou SELECT INTO OUTFILE pour la récupération d’urgence.
• Utilisez la réplication pour maintenir des copies de données redondantes résilientes.
• Surveillez la fragmentation et réorganisez les tables avant l’apparition de problèmes d’espace.
• Gardez tous les logiciels MySQL à jour pour bénéficier des dernières corrections.
• Suivez les meilleures pratiques de sécurité pour limiter les perturbations dues à un accès non autorisé.
• Configurez la surveillance pour identifier les problèmes comme les écritures non vidées ou les longues attentes de sémaphores qui peuvent précéder la corruption.

Conclusion

Malgré les précautions, la corruption de base de données se produit toujours. Mais MySQL fournit des points de contrôle, des sommes de contrôle, des structures de données résistant aux crashes et d’autres fonctionnalités pour maximiser la détection et la récupération de nombreux scénarios d’échec.

Connaître les processus de réparation et de reconstruction manuels pour les tables MyISAM et InnoDB permet de restaurer rapidement l’accès lorsque la récupération automatisée ne suffit pas. Porter attention aux meilleures pratiques MySQL permet d’éviter la plupart des problèmes de corruption en premier lieu.

Avec une configuration, une surveillance et des procédures de sauvegarde appropriées, les administrateurs de bases de données MySQL peuvent agir rapidement pour revenir à un fonctionnement normal avec une perte de données minimale en cas de corruption.