Erreurs MySQL courantes et comment les corriger rapidement

Les erreurs MySQL nécessitent généralement une première lecture rapide : vérifiez le journal des erreurs, identifiez la requête ou le thread défaillant, et évitez de deviner à partir du seul symptôme de l'application. Comprendre comment diagnostiquer et résoudre rapidement les erreurs courantes—allant des goulots d'étranglement de performance aux pannes de service critiques—est essentiel pour maintenir une haute disponibilité.

Ce guide couvre les défaillances MySQL courantes que vous pouvez trier rapidement : requêtes lentes, interblocages, retard de réplication et avertissements de corruption.

Identification et diagnostic des erreurs MySQL

Avant d'appliquer des correctifs, une identification précise est essentielle. Les principales sources d'informations de diagnostic MySQL sont le journal des erreurs MySQL et le journal des requêtes lentes. Les vérifier en premier est le moyen le plus efficace de localiser la cause racine d'un problème.

Vérification du journal des erreurs MySQL

Le journal des erreurs enregistre les événements critiques du serveur, les informations de démarrage/arrêt et les erreurs graves. Son emplacement varie selon le système d'exploitation et la configuration, mais on le trouve souvent dans le répertoire de données.

Astuce : Utilisez des commandes comme SHOW VARIABLES LIKE 'log_error'; pour trouver le chemin exact si vous n'êtes pas sûr.

Utilisation du journal des requêtes lentes

Si les performances se dégradent sans messages d'erreur explicites, le journal des requêtes lentes est votre prochaine étape. Il capture les requêtes qui dépassent un temps d'exécution prédéfini.

Pour l'activer (s'il n'est pas déjà actif), vous devez définir ces variables dans votre fichier de configuration (my.cnf ou my.ini) et redémarrer le serveur :

[mysqld]
slow_query_log = 1
long_query_time = 2  # Enregistrer les requêtes prenant plus de 2 secondes
slow_query_log_file = /var/log/mysql/mysql-slow.log

Scénarios d'erreur courants et correctifs immédiats

Voici quatre des défis opérationnels les plus fréquents rencontrés dans les environnements MySQL et des mesures concrètes pour les résoudre.

1. Performance des requêtes lentes

Les requêtes lentes sont la cause la plus courante de perte de performance. Elles proviennent souvent d'index manquants, de structures de requêtes inefficaces ou d'une mauvaise conception de base de données.

Diagnostic

Analysez le journal des requêtes lentes. Pour une requête lente spécifique, utilisez la commande EXPLAIN pour voir comment MySQL l'exécute :

EXPLAIN SELECT * FROM large_table WHERE column_a = 'value';

Recherchez type: ALL (scan complet de table) ou un nombre excessif de lignes examinées.

Correctifs rapides

Ajouter des index manquants : Si EXPLAIN montre un scan complet sur une colonne fréquemment filtrée, créez un index sur cette colonne : CREATE INDEX idx_column_a ON large_table (column_a);
Réécrire les requêtes : Évitez SELECT * dans le code de production. Utilisez les JOIN avec parcimonie et assurez-vous que les clauses WHERE utilisent des colonnes indexées.
Analyser les statistiques de table : Parfois, des statistiques obsolètes perturbent l'optimiseur. Exécutez ANALYZE TABLE table_name;.

2. Interblocages de transactions

Un interblocage se produit lorsque deux transactions ou plus attendent des verrous détenus par l'autre, entraînant une impasse. MySQL (utilisant InnoDB) détecte et résout généralement cela automatiquement en annulant une transaction.

Diagnostic

Vérifiez le journal des erreurs pour les messages faisant référence à LATEST DETECTED DEADLOCK. Vous pouvez également vérifier le statut InnoDB :

SHOW ENGINE INNODB STATUS;

Regardez sous la section TRANSACTIONS pour le graphique détaillé de l'interblocage, qui montre quelles transactions étaient impliquées et quelles instructions ont causé l'attente.

Correctifs rapides

Raccourcir les transactions : Gardez les transactions aussi brèves que possible. Validez ou annulez rapidement.
Ordre d'accès cohérent : Assurez-vous que tout le code d'application accède aux tables et aux lignes dans le même ordre défini. Si la transaction A verrouille la table X puis la table Y, la transaction B doit également verrouiller X puis Y.
Utiliser le verrouillage au niveau des lignes : Assurez-vous d'utiliser des clauses WHERE appropriées dans les instructions UPDATE et DELETE afin qu'InnoDB puisse verrouiller uniquement les lignes nécessaires, et non des tables entières (bien qu'InnoDB utilise par défaut le verrouillage au niveau des lignes pour les tables transactionnelles).

3. Retard ou échec de réplication

Dans les configurations source-réplica, le retard de réplication se produit lorsque le réplica prend du retard sur la source, entraînant des lectures obsolètes. Les anciennes commandes et champs MySQL utilisent encore la terminologie master et slave, vous pouvez donc voir les deux noms en production.

Diagnostic

Vérifiez le statut du réplica en utilisant les threads IO et SQL :

SHOW REPLICA STATUS\G
-- Sur les anciennes versions de MySQL : SHOW SLAVE STATUS\G

Champs clés à examiner :

Replica_IO_Running ou Slave_IO_Running : Doit être Yes.
Replica_SQL_Running ou Slave_SQL_Running : Doit être Yes.
Seconds_Behind_Source ou Seconds_Behind_Master : Indique le retard en secondes. Si cette valeur augmente, le réplica prend du retard.

Correctifs rapides

Résoudre les erreurs du thread SQL : Si l'applicateur SQL est arrêté, examinez la dernière erreur SQL. Sauter un événement avec sql_slave_skip_counter ou des commandes de réplication plus récentes peut provoquer une dérive des données, donc utilisez-le uniquement après avoir compris la transaction échouée et avoir un plan pour réconcilier les données.
Augmenter les ressources du réplica : Si le retard est constant sous une charge d'écriture élevée, le réplica peut avoir besoin de plus de CPU ou d'un débit d'E/S disque plus rapide pour traiter les événements du journal binaire assez rapidement.
Resynchroniser : Si le retard est sévère ou si le réplica est cassé, arrêtez la réplication, assurez-vous que le réplica pointe vers la bonne position du journal binaire de la source, et redémarrez.

4. Erreurs de corruption de données

La corruption de données, bien que rare avec les configurations InnoDB modernes, peut se manifester par l'incapacité de démarrer le serveur, des erreurs de somme de contrôle ou des résultats de requête étranges. La corruption indique souvent une défaillance matérielle (disque/mémoire) ou des arrêts inappropriés.

Diagnostic

La corruption est généralement immédiatement apparente via des messages d'échec de démarrage dans le journal des erreurs, faisant souvent référence à des tablespaces ou des pages spécifiques échouant à un test de somme de contrôle.

Correctifs rapides

Exécuter la vérification/réparation de table (MyISAM) : Pour les tables MyISAM, utilisez CHECK TABLE table_name; suivi de REPAIR TABLE table_name;.
Mode de récupération InnoDB : Si InnoDB ne parvient pas à démarrer, vous pouvez le lancer temporairement en mode de récupération pour vider les données :
```
[mysqld]
innodb_force_recovery = 1
```
Démarrez le serveur, videz immédiatement toutes les données critiques en utilisant mysqldump, arrêtez, supprimez les fichiers de données corrompus, et redémarrez sans le drapeau de récupération.

Avertissement : innodb_force_recovery ne doit jamais être utilisé de manière permanente. Il contourne les vérifications critiques et peut entraîner une dégradation supplémentaire des données si des écritures sont tentées.
Restaurer à partir d'une sauvegarde : La solution la plus sûre pour une corruption sévère est de restaurer l'intégralité de la base de données à partir de la dernière sauvegarde connue.

À retenir

Résolvez les problèmes MySQL à partir de preuves, pas de suppositions. Le journal des erreurs, le journal des requêtes lentes, EXPLAIN, le statut InnoDB et le statut de réplication montrent généralement la prochaine étape. Gardez les sauvegardes testées avant de toucher à la récupération de corruption ou aux commandes de saut de réplication.

Bonne pratique : Surveillance proactive

Le correctif le plus rapide est souvent la prévention. Mettez en œuvre des outils de surveillance complets (comme Prometheus/Grafana, Percona Monitoring and Management (PMM), ou les outils du fournisseur cloud) pour surveiller les métriques clés :

Nombre de connexions et taux de succès du cache de threads.
Utilisation et taux de succès du pool de tampons InnoDB.
Retard de réplication (Seconds_Behind_Master).
Utilisation des E/S disque.

Les alertes basées sur ces métriques vous permettent de traiter les requêtes lentes ou les problèmes de réplication avant qu'ils ne dégénèrent en pannes critiques.