La réplication avec MySQL 3.23.x

Introduction

La réplication a vu le jour avec MySQL version 3.23.15. Un serveur peut jouer le rôle de master alors que les autres serveurs sont esclaves de ce dernier (slaves). Le serveur maitre conserve un log binaire des mises à jour (binary log) et un fichier d’index pour les logs binaires (cf documentation concernant les fichiers de logs d’un serveur MySQL).

Généralités sur l’implémentation de la réplication

La réplication sous MySQL est entièrement basée sur la conservation de toutes les modifications effectuées sur une base de données (instructions update, delete, etc.) dans le log binary du serveur maître (master), modifications qui seront lues par les serveurs esclaves (slaves) et exécutées.

Tous les serveurs esclaves mis en place nécessiteront des copies de toutes les données du serveur master existantes avant le démarrage du log binaire sur le serveur master. Si les serveurs esclaves démarrent avec des données qui ne correspondent pas à celles existant lorsque le log binaire a été mis en place sur le serveur master, les serveurs esclaves peuvent se retrouver en situation d’échec.

Une future version de MySQL (v 4.0) supprimera la nécesité de conserver une large photo des données pour les nouveaux serveurs esclaves. À l’heure actuelle il est encore nécessaire de bloquer toutes les écritures soit par un verrouillage global en lecture ou bien en arrêtant le serveur master durant la prise de la photo des données.

Une fois qu’un serveur esclave est configuré et en cours d’exécution, ce dernier se connecte au serveur master et attend des mises à jour à traiter. Si le serveur master tombe ou bien que le serveur esclace perd la connexion avec le serveur master, ce dernier serveur esclave tentera de se connecter selon un intervalle en secondes donné par le paramètre master-connect-retry jusqu’à ce que la reconnexion soit établie.

Chaque serveur esclave conserve la trace des dernières mises à jour traitées. En revanche le serveur master n’a aucune connaissance du nombre de serveurs esclaves ou combien de serveurs esclaves sont en cours sur le binary log du serveur master à un moment donné.

Mise en place de la réplication

Dans cet exemple, toutes les bases de données sont répliquées et la réplication n’a pas encore été configurée. Il est nécessaire dans un premier temps d’arrêt le serveur maître pour réaliser les étapes ci-dessous :

- S’assurer dans un premier temps que la version de MySQL est supérieure à la version 3.23.29.

- Créer un user destiné à la réplication sur le serveur master avec le privilège FILE et la permission de se connecter à partir de tous les serveurs esclaves. Si ce user est uniquement destiné à la réplication, aucun privilège additionnel n’est nécessaire. Par exemple, créer le user repl qui peut accéder au serveur master à partir de n’importe quel host :

GRANT FILE ON *.* TO repl@"%" IDENTIFIED BY '<password>';

- Arrêter le serveur MySQL master :

mysqladmin -u root -p<password> shutdown

- Effectuer une photographie des données sur le serveur master. La méthode la plus simple consiste à utiliser la commande tar pour produire une archive de tout le répertoire data :

tar -cvf /tmp/mysql-snapshot.tar /path/data-dir

- Dans le fichier my.cnf du serveur master, ajouter les options log-bin et server-id=unique number dans le section [mysqld]. L’identifiant du serveur esclave doit être différent de l’identifiant du serveur master.

my.cnf

[mysqld]
       log-bin
       server-id=1

- Redémarrer le serveur MySQL master

- Dans les fichiers my.cnf des serveurs esclaves, ajouter les données ci-dessous :

master-host=<hostname du master>
master-user=<user de la replication>
master-password=<mot de passe du user de la replication>
master-port=<port TCP/IP du serveur master>
server-id=<identifiant unique compris entre 2 et 2^32-1>

Chaque identifiant doit être unique pour server-id dans le système de réplication. Si le paramètre server-id n’est pas spécifié, il vaudra 1 si master-host n’est pas défini, sinon il vaudra 2. Dans le cas de l’omission de server-id, le serveur master refuse les connections à partir des serveurs esclaves et le serveur esclave refuse de se connecter au serveur master.

- Copier la photo des données dans les répertoires de données des serveurs esclaves en s’assurant des privilèges sur les fichiers et les répertoires.

- Démarrer en dernier lieu les serveurs esclaves.

Les serveurs esclaves se connectent par la suite au serveur master et prennent en compte les mises à jour effectuées depuis que la photographie a été prise.

Si le paramètre server-id a été omis, l’erreur ci-dessous est reportée dans le fichier de log des erreurs :

Warning: one should set server_id to a non-0 value if master_host is set.
The server will not act as a slave.

Tous les messages d’erreur sont reportés dans le log d’erreur du serveur esclave si le serveur esclave ne peut se connecter pour diverses raisons.

Lors de la mise en place de la réplication sur un serveur esclave, le fichier master.info est créé dans le même répertoire que le fichier de log des erreurs. Le fichier master.info est utilisé par le serveur esclave pour le traitement du log binaire du serveur master. Ne pas tenter de supprimer ou éditer ce fichier, la commande change master to est prévue à cet effet.

Commandes SQL pour la réplication

La réplication peut être gérée dans une interface SQL.

Commandes SQL pour le serveur esclave

Démarrage et arrêt du thread slave (SLAVE START, SLAVE STOP)

Démarrage du thread slave :

SLAVE START

Arrêt du thread slave :

SLAVE STOP

Statut du thread slave (SHOW SLAVE STATUS)

La commande SHOW SLAVE STATUS affiche le statut concernant le thread esclave.

Réinitialiser le thread dans la lecture des logs binaires (RESET SLAVE)

Il est possible de faire perdre au thread slave sa position dans le log binaire du serveur master avec la commande RESET SLAVE.

Anciennement, dans les versions antérieures à la version 3.23.26, la commande FLUSH SLAVE correspondait à la commande RESET SLAVE actuelle.

Suppression de lectures d’évènements dans le log binaire du serveur master

Avec la commande SET SQL_SLAVE_SKIP_COUNTER=n, il est possible d’empêcher la lecture de n évènements sur le log binaire du serveur master. Toutefois, le thread slave doit être arrêté, sinon une erreur est reportée.

Copie d’une table du serveur Master vers le serveur esclave (LOAD TABLE ... FROM)

Il est possible d’effectuer une copie d’une table du serveur master dans le serveur esclave avec la commande LOAD TABLE :

LOAD TABLE tbl_name FROM MASTER

Changement des paramètres du serveur master (CHANGE MASTER TO...)

La commande CHANGER MASTER TO master_def_list permet de changer les paramètres du serveur master et de redémarrer le thread slave avec ces modifications. master_def_list est une liste de paramètres séparés par des virgules, paramètres qui sont :

MASTER_HOST
MASTER_USER
MASTER_PASSWORD
MASTER_PORT
MASTER_CONNECT_RETRY
MASTER_LOG_FILE
MASTER_LOG_POS

Exemple :

CHANGE MASTER TO
    MASTER_HOST='master2 ',
    MASTER_USER='replication',
    MASTER_PASSWORD='pwd_replication',
    MASTER_PORT=3306,
    MASTER_LOG_FILE='master2-bin.001',
    MASTER_LOG_POS=4;

Il est nécessaire de spécifier que les paramètres qui sont modifiés. Les valeurs resteront les mêmes si des paramètres sont omis, à l’exception d’un changement du host ou du port. Dans ce cas, le slave suppose que puisque le host ou le port diffère, le serveur master est différent, aussi les anciennes valeurs concernant le log ne sont plus appliquables et sont automatiquement initialisées à 0 ou à vide.

Si le thread slave est redémarré, l’ancien master est alors pris en compte, ce qui n’est pas souhaitable, aussi il est nécessaire de supprimer le fichier master.info avant de redémarrer le thread slave.

Commandes SQL pour le serveur master

Activation et désactivation de l’écriture du log binaire du serveur master (SQL_LOG_BIN)

Si le user en a le privilège, il est possible de désactiver ou activer l’écriture du log binaire :

SET SQL_LOG_BIN = 0 (désactivation)

SET SQL_LOG_BIN = 1 (activation)

Suppression des logs binaires dans le fichier d’index (RESET MASTER)

La commande RESET MASTER supprime tous les log binaires dans le fichier d’index. Dans les versions antérieures à la version 3.23.26, la commande FLUSH MASTER était destiné à cet usage.

Listing des logs binaires du serveur master (SHOW MASTER LOGS)

La commande SHOW MASTER LOGS disponible depuis la version 3.23.28 liste les logs binaires sur le serveur master, cette commande doit être invoquée avant la commande PURGE MASTER LOGS TO pour déterminer jusqu’ou aller dans la purge des logs binaires.

Information sur le statut dans les logs binaires du serveur master

La commande SHOW MASTER STATUS liste les informations concernant le log binaire du serveur master.

Suppression de logs binaires sur le serveur master (PURGE MASTER LOGS TO ...)

Depuis la version 3.23.28, la suppression des logs listés dans le fichier d’index avant un log spécifique peut simplement être réalisé avec la commande PURGE MASTER LOGS TO 'logname'.

Cette commande supprime tous les logs binaires antérieurs au log binaire spécifié ainsi que les entrées dans le fichier d’index pour ces derniers. Une erreur survient si un thread esclave est en lecture sur un log que la commande tente de supprimer. Il est impératif de vérifier avec la commande SHOW SLAVE STATUS sur quels logs binaires les threads esclaves sont positionnés, puis d’effectuer un listing des logs binaires avec la commande SHOW MASTER LOGS pour déterminer les logs binaires les plus anciens qu’il est sûr et certain de supprimer en toute sécurité.

Options dans les fichiers *.cnf liées à la réplication

La version 3.23.30 est au minimum recommandée pour la mise en place de la réplication avec MySQL.

Options dans le fichier *.cnf pour le serveur master

Dans le tableau ci-dessous sont répertoriées les options pour le serveur master :

Paramètre	Description
`log-bin=filename`	Ecrit un log binaire dans le chemin spécifié
`log-bin-index=filename`	L’utilisateur peut lancer la commande `FLUSH LOGS`, et il est donc nécessaire de savoir quel est log couramment actif et lesquels sont obsolètes. Cette information est donnée dans le fichier d’index des logs binaires (`'hostname'.index`). Ce dernier fichier peut être renommé avec l’option `log-bin-index`
`sql-bin-update-same`	Si cette option est appliquée, la valeur appliquée pour `SQL_LOG_BIN` sera automatiquement appliquée à `SQL_LOG_UPDATE`.
`binlog-do-db=database_name`	Cette option permet de désigner sur quelle base de données du serveur `master` les mises à jour sont consignées dans le log binaire. Toutes les autres bases de données sont ignorées.
`binlog-ignore-db=database_name`	Cette option permet d’indiquer quelle base de données doit être ignorée pour l’écriture dans le log binaire.

Options dans le fichier *.cnf pour le serveur slave

Dans le tableau ci-dessous sont répertoriées les options pour le serveur slave :

Paramètre	Description
`master-host=host`	Nom du host ou adresse IP du serveur `master`. Si ce paramètre n’est pas appliqué, le thread ne peut démarrer.
`master-user=username`	User que le thread slave utilise pour se connecter au serveur `master`. Ce user doit avoir le privilège `FILE`. Si ce user n’est pas créé, le user test est pris par défaut.
`master-password=password`	Mot de passe du user utilisé par le thread slave pour se connecter au serveur `master`.
`master-port=portnumber`	Port d’écoute du serveur `master`. Si ce paramètre n’est pas appliqué, `MYSQL_PORT` est pris en compte.
`master-connect-retry=seconds`	Le nombre en secondes au bout duquel le thread slave réiterera ses tentatives de connexions après un échec de connexion vers le serveur `master`.
`master-info-file=filename`	Localisation du fichier qui stocke le dernier point d’arrêt dans le process de réplication. Par défaut ce dernier est nomenclature `master.info`.
`replicate-do-table=db_name.table_name`	Indique au thread slave de restreindre la réplication à une table spécifique. Pour spécifier plusieurs tables, inscrire cette option plusieurs fois pour chaque table
`replicate-ignore-table=db_name.table_name`	Indique au thread slave d’ignorer certaines tables dans le système de réplication. Pour spécifier plusieurs tables, inscrire cette options plusieurs fois pour chaque table
`replicate-wild-do-table=db_name.table_name`	Indique au thread slave de restreindre la réplication aux tables respectant les critères données. Exemple : `replicate-wild-do-table=db%.tb_%`. La réplication ne s’appliquera que pour les tables dont le nom commence par `tb_` et pour les bases de données dont le nom commence par `db_`.
`replicate-wild-ignore-table=db_name.table_name`	Indique au thread slave d’ignorer dans la réplication les tables respectant les critères données. Exemple : `replicate-wild-ignore-table=db%.tb_%`. La réplication ne s’appliquera pas pour les tables dont le nom commence par `tb_` et pour les bases de données dont le nom commence par `db_.`
`replicate-ignore-db=database_name`	Indique au thread slave d’ignorer une base de données pour la réplication. Pour spécifier plus d’une base de données, inscrire cette option pour chaque base de données à ignorer.
`replicate-do-db=database_name`	Indique au thread slave de restreindre la réplication à une base de données. Pour spécifier plus d’une base de données, inscrire cette option pour chaque base de données.
`log-slave-updates`	Indique au thread slave d’écrire dans le log binaire les mises à jour. Par défaut cette option est appliquée à `off`.
`replicate-rewrite-db=from_name-> to_name`	Cette option permet d’effectuer une mise à jour d’une base de données du serveur `master` vers une autre base de données du serveur esclave portant un nom différent
`slave-skip-errors=err_code1,err_code2`	Disponible seulement depuis la version 3.23.47. Cette option permet d’indiquer au thread slave de poursuivre la réplication lorsque la requête rencontre un ou des codes erreur donnés. Normalement, la réplication s’arrête à la première erreur rencontrée. Utiliser la commande `SHOW SLAVE STATUS` pour de plus amples informations. Tous les codes erreur sont recensés dans le fichier `/docs/mysqld_error.txt`. Exemple : `slave-skip-errors=1062,1053 \| all` Cette option doit être exceptionnelle dans une réplication.
`skip-slave-start`	Permet d’indiquer au serveur esclave de ne pas démarrer le thread slave à son démarrage. L’utilisateur démarrera ce dernier avec la commande `SLAVE START` après d’éventuelles correction d’erreurs.
`slave_read_timeout=#`	Nombre de secondes que le thread slave doit attendre pour les données du serveur `master` avant de tomber en timeout et d’abandonner la lecture.