Les journaux d’activité en mode table dans MySQL / MariaDB

Les journaux d'activité en mode table dans MySQL / MariaDBJe 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;

Base de données  / Audit MariaDB Audit MySQL MariaDB MySQL 

Commentaires

Super billet comme d’hab, ce serait interessant de rassembler dans le même billet journaux d’activité et les slow query

@fran6t

Merci. Oui, j’y songe.

Laisser un commentaire

(requis)

(requis)