Archives de catégorie : MySQL

MySQL – replication Master-Master

Pour faire suite à mon article sur la réplication Master-Slave, voici comme mettre en place une réplication Master-Master complète de manière à avoir 2 serveurs MySQL identiques en tout point.

Le principe est le même que précédemment, sauf que mes serveurs maîtres seront esclaves de leurs voisins.

Je vais prendre une architecture simple :

Master01 : 192.168.0.101
Master02 : 192.168.0.102

Master01 sera esclave de Master02 et vice versa.

Je pars du principe que le serveur Master01 est en production et qu’il contient déjà des bases avec des données critiques et que les logs binaires ne sont pas activés. Master02 quand à lui est tout neuf.

Attention, je n’ai testé cette procédure qu’avec des schémas assez simples. J’ai même réussi à désynchroniser les 2 serveurs en écrivant cette procédure (sans casse, il suffit de repartir d’un dump et de réaligner les 2 serveurs). Donc à réaliser avec prudence.

Première chose à faire, des sauvegardes :

mysqldump -u root -p --all-databases > all-databases.`date +%Y%m%d`.sql
cp /etc/mysql/my.cnf /etc/mysql/my.cnf.`date +%Y%m%d`

Configuration de Master02 :

Afin de réduire l’interruption de service, on commence par préparer le Master02.

On l’arrête :

service mysql stop

Dans la section [mysqld] :

bind-address                 = 0.0.0.0
server-id                      = 102
auto_increment_offset = 2
#Le pas des auto incréments, il faut le choisir de manière
#à ne pas avoir de collisions entre les différents masters
auto_increment_increment = 2
log-bin = /var/log/mysql/master02.bin
log-slave-updates
master-host = 192.168.0.101
master-port = 3306
master-user = replicationMM
master-password = replicationMMpassword
master-connect-retry = 10

On relance le serveur.

service mysql start

Dans le cas d’une Debian (je ne sais pas pour les autres distributions), 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 notre serveur. Comme on va faire une réplication totale, ce compte ne va plus fonctionner.

On va donc lui mettre le même mot de passe que sur Master01, on trouve ces informations dans le fichier /etc/mysql/debian.cnf qu’il suffit de recopier à l’identique sur Master02 (attention le mot de passe apparait 2 fois). Dès lors, il ne faut plus arrêter Master02 tant que l’opération n’est pas terminée.

On se connecte à Master02 pour arrêter la réplication (qui ne pourrait pas fonctionner de toute manière à cette étape) :

STOP SLAVE;

Configuration de Master01 :

Dans la section [mysqld] :

#bind-address = 127.0.0.1
bind-address = 0.0.0.0
server-id = 101
auto_increment_offset = 1
#Le pas des auto increments, il faut le choisir de manière
#à ne pas avoir de collisions entre les différents masters
auto_increment_increment = 2
log-bin = /var/log/mysql/master01.bin
log-slave-updates
master-host = 192.168.0.102
master-port = 3306
master-user = replicationMM
master-password = replicationMMpassword
master-connect-retry = 10

On relance le serveur et on vérifie que tout est OK (c’est un serveur de production).

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.

service mysql restart

On se connecte à Master01 en root afin d’arrêter la réplication (qui ne …) et de créer notre utilisateur de réplication :

STOP SLAVE;
GRANT REPLICATION SLAVE ON *.* TO 'replicationMM'@'192.168.0.101' IDENTIFIED BY 'replicationMMpassword';
GRANT REPLICATION SLAVE ON *.* TO 'replicationMM'@'192.168.0.102' IDENTIFIED BY 'replicationMMpassword';

Puis on fait un dump afin de donner à Master02 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 > all-databases.sql

Dump qu’on importe dans Master02 :

mysql -u root -p < all-databases.sql

Il est temps de lancer la réplication.

Sur Master01 :

SHOW MASTER STATUS /G

->File: master01.000001
->Position: 516

Sur Master02 :

CHANGE MASTER TO MASTER_HOST='192.168.0.101', MASTER_USER='replicationMM', MASTER_PASSWORD='replicationMMpassword', MASTER_LOG_FILE='master01.000001', MASTER_LOG_POS=516;
FLUSH PRIVILEGES;
START SLAVE;

Puis dans l’autre sens :

Sur Master02 :

SHOW MASTER STATUS /G

->File: master02.000001
->Position: 518

Sur Master01 :

CHANGE MASTER TO MASTER_HOST='192.168.0.102', MASTER_USER='replicationMM', MASTER_PASSWORD='replicationMMpassword', MASTER_LOG_FILE='master02.000001', MASTER_LOG_POS=518;
FLUSH PRIVILEGES;
START SLAVE;

Il faut maintenant vérifier que tout s’est bien déroulé.

Sur les 2 serveurs :

SHOW SLAVE STATUS /G

Slave_IO_Running et Slave_SQL_Running doivent être à Yes sur les 2 serveurs.

Si c’est le cas, nos 2 serveurs sont maintenant en réplication Master-Master

Il faut également vérifier que les bases sont identiques. En ce qui me concerne, je ne connais pas mieux que diff pour ce genre de choses.

Je fais un dump simultané (ce qui n’est pas toujours possible) de toutes les bases des 2 serveurs que je compare avec diff. S’il n’y a pas pas de différence, c’est bon signe.

Il est temps de tester le résultat.

Arrêt de Master02

Je vais commencer par arrêter Master02, ce qui va permettre de vérifier que le compte debian-sys-maint fonctionne toujours (si ce n’est pas le cas, vous aurez un failed).

service mysql stop

Puis je fais quelques modifications dans Master01 (la création d’une base temporaire avec quelques tables et un jeu de données par exemple).

Je relance Master02 :

service mysql start

Et je vérifie que les nouvelles données sont bien présentes sur les 2 serveurs.

Puis le dernier test, je supprime ma base temporaire depuis Master02.

Si elle est également supprimée sur Master01, je peux ré-ouvrir l’accès au serveur de production.

Nous disposons maintenant de 2 serveurs identiques et qui doivent le rester. La prochaine étape est d’utiliser cette architecture derrière un load balancer en Actif/Passif (je n’ai pas encore assez confiance pour faire de l’Actif/Actif).

Source :

MySQL – replication Master-Slave

Ce petit article est un mémo pour faire une réplication Master-Slave.

Le principe est très simple, le Master enregistre toutes les modifications dans un fichier de logs (log binaire), les Slaves vérifient l’état de ces fichiers et appliquent les modifications.

Il ne faut bien évidemment pas écrire directement dans les bases répliquées sur les Slaves.

On va partir sur une architecture simple composée d’un serveur maitre et de 2 serveurs esclaves, avec une base en réplication (mabase).

Master : 192.168.0.101
Slave 1 : 192.168.0.201
Slave 2 : 192.168.0.202

Configuration du master

Dans la section [mysqld]
Le Master doit pouvoir être contacté par les Slaves :

bind-address = 192.168.0.101

On peut également mettre la ligne en commentaire, ce qui équivaut à 0.0.0.0.

Les logs binaires doivent être activés (ici on les active pour toutes les bases, mais ce n’est pas obligatoire) :

#l'ID du serveur doit être unique sur l'ensemble des serveurs impliqués
#dans la réplication
server-id = 1
#Emplacement des logs binaires, s'il y a beaucoup de mouvements dans la base
#il faut prévoir un emplacement avec suffisamment d'espace
log_bin = /var/log/mysql/mysql-bin.log
#Durée de vie des anciens logs binaires
#pour supprimer les anciens logs, utilisez : mysqladmin -uroot -p flush-logs
expire_logs_days = 7
#Taille maximale d'un log binaire avant rotation
max_binlog_size = 256M

On relance le serveur :

service mysql restart

La réplication ne prenant en compte que les modifications, il faut faire un dump de la base source à un instant T, pour plus de sécurité, on verrouille les tables (attention, selon le moteur des tables, les options de verrouillage peuvent changer).
On se connecte à la base maitre :

USE mabase;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
 +------------------+----------+---------------+------------------+
 | File             | Position | Binlog_Do_DB  | Binlog_Ignore_DB |
 +------------------+----------+---------------+------------------+
 | mysql-bin.000004 |      169 | mabase        |                  |
 +------------------+----------+---------------+------------------+

Il faut bien noter les informations de fichier courant (File) et de position (Position).

On fait le dump :

mysqldump -u root -p --add-drop-database --master-data=2 --databases mabase > mabase.sql

Le paramètre –master-data=2 permet d’ajouter les informations de fichier courant et de position en commentaire dans le dump.

On peut alors enlever le verrou, on en profite pour créer l’utilisateur qui sera utilisé pour la réplication :

USE mabase;
UNLOCK TABLES;
GRANT REPLICATION SLAVE ON *.* TO 'copieur'@'%' IDENTIFIED BY 'password';

En production, il faudrait être plus restrictif au niveau des droits.

S’en est fini des opérations sur le maitre. Toutes les modifications depuis le redémarrage sont enregistrées dans les logs binaires.

Esclaves :

Les 2 serveurs esclaves ont la même configuration dans cet exemple, mais on peut aussi répliquer une partie des bases sur l’un et une partie sur l’autre.

On arrête les 2 serveurs et on modifie la section [mysqld] comme suit :

#201 pour le premier esclave, 202 pour le second
server-id=201
master-user=copieur
master-password=password
master-connect-retry=60
replicate-wild-do-table = mabase.%

On peut relancer les 2 serveurs.
Il faut maintenant restaurer le dump (que l’on aura transféré au préalable) sur les esclaves :

mysql -u root -p mabase < mabase.sql
Puis on se connecte pour mettre en marche la réplication :

SLAVE STOP;
CHANGE MASTER TO MASTER_HOST='192.168.0.101', MASTER_USER='copieur', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=169;
START SLAVE;

Si tout s’est bien déroulé, vos esclaves sont entrain de se synchroniser avec le serveur maitre.

sources :
MySQL
Plus d’informations sur :
www.networklife.net

MySQL – moteurs de stockage

Ce petit article est un mémo sur les principaux moteurs de MySQL.

Il existe de nombreux moteurs de stockage pour MySQL, ils ont leurs points forts et leurs points faibles, mais en les combinant ont devrait pouvoir obtenir quelque chose de très efficace.

Les principaux moteurs sont :

MyISAM :

C’est le moteur le plus souvent utilisé car par défaut, adapté aux sites WEB.

Avantages :

  • efficace en lecture
  • recherche en FULL-TEXT
  • les clefs sont mises en cache
  • permet de lire et d’écrire en même temps dans une table sans problématique de verrouillage
  • très simple à sauvegarder (copie de fichiers)

Inconvénients :

  • ne gère pas les clés étrangères
  • ne gère pas les transactions

InnoDB :

C’est le moteur le plus complet, adapté aux applications critiques.

Avantages :

  • il gère les transactions
  • il gère les clefs étrangères
  • très configurable
  • sauvegardes à chaud
  • gère très bien les gros volumes de donnée
  • très efficace en écriture

Inconvénients :

  • assez consommateur CPU et en mémoire

Memory :

Les données sont stockées en mémoire, adapté pour des hautes performances.

Avantages :

  • très rapide (en théorie)

Inconvénients :

  • les données sont en mémoire, donc volatiles
  • ne peut pas être transformé en un autre type (via alter)
  • nécessite beaucoup de mémoire

BlackHole :

C’est un trou noir, il ne stock rien, adapté pour de la réplication en écriture (via les logs binaires).

Avantages :

  • les mêmes que /dev/null

Inconvénients :

  • mal documenté

Archives :

Idéal pour les logs

Avantages :

  • les données sont compressées
  • très rapide en écriture

Inconvénients :

  • la suppression de données n’est pas possible directement
  • ne supporte pas les index

Voici pour les moteurs dont je vois immédiatement un intérêt pour mes usages, je laisse pour le moment le moteur NDB (moteur utilisé pour le mode cluster) de coté.

Tests

J’ai fait une petite série de tests, l’injection d’1 million de lignes sur une petite VM.

J’ai gardé la configuration par défaut de Debian, exception faite de max_heap_table_size=1677721600 (pour le moteur memory).

Le test avec BlackHole sert de référence.

Première série

Avec ID (INT)/Nom(VARCHAR)/Prenom(VARCHAR) sans index

  • BlackHole : 9.2sec/0Mo
  • MyISAM : 10.3sec/264.2Mo
  • InnoDB : 17.2sec/204Mo
  • Memory : 10.8sec/269.2Mo (en ram)
  • Archives : 11.9sec/5.4Mo

Seconde série

Avec ID (INT)/Nom(VARCHAR)/Prenom(VARCHAR) avec un index sur ID

  • BlackHole : 9.2sec/0Mo
  • MyISAM : 13.1sec/275.2Mo (dont 9.8Mo d’index)
  • InnoDB : 17.3sec/205Mo
  • Memory : 12.1sec/270Mo (en ram)
  • Archives : 11.9sec/5.4Mo (il n’y a pas de notion d’index)

Mon test présente un gros goulot d’étranglement (lié à la VM) qui limite les moteurs Memory et BlackHole, je ne les ai laissé que pour trace.

En complément et à titre de comparaison sur la volumétrie avec des données réelles, j’ai repris une table d’un peu plus de 20 millions de lignes :

  • InnoDB : 1Go
  • Archives : 80Mo
sources :