Formation MySQL : amelioration de l'evolutivite et de la disponibilite avec MySQL 5.5

Formation  MySQL : amélioration de l'évolutivité et de la disponibilité avec MySQL 5.5

...

Principes fondamentaux de la réplication

Pour les besoins de ce livre, nous définissons « réplication » comme la duplication des données vers un ou plusieurs emplacements. Dans les sections suivantes, nous présentons ce qui différencie les différents types de réplication les plus populaires.

La réplication permet à une base de données de copier ou de dupliquer des changements d'un emplacement physique ou d'un système vers un autre (généralement depuis un système « maître » vers un système « esclave »). Elle permet généralement d'accroître la disponibilité et l'évolutivité d'une base de données, bien que souvent les utilisateurs exécutent aussi des opérations de sauvegarde ou des requêtes d'analyse sur les systèmes esclaves, déchargeant ainsi ces fonctions des systèmes maîtres.

MySQL prend en charge en natif la réplication en tant que fonctionnalité standard de la base de données. Selon la configuration, vous pouvez répliquer toutes les bases de données, une sélection de bases de données ou même des tables sélectionnées dans une base de données.

La réplication MySQL fonctionne simplement avec un serveur agissant en tant que maître et un ou plusieurs serveurs agissant en tant qu'esclaves. Le serveur maître consigne les changements dans la base de données. Une fois ces modifications consignées, elles sont envoyées vers le ou les esclaves, puis appliquées immédiatement ou après un délai défini.

L'utilisation de la réplication MySQL permet de déployer horizontalement d'importantes fermes de serveurs Web dans lesquelles les lectures (SELECTs) représentent la majeure partie des opérations effectuées dans la base de données. Les esclaves représentent une charge de travail infime sur les serveurs maîtres (généralement 1 % de charge de travail par esclave). Il n'est pas rare d'avoir 30 esclaves déployés par maître dans les sites Web importants'.

Réplication asynchrone

MySQL prend en charge en natif la réplication à sens unique, asynchrone. La réplication asynchrone signifie que les données sont copiées d'une machine à une autre, entraînant un délai dans la copie des modifications de données. Les données risquent surtout de ne pas être copiées vers / appliquées à l'esclave, lorsque la validation de la transaction est confirmée à l'application. Ce délai dépend souvent de la bande passante réseau, de la disponibilité des ressources et de la charge du système. Cependant, avec les composants et un réglage appropriés, la réplication elle-même peut sembler presque instantanée pour de nombreuses applications.

 Réplication synchrone

La réplication synchrone correspond à la validation simultanément des données sur une ou plusieurs machines, généralement connue sous le nom « validation à deux phases ». Cette réplication permet d'optimiser la cohérence entre plusieurs systèmes, mais l'augmentation des messages pénalise les performances.

Avec les moteurs de stockage InnoDB ou MyISAM, MySQL ne prend pas en charge en natif la réplication synchrone. Il existe toutefois des technologies, telles que Distributed Replicated Block Device ou DRBD, qui fournissent une réplication synchrone du système de fichiers sous-jacent permettant à un deuxième serveur MySQL de prendre le relais (à l'aide de cette deuxième copie) en cas de perte de la copie/du serveur initial. Pour plus d'informations, reportez-vous à :

Si vous utilisez MySQL Cluster, les données sont répliquées de façon synchrone entre les nœuds de données dans le Cluster (site), puis de façon asynchrone entre des Clusters géographique¬ment séparés.

Réplication semi-synchrone

La réplication semi-synchrone est une nouvelle fonctionnalité de MySQL 5.5 (au moment de la rédaction de ces lignes, MySQL 5.5 est une version Candidate). Cela signifie que si la réplication semi-synchrone est activée sur le maître et qu'un esclave semi-synchrone au moins est configuré, un thread exécute une validation de transaction sur les blocs maîtres une fois la validation appliquée localement et attend jusqu'à ce qu'un esclave semi-synchrone au moins renvoie au maître la confirmation de réception de tous les événements associés à la transaction, ou jusqu'à une expiration. En cas d'expiration, le maître valide transaction mais rétablit le mode asynchrone.

En utilisant la réplication asynchrone, si le maître tombe en panne, il n'est pas possible de savoir immédiatement si les transactions validées par le maître ont été répliquées sur l'esclave. Par conséquent, une reprise d'un maître vers un esclave peut entraîner une reprise à partir d'un serveur qui ne contient pas toutes les transactions relatives au maître. La réplication semi¬synchrone réduit le risque de transactions « orphelines » sur le serveur maître, car toutes les transactions validées ont été reçues par l'esclave. En d'autres termes, pour tout client thread, les modifications effectuées sur le maître lors de sa transaction « en cours » sont perdues (le client doit réessayer), mais toutes les modifications de transactions, dont la validation a été confirmée, sont préservées.

La réplication semi-synchrone a un impact sur les performances car les validations sont plus lentes en raison de la nécessité d'attendre les esclaves. Il s'agit de la contrepartie de l'augmentation de l'intégrité des données. Le ralentissement correspond au moins au délai TCP/IP aller-retour d'envoi de la validation à l'esclave, d'enregistrement par l'esclave dans son journal de relais et d'attente de l'accusé de réception de l'esclave. Par conséquent, la réplication semi-synchrone est plus efficace avec des serveurs physiquement colocalisés qui communiquent via des réseaux rapides.

La réplication semi-synchrone n'est pas disponible actuellement pour les tables utilisant le moteur de stockage MySQL Cluster.

Réplication par instruction

Par défaut, MySQL exploite la réplication par instruction lorsque les instructions SQL (pas les modifications de données) sont répliquées à partir du maître vers le ou les esclaves. La réplication par instruction est incluse dans MySQL server depuis la version 3.23.

L'un des avantages de la réplication par instruction est la réduction, dans certains cas, de la quantité des données écrites dans les fichiers journaux, par exemple lorsque des mises à jour ou

des suppressions affectent de nombreuses lignes. Pour des instructions simples qui affectent quelques lignes seulement, la réplication par ligne peut occuper moins d'espace.

La réplication par instruction présente toutefois quelques inconvénients, notamment l'absence de prise en charge des instructions au comportement non déterministe, par exemple une fonction d'heure actuelle.

Réplication par ligne

Introduite par la version MySQL 5.1, la réplication par ligne consigne les modifications dans des lignes de table individuelles, contrairement aux instructions. Avec la réplication par ligne, le maître écrit des messages, également connus en tant qu'événements dans le journal binaire, qui indiquent comment les lignes de table individuelles ont été modifiées. Elle s'apparente aux formes plus traditionnelles de réplication incluses dans d'autres RDMS. Généralement, la réplication par ligne nécessite moins de verrous sur le maître et l'esclave, ce qui permet d'atteindre un niveau de concurrence plus élevé. L'un des inconvénients de la réplication par ligne est qu'elle génère habituellement un volume de données à consigner plus important. Par exemple, une instruction qui modifie 100 lignes dans une table représente 100 changements à consigner avec la réplica¬tion par ligne, alors qu'avec la réplication par instruction seule l'instruction SQL doit être répliquée.

Avant la version MySQL 5.5, les types de colonne devaient être identiques sur le maître et l'esclave lors de l'utilisation de la réplication par ligne. Depuis la version 5.5, vous pouvez autori-ser ou non le type promotion et/ou rétrogradation, ou imposer le contrôle de type strict.

Si MySQL Cluster est utilisé, la réplication par ligne doit être utilisée.

Réplication en format mixte

Depuis la version MySQL 5.1.8, le format de journalisation binaire peut être modifié en temps réel, selon l'événement à consigner, en utilisant la journalisation en format mixte. Avec le format mixte activé, la réplication par instruction est utilisée par défaut, mais elle bascule automatiquement vers une réplication par ligne dans certaines conditions, par exemple :

• Une instruction DML met à jour une table MySQL Cluster
• Une instruction contient un objet UUID()
• Deux tables ou plus avec des colonnes AUTO_INCREMENT sont mises à jour
• Un INSERT DELAYED est exécuté, quel qu'il soit
• Lorsque le contenu d'une vue nécessite la réplication par ligne, l'instruction qui crée la vue l'utilise également, par exemple lorsque l'instruction créant une vue utilise la fonction UUID()
• Un appel à une fonction définie par l'utilisateur (UDF) est effectué

...

Haute disponibilité

Dans ce scénario, l'idée est de répliquer les modifications depuis un serveur maître vers un esclave afin de basculer sur le serveur esclave en cas de déconnexion du maître suite à une erreur, un incident ou pour effectuer une maintenance.

Comme pour le déploiement horizontal, la sélection du serveur approprié peut être mise en œuvre de différentes façons. Par exemple, si vous utilisez Connector/J avec la configuration illustrée dans la Figure 9 (« noir » est le maître et « bleu » est l'esclave), l'application peut utiliser « jdbc:mysql://black,blue/clusterdb » comme chaîne de connexion. Connector/J envoie ensuite toutes les opérations vers « noir » tant qu'il est disponible, puis bascule vers « bleu » lorsqu'il n'est pas disponible.

Réplication géographique

Avec la réplication géographique, l'objectif est de répliquer des données entre deux emplacements géographiquement distincts, généralement très éloignés. La réplication asynchrone est la solution la plus adaptée dans ce scénario basé sur l'impact potentiel de la latence du réseau. Par exemple, les données d'une agence centrale à Paris sont répliquées vers une agence régionale à Marseille, autorisant ainsi des requêtes d'exécution à partir d'un magasin de données local. Cette approche est également idéale pour mettre en œuvre la récupération d'urgence en cas de catastrophe sur l'un des sites (par exemple une panne électrique ou une catastrophe naturelle).

 Figure 4 Réplication pour redondance géographique

Sauvegarde de base de données

Pour éviter toute détérioration des performances ou verrouillage qu'une sauvegarde sur le maître peut engendrer, vous pouvez exécuter à la place la sauvegarde sur le serveur esclave. Notez que les sauvegardes en ligne peuvent être exécutées sur la base de données maître en utilisant MySQL Cluster.

Analyses

De nombreuses requêtes d'informatique décisionnelle ou d'analyse peuvent être exigeantes en ressources et nécessiter des délais d'exécution importants. Pour répondre à ces requêtes d'analyses, il est possible de créer des esclaves. Dans cette configuration, l'exécution des requêtes n'a aucun impact sur les performances du maître.

Cette méthode peut se révéler très utile avec MySQL Cluster qui est idéal pour les applications utilisant principalement une Clé Primaire basée sur les lectures et écritures, mais dont l'exécution peut être lente avec des requêtes très complexes sur de vastes ensembles de données. Il suffit de répliquer les données MySQL Cluster vers un deuxième moteur de stockage (généralement MyISAM ou InnoDB), puis de générer vos rapports à cet emplacement. Cette action peut être exécutée tout en répliquant des données vers un site MySQL Cluster distant pour une redondance géographique, comme illustrée dans la Figure 5 ci-dessous.

…

Topologies de réplication

MySQL prend en charge diverses topologies de réplication. Nous présentons ci-dessous certaines de ces topologies et d'autres prises en charge avec des restrictions.

 Figure 6 Topologies de réplication MySQL courantes

Maître vers esclave

Cette topologie est la plus populaire et la plus facile en termes de configuration et d'administration. Elle implique deux serveurs, un maître et un esclave. Toutes les écritures sont exécutées sur le maître et les lectures peuvent être partagées entre le maître et l'esclave.

Maître vers plusieurs esclaves

Dans ce scénario, plusieurs esclaves sont associés à un seul maître. Cette topologie offre un degré de déploiement horizontal supérieur contre une administration accrue.

Maître vers esclave(s) vers esclave(s)

Cette configuration est une extension d'une configuration maître/esclave ou maître/esclaves. Dans ce cas, un ou plusieurs esclaves supplémentaires sont associés à l'esclave déjà associé au serveur maître d'origine. En réalité, le ou les esclaves au centre de cette configuration agissent à la fois comme maître et comme esclave. Dans cette configuration, toutes les écritures sont exécutées sur le maître principal.

Maître vers maître (plusieurs maîtres)

Dans une configuration maître/maître, deux serveurs sont associés dans une chaîne et sont réciproquement maîtres et esclaves. Cette configuration permet d'écrire indifféremment sur l'un ou l'autre système, mais comme la modification va être répliquée, le degré de complexité dans l'installation, la configuration et l'administration augmente de façon importante. De plus, sauf si vous utilisez MySQL Cluster, il n'existe pas de détection ni de résolution des conflits. Par conséquent, l'application doit s'assurer de ne pas mettre à jour une ligne sur un serveur alors qu'une modification apportée à la même ligne sur l'autre serveur n'est pas encore répliquée.

Il est également possible d'organiser plusieurs serveurs MySQL en anneau pour augmenter les niveaux d'évolutivité et de performance (à condition que l'application évite d'envoyer des mises à jour conflictuelles vers la même ligne sur différents serveurs). MySQL 5.5 présente une nouvelle fonctionnalité de filtrage qui permet de mieux gérer les échecs sur un serveur alors que la réplication de ses mises à jour est en cours vers les autres serveurs de l'anneau.

Figure 7 Anneau à maîtres multiples

Lors de l'utilisation de la réplication à maîtres multiples avec des colonnes d'auto-incrémentation, vous devez utiliser les paramètres auto_increment_offset et auto_increment_increment sur chaque serveur pour vous assurer qu'aucune valeur en double n'est affectée. Un exemple pour 2 serveurs (noir et bleu) est illustré dans la Table 1 ci-dessous.

Maîtres multiples vers esclave (source multiple)

Cette topologie de réplication n'est pas prise en charge actuellement par MySQL. Dans une configuration à maîtres multiples, un esclave « sert deux maîtres » principalement, en d'autres termes il réplique les modifications à partir de plusieurs maîtres.

MySQL Cluster permet à plusieurs serveurs MySQL d'écrire sur le même Cluster. Par conséquent, il est possible de configurer cette fonctionnalité (alors que chaque serveur MySQL est l'esclave d'un seul maître) si elle est requise pour une application appropriée.

6 Réplication de flux de travail interne

Avec la réplication MySQL, le maître écrit des mises à jour dans ses fichiers journaux binaires et gère un index de ces fichiers afin de conserver une trace de la rotation des journaux. Les fichiers journaux binaires servent de registre de mises à jour à envoyer aux serveurs esclaves. Lorsqu'un esclave se connecte à son maître, il détermine la dernière position lue dans les journaux en fonction de sa dernière mise à jour réussie. L'esclave reçoit ensuite toutes les mises à jour qui ont eu lieu depuis cette date. L'esclave se bloque par la suite et attend que le maître l'informe de nouvelles mises à jour. Une illustration simple de ces concepts est présentée dans la Figure 8 ci-dessous.

 Figure 8 Mise en œuvre de la réplication MySQL

Threads de réplication

Plusieurs threads sont utilisés pour mettre en œuvre la réplication de mises à jour depuis le maître vers le ou les esclaves. Chaque thread est décrit dans cette rubrique. Si vous utilisez MySQL Cluster, un thread supplémentaire est impliqué. Pour plus d'informations, reportez-vous à la section 11.

Thread Binlog Dump

Le maître crée ce thread pour envoyer le contenu du journal binaire à l'esclave. Le thread Binlog Dump acquiert un verrou dans le journal binaire du maître pour lire chaque événement à envoyer à l'esclave. Dès que l'événement est lu, le verrou est annulé, même avant que l'événement ne soit envoyé à l'esclave. Un maître auquel plusieurs esclaves sont « raccordés » crée un thread de vidage binlog pour chaque esclave actuellement connecté, chaque esclave disposant de ses propres threads I/O et SQL.

Thread I/O esclave

Lorsqu'une instruction START SLAVE est émise sur l'esclave, elle crée un thread I/O qui se connecte au maître et lui demande d'envoyer les mises à jour enregistrées dans ses journaux binaires. Le thread I/O esclave lit ensuite les mises à jour que le thread de vidage binlog du maître envoie, puis les copie localement sur l'esclave en tant que journaux de relais dans le répertoire de données de l'esclave.

Thread SQL esclave

L'esclave crée ce thread pour lire les journaux de relais écrits par le thread I/O esclave, et exécute les mises à jour contenues dans les journaux de relais.

Réplication de fichiers journaux

Durant la réplication, le serveur MySQL crée plusieurs fichiers qui sont utilisés pour conserver le journal binaire relayé depuis le maître, et enregistre les informations relatives à l'état et l'emplacement actuel dans le journal relayé.

Trois types de fichiers sont utilisés dans le processus par l'esclave :

le journal de relais

Le journal de relais sur l'esclave contient des événements qui ont été lus sur le journal binaire du maître. Les événements dans le journal binaire sont finalement exécutés sur l'esclave par son thread SQL.

master.info

Les informations relatives à l'état et à la configuration actuelle de l'esclave se situent dans le fichier master.info. Ce fichier contient les informations de connectivité de réplication de l'esclave, y compris le nom d'hôte du maître, les identifiants de connexion utilisés et la position actuelle de l'esclave dans le journal binaire du maître.

relay-log.info

Les informations d'état relatives au point d'exécution dans le journal de relais de l'esclave se situent dans le fichier relay-log.info.

Le journal binaire et le fichier d'index associé, pour le suivi de toutes les mises à jour à répliquer, se situent sur le maître.

7 Configuration de la réplication MySQL

Dans cette section, nous présentons une méthode de configuration de la réplication MySQL. Cette procédure est écrite pour la configuration d'un esclave unique, mais peut être répétée pour configurer plusieurs esclaves. Pour les besoins de ce guide, nous supposons que vous avez téléchargé et installé avec succès au moins deux serveurs MySQL.

Remarque au sujet de l'exemple

Pour les besoins de ce document, nous avons configuré deux serveurs MySQL 5.5 qui jouent les rôles suivants :

Maître

Nom d'hôte : noir IP : 192.168.0.31

Esclave

Nom d'hôte : bleu IP : 192.168.0.34

...

Étape 1: Configurer les fichiers cnf du maître et de l'esclave

La première étape de configuration de la réplication permet de modifier le fichier « my.cnf » sur les serveurs utilisés en tant que maître et esclave. Une valeur par défaut est fournie avec l'installation MySQL. Nous fournissons toutefois des fichiers locaux de configuration « master.cnf » et « slave.cnf », à utiliser au démarrage des serveurs MySQL si une base de données MySQL de production est déjà exécutée sur ces serveurs.

Au minimum, nous souhaitons ajouter deux options à la section [mysqld] du fichier master.cnf :

• log-bin : dans cet exemple, nous choisissons black-bin.log
• server-id : dans cet exemple, nous choisissons 1. Le serveur ne peut pas agir en tant que maître si la journalisation binaire n'est pas activée. La variable server_id doit être un nombre entier positif comprise entre 1 to 232

master.cnf :

[mysqld]

server-id=1

log-bin=black-bin.log

datadir=/home/billy/mysql/master/data

innodb_flush_log_at_trx_commit=1

sync_binlog=1

Remarque : Pour optimiser la durabilité et la cohérence dans la configuration d'une réplication utilisant InnoDB avec les transactions, vous devez également spécifier les options innodb_flush_log_at_trx_commit=1, sync_binlog=1.

Vous devez ensuite ajouter l'option server-id à la section [mysqld] du fichier slave.cnf de l'esclave. La valeur server-id, comme la valeur master_id, doit être un nombre entier positif compris entre 1 et 232 - 1. Il est également nécessaire que l'ID de l'esclave soit différent de celui du maître. Si vous configurez plusieurs esclaves, chacun doit avoir une valeur server-id unique, distincte de celle du maître et des autres esclaves. Les valeurs server-id peuvent être comparées à des adresses IP : ces ID identifient de manière unique chaque instance de serveur dans la communauté des serveurs de réplication.

Vous pouvez également définir les noms de fichiers à utiliser par l'esclave en définissant relay-log-index et relay-log.