Discussion :

[momoba]

Bonjour,
J’aimerais savoir s’il est possible de comparer deux BDD MySQL avec une certaine commande SQL, perso je n’ai pas trouvé puis enfin de mettre à jour la table où il manque des données.
Dans mon cas j’ai deux bases de données que je synchronise en temps réel avec une architecture maitre/maitre. Et en fait en cas de panne, si le serveur primaire tombe, le secondaire fonctionne toujours et sa base de données se rempli. Lorsque le serveur primaire est réparé, il aura une différence de données avec le secondaire et j’aimerais donc comparer les deux bases et mettre à jour automatiquement le primaire pour qu’il ajoute les données manquantes.

Merci pour vos idées

[Artemus24]

Salut momoba.

j’ai deux bases de données que je synchronise en temps réel avec une architecture maitre/maitre.

C'est une réplication bidirectionnelle.

j’aimerais donc comparer les deux bases et mettre à jour automatiquement le primaire pour qu’il ajoute les données manquantes.

Il est inutile de comparer les deux bases.

Vous avez à votre disposition, vos fichiers binary_log pour chaque master (primaire et secondaire).
Chaque soir (disons à minuit), vous redémarrez votre serveur MySql afin de créer un nouveau fichier binary_log.
Le primaire a planté disons le 2018-04-17 à 15:00.
De ce fait, vous êtes en mesure de récupérer tout ce qui n'a pas été répliqué (ou desynchronisé) depuis cet instant. Comment ?
En récupérant les données inscrites dans le fichier binary_log du secondaire, à partir du point de plantage.

Si vous ne connaissez pas le fichier binary_log, vous l'obtiendrez en faisant "SHOW MASTER STATUS;".

1) vérifiez la position ou la date dans le fichier binary_log :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

mysqlbinlog  --database=base  --result-file=03.Verify.sql  --start-datetime="2017-12-06 01:43:43"  --stop-datetime="2017-12-06 01:43:52"  F:/Wamp/logs/mysql_binary.000003

Dans cet exemple, je vais extraire mes data à partir du fichier binary_log "F:/Wamp/logs/mysql_binary.000003"
Je vais ranger me résultat dans 03.verify.sql.
Je l'ai fait entre la date de mon plantage "2017-12-06 01:43:43" et celle où j'ai par exemple arrêté le secondaire.

Ou peut aussi le faire à partir des positions :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

mysqlbinlog  --database=base  --result-file=04.Binlog.sql  --start-position=4725  --stop-position=4863  F:/Wamp/logs/mysql_binary.000003

J'obtiens un fichier qui a cette allure :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#171206  1:43:19 server id 1  end_log_pos 123 CRC32 0x61a4702e 	Start: binlog v 4, server v 5.7.20-log created 171206  1:43:19 at startup
ROLLBACK/*!*/;
BINLOG '
Jz0nWg8BAAAAdwAAAHsAAAAAAAQANS43LjIwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAnPSdaEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AS5wpGE=
'/*!*/;
# at 1634
#171206  1:43:43 server id 1  end_log_pos 1699 CRC32 0x69542c3f 	Anonymous_GTID	last_committed=6	sequence_number=7	rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1699
#171206  1:43:43 server id 1  end_log_pos 1781 CRC32 0xd46649a9 	Query	thread_id=3	exec_time=0	error_code=0
SET TIMESTAMP=1512521023/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=48/*!*/;
SET @@session.lc_time_names=5/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 1781
# at 1813
#171206  1:43:43 server id 1  end_log_pos 1813 CRC32 0xa24f4de7 	Intvar
SET INSERT_ID=5/*!*/;
#171206  1:43:43 server id 1  end_log_pos 1951 CRC32 0x8eed211f 	Query	thread_id=3	exec_time=0	error_code=0
use `base`/*!*/;
SET TIMESTAMP=1512521023/*!*/;
insert into `test` (`val`) values ('quatre'),('cinq'),('six')
/*!*/;
# at 1951
#171206  1:43:43 server id 1  end_log_pos 1982 CRC32 0x5fddd471 	Xid = 23
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

Les "# at", ce sont les positions.
Les "#171206 1:43:43", ce sont les dates et heures.
Il va falloir faire plusieurs fois le test afin de trouver la bonne position.

2) faire le recovery !

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

mysql --database=base < 03.Binlog.sql

Binlog.sql ou verify.sql, ce sont les mêmes fichiers.
Faire un recovery, c'est faire un import à partir de l'extrait d'une fichier binary_log.

J'ai fait ce genre de test, et cela fonctionne parfaitement.
Par contre, je ne l'ai jamais fait en grandeur nature pour la simple raison que je n'utilise pas la réplication.

@+

[momoba]

Bonjour Artemus24 ,

Merci pour les éclaircissements apportés mais juste pour la partis de "# at" j'ai pas compris qu'est-ce que vous entendais par position et ensuite je
suppose qu'il faudra dé-commenté toutes les partis dans le code ou juste une seule partis m’intéresse?

Merci de votre reponse

[Artemus24]

Salut momoba.

juste pour la partis de "# at" j'ai pas compris qu'est-ce que vous entendez par position

Les fichiers binary_log se remplissent en continue.
Éventuellement, quand vous redémarrez votre serveur MySql, vous pouvez en obtenir plusieurs fichiers, qui sont numérotés de 1 à N.

Vous devez retrouver dans les fichiers binary_log, le moment précis de la dernière mise à jour valide de votre base de données.
Cela peut se faire de deux façons, soit par la date+heure soit par la position.

Comment procéder ? Je ne connais pas d'autres méthodes que par une recherche manuelle dans les fichiers binary_log.

je suppose qu'il faudra dé-commenté toutes les partis dans le code ou juste une seule partis m’intéresse?

Il n'y a rien à dé-commenter où que ça soit. D'ailleurs, je ne comprends votre interrogation ?

Je reprends les explications :

1) le serveur primaire à planté disons à 2018-04-17 à 15:00.

2) vous faites en sorte de réparer votre serveur primaire.
Comme il est désynchronisé par rapport à votre serveur secondaire, vous devez connaitre le moment précis de la dernière mise è jour valide.
Comment ?

3) pour ce faire, vous comparez les fichiers binary_log des deux serveurs afin de déterminer la position ou la date+heure.
Autrement dit, la dernière mise à jour présente dans le fichier binary_log du serveur primaire, doit se retrouver dans le fichier binary_log du serveur secondaire.
Et vous prenez la position (at) ou la date+heure juste après, toujours dans le fichier binary_log du serveur secondaire.

4) quand vous avez cette position ou cette date+heure, vous extrayez le contenu du fichier binary_log du serveur secondaire de la position que vous avez déterminé jusqu'à la fin.
Le mieux serait d'arrêter le serveur secondaire afin d'avoir la même synchronisation.

5) ensuite pour récupéré l'extrait de l'état §4) que vous rapatriez vers le serveur primaire, et vous l'importer.

La manipulation à faire est très simple. La difficulté est de trouver la bonne position.

@+

[momoba]

Ha d'accord je viens de comprendre la démarche.

je vous remercie de votre réponse.