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

How to solve the MySQL gh-ost to double primary table structure primary key conflict, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can get something.

1) background:

Recently, I helped the business side to troubleshoot two cases of master replication data loss or primary key conflict. My colleagues on the DB side also asked me what is this principle. While answering them, by the way, record this question.

2) phenomena:

The company's north-south business adopts master master replication, and the primary key of the table is self-increasing ID, and the self-increasing ID of each main database is staggered, but the primary key conflict occurs in the primary key of the business, which is initially thought to be caused by improper setting or modification, but looking at the history, no one has ever operated, and the configuration is correct, and no one restarts. In the investigation of binlog, an interesting phenomenon is found. In binlog, a main library of MySQL, two primary keys ID are the same binlog, and the binlog is generated by insert. The specific phenomena are as follows:

Divide one at 17:19

One at 17:22 minutes.

At first, I feel confused. How can I insert the same table with the same primary key? It suddenly occurred to me that this should be caused by the change of the table structure of gh-ost (our company has a serious shortage of DBA, and some of the DB operations can only be borne by business operators).

3) Simulation analysis:

Premise: suppose there are two hosts, called main library An and main library B. there is only one primary key in the table T _ ~ ~ T. The table before ghost modification is called T, and the modified table is T'(T and T 'are actually the same table, but the generated time is different, so it is convenient to express)

The main library B of ① performs the rename table T to updated TO T operation. At this time, the main library An is the T 'table.

② also inserts the data from table 13 in main library A, but main library B has not received it yet (delay relationship)

The T table of the ③ main library An is modified by the rename table T to updated TO T from the main library B. since the T 'table in the main library B has not yet received the 13 sent in the second step, the T' table of the main library A certainly does not have the value of 13.

At this time, ④ main library An inserts 13 into T' and is copying to the T' of main library B (due to delay, it has not been sent to the T' table of main library)

The T'of the ⑤ main library B received the 13 of the main library An inserted into T in the second step

In the fourth step of ⑥, 13 of the writes to T' in the main library A have been transferred to the main library B, and it is found that the T' table of the main library B already has 13 (from step 5), and then the primary key conflicts.

At this point, the cause of the primary key conflict has been found, which means that data has been lost (this analysis requires you to have a full understanding of the principle of gh-ost).

4) how to avoid

In a project that modifies a table structure with gh-ost, if both hosts have writes, the writes must be cut to one side and then modified.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.