Shulou(Shulou.com)06/01 Report--

Yesterday, I handled a case of abnormal replication of GTID mode enabled under MySQL version 5.6. no operation on MASTER can be applied on SLAVE. It is recorded in SLAVE's RELAY LOG, but there is no trace of SLAVE's BINLOG. Because GTID is enabled, it is easy to troubleshoot. You only need to parse RELAY LOG and BINLOG into text files on SLAVE, and then directly search MASTER's UUID to find out whether MASTER copied transactions are applied to SLAVE. In the course of investigation, it was once suspected that it was because the BINLOG-DO or IGNORE rules were set, or the REPLICATION-DO or IGNORE rules were set, or even the serious BUG of GTID, but no clue was found. Until the following information is found in SHOW SLAVE STATUS:

Here) collapse or open

Analysis:

Nima, it's not supposed to be continuous. How could it be so weird? what to do? it's easy to catch the urgent. Let's slowly analyze why there is a breakpoint and a gap between GTID and SLAVE from MASTER to SLAVE.

Case 1 is normal. After GTID is enabled in MySQL 5.6, when deploying REPLICATION replication, you can set MASTER_AUTO_POSITION = 1 to let SLAVE automatically select the appropriate transaction point for replication according to GTID. DBA basically does not need to pay attention to and worry about the inconsistency of the master, which makes DBA feel at ease. When MASTER_AUTO_POSITION = 1 is enabled, it is normal that there is a gap in the middle of the GTID and the problem of a breakpoint occurs. Unless the following is the case: 1. Pause the SLAVE process manually

2. Continue to write data on MASTER

3. Refresh LOG on MASTER

4. Delete the old BINLOG on MASTER and keep only the latest BINLOG

5. Start MASTER on SLAVE, and an error will be reported, such as the following:

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.'

The solution to this problem can be as follows: 1. Close MASTER_AUTO_POSITION, that is, set MASTER_AUTO_POSITION = 0

2. Manual CHANGE BINLOG FILE & POS

In this case, MASTER_AUTO_POSITION = 1 cannot be set again, or an error will be reported again.

Case 21. Configure REPLICATION replication normally, but set MASTER_AUTO_POSITION = 0, which is the traditional way to specify BINLOG FILE & POS manually.

2. Temporarily shut down the SLAVE process during replication

3. Manually modify the BINLOG FILE & POS information to point to the new BINLOG FILE & POS point

4. Start SLAVE, and you will find that the GTID breakpoint reappears.

In the master-slave high availability mode, the master-slave switch may occur and then switch back again, and the breakpoint problem mentioned above may also occur. Therefore, we recommend using dual-master to deploy highly available handover, which can basically switch back and forth at will without manually specifying new BINLOG FIEE & POS information. Finally, when RESET MASTER is executed on MASTER, all BINLOG FILE & POS on MASTER are reset, and the information read on SLAVE is naturally inconsistent. Well, with all that said, let's finally talk about how to deal with GTID breakpoints.

Method 1: manually modify BINLOG FILE & POS1 and close SLAVE

2. Manual CHANGE BINLOG FILE & POS, point to the newly generated BINLOG FILE & POS on MASTER, and set MASTER_AUTO_POSITION = 0

3. Start SLAVE

Method 2: manually modify the GTID_PURGED value 1 and turn off SLAVE

2. Execute RESET MASTER on SLAVE and reset BINLOG FILE & POS on SLAVE (if this node is used for replication relay, pay attention to whether all binlog has been read to avoid data loss)

3. Execute SET @ @ GLOBAL.GTID_PURGED = '35cc99c6-0297-11e4-9916-782bcb2c9453:1-2455' on SLAVE

4. Start SLAVE

This is a bit confusing, meaning that we tell SLAVE to actively discard certain interval transactions transmitted over the MASTER. In this example, we abandoned the range of 1-2455, that is, when GTID started from 2466, we will continue to use RELAY LOG again, compared to our initial message:

Retrieved_Gtid_Set: 35cc99c6-0297-11e4-9916-782bcb2c9453:1-451,

Executed_Gtid_Set: 35cc99c6-0297-11e4-9916-782bcb2c9453:1-2455 11e4 792490-4517929

We force SLAVE to ignore only the range 1-2455 and continue to copy from 2466, eliminating the range that would have been ignored: 792490-4517929, ensuring that newly generated transactions continue to be applied. This can be done by referring to the MySQL manual: Excluding transactions with gtid_purged.

There is another thankless practice, that is, to perform some useless empty transactions on MASTER, so that the serial number of GTID has been increasing until it exceeds 2555, and then concocted in accordance with the law in the range of 792490-4517929, but we very much do not recommend this practice, which is both troublesome and easy to misoperate. Having said that, in MySQL version 5.6 and above, we strongly recommend that you enable MASTER_AUTO_POSITION = 1, let MySQL make its own judgment, reduce some unnecessary problems, and adopt a dual-master (one master is set to read-only), so that the two masters can switch to each other at will without having to worry about data inconsistencies.

The MySQL version I used in the above process: 5.6.17-65.0-rel65.0-log Percona Server with XtraDB (GPL), Release rel65.0, Revision 587. the MySQL version of the actual case forgot to record at that time, but it must be above 5.6. ~ ~

Original: http://imysql.com/2014/07/31/mysql-faq-exception-replication-with-gtid.shtml

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.