Récupération de tables MySQL corrompues : une approche pratique

La corruption de table est l'un de ces problèmes où il faut à la fois agir rapidement et avec précaution. Une mauvaise commande de réparation peut transformer un incident récupérable en une perte permanente. Votre premier objectif n'est pas de « réparer » la table. Votre premier objectif est de préserver chaque octet que vous avez encore, d'arrêter la propagation des dégâts, et ensuite seulement de choisir la voie de récupération la moins risquée.

La procédure exacte dépend fortement du moteur de stockage. La récupération InnoDB consiste généralement à faire démarrer le serveur suffisamment longtemps pour vider les données propres ou restaurer à partir d'une sauvegarde. La récupération MyISAM implique souvent des outils de réparation de table. Traitez-les comme des scénarios différents, pas comme des commandes interchangeables.

Comprendre la corruption des tables MySQL

Avant de plonger dans la récupération, il est essentiel de comprendre ce qu'implique la corruption de table et pourquoi elle se produit. La corruption se produit lorsque la structure interne ou les données des fichiers d'une table deviennent incohérentes ou illisibles par le serveur MySQL.

Causes courantes de corruption

Plusieurs facteurs peuvent contribuer à la corruption des tables MySQL :

Défaillances matérielles : Disques durs défectueux, RAM défaillante (surtout sans mémoire ECC), ou alimentations électriques peu fiables (sans onduleur) peuvent entraîner une écriture incorrecte ou une perte de données lors des écritures.
Problèmes de système d'exploitation : Des bogues dans l'OS, des erreurs de système de fichiers ou des paniques du noyau peuvent interférer avec la capacité de MySQL à lire ou écrire les fichiers de données de manière cohérente.
Arrêts inappropriés : L'arrêt brutal du serveur MySQL (par exemple, en raison d'une panne de courant, d'un kill -9 ou d'un crash système) sans processus d'arrêt gracieux peut laisser les fichiers de données dans un état incohérent.
Bogues MySQL : Bien que rares dans les versions stables, des bogues spécifiques au sein du serveur MySQL lui-même pourraient potentiellement entraîner une corruption dans certaines circonstances.
Problèmes d'espace disque : Un manque d'espace disque lors des opérations d'écriture peut entraîner des fichiers de données incomplets.
Logiciels malveillants/virus : Bien que moins courants sur les serveurs de bases de données, les logiciels malveillants peuvent parfois corrompre des fichiers.

Symptômes de corruption

Reconnaître les signes de corruption tôt peut grandement aider à la récupération. Les symptômes courants incluent :

Messages d'erreur : Les journaux du serveur MySQL ou les applications clientes affichent des erreurs comme « La table est marquée comme plantée et doit être réparée », « Impossible d'ouvrir le fichier : '.frm' », « Erreur N du moteur de stockage » ou « L'index de la table '
' est corrompu ».
Résultats de requêtes inattendus : Les requêtes renvoient des données incorrectes, des résultats incomplets ou aucun résultat pour des tables qui devraient contenir des données.
Pannes/redémarrages du serveur : Le serveur MySQL plante de manière inattendue lors de la tentative d'accès à des tables spécifiques.
Utilisation élevée du CPU/E-S : Le serveur présente une consommation de ressources inhabituellement élevée sans raison claire, souvent en raison de tentatives répétées et échouées de lire des données corrompues.
Incapacité d'accéder aux tables : Vous pourriez être incapable d'interroger, de mettre à jour ou de supprimer une table.

Détection des tables corrompues

Une détection rapide est essentielle pour minimiser la perte de données et les temps d'arrêt. MySQL fournit plusieurs outils et méthodes pour identifier les tables corrompues.

1. Journaux d'erreurs MySQL

Le fichier error.log (l'emplacement varie selon l'OS, par exemple /var/log/mysql/error.log sous Linux) est votre première ligne de défense. MySQL enregistre des informations détaillées sur le démarrage du serveur, les arrêts et les erreurs critiques, y compris celles liées à la corruption de table. Examinez régulièrement ces journaux.

2. Instruction `CHECK TABLE`

L'instruction SQL CHECK TABLE est le moyen le plus simple de vérifier une ou plusieurs tables pour détecter des erreurs. Elle renvoie un statut pour chaque table, indiquant si elle est OK ou Corrompue.

-- Vérifier une seule table
CHECK TABLE votre_base.votre_table;

-- Vérifier plusieurs tables
CHECK TABLE nom_table1, nom_table2, nom_table3;

-- Effectuer une vérification étendue (plus approfondie mais plus lente)
CHECK TABLE votre_base.votre_table EXTENDED;

3. Utilitaire `mysqlcheck`

mysqlcheck est un client en ligne de commande qui vérifie, répare, optimise et analyse les tables. C'est essentiellement un wrapper autour des instructions CHECK TABLE, REPAIR TABLE, ANALYZE TABLE et OPTIMIZE TABLE, ce qui le rend pratique pour les opérations par lots.

# Vérifier toutes les tables d'une base de données spécifique
mysqlcheck -u root -p --databases votre_base --check

# Vérifier toutes les tables de toutes les bases de données
mysqlcheck -u root -p --all-databases --check

# Combiner vérification et réparation pour toutes les bases de données (réparation automatique)
mysqlcheck -u root -p --all-databases --check --auto-repair

Avant de commencer : préparations critiques

Avant de tenter une récupération, suivez ces étapes cruciales pour éviter toute perte de données supplémentaire.

1. SAUVEGARDE IMMÉDIATE ! (Logique et/ou physique)

C'est l'étape la plus critique. Même si vous suspectez une corruption, créez une sauvegarde avant de tenter une réparation afin d'avoir une solution de repli. Priorisez une sauvegarde logique à l'aide de mysqldump si le serveur est toujours en cours d'exécution et peut lire les données concernées. Si le serveur est arrêté ou instable, effectuez une copie physique du répertoire de données ou du répertoire de la base de données concernée pendant que MySQL est arrêté. Si votre environnement utilise des instantanés, prenez-en un avant de modifier les paramètres.

# Exemple : Créer une sauvegarde logique de votre base de données
mysqldump -u root -p votre_base > /chemin/vers/sauvegarde_base_pre_corruption.sql

2. Arrêter les écritures sur la table/base de données concernée

Pour éviter une corruption supplémentaire et garantir la cohérence des données pendant le processus de réparation, interrompez toutes les opérations d'écriture sur les tables concernées ou sur l'ensemble de la base de données. Vous pouvez y parvenir en :

Arrêtant les serveurs d'applications qui interagissent avec la base de données.
Mettant la base de données en mode lecture seule (si possible).
Utilisant FLUSH TABLES WITH READ LOCK; (nécessite des privilèges super, bloque toutes les écritures jusqu'à ce que UNLOCK TABLES; soit émis).
Arrêtant complètement le serveur MySQL si la corruption est grave.

3. Identifier le moteur de stockage

MySQL prend en charge divers moteurs de stockage, principalement InnoDB et MyISAM. Les procédures de récupération diffèrent considérablement entre eux. Déterminez le moteur de stockage de votre table corrompue :

SHOW CREATE TABLE votre_base.votre_table;

Recherchez la clause ENGINE= dans la sortie. ENGINE=InnoDB indique une table InnoDB, tandis que ENGINE=MyISAM indique une table MyISAM. InnoDB est le moteur par défaut et généralement plus robuste, tandis que MyISAM est plus ancien et moins tolérant aux pannes.

Si la table est inaccessible et que SHOW CREATE TABLE échoue, inspectez les métadonnées à partir d'une sauvegarde, des fichiers de migration de déploiement ou d'un autre environnement avec le même schéma. Deviner est risqué car une commande destinée à MyISAM peut être inutile ou dangereuse pour InnoDB.

Une liste de contrôle de triage pratique

Avant de réparer quoi que ce soit, notez ce que vous savez :

Quelle table ou base de données est concernée ?
MySQL est-il en cours d'exécution, en boucle de crash ou refuse-t-il de démarrer ?
La table concernée est-elle InnoDB ou MyISAM ?
Quelle est la date de la dernière sauvegarde valide connue ?
Les réplicas sont-ils sains et montrent-ils la même corruption ?
L'application peut-elle être mise en mode lecture seule ?

Cette liste de contrôle est importante car la meilleure réponse peut être « promouvoir un réplica sain » ou « restaurer la sauvegarde de la nuit dernière », pas « exécuter une commande de réparation en production ». Si vous avez une réplication, vérifiez les réplicas avant de redémarrer quoi que ce soit. Un réplica retardé peut parfois vous éviter de restaurer des sauvegardes plus anciennes, mais seulement si vous l'arrêtez avant qu'il ne rejoue l'événement dommageable.

Récupération des tables corrompues : approches étape par étape

Pour les tables InnoDB

Les tables InnoDB sont transactionnelles et conçues pour être résistantes aux pannes. Dans la plupart des cas, le mécanisme de récupération intégré de MySQL gère automatiquement les incohérences lors du redémarrage. Cependant, une corruption grave peut nécessiter une intervention manuelle.

1. Récupération automatique après crash d'InnoDB

Si le serveur a planté, un simple redémarrage de MySQL résout souvent le problème. InnoDB tentera automatiquement d'annuler les transactions incomplètes et de ramener les fichiers de données à un état cohérent.

2. Utilisation de `innodb_force_recovery` (À utiliser avec une extrême prudence !)

Si la récupération automatique échoue et que le serveur ne démarre pas ou que les tables restent inaccessibles, innodb_force_recovery peut être utilisé. Cette option force InnoDB à démarrer même s'il détecte une corruption, vous permettant de vider les données. Elle ne doit jamais être utilisée pour des opérations régulières, mais uniquement en dernier recours pour extraire des données. Des niveaux plus élevés peuvent ignorer le travail de récupération normal et exposer des données incohérentes.

Modifiez votre fichier my.cnf (ou my.ini) et ajoutez ou modifiez le paramètre innodb_force_recovery sous la section [mysqld]. Commencez par le niveau 1 et augmentez progressivement si nécessaire. N'oubliez pas de supprimer ce paramètre après les tentatives de récupération. Les niveaux sont (du moins au plus agressif) :

1 (SRV_FORCE_IGNORE_CORRUPT) : Ignore les pages corrompues. Permet les SELECT sur les tables.
2 (SRV_FORCE_NO_BACKGROUND) : Empêche le thread principal de s'exécuter, arrêtant les opérations en arrière-plan.
3 (SRV_FORCE_NO_TRX_UNDO) : N'exécute pas les annulations de transactions.
4 (SRV_FORCE_NO_IBUF_MERGE) : Empêche les fusions du tampon d'insertion.
5 (SRV_FORCE_NO_UNDO_LOG_SCAN) : Ne regarde pas les journaux d'annulation. Les instructions SELECT peuvent échouer.
6 (SRV_FORCE_NO_LOG_REDO) : N'effectue pas de restauration du journal de redoing. Risque le plus élevé de perte de données.

Processus de récupération avec innodb_force_recovery :

Sauvegardez à nouveau : Assurez-vous d'avoir la sauvegarde la plus récente possible avant de procéder.
Arrêtez MySQL : sudo systemctl stop mysql (ou équivalent).
Modifiez my.cnf : Ajoutez innodb_force_recovery = 1.
Démarrez MySQL : sudo systemctl start mysql.
Tentez de vider les données : Si le serveur démarre, effectuez immédiatement un mysqldump de la base de données/des tables concernées. Si une table échoue, videz les tables saines séparément afin qu'un seul objet défectueux ne bloque pas toute la récupération.
```
mysqldump -u root -p votre_base > /chemin/vers/dump_force_base.sql
```
Arrêtez MySQL : sudo systemctl stop mysql.
Supprimez innodb_force_recovery de my.cnf : C'est crucial.
Démarrez MySQL : sudo systemctl start mysql.
Supprimez la base de données/les tables corrompues : Si le dump a réussi, supprimez la base de données/les tables problématiques.
```
DROP DATABASE votre_base;
```

Recréez et importez : Recréez la base de données et importez les données à partir de votre fichier de dump.

mysql -u root -p -e "CREATE DATABASE votre_base;"
mysql -u root -p votre_base < /chemin/vers/dump_force_base.sql

3. Restauration à partir d'une sauvegarde

Si vous disposez d'une sauvegarde récente et saine, c'est souvent la méthode de récupération la plus rapide et la plus fiable pour une corruption InnoDB grave. Supprimez la base de données/les tables corrompues et restaurez à partir de la sauvegarde.

Effectuez la restauration dans une instance séparée dans la mesure du possible. Cela vous permet de confirmer que la sauvegarde est utilisable, d'exécuter des tests de validation de l'application et de comparer les nombres de lignes avant de remplacer les données de production. Une sauvegarde qui existe mais n'a jamais été restaurée reste une hypothèse.

Pour les tables MyISAM

Les tables MyISAM sont plus simples mais non transactionnelles, ce qui les rend plus sensibles à la corruption due à des arrêts inappropriés. La récupération implique généralement l'utilisation d'utilitaires de réparation.

1. Instruction `REPAIR TABLE`

L'instruction REPAIR TABLE tente de réparer les tables MyISAM corrompues. Utilisez-la uniquement après avoir sauvegardé les fichiers de la table. La réparation peut reconstruire les index ou supprimer les lignes endommagées en fonction des dégâts et du mode de réparation.

-- Réparation standard
REPAIR TABLE votre_base.votre_table;

-- Réparation rapide (moins approfondie, plus rapide)
REPAIR TABLE votre_table QUICK;

-- Réparation étendue (plus approfondie, plus lente, peut reconstruire les index)
REPAIR TABLE votre_table EXTENDED;

2. Utilitaire `mysqlcheck` (avec option de réparation)

Comme mentionné précédemment, mysqlcheck peut également effectuer des réparations. Ceci est utile pour réparer par lots plusieurs tables ou bases de données.

# Réparer toutes les tables d'une base de données spécifique
mysqlcheck -u root -p --databases votre_base --repair

# Réparer toutes les tables de toutes les bases de données
mysqlcheck -u root -p --all-databases --repair

3. Utilitaire `myisamchk` (en ligne de commande)

myisamchk est un utilitaire en ligne de commande de bas niveau pour vérifier et réparer directement les tables MyISAM. Il opère sur les fichiers physiques .MYI (index) et .MYD (données). Important : Le serveur MySQL doit être arrêté lors de l'utilisation de myisamchk pour éviter une corruption supplémentaire ou des conflits de fichiers.

Processus de récupération avec myisamchk :

Sauvegardez ! Copiez les fichiers votre_table.frm, votre_table.MYI et votre_table.MYD dans un emplacement sûr.
Arrêtez MySQL : sudo systemctl stop mysql (ou sudo service mysql stop).
Accédez au répertoire de données : Changez de répertoire pour accéder à l'emplacement de vos fichiers de base de données (par exemple, /var/lib/mysql/nom_de_votre_base).
```
cd /var/lib/mysql/nom_de_votre_base
```
Vérifiez la table :
```
myisamchk votre_table.MYI
```
Cela affichera des informations sur l'état de santé de la table.
Réparer la table :
- Réparation sûre : myisamchk -r votre_table.MYI (annule les lignes corrompues, plus sûr)
- Réparation agressive : myisamchk -o votre_table.MYI ou myisamchk -f votre_table.MYI (tente de reconstruire l'index, peut perdre certaines données ; à utiliser si -r échoue)
- Réparation très agressive : myisamchk -r -f votre_table.MYI (combine reconstruction et forçage)
Redémarrez MySQL : sudo systemctl start mysql (ou sudo service mysql start).

Après toute réparation MyISAM, effectuez des vérifications au niveau de l'application. Une table peut être structurellement réparée tout en manquant encore des lignes importantes pour l'entreprise. Par exemple, une table de commandes peut passer le CHECK TABLE mais avoir encore des lacunes qui nécessitent un rapprochement avec les enregistrements de paiement, les journaux ou les sauvegardes.

Prévenir la corruption future

Bien que savoir comment récupérer soit essentiel, prévenir la corruption en premier lieu est toujours la meilleure stratégie. Mettez en œuvre ces bonnes pratiques :

Sauvegardes régulières et vérifiées : Mettez en place une stratégie de sauvegarde robuste (à la fois logique et physique) et testez régulièrement vos sauvegardes pour vous assurer qu'elles sont restaurables.
Arrêts gracieux : Arrêtez toujours MySQL gracieusement en utilisant systemctl stop mysql, mysqladmin shutdown ou le gestionnaire de services. Évitez kill -9.
Matériel robuste : Investissez dans du matériel fiable, y compris de la RAM ECC (mémoire à code correcteur d'erreurs) et des configurations RAID pour la redondance des disques. Utilisez un onduleur (UPS) pour vous protéger contre les pannes de courant.
Surveillez les ressources système : Gardez un œil sur l'espace disque, les performances d'E-S, l'utilisation du CPU et la mémoire. L'épuisement des ressources peut entraîner des problèmes inattendus.
Utilisez InnoDB (par défaut et recommandé) : InnoDB est transactionnel et offre des capacités de récupération après crash supérieures à MyISAM. Il devrait être votre choix par défaut pour les nouvelles tables.
Maintenez MySQL à jour : Restez à jour avec les versions de MySQL et appliquez rapidement les correctifs de sécurité et les corrections de bogues. Les versions plus récentes incluent souvent des améliorations en matière de stabilité et d'intégrité des données.
Examinez régulièrement les journaux d'erreurs : Prenez l'habitude de vérifier les journaux d'erreurs MySQL pour détecter les signes avant-coureurs avant qu'ils ne se transforment en corruption à part entière.
Bonnes pratiques pour le système de fichiers et l'OS : Utilisez des systèmes de fichiers robustes (par exemple, ext4, XFS) et assurez-vous que votre système d'exploitation est bien entretenu.

Ce que « récupéré » devrait signifier

Ne vous arrêtez pas à « le serveur démarre ». Une base de données récupérée devrait passer quelques vérifications pratiques :

CHECK TABLE ou une validation appropriée au moteur renvoie des résultats propres.
Les tests de validation de lecture et d'écriture de l'application réussissent.
Les nombres de lignes pour les tables critiques correspondent aux attentes ou aux nombres de sauvegardes connus.
Les journaux d'erreurs n'affichent plus d'erreurs répétées du moteur de stockage.
Les sauvegardes ont repris et au moins un nouveau test de restauration a réussi.

La corruption de table MySQL est grave, mais la voie de récupération est gérable lorsque vous préservez les preuves, arrêtez les écritures, identifiez le moteur et évitez les commandes de réparation agressives jusqu'à ce que vous ayez une solution de repli. Dans de nombreux incidents, la solution la plus sûre est une restauration vérifiée. Lorsque vous avez besoin d'outils de récupération tels que innodb_force_recovery, REPAIR TABLE ou myisamchk, utilisez-les pour l'extraction et la réparation contrôlée, pas comme une maintenance de routine.