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.
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
...
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
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.
D
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
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
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
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 :
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 pouvez également définir les noms de fichiers à utiliser par l'esclave en définissant relay-log-index et relay-log.
