Comment réparer la base de données MySQL

Présentation

MySQL propose plusieurs façons différentes d'analyser et de réparer les bases de données. Les erreurs et les anomalies apparaissent pour de nombreuses raisons, dont certaines incluent :

Une mise à jour ou une rétrogradation de MySQL.
Modifications rapides des index ou des données
Incohérences des tableaux.

Lors de la recherche de données corrompues et de la réparation de tables, les mesures prises pour corriger les erreurs dépendent de la manière dont vous vous connectez à la base de données, de l'état du serveur de base de données, du type d'erreur et du moteur de base de données utilisé.

Cet article vous montre plusieurs façons de dépanner et de réparer une base de données MySQL.

Prérequis

Terminal ou ligne de commande avec privilèges root.
MySQL version 8.0 est installé et configuré.
Une base de données ou une table de base de données corrompue.

Comment vérifier la table MySQL pour les erreurs

L'exécution des diagnostics est la première étape du dépannage des problèmes. Il existe deux façons de vérifier une table MySQL pour les erreurs :

Grâce à un MySQL CHECK TABLE requête.
Utiliser le programme terminal mysqlcheck .

Vérifier une table avec CHECK TABLE

Le CHECK TABLE la requête fonctionne dans MySQL pendant que le service est en cours d'exécution. L'utilisation générale est :

CHECK TABLE <table name> [, table name, table name] [option] [option];

L'utilisation la plus simple est une table sans aucune option :

CHECK TABLE <table name>;

La sortie de la CHECK TABLE requête affiche un tableau informatif sur les résultats de la vérification :

Les options disponibles lors de la vérification des tables sont :

FOR UPGRADE - découvre les incohérences de version. Par exemple, MySQL 8.0 ne prend pas en charge les années à deux chiffres, donc une table contenant de telles valeurs est marquée pour la mise à niveau.
QUICK - ne vérifie pas les lignes pour une liaison incorrecte. Ceci est utile pour éviter les délais d'attente.
QUICK - effectue une vérification sur les tables qui ne sont pas fermées correctement.
CHANGED - ne vérifie que les tables avec des modifications depuis la dernière vérification ou qui ne se sont pas fermées correctement.
MEDIUM - vérifie les lignes du tableau pour vérifier la validité des liens supprimés et effectue une somme de contrôle pour les lignes.
EXTENDED - vérifie la cohérence complète.

Sans aucune option, CHECK TABLE effectue un MEDIUM vérifiez les tables et les vues MyISAM. Le FOR UPGRADE et QUICK effectuer des vérifications sur les tables et les vues du moteur InnoDB tandis que les autres options sont ignorées.

Vous pouvez également combiner les options pour une vérification plus détaillée. Par exemple, pour faire une vérification rapide pour savoir si une table s'est bien fermée, exécutez :

CHECK TABLE <table name> FAST QUICK;

Toutes les options qui ne s'appliquent pas aux tables sont ignorées.

Vérifier une table avec mysqlcheck

Le mysqlcheck command est la version en ligne de commande de CHECK TABLE . Le programme s'exécute dans le terminal pendant que le service MySQL fonctionne. Pour effectuer la vérification :

1. En tant qu'utilisateur root, accédez au répertoire dans lequel les bases de données sont stockées :

sudo su
cd /var/lib/mysql

2. Vérifiez toute la base de données avec :

mysqlcheck <database name>

Vous pouvez également vérifier une table spécifique dans la base de données en fournissant également le nom de la table :

mysqlcheck <database name> <table name>

Comment réparer la base de données MySQL

Il existe plusieurs méthodes pour réparer une base de données dans MySQL. Cependant, gardez à l'esprit que ces méthodes ne sont pas une solution miracle et peuvent entraîner une perte de données. Si les tables de la base de données sont souvent corrompues, identifiez la raison de l'incident.

Avant d'apporter des modifications et de commencer les réparations, créez une copie du répertoire :

cp -rfv /var/lib/mysql /var/lib/mysql_copy

Vous pouvez également créer une version de sauvegarde de la base de données avant d'essayer l'une des solutions de réparation. Suivez notre guide :Comment sauvegarder et restaurer une base de données MySQL.

Vous trouverez ci-dessous trois options pour réparer une base de données MySQL.

Réparation de la base de données MySQL avec la requête REPAIR TABLE

Après avoir scanné et localisé la base de données et les tables avec des problèmes, le moyen le plus rapide de résoudre le problème est avec le REPAIR TABLE requête :

Si le serveur s'arrête pendant la réparation, relancez la REPAIR TABLE opération lorsque le serveur redémarre. Assurez-vous de le faire avant d'effectuer toute autre opération sur la même table. Dans des conditions normales, une table MyISAM n'entraînera pas de perte de données en utilisant cette méthode.

Réparer la base de données MySQL avec mysqlcheck

Le mysqlcheck La commande est une alternative à la réparation des tables de base de données dans le terminal.

1. Tout d'abord, accédez au dossier de la base de données MySQL en tant qu'utilisateur root :

cd /var/lib/mysql

2. Ajoutez le -r l'option mysqlcheck commande pour réparer la table de la base de données :

mysqlcheck -r <database> <table name>

Le mysqlcheck -r la commande est la version terminale de la REPAIR TABLE requête.

Réparer la base de données MySQL avec ALTER TABLE

Utilisez le ALTER TABLE query pour reconstruire la table en utilisant le même moteur de stockage. Par exemple, si vous avez une table InnoDB, exécutez :

ALTER TABLE <table name> ENGINE = InnoDB;

Pour reconstruire une table MyISAM, utilisez :

ALTER TABLE <table name> ENGINE = MyISAM;

Si vous n'êtes pas sûr du moteur de stockage utilisé par la table, exécutez la requête suivante pour le savoir :

SHOW CREATE TABLE <table name>;

Exécuter des diagnostics et des réparations spécifiques au moteur sur la base de données MySQL

Selon le moteur de base de données utilisé, MySQL fournit des approches spécifiques au moteur pour analyser et réparer une table de base de données.

Les tables MyISAM sont sujettes aux corruptions. Cependant, la plupart du temps, les erreurs sont faciles à corriger.

Lorsque vous travaillez avec des bases de données InnoDB, des corruptions apparaissent lorsque les valeurs de somme de contrôle ne correspondent pas. Les erreurs se produisent généralement en raison d'une panne de courant ou d'un autre problème matériel.

Vérifier et réparer les tables MyISAM avec myisamchk

MySQL fournit un vérificateur spécifique pour les tables MyISAM.

1. Arrêtez le serveur MySQL. Ouvrez le terminal et exécutez :

systemctl mysql stop

2. En tant qu'utilisateur root, accédez à l'emplacement de la base de données :

cd /var/lib/mysql/<database name>

3. Pour vérifier une table spécifique, exécutez :

myisamchk <table name>

4. Réparez une table en exécutant :

myisamchk --recover <table name>

5. Redémarrez le serveur MySQL :

systemctl restart mysql

6. Enfin, vérifiez la table dans MySQL après la récupération :

sudo mysql -u <user> -p <password>
USE <database name>;
SELECT * FROM <table name> LIMIT 4;

Exécuter le processus de récupération InnoDB

La documentation MySQL suggère d'exécuter le processus de récupération InnoDB pour les bases de données InnoDB.

1. Localisez et ouvrez le fichier de configuration globale à l'aide de votre éditeur de texte préféré. Pour les systèmes Ubuntu et Debian, l'emplacement est :

cd /etc/mysql/my.cnf

2. Ajoutez l'option suivante à my.cnf fichier :

[mysqld]
innodb_force_recovery=4

Par défaut, l'option de récupération forcée est définie sur 0 (désactivé) et monte jusqu'à 6. Chaque nouveau niveau inclut les fonctionnalités de récupération des nombres inférieurs.

3. Enregistrez et fermez le fichier.

4. Redémarrez le serveur MySQL pour vous assurer que le nouveau fichier de configuration s'applique :

systemctl restart mysql

5. Dans le dossier home, exportez toutes les bases de données vers un fichier de sauvegarde avec le mysqldump commande :

sudo mysqldump --all-databases --add-drop-database --add-drop-table --routines > <file name>.sql

6. Démarrez le client MySQL et supprimez toutes les tables de base de données concernées. Pour supprimer une table de base de données, essayez :

DROP TABLE IF EXISTS <table name>;

Si la table ne disparaît pas, essayez de supprimer manuellement la table de la base de données. Connectez-vous en tant qu'utilisateur root et accédez au répertoire où se trouvent les bases de données :

sudo su

cd /var/lib/mysql/<database name>

Supprimez la table de la base de données :

rm -r <table name>

7. Arrêtez le serveur MySQL :

systemctl stop mysql

8. Dans le mon.cnf fichier, commentez le innodb_force_recovery option ajoutée à l'étape 2 pour désactiver le mode de récupération. Ensuite, enregistrez le fichier et fermez.

9. Redémarrez le serveur MySQL :

systemctl start mysql

10. Restaurez la base de données à partir de la sauvegarde .sql fichier dans le shell MySQL. Connectez-vous au client MySQL :

sudo mysql -u <username> -p <password>

11. Exécutez la requête suivante pour importer les bases de données :

USE <database name>;
# Turning off auto commit is optional and speeds things up for larger databases
SET autocommit=0; 
SOURCE <file name>.sql;

12. Enfin, testez la base de données pour confirmer que tout fonctionne correctement. Sinon, restaurez la base de données à partir de la sauvegarde et essayez une autre méthode.