Synchronisation MySQL

[elpache]

Bonjour

Je recherche un outil, qui me permettrait d'effectuer une synchronisation bidirectionnelle entre 2 bases de données MySQL (version > 4.1)
J'ai en effet 2 bases, une offline, une online, et je voudrais pouvoir les synchroniser afin qu'elles contiennent toutes les 2 exactement les mêmes données.

Qqn aurait-il ca dans ses poches ?

Sinon qqn a-t-il déjà eu à développer ce genre d'outils ? Si oui, quelles sont les contraintes ?

Merci

[psykoko]

si kk1 a ça, ça serait génial
moi je fais des copie de base tous les jours, c limite

[Vell]

Salut,

Je ne l'ai jamais fait, je vais donc parler au conditionnel

Si tu fais la sauvegarde autrement qu'en configuration "cluster" directement avec les serveurs, ce que je pense car tu dois sûrement être sur un mutualisé, et même sur un dédié il en faut au moins deux pour pouvoir faire du clustering...

Alors ce serait sûrement un script php ?
L'avantage : tu peux faire une interface web, tu peux utiliser des services comme webcron.

Les désavantages : la LENTEUR ! Les risques liés à la mémoire et aux temps d'exécution sur les grosses tables.

Si tu as un accès SSH sur la machine (les perso.cegetel.net en ont, outch !) tu as peut être accès à des commandes te facilitant la sauvegarde, et dans ce cas là ce serait bien plus rapide...

Ce qui revient à dire que mon post est presque inutile, mais presque seulement

[arsenick]

le logiciel rsync me semble approprié ... non ?

[Nikos38]

La synchronisation est prise en compte en natif avec mysql mais celà nécessite un accès à la config des 2 mysql(l'un en esclave, l'autre en maitre)

[elpache]

Si je ne me trompe pas c la réplication qui est existe en natif dans MySQL. Comme tu le dis, il y a le master et un ou plusieurs esclaves.
Ce n'est pas ce que je recherche. A moins que je ne me trompe !
Ce dont j'ai besoin c'est d'une véritable synchronisation bidirectionnelle, car a priori, on ne sait pas quelle base est la plus mise à jour avant la synchro.

Arsenick, je ne connais pas très bien rsync, ca fonctionne sur quel principe ?

[florianavs18]

Voila de la lecture : http://dev.mysql.com/doc/mysql/en/replication.html

Si tu as besoin de plus d'infos, post ici :).

Mais il faut faire attention au bidirectionnel, exemple :

Contexte : un serveur mysql A, un serveur mysql B, une table "personne" (id [AUTO_INCREMENT, PRIMARY], nom, prenom) sur chaque serveur (structure identique). Pour l'exemple, on va partir de la table vide.

Dans le cas d'une réplication bi directionnelle voila ce qui se passerai :

Contexte : sur A, AUTO_INCREMENT = 0 sur B, AUTO_INCREMENT = 0

T1 : Sur A, j'insere un enregistrement :

INSERT INTO personne SET nom = "machin1", prenom = "k"

l'id est fixé à 1

Contexte : sur A, AUTO_INCREMENT = 1 sur B, AUTO_INCREMENT = 0


T1bis : B récupère le binlog de A qui lui dis

Code: Tout sélectionner

INSERT INTO personne SET nom = "machin1", prenom = "k"

attention, le binlog ne dira jamais a B :

Code: Tout sélectionner

INSERT INTO personne SET id = 1, nom = "machin1", prenom = "k"

Contexte : sur A, AUTO_INCREMENT = 1 sur B, AUTO_INCREMENT = 1

T2 : Sur B, j'insere un enregistrement :

Code: Tout sélectionner

INSERT INTO personne SET nom = "machin2", prenom = "k"

l'id est fixé à 1

Contexte : sur A, AUTO_INCREMENT = 1 sur B, AUTO_INCREMENT = 2

T2bis : A récupère le binlog de B qui lui dis

Code: Tout sélectionner

INSERT INTO personne SET nom = "machin2", prenom = "k"

Contexte : sur A, AUTO_INCREMENT = 2 sur B, AUTO_INCREMENT = 2

Jusque là, tout va bien ! mais entrons dans un contexte un peu plus réaliste : A recois des requetes d'INSERT, d'UPDATE de DELETE (SELECT n'est pas log dans binlog car il ne modifie pas la table), idem pour B.

Le binlog dans la réalité est un fichier log que le serveur esclave (selon la direction, A ou B) viens chercher à intervalles régulier sur le serveur maitre puis dans un second temps l'execute sur lui meme. Les binlog sont traités au meme niveau que les autres reqûetes, pas de priorités.

Imaginons donc maintenant que sur T3 on est la chose suivante :

Contexte : sur A, AUTO_INCREMENT = 2 sur B, AUTO_INCREMENT = 2

T3 : Sur B, j'insere un enregistrement :

Code: Tout sélectionner

INSERT INTO personne SET nom = "machin3", prenom = "k"

l'id est fixé à 3

Contexte : sur A, AUTO_INCREMENT = 2 sur B, AUTO_INCREMENT = 3

T3 (même instant) : Sur A, j'insere un enregistrement :

Code: Tout sélectionner

INSERT INTO personne SET nom = "machin4", prenom = "k"

l'id est fixé à 3

Contexte : sur A, AUTO_INCREMENT = 3 sur B, AUTO_INCREMENT = 3

T3bis : A récupère le binlog de B qui lui dis

Code: Tout sélectionner

INSERT INTO personne SET nom = "machin3", prenom = "k"

Contexte : sur A, AUTO_INCREMENT = 4 sur B, AUTO_INCREMENT = 3

T3bis (même instant) : B récupère le binlog de A qui lui dis

Code: Tout sélectionner

INSERT INTO personne SET nom = "machin4", prenom = "k"

Contexte : sur A, AUTO_INCREMENT = 4 sur B, AUTO_INCREMENT = 4

Qu'avons nous en T4 dans les deux tables ?

A.personne :
1 machin1
2 machin2
3 machin3
4 machin4
5

B.personne :
1 machin1
2 machin2
3 machin4
4 machin3
5

On a donc une erreur dans le contenu des tables et la relation id, nom, prénom entre les deux serveurs. Mais pour A comme pour B tout est normal !

Voila un probleme de la synchronisation bidirectionnelle.

Ce probleme d'id en AUTO_INCREMENT peux etre résolu soit pas le programme en lui meme, soit en faisant appel à une table externe unique qui attribue l'id, soit par vérification sur les deux serveurs... bref plein de solutions, mais un casse tête a l'arrivée


Note :
Pour faire du bidirectionnel, il faut spécifier à mySQL qu'il peut se synchroniser avec un serveur ayant un ID supérieur ou égal a lui : --replicate-same-server-id


Florian

[jefaisquepasser]

Florian, ton système implique d'être en mode connecté non ?
Car si j'ai bien compris après modification d'une base le binlog de l'autre base est appelé immédiatement pour effectuer les mêmes modifications !

[florianavs18]

Non, tu peux travailler en mode déconnecté, le serveur esclave gardera en mémoire la derniere position (dans le binlog) qu'il a traité.

Seulement, la mise à jour est plus longue si tu déconnecte le slave. c'est juste un probleme de temps.

Pour arreter la synchronisation sur le serveur esclave :

Code: Tout sélectionner

STOP SLAVE;

pour le redémarrer :

Code: Tout sélectionner

START SLAVE;

Florian

[elpache]

Merci Florian
Encore quelques précision : la réplication implique qu'il y ait une base master et une base (ou plusieurs) base slave.
Or dans la synchro, on ne sait pas a priori laquelle sera master et laquelle sera slave !
Est-il possible de déterminer ce role dynamiquement ?
En effet, dans mon cas, la synchro doit se faire à intervalle régulier et de façon totalement automatisée.
Par ailleurs comment se déroule le maintien de la cohérence des données. Exemple : dans la base A j'ai 2 tables reliées par une clé externe (ID). Dans la base B, j'ai les 2 memes tables mais reliées par des valeurs de ID différentes suite à des ajouts / suppressions ...

Comment dans ce cas être sur que mes données seront toujours cohérentes ? Que les liaisons seront toujours respectées ?
Bref la réplication dont du parles prend-elle en compte ces cas de figure ?

Merci

[florianavs18]

Je pense que dans ton cas, il faudrait faire un *système à bascule*. je m'explique : de ce que je lis, je pense que tu as deux phases de travail, une premiere en développement, où tu développes chez toi ou au boulot, puis une seconde ou du est en phase de production et l'ajout se fais soit pas les utilisateurs du site ou par toi en mode administration.

Donc deux phases séparées dans le temps.

Je te propose la chose suivante :

Le contexte : un serveur A qui est le serveur de prod, chez l'hébergeur et un serveur B qui est le serveur de dév, chez toi ou au boulot. On utilisera qu'une seule base de donnée bdd avec une seule table tbl pour notre exemple

On fais l'installation :

sur A :

Code: Tout sélectionner

STOP SLAVE;
RESET MASTER;

sur B :

Code: Tout sélectionner

STOP SLAVE;
RESET SLAVE;
CHANGE MASTER TO MASTER_HOST='ipA', MASTER_USER='replA', MASTER_PASSWORD='replA';
LOAD DATA FROM MASTER;
START SLAVE;


On a donc maintenant B qui est la copie conforme de A et continue a se synchroniser avec A.

Donc tu es dans une phase de production.
Imaginons maintenant que tu veuilles passer en pahse de développement :

sur B :

Code: Tout sélectionner

STOP SLAVE;
RESET MASTER;

sur A :

Code: Tout sélectionner

STOP SLAVE;
RESET SLAVE;
CHANGE MASTER TO MASTER_HOST='ipB', MASTER_USER='replB', MASTER_PASSWORD='replB';
LOAD DATA FROM MASTER;
START SLAVE;


On a donc maintenant A qui est la copie conforme de B et continue a se synchroniser avec B.

Tu viens de passer en phase de développement.
Par contre, il y a un temps d'erreur sur A ou la bdd est vide (le temps du transfert qui est proportionnel à la taille des données a transférer).

Voyons comment résoudre ce problème, du moins le diminuer :

1. Tu as peu de donnée et tu peux te permettre de fermer l'accès à une heure creuse genre 3h du mat'. Là, pas de problème, tu ne prend pas de gant et *risque* qu'un utilisateur n'est pas de donnée a visualiser.

2. Tu veux faire ça bien :
On va prendre uniquement le cas Dev > Prod car sur le serveur de Dev le downtime n'est pas génant.


Précisions concernant replA, replB et replbackupB :
replA : REPLICATION SLAVE, SUPER, RELOAD et SELECT sur bdd
replB : REPLICATION SLAVE, SUPER, RELOAD et SELECT sur bdd
replbackupB : REPLICATION SLAVE, SUPER, RELOAD et SELECT sur bdd_backup


sur B :

Relevé le champ Position et File sur le résultat de la requête :

Code: Tout sélectionner

SQL :
SHOW MASTER STATUS;

en ligne de commande et selon le répertoire d'installation de mysql :

Code: Tout sélectionner

cd /usr/local/mysql/data
rm -R ./bdd_backup
/etc/init.d/mysqld stop
cp -R ./bdd ./bdd_backup
/etc/init.d/mysqld start

sur A :

Code: Tout sélectionner

SQL :
STOP SLAVE;
RESET SLAVE;
CHANGE MASTER TO MASTER_HOST='ipB', MASTER_USER='replbackupB', MASTER_PASSWORD='replbackupB';
LOAD DATA FROM MASTER;


en ligne de commande et selon le répertoire d'installation de mysql :

Code: Tout sélectionner

cd /usr/local/mysql/data
/etc/init.d/mysqld stop
mv ./bdd ./bdd_old
mv ./bdd_backup ./bdd
/etc/init.d/mysqld start

Code: Tout sélectionner

RESET SLAVE;
CHANGE MASTER TO MASTER_HOST='ipB', MASTER_USER='replB', MASTER_PASSWORD='replB', MASTER_LOG_POS = 'Position', MASTER_LOG_FILE = 'File';
START SLAVE;


Si tout est ok, en ligne de commande et selon le répertoire d'installation de mysql :

Code: Tout sélectionner

rm -R ./bdd_old
/etc/init.d/mysqld restart


Note : il faut bien tester sur des serveurs de développement avant ! et ne pas le faire en live sur le serveur de prod :)


Florian

[elpache]

Merci pour toutes ces précisions

Vais tester tout ca

Encore merci