Les journaux d’activité en mode table dans MySQL / MariaDB
4 mars 2019
Je dois, à partir de demain, réaliser un audit sur une base de données MySQL version 5.5 d’une durée de deux jours. La 1ère journée sera consacrée à la collecte des données ; la 2e à l’écriture d’un rapport préconisant un ensemble de points d’amélioration.
Consigner l’activité de MySQL / MariaDB dans la table mysql.general_log
L’activation et la désactivation de la consignation générale des logs peut se faire dynamiquement sur MySQL/MariaDB, sans avoir à
Pour spécifier la destination des logs, vous disposez de la directive log_output. Elle peut prendre trois valeurs : FILE, TABLE, NONE. Par défaut, sur Linux, les logs sont consignés dans le fichier /var/lib/mysql/$(hostname).log. Il n’y a d’ailleurs aucun moyen de les stocker dans un autre répertoire, même en recourant à l’usage du bind mount ou du lien symbolique.
Pour envoyer les données dans la table mysql.general_log, vous devez tout d’abord, à partir de l’interpréteur de commandes MySQL ou de l’interface de HeidiSQL indiquer la destination des logs :
SET GLOBAL log_output='TABLE';
Activer l’écriture des logs dans la table mysql.general_log.
Après avoir choisi la destination, vous devez activer la consignation de tous les logs.
SET GLOBAL general_log=1;
Lecture des requêtes exécutées
Dans le cadre d’un audit, ce qui est intéressant, c’est de voir les requêtes les plus exécutées, ainsi que les mauvaises pratiques comme l’exécution de la commande SELECT * FROM…, au lieu de préciser les colonnes à afficher, empêchant accessoirement l’utilisation des index par l’optimiseur.
SELECT user_host AS utilisateur_machine, argument AS ordre_sql, COUNT(*) AS nb FROM mysql.general_log WHERE command_type='Query' GROUP BY user_host, argument ORDER BY 3 DESC;
Exportation des données de la table
Il peut être intéressant d’exporter les données de la table mysql.general_log dans un fichier CSV. L’utilisateur MySQL doit posséder les droits en écriture au niveau du dossier où vous écrivez le fichier.
SELECT * FROM mysql.general_log INTO OUTFILE '/var/lib/mysql/general_log.csv' FIELDS TERMINATED BY '\t' ENCLOSED BY '' LINES TERMINATED BY '\n';
Désactivation de l’enregistrement et purge des données de la table
Une fois l’enregistrement des données terminé, vous devrez dans un 1er temps désactiver l’enregistrement des logs.
SET GLOBAL general_log=0; SET GLOBAL log_output='NONE'
Une fois l’analyse terminée, il ne vous reste plus qu’à purger les données de la table mysql.general_log.
TRUNCATE TABLE mysql.general_log;