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

In this issue, the editor will bring you about how to analyze the "one master, two slaves" and master-slave switch based on GTID. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

Fault description:

For the master library with two slaves and two slaves, the master library stops, and the master library is tentatively designated as A, the first slave library is B, the second slave library is C, and the slave library B is lower than the slave library C. now, slave library B is set as master library, and slave library C is connected to slave library B, but C slave library cannot be synchronized:

B from the library:

Mysql > show master status\ G

1. Row

File: mysql-bin.000312

Position: 656595484

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set: 28aec2b2-815a-11e6-a848-6c3be5b34862:1-22169328

2fc072e2-7f1a-11e6-b9ec-c81f66d60579:1-86654017

1 row in set (0.00 sec)

C from the library:

...

Last_IO_Errno: 1236

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'

...

Master_Server_Id: 1663306

Master_UUID: 28aec2b2-815a-11e6-a848-6c3be5b34862

Retrieved_Gtid_Set: 2fc072e2-7f1a-11e6-b9ec-c81f66d60579:26956787-86654006

Executed_Gtid_Set: 2fc072e2-7f1a-11e6-b9ec-c81f66d60579:1-86654006

Auto_Position: 1

Both An and B use vip as the main library, and the binlog file names of An and B are not the same; (these two conditions are irrelevant in this case, just explain the background, so I won't go into details.)

Now you can see that 86654007-86654017 of the transactions in the original master database cannot be synchronized. This log exists on the B slave database (now the master database), and there is no purge.

C from the library directly chang master to B slave library is right. But after referring to B, C is still unable to synchronize.

2fc072e2-7f1a-11e6-b9ec-c81f66d60579:1-86654017 this is the gtid of the downtime main library, which is A.

28aec2b2-815a-11e6-a848-6c3be5b34862:1-22169328 this is the gtid after B upgrading from the library to the main library.

First talk about the process of the solution, and finally analyze the problem.

Solution:

1. C refers to B, reset slave all for a moment, and change master to vip.... I can't. Or 1236.

two。 Repeat the first half of the operation, and then refer directly to the entity ip.

3. Take out the transactions on C that lack the A main library, pour them in, and then reassign them to BMageSet global gtid_purged='28aec2b2-815a-11e6-a848-6c3be5b34862:1.

2fc072e2-7f1a-11e6-b9ec-c81f66d60579:1-86654017; same error report

4. Through the binlog log on the B main database, fish out the transactions missing from the A main library and fill them in, and then reassign B, SET @ @ GLOBAL.GTID_PURGED='28aec2b2-815a-11e6-a848-6c3be5b34862:1-75147Magi 2fc072e2-7f1a-11e6-b9ec-c81f66d60579:1-86654017'.

Ok! Succeed!

Finally, verify the data, the data is consistent!

At this point, Daniel can probably see what the problem is. Let's analyze it step by step.

First of all, let's analyze the information collection of C reporting error after A main library is down and B takes over as the main library:

B from the library:

Mysql > show master status\ G

* * 1. Row *

File: mysql-bin.000312

Position: 656595484

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set: 28aec2b2-815a-11e6-a848-6c3be5b34862:1-22169328

2fc072e2-7f1a-11e6-b9ec-c81f66d60579:1-86654017

C slave library: show slave status\ G

...

Last_IO_Errno: 1236

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'

...

Master_Server_Id: 1663306

Master_UUID: 28aec2b2-815a-11e6-a848-6c3be5b34862

Retrieved_Gtid_Set: 2fc072e2-7f1a-11e6-b9ec-c81f66d60579:26956787-86654006

Executed_Gtid_Set: 2fc072e2-7f1a-11e6-b9ec-c81f66d60579:1-86654006

Auto_Position: 1

Relevant information can be obtained from the above information:

1.B current GTID information of the main database, 28aec2b2-815a-11e6-a848-6c3be5b34862:1-22169328, 2fc072e2-7f1a-11e6-b9ec-c81f66d60579:1-86654017

28aec2b2-815a-11e6-a848-6c3be5b34862:1-22169328 this is the GTID of the B main library, indicating that the transaction has been executed on B and completed to 22169328.

2fc072e2-7f1a-11e6-b9ec-c81f66d60579:1-86654017 this is the GTID of A main library, indicating that it has been executed and completed to 86654017 on A.

2. C error message prompt: the binlog requested by C no longer exists in the main library.

3. Take a look at the GTID information executed by C:

Master_UUID: 28aec2b2-815a-11e6-a848-6c3be5b34862 can see from this information that C, as a slave library, has specified the master library as B.

Retrieved_Gtid_Set: 2fc072e2-7f1a-11e6-b9ec-c81f66d60579:26956787-86654006 the information here indicates that C synchronizes from location 26956787 of A main library and synchronizes to location 86654006

Executed_Gtid_Set: 2fc072e2-7f1a-11e6-b9ec-c81f66d60579:1-86654006 this means that the location where the A library log is executed from library C indicates that it has been executed up to 86654006

The reason is that the B machine itself generates gtid. (Executed_Gtid_Set: 28aec2b2-815a-11e6-a848-6c3be5b34862:1-22169328

2fc072e2-7f1a-11e6-b9ec-c81f66d60579:1-86654017). 28aec2b2-815a-11e6-a848-6c3be5b34862:1-22169328 this is when the local gtid.An is down and C connects to B from the library, it is necessary to read all the binlog that has not been executed by C. It's a little roundabout. It means that the gtid,C executed by the B machine itself also needs to be pulled and executed. In this case, 28aec2b2-815a-11e6-a848-6c3be5b34862:1-22169328 these are also to be implemented.

B has already generated the native gtid before becoming the main library, the analysis may be after installing the database, open the gtid, and then import the data to generate the corresponding gtid. Because the time is a little long. This part of the binlog has been deleted on the B machine. When C goes to the main library to pull the binlog, because it is the first time to pull from the B host, it will start from the first gtid, because this part of the binlog no longer exists on the B machine. That's why the above error was reported.

If the problem is found, it will be solved. The solution has been mentioned above and will not be repeated.

Gtid is globally unique, so theoretically, after B is upgraded to the main library, C can be synchronized immediately. This example, also due to its own operational error, turned on binlog before B became slave, causing this part of the binlog to be removed. Therefore, C has no way to pull the previously generated binlog log.

With reference to this example, I personally suggest that when creating a slave library, do not open binlog. If the slave library does not have the same operation as the master library, do not open it all the time. Open binlog before you need to become the master library.

The above is the editor for you to share how to analyze the GTID-based one master, two slaves and master-slave switch, if you happen to have similar doubts, you can refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.