Les fichiers de log MySQL

Introduction

MySQL dispose de plusieurs fichiers de log qui permettent de comprendre ce qui se passe dans le process mysqld. La compréhension de ces fichiers de logs est indispensable pour la mise en place de la réplication.


Le log d’erreur	Recense tous les problèmes rencontrés au démarrage, à l’exécution et à l’arrêt de `mysqld`.
Le log isam	Recense tous les changements sur les tables ISAM. Utilisé uniquement pour le débogage du code isam.
Le log de requête	Recense les connexions établies et les requêtes exécutées
Le log de mise à jour	Obsolète : recense toutes les commandes qui modifient les données
Le log binaire (binary log)	Recense toutes les commandes qui effectuent des modifications, également utilisées pour la réplication
Le log slow	Recense toutes les requêtes dont le temps d’exécution est supérieur au paramètres `long_query_time` ou bien les requêtes qui n’ont pas pris en compte des index.

Tous les fichiers de log sont généralement dans le répertoire de données data. Il est possible de forcer la main au process mysqld afin de demander la réouverture des fichiers de log (ou dans d’autres cas de basculer vers un nouveau log) en exécutant la commande flush logs (cf la syntaxe FLUSH).

Le log d’erreur

mysqld écrit toutes les erreurs à la sortie standard stderr, erreurs qui sont redirigées par le script safe_mysqld vers un fichier nommé hostname.err (en environnement Windows, mysqld écrit directement vers le fichier \mysql\data\mysql.err).

Ce fichier contient les informations au sujet du démarrage et de l’arrêt de mysqld ainsi que toutes les erreurs critiques trouvées lors de l’exécution de mysqld. Si mysqld est arrêté de manière inattendue, safe_mysqld écrit un ligne sur le redémarrage de mysqld dans ce fichier.

Le log d’erreur comporte également les warnings si mysqld a constaté qu’une table nécessite une vérification ou un réparation.

Sur certains systèmes d’exploitation, le log d’erreur enregistre une 'stack trace' lorsque mysqld s’arrête de façon inopinée.

Exemple : fichier localhost.localdomain.err

020601 16:07:17  mysqld started
/opt/mysql/bin/mysqld: ready for connections
020602  4:38:22  /opt/mysql/bin/mysqld: Normal shutdown
020602  4:38:23  /opt/mysql/bin/mysqld: Shutdown Complete
020602 04:38:23  mysqld ended

Le log de requêtes

Pour avoir connaissance de ce qui se produit avec mysqld, il est possible de démarrer ce dernier avec l’option :

--log[=file]

Cette option permet de recenser toutes les connexions et les requêtes dans un fichier de log (par défaut, ce fichier de log s’appelle hostname.log). Ce fichier de log peut s’avérer très pratique pour détecter les erreurs.

Avec les options –l ou --log, mysqld écrit un fichier de log général avec comme nom de fichier : hostname.log. Le redémarrage ou les rafraîchissements n’engendrent pas la création d’un nouveau fichier (bien que le fichier de log soit fermé puis réouvert). Il suffit simplement d’effectuer une sauvegarde de ce dernier fichier de log.

mv hostname.log hostname-old.log
mysqladmin flush-logs
cp hostname-old.log to-backup-directory
rm hostname-old.log

Le log de mise à jour (update log)

Ce fichier de log est obsolète et remplacé par le log binaire de mise à jour (binary update log)

Lors du démarrage avec l’option -- log-update[=file_name], mysqld écrit un fichier de log contenant toutes les commandes SQL qui mettent à jour des données.

Si aucun nom de fichier n’est donné, ce fichier a pour nomenclature le nom du host. Si un nom de fichier est donné mais qu’en revanche aucun chemin n’est spécifié, le fichier est écrit dans le répertoire data.

Si le nom du fichier ne possède pas d’extension, mysqld créé un fichier de log du style : file_name.###, où ### est un nombre incrémenté lorsque les commandes ci-dessous sont lancées :

mysqladmin refresh
mysqladmin flush-logs
flush logs

ou bien au redémarrage du serveur.

Seules les commandes qui ont effectué effectivement des modifications de données sont inscrites dans ce fichier de log. Ainsi une commande UPDATE ou DELETE avec une clause WHERE qui n’a pas trouvé de lignes ne sera pas transcrite dans le log. Les commandes UPDATE qui appliquent des mises à jour sans modifications sont également écartées.

L’écriture dans le fichier de log est effectuée immédiatement après la fin de l’exécution de la requête mais avant que les verrous soient enlevés et que la validation par la commande commit soit réalisée. Cela permet de s’assurer que le log est fidèle à l’ordre d’exécution.

Pour réaliser la mise à jour d’une base de données à partir de logs d’update, l’opération est la suivante en supposant que les logs d’update sont de la forme file_name.###.

ls –1 –t –r file_name.[0-9]* | xargs cat | mysql

ls permet de récupérer tous les fichiers de logs de mise à jour dans le bon ordonnancement.

Cette opération s’avère très utile lors de la récupération des fichiers de backup après un crash et qu’il est nécessaire de relancer les mises à jour jouées entre le backup et le crash.

Le log binaire de mise à jour (binary log)

Généralités

Le log binaire remplacera à terme le log de mise à jour, aussi le basculement vers ce fichier de log est impératif pour les versions futures de MySQL. Par ailleurs ce fichier est utilisé pour la réplication sous MySQL.

Le log binaire contient toutes les informations du log d’update mais dans un format plus efficace. Ce dernier contient également l’information concernant le temps de mise à jour sur une base de données pour une requête.

Le log binaire est impliqué dans le système de réplication.

Avec l’option --log-bin[=file_name] au démarrage de mysqld, mysqld écrit dans un fichier de log toutes les commandes SQL qui effectuent des mises à jour. Lorsqu’aucun nom de fichier n’est spécifié, ce fichier de log a pour nomenclature le nom du host suffixé par –bin. Si le nom du fichier est donné, mais que le chemin n’est pas spécifié, le fichier est écrit dans le répertoire data.

Si une extension est fournie avec l’option --log-bin=filename.extension, l’extension sera supprimée.

mysqld ajoute une extension numérique au fichier de log binaire qui est incrémentée lorsque les commandes ci-dessous sont lancées :

mysqladmin refresh
mysqladmin flush-logs
flush logs

ou bien au redémarrage du serveur.

Il est également possible de spécifier ce qui doit être écrit dans le fichier de log binaire par mysqld :


`binlog-do-db=database_name`	Permet d’indiquer au master que les mises à jour pour la base de données spécifiée doivent être écrites dans le log binaire, toutes les autres bases non explicitement mentionnées sont exclues.
`binlog-ignore-db=database_name`	Permet d’indiquer au master que les mises à jour pour la base de données spécifiée ne doivent pas être écrites dans le log binaire.

Pour connaître les différents fichiers de log binaires qui ont été utilisés, mysqld créé également un fichier d’index de log binaire qui contient le nom de tous les fichiers binaires de log utilisés. Par défaut ce fichier a le même nom que le fichier de log binaire avec l’extension .index. Il est possible de changer le nom du fichier d’index des logs biaires avec l’option --log-bin-index=[filename]

Si de la réplication est mise en œuvre, il est impératif de ne pas supprimer les anciens fichiers de log binaire avant d’être surs que les esclaves n’auront pas besoin de ces derniers. La meilleure méthode consiste à lancer la commande mysqladmin flush-logs une fois par jour et de supprimer les logs binaires lorsque ces derniers ont plus de trois jours.

Il est possible d’examiner un fichier de log binaire avec la commande mysqlbinlog. A titre d’exemple, il est possible de mettre à jour un serveur MySQL avec la commande ci-dessous :

mysqlbinlog log-file | mysql -h server_name

Il est également possible d’utiliser le programme mysqlbinlog pour lire un log binaire à partir d’un serveur MySQL distant : mysqlbinlog --help permet d’obtenir de plus amples informations.

L’utilisation de BEGIN [WORK] et SET AUTOCOMMIT=0 imposent l’utilisation du log binaire de MySQL pour les backups plutôt que les anciens fichiers de log de mise à jour.

Toutes les mises à jour (update, delete ou insert) qui modifient une table transactionnelle (tables BDB ou InnoDB) sont mises en cache jusqu’à la rencontre de la commande commit. Toutes les mises à jour sur les tables non transactionnelles sont stockées immédiatement dans le log binaire.

Chaque thread alloue au démarrage un buffer dont la taille est spécifiée par le paramètre binlog_cache_size. Si la requête est plus grande que la taille du buffer, le process ouvre un fichier temporaire pour manipuler un cache plus important. Le fichier temporaire est détruit lorsque le thread s’arrête.

Le paramètre max_binlog_cache_size peut être utilisé pour restreindre la taille totale utilisée dans le cache pour une requête multi transactionnelle.

Mise en place

La mise en place des binary logs (pour la future implémentation de la réplication avec MySQL) peut être réalisée en créant le répertoire $MYSQL/logs, puis le sous répertoire $MYSQL/logs/blogs dans lequel seront localisés les fichiers de log binaire.

Le daemon mysql est lancé ensuite comme suit pour la génération des fichiers de log binaires qui auront pour nomenclature blogs.###

./bin/safe_mysqld --log-bin=$MYSQL/logs/blogs/blogs

Dès la première génération d’un fichier de log binaire, le fichier blog.index est créé dans le répertoire $MYSQL/logs/blogs, ce dernier recensant historiquement les fichiers de log binaires de MySQL :

blogs.index

/opt/mysql/logs/blogs/blogs.001

La consultation des fichiers de log binaires est simplement réalisée avec le binaire mysqlbinlog dans le répertoire $MYSQL/bin.

Exemple :

./bin/mysqlbinlog /opt/mysql/logs/blogs/blogs.001 --result-file=/opt/mysql/result.001


Fichier de résultat : result.001
# at 4
#020813  1:21:05 server id  1     Start: binlog v 1, server v 3.23.49a-log created 020813  1:21:05
# at 73
#020813  1:23:41 server id  1     Query  thread_id=2   exec_time=0   error_code=0
use sim_sybase;
SET TIMESTAMP=1029216221;
insert into t_test values (2,"tot");
# at 143
#020813  1:24:13 server id  1     Query  thread_id=2   exec_time=0   error_code=0
SET TIMESTAMP=1029216253;
update t_test
set lib='Essai'
where lib like 'L%';

Le log slow

Généralités

Lors du démarrage de mysqld avec l’option --log-slow-queries[=file_name], mysqld écrit dans un fichier de log toutes les commandes SQL dont le temps d’exécution est supérieur au paramètre long_query_time. Le temps pour l’initialisation des verrous n’est pas pris en compte.

Si aucun nom de fichier n’est donné, le nom par défaut est le nom de la machine hote suffixée avec –slow.log. Si le nom du fichier est donné, mais pas le chemin, ce fichier est écrit dans le répertoire data.

Le log de requête longue est très utile pour traquer les requêtes dont le temps d’exécution est anormal et donc candidates à de l’optimisation. Avec un large log, la tâche peut s’avérer fastidieuse. Il est possible de d’utiliser la commande mysqldumpslow pour obtenir un résumé des requêtes qui apparaissent dans le log.

Avec l’option --log-long-format alors les requêtes qui n’utilisent pas d’index sont enregistrées et listées.

Mise en place

La mise en place des logs slows peut être réalisée en créant le répertoire $MYSQL/logs, puis le sous répertoire $MYSQL/logs/slogs dans lequel seront localisés les fichiers de log slow.

Le daemon mysql est lancé ensuite comme suit pour la génération du fichier de log slow slogs :

./bin/safe_mysqld --log-slow-queries=$MYSQL/logs/slogs/slogs