Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to fix the mixed problem of MySQL GTID". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to fix the mixed problem of MySQL GTID".

The overall service is deployed peer-to-peer in two computer rooms and then concatenated by cascading replication.

Before the actual switch, node B is easy to remove because it is from the library, so the overall deployment architecture is only Agraine C, D

At the same time, in order to ensure the availability of the entire business access domain name, dual-master replication will be temporarily enabled before switching, which can maximize the integrity of the data. Of course, there are two modes, one is the maximum protection mode, which means that data can only be written from one entry, if double writes are likely to cause data conflicts, and the second is the maximum available mode, which means that the whole process data can always be written on both sides. The choice of this model is related to the specific business characteristics (read more and write less, read more and write more, etc.).

Therefore, the dual master configuration between An and C is particularly important, and it is also the basis of the whole smooth switching data integrity.

At present, the basic GTID information of the Ameno Cpene D node is as follows:

A: show master status

Executed_Gtid_Set: avut 1-222717169 Magi Blav 1-697

C:show slave status

Executed_Gtid_Set: azure 1-222716771 Magazine 1-700

D:show slave status

Executed_Gtid_Set: azure 1-222716771 Magazine 1-700

The meaning of this data expression is more profound, that is, in the data chain, there is the GTID information of node B that has been removed, while from the GTID information of CMagie D, we can see that a data transaction is lost in B (of course, this process is not a real data change, but related to non-standard operation).

So in this case, if you want to configure dual-master, what you need to solve is the difference of B-related GTID. One is to erase the traces of B directly, and this process needs to be operable on CMagar D, but there will be problems when you actually copy dual-master.

If you think of GTID as a kind of data consanguinity point of view, you will find that the whole GTID is really a very intelligent design. Suppose red is the data consanguinity of An and green is the data consanguinity of B.

After abandoning B, AMagol C turns on two masters, and the whole data consanguinity is as follows:

So any data change in the entire replication topology can be traced back with reasonable evidence, which is a valuable thing for GTID design.

With regard to the repair method, it is also relatively clear, that is to "fallback" the consanguinity B part of the data of C and D, as follows:

A: show master status

Executed_Gtid_Set: avut 1-222717169 Magi Blav 1-697

C:show slave status

Executed_Gtid_Set: avut 1-222716771 Borel 1-697

D:show slave status

Executed_Gtid_Set: avut 1-222716771 Borel 1-697

Modify C and D once according to this pattern, and the entire two-way replication can be built quickly.

The principle of reset GTID can be referred to the following figure, and clipping can be achieved indirectly through gtid_purged.

The steps for C-side repair are as follows:

1) stop slave

2) show slave status\ G

3) reset master

Keep in mind that it is executed on the GTID side, and the purpose of this phase is to reconfigure the calibration value of the Slave. The mysql.gtid_executed should be empty at this time.

4) reset the GTID_ purged value

SET @ @ GLOBAL.GTID_PURGED='A:1-222716771 Bazu 1-697'

5) Delete the replication configuration from the library

Reset slave all

6) configure replication relationship

CHANGE MASTER TO MASTER_USER='dba_repl', MASTER_PASSWORD='xxxx', MASTER_HOST='xxxxx',MASTER_PORT=xxxx,MASTER_AUTO_POSITION = 1

7) restart the Slave node to check the status

Start slave; show slave status\ G

After the repair, this part is intended to write a patrol GTID and repair script logic, can do this part of the management in more detail.

Thank you for reading, the above is the content of "how to fix the mixed problem of MySQL GTID". After the study of this article, I believe you have a deeper understanding of how to repair the mixed problem of MySQL GTID, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.