Recovery d’une base MySQL avec les logs binaires

Logo

Introduction

Et que se passe-t-il pour une base MySQL lorsqu’à une heure donnée une opération malencontreuse de suppression est réalisée et que l’on souhaite à partir de la sauvegarde de la nuit revenir à un état juste avant cette suppression malencontreuse ?

Les fichiers de log binaires (binary logs) permettent de répondre à ce problème de recovery. Toutes les commandes DML et DDL sont tracées dans ces fichiers de logs binaires.

Dans cette documentation, l’installation de MySQL est réalisée selon la norme évoquée dans la documentation Normalisation et installation de serveurs MySQL 4.1.x et 5.0.x sous Solaris.

Pour rappel voici les variables d’environnement implémentées pour le serveur CGC_P1_MYS qui sert d’exemple dans cet article :

CGC_P1_MYS > env
          _=/usr/xpg4/bin/env
APPNAME=dba
HZ=100
PATH=/Software/mysql/mysql-4.1/bin:/usr/xpg4/bin:/usr/bin:/usr/ucb:/etc:/usr/ccs/bin:/usr/sbin:.:/Software/mysql/dba/bin
SCRIPT=/Software/mysql/dba/CGC_P1_MYS/cfg/script
MYSQL_VERSION=4.1
BINLOG=/dba/mysql/CGC_P1_MYS/binlogs
CFG=/Software/mysql/dba/CGC_P1_MYS/cfg
LOG=/Software/mysql/dba/CGC_P1_MYS/log
EDITOR=vi
LOGNAME=mysql
DMP=/Software/mysql/dba/CGC_P1_MYS/dmp
PS1=CGC_P1_MYS >
SERVERNAME=CGC_P1_MYS
DBA=/Software/mysql/dba
DCD=/Software/mysql/dba/etc
DCA=/Software/mysql/dba/etc/CGC_P1_MYS.mys
SHELL=/bin/ksh
HOME=/Software/mysql
TERM=xterm
LD_LIBRARY_PATH=/Software/mysql/mysql-4.1/lib:
PWD=/Software/mysql/mysql-4.1.11
TZ=MET
RUN=/Software/mysql/dba/CGC_P1_MYS/run
ENV=/Software/mysql/.kshrc
MYSQL_HOME=/Software/mysql/mysql-4.1

Les fichiers de logs binaires

Généralités sur les fichiers de logs binaires

Les fichiers binaires de log comportent uniquement les commandes qui mettent à jour effectivement des données ainsi que les commandes DDL.

Les commandes UPDATE ou DELETE qui ne mettent à jour aucune donnée ne sont pas transcrites dans les fichiers binaires de log. Même les commandes UPDATE qui écrasent la valeur d’une colonne avec la même valeur ne sont pas transcrites dans les fichiers binaires de log.

Logs binaires MySQL

Lorsqu’un fichier binaire de log a atteint sa taille maximale, le serveur MySQL ouvre un nouveau fichier binaire de log et ainsi de suite en mettant à jour un fichier d’index.

Paramètres de configuration log-bin, log-bin-index, max_binlog_size, binlog-do-db, binlog-ignore-db

Les paramètres de configuration pour mettre en œuvre les fichiers de log binaires pour un serveur MySQL sont les suivants :
log-bin Chemin et préfixe des fichiers binaires de log, par défaut les fichiers binaires de log sont nomenclaturés par le nom du host suivi de -bin.
log-bin-index Chemin et nomenclature du fichier d’index des logs binaires.
max_binlog_size Taille maximale des fichiers binaires de log.

Une transaction n’est jamais répartie sur plusieurs fichiers binaires de log, c’est pourquoi un fichier binaire de log peut dépasser le paramètre max_binlog_size.
binlog_cache_size Pour les tables transactionnelles (InnoDB), toutes les mises à jour sont mises en cache jusqu’à la commande COMMIT. Chaque thread alloue un buffer de la taille de binlog_cache_size pour les requêtes. Si la requête est plus volumineuse que le paramètre binlog_cache_size, le thread ouvre un fichier temporaire pour stocker la transaction. Ce dernier fichier est détruit une fois la transaction validée.
binlog-do-db Indique au serveur de tracer dans les fichiers binaires de log les mises à jour uniquement si la base de données courante est la base de données spécifiée par le paramètre binlog-do-db (exemple : binlog-do-db=cgcam).

Si binlog-do-db=sales, dans ce cas si l’on écrit :

USE prices ;
UPDATE sales.january set amount=amount + 1000;

comme la base de données courante est prices et non sales, cette commande SQL n’est pas transcrite dans le log binaire !
binlog-ignore-db Indique au serveur d’ignorer dans les fichiers binaires de log les mises à jour uniquement si la base de données courante est la base de données spécifiée par le paramètre binlog-ignore-db (exemple : binlog-ignore-db=cgcam).

Pour le serveur CGC_P1_MYS, les paramètres ci-dessous sont implémentés dans le fichier de configuration $CFG/CGC_P1_MYS.cnf pour mettre en route les fichiers de log binaires :

$CFG/CGC_P1_MYS.cnf
log-bin = /dba/mysql/CGC_P1_MYS/binlogs/blog_CGC_P1_MYS
log-bin-index = /dba/mysql/CGC_P1_MYS/binlogs/blog_CGC_P1_MYS.index
max_binlog_size = 10M

De manière complémentaire, une nouvelle variable d’environnement est créée dans le contexte de la normalisation de MySQL sous Solaris, variable d’environnement qui identifie le répertoire où sont localisés les fichiers de log binaire :

Fichier $DBA/etc/defaults_srv.ksh :
export BINLOG = /$APPNAME/mysql/$SERVERNAME/binlogs

Rotation des fichiers binaires de logs : flush logs

La commande FLUSH LOGS force la rotation des fichiers binaires de logs.

mysql-CGC_P1_MYS > flush logs;

Un nouveau fichier binaire de log est également créé lorsque :

  • le serveur redémarre.
  • le paramètre max_binlog_size est atteint.
  • mysqladmin refresh est exécuté.
  • mysqladmin flush-logs est exécuté.

Traduction des fichiers binaires de log : mysqlbinlog

L’exécutable mysqlbinlog lit et traduit les fichiers binaires de log :

Syntaxe :

CGC_P1_MYS > mysqlbinlog [--database = <db_name> ]
                            [ --short-form ]
                            [ --offset=N ]
                            [ --position=i ]
                            [ --result-file[=<filename>]]
                            -u<user>
                            -S/tmp/CGC_P1_MYS.sock
                            -p
                            $BINLOG/<binlog_file>
--database Permet de filtrer la traduction pour une base de données seulement
--short-form Donne la version simplifiée de la traduction
--offset=N Écarte les N premières entrées de la traduction
--position=i Démarre la traduction du fichier binaire de log à partir de la position i
--result-file Redirection de la traduction dans un fichier

Avec la version 4.1.4, les deux nouvelles options --stop-date et --start-date très importantes sont apparues pour le binaire mysqlbinlog :

CGC_P1_MYS > mysqlbinlog [--database = <db_name> ]
                            [ --short-form ]
                            [ --offset=N ]
                            [ --position=i ]
                            [ --result-file[=<filename>]]
                            [ --start-date='YYYY-mm-dd hh:mm:ss' ]
                            [ --stop-date='YYYY-mm-dd hh:mm:ss' ]
                            -u<user>
                            -S/tmp/CGC_P1_MYS.sock
                            -p
                            $BINLOG/<binlog_file>
--start-date Traduit les entrées dans le fichier binaire de log à partir d’une date et une heure
--stop-date Traduit les entrées dans le fichier binaire de log jusqu’à une date et une heure

Exemple de la traduction du fichier binaire de log $BINLOG/CGC_P1_MYS.017 en mode complet pour uniquement la base de données cgcam :

CGC_P1_MYS > mysqlbinlog --database=cgcam
                            -uroot
                            -S/tmp/CGC_P1_MYS.sock
                            -p
                            $BINLOG/CGC_P1_MYS.017
…
# at 12223
#050620 11:16:02 server id 1  log_pos 12223     Query   thread_id=136   exec_time=0     error_code=0
SET TIMESTAMP=1119258962;
DELETE FROM t_si_formulaires WHERE id = 7;
…

# at 12223 : donne la position de la commande dans le fichier binaire de log (option --position)

En mode simplifiée :


CGC_P1_MYS > mysqlbinlog --database=cgcam -short-form
                                             -uroot
                                             -S/tmp/CGC_P1_MYS.sock
                                             -p
                                             $BINLOG/CGC_P1_MYS.017
…
SET TIMESTAMP=1119258962;
DELETE FROM t_si_formulaires WHERE id = 7;
…

Sauvegarde d’une base mysql avec mysqldump

Les bases utilisateurs sont sauvegardées avec l’utilitaire mysqldump, utilitaire qui exporte le schéma de création des objets et les données sous la forme de commandes DML Insert.

Syntaxe :

CGC_P1_MYS > mysqldump --databases <db_name> --add-drop-table -S/tmp/CGC_P1_MYS.sock -p > $DMP/<dbname>_YYYmmdd.dmp

L’option --add-drop-table indique à l’utilitaire mysqldump d’exporter également les commandes DDL de recréation des objets en plus des ordres DML insert dans les tables.

Le fichier résultat est un simple fichier ascii dont voici un extrait pour illustration :

-- MySQL dump 9.11
--
-- Host: localhost    Database: cgcam
-- ------------------------------------------------------
-- Server version    4.0.23-nt-max-log

--
-- Current Database: cgcam
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ cgcam;

USE cgcam;
….
--
-- Table structure for table `t_si_tbx_folders`
--
DROP TABLE IF EXISTS t_si_tbx_folders;
CREATE TABLE t_si_tbx_folders (
   id int(3) NOT NULL default '0',
   libelle varchar(20) NOT NULL default '',
   n_image varchar(60) NOT NULL default '',
   UNIQUE KEY idx_id (id)
) TYPE=MyISAM;

--
-- Dumping data for table `t_si_tbx_folders`
--

INSERT INTO t_si_tbx_folders VALUES (1,'Sybase','logo_sybase.gif');
INSERT INTO t_si_tbx_folders VALUES (2,'Oracle','logo_oracle.gif');
INSERT INTO t_si_tbx_folders VALUES (4,'MS SQL Server','logo_sqlserver.gif');

À l’issue du backup avec mysqldump, la commande flush logs est lancée pour forcer la bascule vers un nouveau fichier binaire de log.

Recovery d’une base mysql avec les fichiers de log binaires

A 13h35 le 20/06/2005, une commande delete a été lancée sur la table t_si_doc_details sans clause where, ce qui est prouvé par le fichier binaire de log :

# at 12468
#050620 13:25:23 server id 1  log_pos 12468     Query   thread_id=153   exec_time=0     error_code=0
SET TIMESTAMP=1119266723;
delete from t_si_doc_details;

À disposition : le dump de la base cgcam à 00h00 le 20/06/2005.

À partir de la remontée de la sauvegarde de la base cgcam, les commandes DML dans les fichiers binaires de log du 20/06/2005 vont être rejoués jusqu’à cette commande DELETE qui sera écartée : dans le contexte de ce cas pratique, seul le fichier binaire de log CGC_P1_MYS.017 contient les commandes pour la journée du 20/06/2005.

Pour ces opérations, le serveur sera mis en mode standalone afin d’empêcher les utilisateurs d’accéder au serveur.

Mise en mode standalone du serveur MySQL (option --skip-networking)

Pour empêcher les applications d’accéder au serveur, il suffit de redémarrer le serveur MySQL avec l’option --skip-networking, option qui empêche d’accéder au serveur MySQL par TCP/IP.

L’option skip-networking doit simplement être décommentée dans le fichier de configuration du serveur CGC_P1_MYS :

$CFG/CGC_P1_MYS.cnf
...
#
skip-networking
...

Une fois cette option décommentée, redémarrer le serveur MySQL CGC_P1_MYS.

Restauration de la sauvegarde

Le contrôle exclusif est positionné sur le serveur CGC_P1_MYS, la sauvegarde de minuit est chargée :

mysql -uroot -p<password> -S/tmp/CGC_P1_MYS.sock < $DMP/cgcam_20050620.dmp

Recovery

Le fichier de log binaire $BINLOG/CGC_P1_MYS.017 contient toutes les commandes pour la journée du 20/06/2005.

La manipulation va alors consister à traduire ce fichier de log pour rejouer toutes les commandes jusqu’à la commande DELETE sur la table t_si_doc_details qui sera écartée.

Pour les versions MySQL 4.1.4 et supérieures

La commande DELETE ayant eu lieu à 13h35:23 le 20/06/2005, la traduction du fichier binaire de log sera réalisée avec l’option --stop-time='2005-06-20 13:20:00' pour la base de données cgcam.


CGC_P1_MYS > mysqlbinlog --database=cgcam
                            --result-file=recovery_cgcam.sql
                            --stop-date='2005-06-20 13:20:00'
                            -uroot
                            -S/tmp/CGC_P1_MYS.sock
                            -p
                            $BINLOG/CGC_P1_MYS.017

Le recovery est alors réalisé en donnant le fichier recovery_cgcam.sql modifié au binaire mysql :

mysql -uroot -p<password> -S/tmp/CGC_P1_MYS.sock < recovery_cgcam.sql

Pour les versions antérieures à la version 4.1.4

Le fichier binaire de log CGC_P1_MYS.017 est intégralement traduit avec le binaire mysqlbinlog pour la base de données cgcam.

CGC_P1_MYS > mysqlbinlog --database=cgcam
                            --result-file=recovery_cgcam.sql
                            -uroot
                            -S/tmp/CGC_P1_MYS.sock
                            -p
                            $BINLOG/CGC_P1_MYS.017

Le fichier de résultat recovery_cgcam.sql est alors édité avec vi ou notepad et toutes les commandes après 13h35 :20 le 20/06/2005 sont supprimées, ce qui écarte la commande delete sur la table t_si_doc_details.

Le recovery est alors réalisé en donnant le fichier recovery_cgcam.sql modifié au binaire mysql :

mysql -uroot -p<password> -S/tmp/CGC_P1_MYS.sock < recovery_cgcam.sql

Suppression du mode standalone du serveur MySQL

À l’issue du recovery, il suffit de redémarrer le serveur CGC_P1_MYS en mettant en commentaire l’option --skip-networking dans le fichier de configuration du serveur CGC_P1_MYS afin d’enlever le contrôle exclusif sur le serveur et rendre le serveur disponible aux applications.