Réplication de bases de données MySQL et séparation des requêtes en lecture / écriture
La réplication MySQL consiste à avoir en temps réel deux bases de données MySQL identiques afin de pouvoir basculer sur un deuxième serveur en cas de défaillance du premier.
La réplication MySQL est basée sur le fait que le serveur va garder la trace de toutes les évolutions de vos bases (modifications, effacements, etc.) dans un fichier de log binaire et les esclaves vont lire les requêtes du maître dans ce fichier de log, pour pouvoir exécuter les mêmes requêtes sur leurs copies. Il est très important de comprendre que le fichier de log binaire est simplement un enregistrement des modifications depuis un point fixe dans le temps (le moment où vous activez le log binaire). Tous les esclaves que vous activez auront besoin de la copie des données qui existaient au moment du démarrage du log. Si vous démarrez vos esclaves sur sans qu’ils ne disposent des données identiques à celles du maître au moment du démarrage du log binaire, votre réplication va échouer.
Configuration de mysql
Il est important de noter que ce dossier s’applique à une configuration de cluster de base de données à partir de zéro.
On ne détaillera pas ici comment répliquer une base qui n’est pas vide initialement.
Nous noterons les ip suivantes pour la configuration:
IP Serveur A (MASTER): 192.168.0.1
IP Serveur B (SLAVE): 192.168.0.2
Prérequis
Sur l’ensemble des serveurs, 1 maître et N esclaves, avoir installé et configuré votre MySQL avec la conf par défaut.
Il faudra donc avoir crée votre base de données, ses tables, ainsi qu’avoir inséré les données de paramètrage en base (En un mot avoir éxécuté le script DDL de votre application).
Sur l’instance master (192.168.0.1)
1/ On configure le premier mysql-server:
Sur une configuration MySql 5.0+ classique, on trouvera ces lignes dans le fichier /etc/mysql/my.cnf (partie [mysqld])
Décommenter server-id = 1, indiquant que le server va maintenant s’executer en mode « Réplication » et se trouve dans la position MASTER.
Puis décommenter également log_bin, indiquant l’emplacement du fichier où le MASTER enregistre les requêtes qu’il reçoit afin que chacun des SLAVES puisse les rejouer.
[mysqld] ... # The following can be used as easy to replay backup logs or for replication. # note: if you are setting up a replication slave, see README.Debian about # other settings you may need to change. server-id = 1 log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M
2/ Après s’être identifié sur le mysql-client du MASTER, on crée un utilisateur pour la réplication (id ‘repl’ et mot de passe ‘repl’ à changer pour plus de sécurité):
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY 'repl';
3/ On redémarre le service MySql, on s’identifie dans le mysql-client:
$ sudo service mysql restart Stopping MySQL database server: mysqld. Starting MySQL database server: mysqld. Checking for corrupt, not cleanly closed and upgrade needing tables.. $ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 47 Server version: 5.1.66-0+squeeze1-log (Debian) Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
et on vérifie le statut Master de notre instance:
mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 0 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
Sur l’instance slave (192.168.0.2)
Sur cette machine, on édite également la configuration: /etc/mysql/my.cnf (partie [mysqld])
Nous allons indiquer que le serveur slave est le serveur numéro 2 (server-id=2) – Il faudrait incrémenter ainsi de suite s’il existait d’autres serveurs slaves.
Et nous indiquons également l’IP du serveur MASTER, ainsi que son port.
Enfin, l’id et le mot de passe définis précédement, et permttant à l’esclave d’accéder aux requêtes enregistrées dans les fichiers de log du maître.
[mysqld] ... # The following can be used as easy to replay backup logs or for replication. # note: if you are setting up a replication slave, see README.Debian about # other settings you may need to change. # On laisse ceci comme commentaire #server-id = 1 #log_bin = /var/log/mysql/mysql-bin.log ########################## # Replication ########################## server-id=2 master-host = 192.168.0.1 master-port = 3306 master-user = repl master-password = repl ########################### expire_logs_days = 10 max_binlog_size = 100M
On redémarre le service et on se connecte au client MySQL:
$ sudo service mysql restart Stopping MySQL database server: mysqld. Starting MySQL database server: mysqld. Checking for corrupt, not cleanly closed and upgrade needing tables.. $ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. ... mysql>SHOW SLAVE STATUS \G;
La commande « SHOW SLAVE STATUS \G; » doit afficher les lignes suivantes:
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 106
Relay_Log_File: mysqld-relay-bin.000011
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1
Relay_Log_Space: 1000
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Notez en lignes 12 et 13 les déclarations suivantes
Slave_IO_Running: Yes Slave_SQL_Running: Yes
Indiquant que l’esclave écoute bien le maître.
Si vous apercevez une erreur aux lignes:
Last_Errno: Last_Error:
vous aurez la dernière erreur MySQL rencontrée pour vous permettre d’analyser et débuguer
Vérification de la réplication des données et de la séparation lecture / écriture
Une classe de test (Java) simple va nous permettre de vérifier le comportement de notre cluster.
Des constantes sont disponibles en début de fichier pour configurer les URL respectives du master et du slave.
Enfin on peut également vérifier dans ce test que la réplication a bin lieu « rapidement » (<50ms).
Un temps de réplication lent (>50ms) pourrait en effet générer des soucis, par exemple:
1/ Une première action de l’utilisateur se traduit par un enregistrement en base,
2/ Puis par une lecture quasi simultanée de cette donnée afin de la renvoyer pour présentation.
La donnée lue sur l’instance SLAVE peut ne pas avoir encore été répliquée… et dans ce cas la vue affiche une donnée érronée au client.
Si le cas se présente, il faut alors envisager de pourvoir « flaguer » qu’une action doit s’effectuer uniquement sur le master (écriture puis lecture), par exemple en utilisant une connexion READ/WRITE:
conn.setReadOnly(false);
En prérequis, avoir une ligne en base de donnée (table Account), avec un utilisateur d’ID ‘1’
package fr.dijit.mysql.test;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.Properties;
import java.util.Random;
import org.junit.Ignore;
import org.junit.Test;
import com.mysql.jdbc.ReplicationDriver;
public class MySqlReplicationTest {
private static final String MASTER_URL = "88.164.236.245:3304";
private static final String SLAVE_URL = "88.164.236.245:3305";
private static final int TIME_WAITING_FOR_REPLICATION = 2; // In millisecond
private static final int NUMBER_OF_LOOPS = 1000;
@Test
public void testReplicationReadWrite() throws Exception {
ReplicationDriver driver = new ReplicationDriver();
Properties props = new Properties();
// We want this for failover on the slaves
props.put("autoReconnect", "true");
// We want to load balance between the slaves
props.put("roundRobinLoadBalance", "true");
props.put("user", "root");
props.put("password", "root");
// Looks like a normal MySQL JDBC url, with a
// comma-separated list of hosts, the first
// being the 'master', the rest being any number
// of slaves that the driver will load balance against
Connection conn = driver.connect("jdbc:mysql:replication://" + MASTER_URL + "," + SLAVE_URL + "/thalys", props);
// Perform read/write work on the master
// by setting the read-only flag to "false"
conn.setAutoCommit(false);
int nbFailure = 0;
for (int i = 0; i < NUMBER_OF_LOOPS; i++) {
String lastNameInMaster = generateString(new Random(), LETTERS, 12);
// First, do a update in the master
conn.setReadOnly(false);
conn.createStatement().executeUpdate("UPDATE account SET lastname='" + lastNameInMaster + "' WHERE id='1';");
conn.commit();
Thread.sleep(TIME_WAITING_FOR_REPLICATION);
// Second, do a query from a slave, the driver automatically picks one from the list
conn.setReadOnly(true);
ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM account WHERE id='1'");
while (rs.next()) {
String lastnameInSlave = rs.getString("lastname");
System.out.print("Master vs. Slave database content: [" + lastNameInMaster + "|" + lastnameInSlave + "]");
if (!lastNameInMaster.equals(lastnameInSlave)) {
nbFailure++;
System.out.print(" >>>> Replication did not complete on time!");
}
System.out.print("\n");
// If we want the test to fail when replication is KO
// assertEquals(lastNameInMaster, rs.getString("lastname"));
}
}
System.out.println("########################");
System.out.println("Total number of failures:\t\t\t" + nbFailure);
System.out.println("Total number of tries:\t\t\t\t" + NUMBER_OF_LOOPS);
System.out.println("Replication that did not complete on time (%):\t" + ((double) nbFailure * 100 / NUMBER_OF_LOOPS) + "%");
}
@Test
public void testReplicationReadOnly() throws Exception {
ReplicationDriver driver = new ReplicationDriver();
Properties props = new Properties();
// We want this for failover on the slaves
props.put("autoReconnect", "true");
// We want to load balance between the slaves
props.put("roundRobinLoadBalance", "true");
props.put("user", "root");
props.put("password", "root");
// Looks like a normal MySQL JDBC url, with a
// comma-separated list of hosts, the first
// being the 'master', the rest being any number
// of slaves that the driver will load balance against
Connection conn = driver.connect("jdbc:mysql:replication://" + MASTER_URL + "," + SLAVE_URL + "/thalys", props);
// Perform read/write work on the master
// by setting the read-only flag to "false"
conn.setAutoCommit(false);
int nbFailure = 0;
for (int i = 0; i < NUMBER_OF_LOOPS; i++) {
// Do only readonly queries, and check with wireshark that SLAVE only receives queries
conn.setReadOnly(true);
ResultSet rs = conn.createStatement().executeQuery("SELECT * FROM account WHERE id='1'");
while (rs.next()) {
String lastnameInSlave = rs.getString("lastname");
System.out.println("Slave content: " + lastnameInSlave);
}
}
System.out.println("########################");
System.out.println("Total number of failures:\t\t\t" + nbFailure);
System.out.println("Total number of tries:\t\t\t\t" + NUMBER_OF_LOOPS);
System.out.println("Replication that did not complete on time (%):\t" + ((double) nbFailure * 100 / NUMBER_OF_LOOPS) + "%");
}
private static final String LETTERS = "abcdefghijklmnopqrstuvwxyz";
private static String generateString(Random rng, String characters, int length) {
char[] text = new char[length];
for (int i = 0; i < length; i++) {
text[i] = characters.charAt(rng.nextInt(characters.length()));
}
return new String(text);
}
}
L’execution de cette classe doit permettre de vérifier que l’écriture, puis la lecture d’une colonne en base donne bien la même valeur.
Si ce n’est pas le cas, la réplication n’a pas pu avoir lieu à temps, ce qui peut arriver si l’on abaisse le temps d’attente de réplication (TIME_WAITING_FOR_REPLICATION) à 0 millisecondes.
Exemple de résultat avec TIME_WAITING_FOR_REPLICATION = 0 (ms):
Master vs. Slave database content: [fsyomiuybpov|fsyomiuybpov] Master vs. Slave database content: [paolvegzlrey|paolvegzlrey] Master vs. Slave database content: [rhsqjfnubzgc|rhsqjfnubzgc] Master vs. Slave database content: [vqepvlyxsrid|vqepvlyxsrid] ... Master vs. Slave database content: [bxjqtjaqfptc|bxjqtjaqfptc] Master vs. Slave database content: [ohebcghtklis|bxjqtjaqfptc] >>>> Replication did not complete on time! Master vs. Slave database content: [prnfbibqbsdq|prnfbibqbsdq] Master vs. Slave database content: [ywcvuyctetab|prnfbibqbsdq] >>>> Replication did not complete on time! Master vs. Slave database content: [losblekkpamp|losblekkpamp] Master vs. Slave database content: [atebzgmpauon|atebzgmpauon] Master vs. Slave database content: [mlodfooufxkc|mlodfooufxkc] Master vs. Slave database content: [kkzxkisyddjj|kkzxkisyddjj] Master vs. Slave database content: [bzimyxbvnzzx|bzimyxbvnzzx] Master vs. Slave database content: [ljmjbjuxdngm|ljmjbjuxdngm] ######################## Total number of failures: 11 Total number of tries: 100 Replication that did not complete on time (%): 11.0%
Nous pouvons sur la base de cette configuration, noter le temps nécessaire et suffisant pour qu’une réplication puisse être effective dans 100% des cas:
X: temps entre écriture et lecture
Y: Nombre de fois (sur 1000), ou la donnée lu sur le SLAVE n’est pas celle du MASTER.
Vérification de la séparation lecture / écriture
Une execution du code précédent en n’effectuant que des opérations de lecture montre bien que l’ensemble des lectures sont réalisées sur le SLAVE:
Sur l’image:
> à gauche le nombre d’opération réseau effectuée en MASTER (58 opérations)
> à droite le nombre d’opération réseau effectuée en SLAVE (30053 opérations)
pour 10 000 lectures d’une même donnée.
Configuration de l’application
L’url de connexion dans le fichier de configuration sera de la forme:
jdbc:mysql:replication://master_ip:master_port,slave1_ip:slave1_port, slave2_ip:slave2_port...etc.../databasename
Et le fichier de configuration doit aussi préciser le pilote MySQL (com.mysql.jdbc.ReplicationDriver, spécialisé dans la réplication), autoReconnect pour tenter la reconnection en cas coupure, et enfin roundRobinLoadBalance afin de réaliser un round robin entre les slaves lors des lectures.
## REPLICATION CONFIGURATION db.default.driver="com.mysql.jdbc.ReplicationDriver" # We want this for failover on the slaves db.default.autoReconnect=true # We want to load balance between the slaves db.default.roundRobinLoadBalance=true
Voilà c’est tout pour aujourd’hui.
J’espère que tout cela vous sera utile.
Pour info j’ai monté mes environnements de test sur deux VM virtualbox nattées.
Pour les joindre, obligé donc de passer par le net, car un sous réseau ne fonctionne pas avec des VMs nattées.
J’ai lu qu’un mode bridge pouvait fonctionner, si vous en savez plus sur le sujet, merci de joindre de l’info en commentaire 😉 !
YaBientôt!
