Activer les logs dans Mysql
I. Présentation
Par défaut dans MySQL, les logs ne sont pas actifs. Dans ce tutoriel nous
allons donc chercher à activer les logs MySQL et faire en sorte que ceux-ci
soient écrit dans "/var/log/mysql". Les logs peuvent souvent être d'une
grande aide pour les débogages ou la compréhension d'événements
exceptionnels (bugs, pannes ..).
II. Activer les logs dans le fichiers de configuration
Pour activer les logs MySQL, nous devons nous rendre dans son fichier de
configuration "/etc/mysql/[Link]". Il faut savoir que plusieurs types de
logs peuvent être écrit dans plusieurs fichiers :
Les logs d'erreurs (log-error) : contient les informations indiquant que
le serveur MySQL a été lancé ou arrêté ainsi que les erreurs critiques
qui sont survenues lorsque le serveur fonctionnait.
Les logs généraux (general_log_file) : contient toutes les informations
générales sur les requêtes clientes et les connexions effectuées.
Les logs binaires (log-bin) : contient toutes les requêtes qui modifient
les données (UPDATE ou DELETE).
Les logs des requêtes lentes (log-slow-queries) : contient les requêtes
faites au serveurs qui vont être plus longues que la valeur
"long_query-time" à s'éxécuter sur le serveur.
Dans notre cas, nous n'allons activer que les trois premiers logs, c'est à dire
"log-error", "general_log_file" et "log-bin" qui iront respectivement dans les
fichiers "[Link]", "[Link]" et "[Link]" dans le
répertoire "/var/log/mysql/". Nous allons ajouter quelques lignes de
configuration en dessous de la ligne "[mysqld]" qui indique que l'on
commence la partie de configuration du serveur MySQL :
[mysqld]
general_log_file=/var/log/mysql/[Link]
log-error=/var/log/mysql/[Link]
log-bin=/var/log/mysql/[Link]
Maintenant que les fichiers sont indiqués dans la configuration, il faut les
créer dans le dossier "/var/log/mysql" qui, lui, existe déjà (créé à
l'installation de mysql) :
cd /var/log/mysql
touch [Link] [Link] [Link]
Il faut également donner au service "mysql" les droits sur ces fichiers :
chown mysql /var/log/mysql -Rf
III. Redémarrer MySQL
Il nous faut maintenant redémarrer notre serveur MySQL pour que les
changements soient pris en compte que les logs puissent s'écrire :
service mysql restart
Si tout se passe correctement, le serveur devrait redémarrer normalement et
vous pourrez observer ces premiers messages dans le fichier
"/var/log/mysql/[Link]" :
IV. Activer les logs de requêtes lentes MySQL
Introduction
Il peut arriver de recevoir des alertes de monitoring concernant des requêtes
mysql lentes (slow queries) : ces erreurs sont produites quand le temps
d'exécution d'une requête MySQL dépasse le temps alloué défini par la
variable MySQL long_query_time. Ces erreurs pouvant être produites par
différents facteurs, nous verrons ici comment activer les logs de ces erreurs
afin de les analyser et les corriger.
Déroulé
Veuillez noter qu'activer les logs de slow queries peut ralentir votre serveur,
étant donné que des écritures seront effectuées en même temps que
l'exécution des requêtes lentes.
Vérifier que les logs ne sont pas déjà actifs
Afin de nous assurer que les logs de slow queries ne sont pas déjà activés,
connectez-vous en SSH à votre machine et entrez dans le shell MySQL :
mysql -u utilisateur -p
[mot de passe]
Une fois dans le shell MySQL, entrez la commande suivante afin d'avoir la
liste de toutes les variables et leurs valeurs définies :
show variables;
Naviguez jusqu'à constater dans la colonne de gauche la variable
"slow_query_log" : sur 'ON', les logs sont actifs. Sur 'OFF', nous devons les
activer.
Activer et configurer les logs slow queries
Toujours dans le shell MySQL, entrez la commande suivante :
set global slow_query_log = 'ON';
Vous pouvez également définir un chemin vers le répertoire où seront
stockés les logs de requêtes lentes :
set global slow_query_log_file ='/var/log/mysql/[Link]';
Enfin, définissez un temps minimum alloué à chaque requête pour vous
assurer de ne pas avoir de faux-positifs de requêtes lentes : 10 secondes est
une valeur correcte, 20 secondes est conseillé si il arrive que
votre serveur faiblisse face au nombre de requêtes et de visiteurs (cette
variable est de base définie à 10 secondes) :
set global long_query_time = '20';
Finalement, vérifiez que les changements ont bien été pris en compte par la
commande suivante :
show variables like '%slow%';
Analyser les logs
En cas d'alertes de monitoring vous indiquant que des requêtes sont notées
comme slow, vous pouvez désormais analyser les logs de slow_queries afin
de comprendre la raison de ces lenteurs :
# montrera les requêtes en temps réel
tail -f /var/log/mysql/[Link] # défini dans notre exemple ci-dessus
# sortie complète du fichier
cat /var/log/mysql/[Link]
# naviguer de haut en bas
less /var/log/mysql/[Link]
Explication du phénomène
Comme expliqué au début de cette fiche, les slow queries sont dédfinie par le
temps d'exécution défini des requêtes ; si la requête met plus de temps que
la limite définie par long_query_time à être exécutée, elle est définie comme
slow querie. La première raison de l'obtention de requêtes longues peut être
le mauvais paramétrage de la variable MySQL "long_query_time" ; un temps
maximum trop faible pourra mettre certaines requêtes un peu lourdes
directement en slow alors qu'elles sont en réalité tout à fait normales.
Une autre raison peut être liée à l'utilisation de MySQL par un service
externe ; dans le cas d'un site internet par exemple, une mauvaise
programmation n'intégrant pas de fermeture automatique des connexions à
MySQL peut remplir le tampon et ainsi rendre les temps d'exécution de
requêtes plus longs.
Une mauvaise configuration de la base de données peut aussi être à l'origine
du phénomène : les jointures de tables et les indexs (key) peuvent générer
ces erreurs - par exemple dans le cas d'une table ne possédant pas de clé
primaire.
Enfin, il ne faut pas exclure l'hypothèse d'une attaque sur votre serveur,
générant de nombreuses requêtes qui finiront par s'entasser et donc
dépasser la limite de temps d'exécution définie par long_query_time.
Conclusion
Les logs sont désormais actifs et devraient vous permettre d'analyser au
mieux les actions lentes effectuées sur votre base de données, vous
permettant de diagnostiquer des problèmes de performances, de
programmation ou d'attaques ciblées. En outre, vous avez désormais un
meilleur visuel sur les actions effectuées sur votre serveur de base de
données.