Outils pour utilisateurs

Outils du site


docs:sql:mysql:master-master

Différences

Ci-dessous, les différences entre deux révisions de la page.

Lien vers cette vue comparative

docs:sql:mysql:master-master [2019/05/07 00:11] (Version actuelle)
yoann créée
Ligne 1: Ligne 1:
 +====== Mysql MASTER<>​MASTER ======
 +
 +Nous allons configurer une réplication entre deux Maîtres MySQL, identifiés par DB1 et DB2, sachant que dans cette configuration,​ chaque Maitre sera en même temps l'​esclave de l'​autre. Le gros avantage c'est qu'en cas de reboot, ou de plantage, à la reprise les bases se resynchroniserons automatiquement.
 +
 +Ce schéma est plus expressif:​\\
 +{{:​docs:​mysql:​mysql_master-master-0.png}}
 +
 +Le grand avantage de cette architecture est d'​avoir deux bases de données sur lesquelles on peut écrire en parallèle sans se soucier de la synchronisation,​ la réplication se charge de synchroniser vers DB2 ce qu'on écrit sur DB1, et vice versa.\\
 +On pourra utiliser MySQL Proxy qui se placera comme partageur de charge entre ces deux bases de données.
 +
 +On part du principe que le serveur DB1 est en production et qu’il contient déjà des bases (ou pas) et que les logs binaires ne sont pas activés. DB2 quand à lui est tout neuf avec mysql fraichement d'​installé.
 +
 +  aptitude install mysql-server-5.5
 +
 +Toutes les requêtes SQL seront exécutés avec le compte root :
 +  mysql -u root -p
 +
 +
 +===== Configuration de DB2 =====
 +
 +On arrete le service mysql
 +  /​etc/​init.d/​mysql stop
 +
 +On edite le fichier /​etc/​mysql/​my.cnf :
 +<​file>​
 +#​bind-address = 127.0.0.1
 +
 +server-id=2
 +auto_increment_offset=2
 +auto_increment_increment=2
 +log-bin=/​var/​log/​mysql/​master2-bin
 +log-slave-updates
 +master-host=192.168.1.93
 +master-port=3306
 +master-user=user-slave1
 +master-password=pwd-slave1
 +master-connect-retry=10 ​
 +</​file>​
 +
 +
 +Un utilisateur de maintenance (debian-sys-maint) est créé avec un mot de passe aléatoire, c’est avec ce compte que les scripts init.d arrêtent ou démarrent le serveur mysql. Comme on va faire une réplication totale (dump), ce compte ne va plus fonctionner.
 +
 +On va donc lui mettre le même mot de passe que sur DB1, on trouve ces informations dans le fichier /​etc/​mysql/​debian.cnf qu’il suffit de recopier à l’identique de DB1 sur DB2 (attention le mot de passe apparait 2 fois).
 +
 +On redemarre le service mysql
 +  /​etc/​init.d/​mysql start
 +
 +Il ne faut plus arrêter DB2 tant que l’opération n’est pas terminée.
 +
 +On arrête la réplication (qui ne pourrait pas fonctionner de toute manière à cette étape)
 +  # mysql -u root -p
 +  mysql> STOP SLAVE;
 +
 +===== Configuration de DB1 =====
 +On edite le fichier /​etc/​mysql/​my.cnf :
 +<​file>​
 +#​bind-address = 127.0.0.1
 +
 +server-id=1
 +auto_increment_offset=1
 +auto_increment_increment=2
 +
 +log-bin=/​var/​log/​mysql/​master1-bin
 +log-slave-updates
 +
 +master-host=192.168.1.153
 +master-port=3306
 +master-user=user-slave2
 +master-password=pwd-slave2
 +master-connect-retry=10
 +</​file>​
 +
 +Vous l'​aurez certainement remarqué, auto_increment_offset et auto_increment_increment sont les clés du bon fonctionnement d'une réplication à double maîtres, car dans ce type de réplication et lors de l'​écriture simultanée sur les deux maîtres, on aura facilement des collisions au niveau des clés auto incrémentées (auto_increment) menant à l'​échec la réplication.\\
 +Prenons l'​exemple de cette requête SQL :
 +  mysql> INSERT INTO personne (id, name) VALUES (null, '​Mahmoud'​);  ​
 +
 +Sachant que la colonne ID est une clé primaire auto incrémentée,​ à son exécution sur DB1, la colonne ID aura la valeur '​1'​.
 +Et si on exécute une autre requête sur DB2 comme suit :
 +  mysql> INSERT INTO personne (id, name) VALUES (null, '​Fourat'​);  ​
 +
 +La colonne ID sera incrémentée et aura la valeur '​1',​ ce qui provoquera un conflit dans la réplication car le la deuxième ligne insérée sur DB2 ne pourra jamais synchronisée sur DB2 puisque la colonne ID est une clé primaire et donc unique.\\
 +Pour remédier à ce problème il faut appliquer configurer auto_increment_offset et auto_increment_increment en tenant compte de cette règle:
 +
 +Pour une configuration à N maîtres, mettre:
 +  * auto_increment_increment = N (sur chaque maître)
 +  * auto_increment_offset = 1,2, ... , N (chaque maître aura une valeur unique)
 +
 +\\
 +\\
 +
 +
 +On continu en relancant le serveur et on vérifie que tout est OK
 +  /​etc/​init.d/​mysql restart
 +
 +A partir de ce moment, toutes les modifications seront sauvées dans les logs binaires. Si votre base bouge beaucoup, il vaut mieux en couper l’accès le temps de finir l’opération.
 +
 +
 +On arrête la réplication (qui ne pourrait pas fonctionner de toute manière à cette étape)
 +  # mysql -u root -p
 +  mysql> STOP SLAVE;
 +
 +On créer nos utilisateurs de réplication :
 +  mysql> GRANT REPLICATION SLAVE ON *.* TO '​user-slave2'​@'​192.168.1.153'​ IDENTIFIED BY '​pwd-slave2'​ ; 
 +  mysql> GRANT REPLICATION SLAVE ON *.* TO '​user-slave1'​@'​192.168.1.93'​ IDENTIFIED BY '​pwd-slave1'​ ;
 +
 +On fait un dump afin de donner à DB2 toutes les données d’avant l’activation des logs binaires :
 +  # mysqldump -u root -p --add-drop-database --master-data=2 --all-databases --lock-all-tables > /​tmp/​all-databases.sql
 +
 +On copie le dump sur DB2 :
 +  # scp /​tmp/​all-databases.sql root@db2:/​tmp/​all-databases.sql
 +
 +On import le dump sur DB2 :
 +  # mysql -u root -p < /​tmp/​all-databases.sql
 +
 +
 +===== Lancement de la réplication DB1 > DB2 =====
 +
 +**Sur DB1 :**
 +  # mysql -u root -p
 +
 +  mysql> SHOW MASTER STATUS\G;
 +  File: master1-bin.000001
 +  Position: 254
 +  Binlog_Do_DB:​
 +  Binlog_Ignore_DB:​
 +  1 row in set (0.20 sec) 
 +
 +**Sur DB2 :**
 +  # mysql -u root -p
 +
 +  mysql> CHANGE MASTER TO
 +  MASTER_HOST='​192.168.1.93',​
 +  MASTER_USER='​user-slave2',​
 +  MASTER_PASSWORD='​pwd-slave2',​
 +  MASTER_LOG_FILE='​master1-bin.000001',​
 +  MASTER_LOG_POS=254;​
 +  ​
 +  mysql> FLUSH PRIVILEGES;
 +  ​
 +  mysql> START SLAVE ;
 +
 +
 +===== Lancement de la réplication DB2 > DB1 =====
 +
 +**Sur DB2 :**
 +  # mysql -u root -p
 +
 +  mysql> SHOW MASTER STATUS\G;
 +  File: master2-bin.000001
 +  Position: 254
 +  Binlog_Do_DB:​
 +  Binlog_Ignore_DB:​
 +  1 row in set (0.20 sec)
 +
 +**Sur DB1 :**
 +  # mysql -u root -p
 +
 +  mysql> CHANGE MASTER TO
 +  MASTER_HOST='​192.168.1.153',​
 +  MASTER_USER='​user-slave1',​
 +  MASTER_PASSWORD='​pwd-slave1',​
 +  MASTER_LOG_FILE='​master2-bin.000001',​
 +  MASTER_LOG_POS=254;​
 +  ​
 +  mysql> FLUSH PRIVILEGES;
 +  ​
 +  mysql> START SLAVE ;
 +
 +
 +
 +===== Vérifier que tout s’est bien déroulé =====
 +
 +La mise en place de la réplication Master-Master est finie. Afin de s'​assurer que la réplication est bien fonctionnelle on peut executer cette requête sur les deux slaves\\
 +
 +  mysql> SHOW SLAVE STATUS\G;
 +
 +Les valeurs des deux colonnes Slave_IO_Running et Slave_SQL_Running doivent être Yes, la documentation de MySQL est mieux placée pour expliquer la signification de chaque colonne.
 +
 +Parallèlement,​ la valeur de Seconds_Behind_Master doit être prise en considération dans le cas ou vous voulez avoir une synchronisation en temps réel entre les deux bases de données, idéalement cette valeur doit être égale à zéro ce qui signifie que le Slave n'a aucune seconde de retard par rapport au Master.
 +
 +===== Ressources (wheezy) =====
 +http://​www.howtoforge.com/​installing-a-web-email-and-mysql-database-cluster-on-debian-6.0-with-ispconfig-3
 +avec les indications d'ici http://​www.howtoforge.com/​forums/​showthread.php?​t=62834
  
docs/sql/mysql/master-master.txt · Dernière modification: 2019/05/07 00:11 par yoann