MySQL propose dans sa suite logicielle, un moteur de Cluster (NDB), mais cette solution de continuité de service est très restrictive en matière applicative et ne s’adapte pas à nos besoins (requêtes à jointures complexes non clusterisables), on peut même affirmer sans trop mentir que son périmètre fonctionnel est très réduit tant les restrictions sont importantes. Le cluster MySQL NDB ne fait donc pas l’objet de cet article et est présenté dans un autre article.

Nous avons choisi une deuxième méthode de clusterisation, une solution dite de Haute Disponibilité (Hight Avaliability ou HA) basée sur plusieurs briques applicatives :

- Deux serveurs MySQL actif / passif

- Réplication de block de disque DRBD

- Automatisation de la surveillance et du démarrage auto des processus par HeartBeat

- Un serveur MySQL en réplication Master/Slave en secours en cas de corruption de blocks disque

1. Introduction

1.1 Quatuor MySQL + DrBD + HeartBeat + Master/Slave

1.1.1 Pourquoi un cluster HA au lieu d’un cluster NDB ?

1.1.2 Les différentes briques du cluster MySQL HA

1.1.2.1 Réplication des données avec « DRBD »

DRBD (Data Replication Block Device) est un programme qui permet de faire de la réplication de données au sens « block » d’unité de stockage (disque, …)

DRBD s’interpose entre l’OS et le périphérique de stockage (dans notre exemple, une partition sur un disque en RAID1) : à chaque fois que l’OS ordonne l’écriture d’un block au contrôleur, c’est sur un périphérique virtuel (/dev/drbd) et une partition virtuelle (/dev/drbd0) que l’OS s’adresse.

DRBD va alors contacter son homologue pour la réplication de ce block (secondary server) et, celui-ci, va écrire le block sur sa propre partition, puis (optionnellement) envoyer une information d’écriture complétée (commit) au serveur maitre (primary server).

Les blocks répliqués peuvent se faire de façon synchrone ou asynchrone en fonction des configurations matérielles et du positionnement des différents nœuds composant un cluster DRBD.

Remarque : Dans le cadre d’un cluster DRBD, la partition virtuelle (/dev/drbd0) du serveur secondaire n’est pas montée et n’est pas accessible par l’OS. En effet, il ne faut pas que l’OS du serveur secondaire écrive des blocks qui n’appartiendraient pas au serveur primaire : le cluster serait alors corrompu.

Remarque : La réplication au sens de DRBD se fait au niveau block-device et non au niveau logique (fileSystem). Si un block de données est corrompu, il sera répliqué et le serveur secondaire aura lui aussi le même block corrompu. Si cette corruption est fatale (partition illisible, filesystem irréparable, …) => Le cluster DRBD est définitivement perdu (le primaire et le secondaire).

1.1.2.2 Bascule d’un nœud mort vers un nœud vivant avec « HeartBeat »

Les deux nœuds du serveur DRBD sont en mode ACTIF / PASSIF. C’est-à-dire que, en cas de crash du primary server (le nœud actif), il faut aller démarrer l’ensemble des services sur le secondary server car ceux-ci sont arrêtés pour que la réplication puisse se faire correctement.

Pour automatiser la détection du nœud mort et d’automatiser la bascule et le démarrage automatique des services sur le nœud secondaire, nous allons utiliser le programme HeartBeat qui fera tout cela pour nous.

1.1.2.3 Réplication MySQL native « Master / Slave »

Afin de se prémunir de la corruption de blocks dans le cadre d’un cluster DRBD (voir 1.1.2.1) , même si ce cas de figure est très rare, nous allons mettre en place une réplication logique des données de notre cluster MySQL afin de pouvoir récupérer une version des données « au plus près » d’un crash et dans le cas de corruption du cluster DRBD.

1.2 Le cluster « exemple » :

Dans ce document, nous allons nous baser sur la configuration du cluster « XXX » (Cluster MySQL pour le client « XXX » de l’entreprise).

Ce cluster est composé de deux machines physiques pour le cluster MySQL / DRBD et d’une VM (pool Xen XXX-POOL) pour l’esclave MySQL issu de la réplication MASTER/SLAVE.

SQL1	Primary DRBD	192.168.13.98 (bond eth0+eth1)192.168.168.1 (réseau DRBD)192.168.194.55 (réseau backup)	login / pass = root / toto
SQL2	Secondary DRBD	192.168.13.99 (bond eth0+eth1)192.168.168.2 (réseau DRBD)192.168.194.56 (réseau backup)	login / pass = root / toto
SQL3	Slave MySQL	10.20.0.36	login / pass = root / toto

Le « réseau DRBD » est destiné à la réplication au sens de DRBD, il est composé de deux interfaces GBits reliées par un cable croisé.

L’ensemble des données à répliquer au sens de DRBD sera monté dans un point de montage « /data » qui contiendra pour MySQL : les configurations, les datas, les sauvegardes, …

Remarque : Les trois machines ont un point commun : elles disposent toutes d’un serveur MySQL, nous utiliserons une normalisation du filesystem qui sera commune à toutes les machines du cluster pour une meilleure lisibilité.

2. Installations et Configurations

2.1 Normalisation des filesystems

Sur les trois nœuds du cluster MySQL, nous allons utiliser le nommage suivant :

/data
/data/downloads	Divers objets hors distrib
/data/mysql
/data/mysql/conf	Le fichier de config de MySQL « my.cnf »Un lien sera fait de /etc/my.cnf vers ce fichier.
/data/mysql/backups	Les backups des databases
/data/mysql/run	Le fichier de socket de MySQL
/data/mysql/datafiles
/data/mysql/datafiles/databases	Les données des bases de données (MyIsam et InnoDB)
/data/mysql/datafiles/innodb_default	Le tablespace par défaut du moteur InnoDB
/data/mysql/logs
/data/mysql/logs/text	Les logs applicatives (pour le debug)
/data/mysql/logs/binnary	Les logs binaires (Redologs)
/data/mysql/logs/undo	Les logs transactionnels (Undologs)
/data/mysql/scripts	Les scripts (sauvegarde, …)

Remarque : N’oublions pas que sur le secondary-DRBD le point de montage « /data » est vide car la partition n’est pas montée, seul le processus de réplication DRBD du serveur y a accès pour écrire les blocks provenant du primary-DRBD.

2.2 Installation du cluster DRBD

2.2.1 Les packages

Cette installation est basée sur une openSuSE 11.3 64bits. Les packages DRBD sont présents dans la distrib et installable par YAST:

- drbd-8.3.7-2.5.x86_64

- yast2-drbd-2.13.1-221.2.noarch (managment par Yast : optionnel)

2.2.2 Configuration

2.2.2.1 Fichier de configuration principal

Un seul fichier de configuration est nécessaire eu bon fonctionnement de DRBD, attention, ce fichier doit être identique que le primary et le secondary.

A. DÉTAILS DU FICHIER /ETC/DRBD.CONF :

global {   usage-count yes;    }

common {

protocol C;

handlers {

pri-on-incon-degr     "/usr/lib/drbd/notify-pri-on-incon-degr.sh;

/usr/lib/drbd/notify-emergency-reboot.sh;

echo b > /proc/sysrq-trigger ;

reboot -f";

pri-lost-after-sb     "/usr/lib/drbd/notify-pri-lost-after-sb.sh;

/usr/lib/drbd/notify-emergency-reboot.sh;

echo b > /proc/sysrq-trigger ;

reboot -f";

local-io-error         "/usr/lib/drbd/notify-io-error.sh;

/usr/lib/drbd/notify-emergency-shutdown.sh;

echo o > /proc/sysrq-trigger ;

halt -f";

startup {    }

disk {

on-io-error detach; ó Sur une I/O error, on arrête de répliquer !

net {

cram-hmac-alg "sha1";

shared-secret "drbd-sql";       ó clé de hashage pour la communication

entre les noeuds

syncer {

rate 800M;                 ó Taux de transfert voulu sur le lien de réplication

al-extents 257;

resource cluster0{                                ó NOM DE NOTRE CLUSTER DRBD

protocol C;    TYPE DE REPLICATION : C ó synchrone

A ó Asynchrone

B ó semi synchrone (en mémoire)

net {

cram-hmac-alg "sha1";

shared-secret "drbd-sql"; ó mot de passe sha1

on sql-primaire {          ó nom DNS du primary DRBD

device /dev/drbd0;   ó nom de la partition virtuelle

disk /dev/sda4;                   ó nom de la partition réelle

address 192.168.168.1:8888; ó IP et port TCP de réplication DRBD

meta-disk internal;

on sql-secondaire {              ó Pareil sur le secondaire

device /dev/drbd0;

disk /dev/sda4;

address 192.168.168.2:8888;

meta-disk internal;

B. IMPORTANCE DU FICHIERS /ETC/HOSTS

Afin de se prémunir des problématiques DNS, il convient d’indiquer dans le fichier hosts de chaque nœud DRBD les adresses et noms de leurs homologues.

- Sur le PRIMARY comme sur le SECONDARY

…

192.168.13.98 sql-primaire

192.168.13.99 sql-secondaire

…

2.2.2.2 La préparation de la partition virtuelle DRBD

Les deux serveurs DRBD sont dans notre cas configurés pour utiliser un disque logique « /dev/sda » en RAID-1 sur deux disques physiques.

Sur les deux serveurs, il va falloir créer une partition (« /dev/sda1 ») de même taille et de même nom.

Dans notre cas, nous allons utiliser une partition de 100Go sur le disque.

Attention cette partition ne doit pas être formatée car DRBD doit y inscrire des données qui lui sont propres (« méta données ») et qui servent à la réplication entre le primary et le secondary. Le formatage s’éffectuera sur la partition virtuelle de DRBD (voir ) qui saura préserver ainsi ses méta datas.

Méthode à appliquer sur le PRIMARY (uniquement) :

1) fdisk /dev/sda on crée la partion de 100Go /dev/sda1

2) drbdadm create-md all création des metadatas dans la partinion

3) rcdrbd start démarrage du démon drbd

4) drbdadm — –overwrite-data-of-peer primary all ceci est le nœud primaire DRBD

5) mkfs.ext3 /dev/drbd0 formatage en préservant les métadonnées

Remarque :

Si des informations apparaissent sur les premiers blocks de la partition et indiquant que celle-ci a déjà été formatée auparavant, DRBD va refuser d’y créer les métadonnées, même si la partition a été détruite et re-créée. Il faut détruire les blocks avec :

- dd if=/dev/zero bs=1M count=1 of=/dev/sda4

- sync

Puis poursuivre au point 2).

Méthode à appliquer sur le SECONDARY (uniquement) :

Même chose que pour le PRIMARY à l’exception du formattage ext3. Nous allons également renseigner ce nœud qu’il est le secondaire.

1) fdisk /dev/sda on crée la partion de 100Go /dev/sda1

2) drbdadm create-md all création des metadatas dans la partition

3) rcdrbd start démarrage du démon drbd

4) drbdadm secondary all ceci est le nœud secondaire DRBD

Remarque : Il faut attendre un certain temps avant que la totalité de la partition drbd soit répliquée sur le nœud secondaire, on peut voir l’évolution avec (dans cette exemple depuis le PRIMARY NODE):

> cat /proc/drbd

version: 8.3.7 (api:88/proto:86-92)

srcversion: 04F43AA72D62F7D4F0CB048

0: cs:SyncSource ro:Primary/Secondary ds:UpToDate/Inconsistent C r----

ns:13985900 nr:0 dw:1947208 dr:12043684 al:930 bm:752 lo:1 pe:115

ua:142 ap:0 ep:1 wo:b oos:101513508

[=>..................] sync'ed: 12.1% (99132/112756)M

finish: 0:13:22 speed: 126,520 (108,976) K/sec

Lorsque la synchronisation sera achevée la commande donnera un message ressemblant à :

> cat /proc/drbd

version: 8.3.7 (api:88/proto:86-92)

srcversion: 04F43AA72D62F7D4F0CB048

0: cs:Connected ro:Primary/Secondary ds:UpToDate/UpToDate C r----

ns:115495756 nr:0 dw:1947208 dr:113549000 al:930 bm:7048 lo:0 pe:0 ua:0 ap:0 ep:1 wo:b oos:0

2.2.3 Arrêt / Marche du Cluster DRBD

Le script d’init /etc/init.d/drbd (ou rcdrbd) accepte les commutateurs « stop » ou « start ». Ceci a pour effet de démarrer le processus DRBD, sur le primary comme sur le secondary, mais les datas ne sont pas encore disponible à ce stade, il faudra monter la partition sur le point de montage voulu, à savoir :

Sur chacun des serveurs, le processus de réplication écoute, comme indiqué dans le fichier de configuration global, sur le port TCP 8888.

2.2.3.1 Démararge du CLUSTER DRBD :

Deux opérations sont à éffectuer :

- Sur le PRIMARY (en premier):

rcdrbd start

mount /dev/drdb0 /data

- Sur le SECONDARY :

rcdrbd start

(pas de montage de /data évidement…)

Arrêt du cluster DRDB :

- Sur le PRIMARY (en premier)

rcdrbd stop

umount /data

- Sur le SECONDARY

rcdrbd stop

2.2.4 Supervision du cluster DRBD

L’état de la réplication DRBD, sur le primaire comme sur le secondaire, s’éffectue en capturant le contenu de « /proc/drbd »

Sur le primary :

> cat /proc/drbd

> version: 8.3.7 (api:88/proto:86-92)

> srcversion: 04F43AA72D62F7D4F0CB048

> 0: cs:Connected ro:Primary/Secondary ds:UpToDate/UpToDate C r----

>    ns:72 nr:0 dw:1164292 dr:115974011 al:218 bm:7071 lo:0 pe:0 ua:0 >ap:0 ep:1 wo:b oos:0

Sur le secondary :

> cat /proc/drbd

> version: 8.3.7 (api:88/proto:86-92)

> srcversion: 04F43AA72D62F7D4F0CB048

> 0: cs:Connected ro:Secondary/Primary ds:UpToDate/UpToDate C r----

>    ns:72 nr:0 dw:1164292 dr:115974011 al:218 bm:7071 lo:0 pe:0 ua:0 >ap:0 ep:1 wo:b oos:0

Avec :

RO=Primary/X sur le primaire, Secondary/X sur le secondaire

avec X={ Primary | Secondary | DUnknown <= problème ! }

CS=état de la connexion réseau de réplication

NS=bytes émis

NR=bytes reçus

DW=données écrites physiquement sur le disque

DR=Données lues sur le disque

2.3 Installation de la réplication MySQL Maitre / Esclave

2.3.1 Configuration du maitre

Activer la journalisation des logs binaires dans /etc/my.cnf :

log-bin

Créer un user pour la réplication et qui sera utilisé par le SLAVE, puis lui donner les droits d’accèder au processus de réplication :

mysql> create user replic identified by replic ;

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replic'@'%' IDENTIFIED BY 'replic';

mysql> GRANT SELECT, PROCESS, FILE, SUPER, RELOAD ON *.* TO 'replic'@'%' IDENTIFIED BY 'replic';

Modifier le fichier de conf global du maitre (/etc/my.cnf)  pour lui indiquer les bases qu’il ne doit pas répliquer :

…

binlog-ignore-db = mysql

binlog-ignore-db = base_a_exclure

…

2.3.2 Configuration de l’esclave

2.3.2.1 Modification du fichier de conf global

Modifier le fichier /etc/my.cnf du SLAVE et ajouter :

server-id           = 10  ó identifiant différent de celui du maitre

relay-log           =/data/mysql/logs/binary/relaybinlog.rotation

relay-log-index     =/data/mysql/logs/binary/relaybinlog.index

relay-log-info-file =/data/mysql/conf/relay-log.info

master-info-file    =/data/mysql/conf/master.info

Redémarrer MySQL pour le prendre en compte (« rcmysql restart »)

2.3.2.2 Initiation du processus de réplication

a) Arreter (éventuellement) le processus ESCLAVE :

Mysql> stop slave ;

b) Faire un dump SUR LE MAITRE des bases à restaurer SUR L’ESCLAVE pour initier le processus :

Maitre> mysqldump           -h localhost

-uroot

--lock-all-tables

--master-data óContient les SCN des binarylogs pour l’esclave

--databases mabase

--flush-logs

--add-drop-database --allow-keywords

--quick

--complete-insert

--add-drop-table

--create-options

--dump-date

--triggers --routines --events

--debug-info

--verbose

--comments

--result-file=mondump.sql

c) restaurer le dump précédent sur l’esclave

esclave> mysql –uroot –D mabase < mondump.sql

d) Repérer dans le dump précédent le nom du fichier de log binaire renseigné par le Maitre lors du dump ainsi que la position dans ce log.

(imaginons que le nom du log soit « ’binarylog.000062 », que la position dans le log soit « 106 » et que l’addresse IP source physique du maitre soit ’192.168.13.98′)

Il faut ensuite informer l’esclave de ces données en tapant :

mysql> change master to

master_host=    '192.168.13.98',

master_user=    'replic',

master_password='replic',

master_log_file='binarylog.000062',

master_log_pos= 106;

Il faut ensuite démarrer le processus de récplication sur l’esclave :

mysql> start slave ;

mysql> show slave status\G ;

2.4 Installation de HeartBeat

2.4.1 Présentation

HeartBeat est basé sur une interrogation régulière de ses voisins afin de déterminer si, oui ou non, un nœud d’un cluster est mort ou non (/etc/ha.d/ha.cf)

S le cas où HeartBeat constate, depuis un nœud « passif », qu’un nœud dit « actif » est mort, alors un ensemble de processus est déclanché pour prendre l’identité de l’ancien nœud actif maintenant mort (/etc/ha.d/haresources).

L’ensemble des programmes et scripts dont HeartBeat va se servir sont dans /etc/ha.d/resource.d/.

2.4.2 Configuration de /etc/ha.d/ha.cf

Ce fichier contient la config générale de HeartBeat, il doit être présent à l’identique sur le nœud ACTIF et sur le nœud PASSIF.

logfile /var/log/ha.log              fichier de log standard

debugfile /var/log/ha-debug.log      fichier de log supplémentaire (debug)

logfacility     local0               facility de syslogd

keepalive 2                          On teste toutes les 2 secondes l’état des nœuds.

warntime 8                           On alerte si un nœud ne répond pas durant 8 secondes.

deadtime 15                          A 15 secondes, on considère que le nœud est mort.

initdead 30                          Temps de démarrage max toléré avant de diagnostiquer que le nœud est mort.

mcast bond0 225.0.0.1 694 1 0        cartes réseaux (mcast ou bcast) pour atteindre les HeartBeats

mcast eth2  225.0.0.1 694 1 0        cartes réseaux (mcast ou bcast) pour atteindre les HeartBeats

auto_failback off                    Pas de retour arrière automatique, trop dangereux

node sql-primaire                 quels sont les nœuds composant la ferme HeartBeat ?

node sql-secondaire               quels sont les nœuds composant la ferme HeartBeat ?

ping 192.168.13.253                  Chaque nœud doit également pouvoir accèder à la gateway

pour être caractérisé de « vivant ».

deadping 5                           si le ping ne répond pas pendant 5 secondes, la cible est considérée comme morte.

respawn hacluster /usr/lib64/heartbeat/ipfail

apiauth ipfail gid=haclient uid=hacluster

2.4.3 Configuration des resources HA

Dans le cas où un nœud passif HeartBeat considère que son pendant actif est mort, il va déclancher un certain nombre d’actions pour prendre son identité. Ces actions sont décrites dans le fichier /etc/ha.d/haresources.

Les ordres commencent par spécifier le hostname du serveur actif à monitorer (le primaire) puis une série d’actions séparées par des espaces.

Si une (ou plusieurs) adresse IP figurent comme action dans une liste d’action, cela signifie que HeartBeat doit dynamiquement assigner à une de ses cartes réseaux cette adresse IP (principe d’adresse IP virtuelle). Ce procédé permet de s’affranchir d’un LoadBalancer.

Exemple sur le custer SQL :

…

otsql-primaire   192.168.13.100    drbddisk::cluster0    Filesystem::/dev/drbd0::/data::ext3    mysql

…

Découpage :

hostname du primaire actif : otsql-primaire       (ne pas oublier de renseigner les fichiers hosts)

Action 1 : drbddisk::cluster0

Action 2 : Filesystem::/dev/drbd0::/data::ext3

Action 3 : 192.168.13.100

Action 4 : mysql

Explications :

Action1 : va déclencher le programme /etc/ha.d/resources.d/drbddisk avec le paramètre cluster0. Ce programme a pour effet de positionner dynamiquement le nœud DRBD comme PRIMARY (voir 2.2.2.2)
Action2 : déclenche le programme /etc/ha.d/resources.d/Filesystem en lui passant trois paramètres « /dev/drbd0 », « /data » et « ext3 ». Ce programme va dynamiquement monter la partition virtuelle DRBD sur la partition physique qui lui est associée sur le point de montage /data.
Action3 : assigne l’adresse IP 192.168.13.100 à une de ses interfaces présente sur ce réseau
Action4 : déclenche le programme /etc/ha.d/resources.d/mysql qui démarre le serveur MySQL sur le nœud nouvellement actif.

2.5 Bascule du cluster : FAIL-OVER / FAIL-BACK

Le Cluster HA DRBD/MySQL doit basculer sur le nœud SECONDAIRE en cas d’indisponibilité du nœud PRIMARY jugé par HeartBeat. Cette indisponibilité est caractérisée par l’impossibilité des deux HeartBeat de se voir mutuellement et d’un certain nombre de critères (Je ne vois pas le HeartBeat en face mais vois-je la passerelle ? etc).

2.5.1 FAIL OVER :

Dans le cas « théorique » où l’on voudrait basculer du nœud primaire vers le nœud secondaire du cluster DRBD (dans le cadre d’une maintenance par exemple), il faut faire croire au nœud secondaire que le processus HEARTBEAT du nœud primaire est mort, il suffit donc de le stopper :

PRIMARY> rchertbeat stop

Sur le primary : Heartbeat va alors stopper les services associés aux haressources (drbd et mysql) puis va se libérer de l’adresse ip virtuelle.

Sur le secondary : le Heartbeat local va constater le décès du heartbeat primaire, va alors s’affecter l’adresse IP virtuelle, monter la partition drbd dans /data puis démarrer mysql => le cluster a basculé.

2.5.2 FAIL BACK :

Pour revenir en arrière (« fail back » ó basculer du secondaire au primaire), il faut dans la même logique taper :

PRIMARY> rcheartbeat start

(attendre quelques instants par sécurité que heatbeat soit ok)

SECONDARY> rcheartbeat stop

(attendre quelques instants par sécurité pour être sur que le nœud PRIMAIRE a bien pris la main)

SECONDARY> rcheartbeat start ß on remet le SECONDARY node en observation.

Pour les mêmes raisons, tout a démarré sur le PRIMARY, tout a été stoppé sur le SECONDARY, la réplication reprend son cours normal.

3. Sauvegarde et restauration des données MySQL du CLUSTER

3.1 Introduction

Le cluster MySQL au sens de DRBD se résume à l’administration d’un serveur MySQL classique. Les données ne sont en effet présentes à un instant « t » que sur un serveur (le nœud DRBD actif) et en secours sur le serveur SLAVE MySQL.

Comme dans le cadre d’un serveur classique, le principe réside dans la réalisation d’un dump d’une base de données effectué base ouverte et active en s’assurant de la cohérence des données. Les tables stoquées par le moteur MyISAM seront verrouillées pendant ce backup et ne sont pas recommandées pour cela alors que les tables du moteur InnoDB ne le seront pas.

3.2 Sauvegarde

Le script /data/mysql/scripts/sauvegarde_full.sh permet de lancer un dump ds bases de données du Cluster en profitant des fonctionnalités transactionnelles de InnoDB pour en assurer la cohérence

Une transaction est ouverte avant de dumper puis est fermée une fois le dump terminé => Les données sont donc cohérentes.

Ce script est automatisé par la crontab et réalise les dumps dans /data/mysql/backups/

Remarque : Les dumps contiennent en fin de fichier l’information « – Dump completed on YYYY-MM-DD HH :MM :SS », une information qui peut être très intéressante dans le cadre d’un recovery.

Attention : les tables du moteur MyISAM ne seront pas cohérentes ! Si un programmeur persiste à utiliser ce moteur, il faudra utiliser le script /data/mysql/scripts/sauvegarde_full_MYISAM.sh mais les tables seront verrouillées pendant le dump, ce qui peut parfois s’avérer très gênant.

3.3 Restauration de données

3.3.1 Introduction

Définition :

Une restauration (ou « RESTORE ») consiste à rétablir les données d’une database à celles exactement présentent dans une sauvegarde (dump SQL dans notre cas).

Un recouvrement (ou « RECOVER ») consiste à rejouer la vie d’une database à partir d’un certain moment (celui d’un RESTORE généralement) jusqu’à un autre point (avant un crash, avant une erreur utilisateur, …)

Avant toute restauration, il faudra empêcher les utilisateurs externes d’accéder à vos databases, sous peine de corrompre les données. Pour cela, il faut ajouter le commutateur « skip-networking » dans la section « [MYSQLD] » de votre fichier de configuration globale « /etc/my.cnf ». Il ne faudra pas oublier d’enlever ce commutateur une fois la restauration terminée…

Le commutateur skip-networking comme son nom l’indique interdit les accès réseau (IP) et seules les opérations basées sur les sockets Unix sont autorisées, dont feront parti vos ordres de restauration en command line.

Plusieurs types de restauration de données peuvent être demandés :

- Restauration totale : on parle de « restauration totale ». Il s’agit de la restauration d’un dump précis. Cette restauration peut être restreinte à une base de données ou une table d’une base de données.

- Restauration partielle sur erreur : (Point In Time Recovery) : Une bêtise à été commise par un utilisateur à une heure H, les utilisateurs demandent à ce que une base B soit restaurée à sa situation avant l’erreur utilisateur et au plus près de H

- Restauration partielle après un crash : Un crash s’est produit. Il est demandé de restaurer une database au plus près possible d’avant le crash.

Une restauration « partielle » consiste en la restauration totale d’un fichier de sauvegarde (RESTORE) et l’application d’un journal de transactions DDL et DML (RECOVER) jusqu’à un certain point (un numéro de transaction ou une date & heure).

3.3.2 Restauration totale

C’est le cas le plus simple, celui où un utilisateur demande la restauration totale d’une base de données par l’injection d’un dump réalisé précédemment.

Imaginons dans ce cas que le dump ainsi pointé par l’utilisateur est /data/backups/mondump.sql.

- Restauration d’une database « MABASE » sauvegarde par le dump « dump_de_ma_base.sql » :

> cd /data/backups/

> mysql -uroot < dump_de_ma_base.sql

3.3.3 Restauration partielle

3.3.3.1 Introduction

Une restauration « partielle » ou « PITR (PointInTimeRecovery)» consiste en une restauration FULL (RESTORE) et en l’application des instructions DDL et DML (RECOVER) jusqu’à une certaine date, heure ou encore un certain numéro de transaction SQL.

Une restauration (RESTORE) n’est possible que si l’on dispose du dump contenant les données exigées (un dump est réalisé à heures fixes ou sur demandes, …) pour une date/heure précise.

Il est possible d’effectuer après cela un RECOVER en fonction de diverses situations (erreur DBA ou erreur utilisateur, crash d’un fichier, crash server, …), ceci afin de remonter la database au plus près de la réalité des données avant l’erreur critique.

MySQL permet d’enregistrer les ordres DDL et DML utiles adressées à votre base de données sous la forme d’un format de fichiers binaires appelés « binary logs ».

L’activation de cette trace SQL se fait par ajout du commutateur « log-bin » dans my.cnf et d’un ensemble de paramètres de gestion des logs binaires (voir section 4.1.1).

mysqlbinlog est un utilitaire permettant de lire les fichiers de logs binaires et d’en exporter les informations sous un format textuel afin de les « rejouer » sur une base de données après un « RESTORE ».

3.3.3.2 Restauration totale au plus près d’avant un crash

Cas classique : Une sauvegarde FULL d’une database MABASE a été effectuée le matin à 00h00 sous la forme d’un dump SQL exécuté par myssqldump.

A 06h00, un fichier composant tout ou partie d’une des databases InnoDB présente une erreur d’intégrité. La database est alors inaccessible.

Méthode :

- 1- Fermer l’accès du serveur aux utilisateurs (Ajouter « skip-networking » dans /etc/my.cnf + rcmysql restart)

- 2- Détruire la database (> mysql –uroot -e ‘drop database dbname ; ’)

- 3- Restaurer le dump SQL (au sens de mysqldump) le plus récent possible. (> mysql –uroot < /data/mysql/backups/dump_dbname_YYYYMMDD_HHIISS.sql)

- 4- Appliquer les ordres DDL et DML des journaux binaires à partir de la date de génération du dump

> mysqlbinlog  /data/mysql/logs/binary/binarylog.*

--database=dbname ß recover restreint à la database « dbname »

--start-datetime="2010-12-29 09:57:23" ß date de fin du dump

--to-last-log ß jusqu’à la fin du dernier log binaire disponible

--result-file=/tmp/ordres_a_rejouer.sql

Remarque : la date de fin du dump se trouve dans le dump sql de la sauvegarde Full, en bas de fichier.

Remarque : mysqlbinlog va ensuite lire les logs binaires et en extraire un contenu textuelle d’ordres DDL et / ou DML, ce fichier est indiqué par –result-file=…. Il faudra appliquer ces logs SQL exactement de la même façon que l’on applique un DUMP :

> mysql -uroot < /tmp/ordres_a_rejouer.sql

- Ouvrir l’accès du serveur aux utilisateurs

Commenter « skip-networking » dans /etc/my.cnf + rcmysql restart

Attention : un PITR à une heure près est risqué car, en une seule seconde, plusieurs instructions peuvent altérer la database, il n’est donc pas certain que une heure à la seconde près soit une méthode « sure » pour pratiquer un Recover dans le temps.

Il est plus sur d’indiquer un numéro de transaction (« log position » en MySQL). Ce numéro est identifié par « MASTER_LOG_POS » qui trouve en haut de chaque dump dans le champ comme par exemple :

-- CHANGE MASTER TO MASTER_LOG_FILE='binarylog.000547', MASTER_LOG_POS=106;

Ici, l’ordre de recover serait :

mysqlbinlog  /data/mysql/logs/binary/binarylog.*

--database=dbname

--start-position  ="107"

--to-last-log

--result-file=/tmp/ordres_a_rejouer.sql

3.3.3.3 Restauration totale, Recouvrement partiel (PITR)

Comme dans le point précédent, il faut d’ahabord restaurer le dump de la dernière sauvegarde FULL.

Ensuite, il faut pratiquer un recover, mais pas jusqu’au au bout.

A. EXEMPLE 1 : INDICATION HORAIRE

Par exemple, à 12:00:00 un programme a détruit des tables, les applis ne fonctionne plus et on demande de restaurer à 11 :59 :00.

La procédure est strictement identique à celle d’un recover total sauf que le commutateur « –to-last-log » doit être remplacé par « –end-datetime= »2010-12-29 11:59:00″ »

mysqlbinlog

/data/mysql/logs/binary/binarylog.*

–database=dbname

–start-position = »107″

–end-datetime= »2010-12-29 11:59:00″

–result-file=/tmp/ordres_a_rejouer.sql

Comme indiqué dans le point précédent, cette méthode est incertaine car en une seconde (11 :59 :00) il peut se passer plusieurs transactions et on peut ainsi rejouer les ordres SQL destructeurs rendant le recovery inutile ou au contraire rater les ordres qui se sont joués en fait la seconde d’après …

B. EXEMPLE 2: INDICATION FONCTIONNELLE :

L’utilisateur vous indique que un ordre « drop table ma_table » a été passé par erreur, provoquant la catastrophe qui a suivi.

Il faut alors repérer dans les logs binaires le LOG_POS de cette ordre pour demander un recover jusqte avant celui-ci.

mysqlbinlog binarylog.* | grep -B 5 -i « DROP TABLE MA_TABLE »

… (5 lignes ‘B’efore)

> at 2627

> 101228 16:39:09 server id 1 end_log_pos 2654

> drop table matable

Dans cet exemple, le dernier LOG_POS AVANT le drop est le « 2654 », il faut donc pratiquer un recover jusqu’à ce numéro inclus :