MySQL – Gestion des binlogs
Par défaut, MySQL stocke chaque requête en écriture dans des fichiers appelés binlogs.
Configuration des binlogs
Par défaut les binlogs sont conservés sur 10 jours, avec des fichiers n’excédant pas 100 Mo :
#log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M #binlog_do_db = include_database_name #binlog_ignore_db = include_database_name binlog_format = mixed
Format
http://dev.mysql.com/doc/refman/5.5/en/binary-log-setting.html
On peut choisir 3 types de format pour les binlogs :
- statement : les requêtes INSERT / UPDATE sont conservées
- row : les modifications de chaque ligne sont conservées (via une sorte de code « binaire » propre à MySQL)
- mixed : en mode statement… sauf dans certains cas où cela passe en mode row
Avantages et inconvénients :
Le mode statement est utile pour conserver en clair toutes les requêtes. Il permet aussi de meilleures performances quand des UPDATE contiennent des clauses WHERE qui modifient de nombreuses lignes. Pour de la réplication, il peut être non fiable car le résultat d’un UPDATE peut donner des résultats différents sur un serveur SLAVE. Cela peut aussi poser des soucis avec les transactions InnoDB.
Le mode row a l’inconvénient de rendre illisibles toutes les requêtes. Dans certains cas particuliers (UPDATE contiennent des clauses WHERE qui modifient de nombreuses lignes), il peut être moins performant. Il a l’avantage d’être plus fiable pour de la réplication.
Le mode mixed est un bon compromis pour de la réplication : il permet de voir la plupart des requêtes en clair, mais évite le problème de fiabilité en passant en mode row quand c’est nécessaire.
Suppression
Pour supprimer les binlogs antérieurs à mysql-bin.00NNNN :
mysql> PURGE BINARY LOGS TO 'mysql-bin.00NNNN';
ou par rapport à une date :
mysql> PURGE BINARY LOGS BEFORE "2011-12-07 00:00:00";
Désactivation
Pour désactiver les binlogs, on ajoutera l’option suivante dans la configuration :
disable-log-bin
Lecture
On pourra lire en ligne de commande le contenu d’un binlog via la commande :
# mysqlbinlog /var/log/mysql/mysql-bin.001789 | less
Note : si vous obtenez une erreur mysqlbinlog: unknown variable 'default-character-set=utf8' c’est que la directive default-character-set a été placée dans la configuration MySQL (/etc/mysql ou .my.cnf) dans la mauvaise section : [client] au lieu de [mysql] (ou [mysqldump]).
Replay
ATTENTION, CES MANIPULATIONS PEUVENT ÊTRE DANGEREUSES POUR VOS DONNÉES, BIEN SAVOIR CE QUE L’ON FAIT.
On pourra ainsi injecter le contenu d’un binlog dans une base… tout simplement avec une commande du type :
# mysqlbinlog /var/log/mysql/mysql-bin.001789 | mysql -P3307
À noter que si une partie des données étaient déjà présentes (cas d’un binlog corrompu lors d’incident lors d’une réplication), on pourra procéder ainsi :
# mysqlbinlog /var/log/mysql/mysql-bin.001789 > mysql-bin.001789.txt # sed -i 's/INSERT INTO/INSERT IGNORE INTO/gi' mysql-bin.001789.txt # cat mysql-bin.001789.txt | mysql -P3307
Log des requêtes lentes
Pour débugger les applications lentes, c’est une fonctionnalité intéressante de trouver quel requête est longue. Pour cela on peut spécifier quand une requêtes est considéré comme longue, le chemin où stocker les requêtes, et l’activation des logs.
long_query_time = 2 #Default 10 ! slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow.log
Source: Evolix