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

This article will explain in detail what to do about the inconsistency between master and slave GTID caused by reset master. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

1. Master database reports synchronization exception (master and slave are replicated in both directions). The error message shows that the target table of the insert statement does not exist.

2. Check the GTID information of the master and slave database and find that the GTID of the master database is higher than that of the slave database (it should be that the reset master operation has been done from the slave library and the GTID has been reset), resulting in some transactions being skipped and causing an error. What is skipped here is the construction table sentence.

3. The way to deal with it is to raise the level of GTID from the library to the main database, so as to eliminate conflicts and ensure that all transactions from the library can be executed in the main database.

4. The next day, the inspection found that some transactions were skipped automatically, and the statement that was skipped was the statement that created the temporary table. Query documents know that the creation of temporary tables will not be synchronized to the standby database, and the related operation transactions of temporary tables will also be replaced by empty transactions.

The following is a detailed process:

Recently, a master database often reported an error in the information of the synchronization slave database. The same problem occurred the next day after skipping the corresponding transaction. The error is as follows:

Last_Error: Error 'Table' mysql.ibbackup_binlog_marker' doesn't exist' on query. Default database: 'mysql'. Query: 'INSERT INTO ibbackup_binlog_marker VALUES (1)'

The details of one of them are as follows:

Mysql > show slave status\ G

* * 1. Row *

Relay_Master_Log_File: mysql-bin.000134

Slave_IO_Running: Yes

Last_Errno: 1146

Last_Error: Error 'Table' mysql.ibbackup_binlog_marker' doesn't exist' on query. Default database: 'mysql'. Query: 'INSERT INTO ibbackup_binlog_marker VALUES (1)'

Skip_Counter: 0

Exec_Master_Log_Pos: 1534712

Relay_Log_Space: 8079

Last_SQL_Errno: 1146

Last_SQL_Error: Error 'Table' mysql.ibbackup_binlog_marker' doesn't exist' on query. Default database: 'mysql'. Query: 'INSERT INTO ibbackup_binlog_marker VALUES (1)'

Replicate_Ignore_Server_Ids:

Master_Server_Id: 3809805896

Master_UUID: 44d3c576-ca26-11e3-a90a-a0369f38458a

Master_Info_File: mysql.slave_master_info

Retrieved_Gtid_Set: 44d3c576-ca26-11e3-a90a-a0369f38458a:28-1420

Executed_Gtid_Set: 258cacac-ca16-11e3-a8a1-a0369f35d966:1-160657756

2437-2439-2450-2465-2476-2478-2490-2492-2503, 2505-2516, 2518-2529, 2531-2542, 2544-2555, 2557-2568, 2570-258, 2584-2595, 2597-2608, 2610-2621, 2623-2634.

The error was an insert statement, but the target table could not be found. This statement in the mysql-bin.000134 log of the slave library, Exec_Master_Log_Pos is 1534712.

Therefore, when the slave library checks the corresponding binlog, you can see that the GTID of the insert statement is xxx:1413, and before the insert statement is the table statement, and the corresponding GTID is 1412:

# at 1534324

# 160301 0:21:00 server id 3809805896 end_log_pos 1534372 CRC32 0x9cf47996 GTID [commit=yes]

SET @ @ SESSION.GTID_NEXT= '44d3c576-ca26-11e3mura90aMuia0369f38458apur1411Compact accounts /

# at 1534372

# 160301 0:21:00 server id 3809805896 end_log_pos 1534519 CRC32 0x0e9481da Query thread_id=947514 exec_time=0 error_code=0

SET timestamp 1456762860

DROP TABLE IF EXISTS `ibbackup_binlog_ Marker` / * generated by server * /

/ *! * /

# at 1534519

# 160301 0:21:00 server id 3809805896 end_log_pos 1534567 CRC32 0xd09025a2 GTID [commit=yes]

SET @ @ SESSION.GTID_NEXT= '44d3c576-ca26-11e3mura90aMuia0369f38458apur1412Compact accounts /

# at 1534567

# 160301 0:21:00 server id 3809805896 end_log_pos 1534712 CRC32 0x04118941 Query thread_id=947514 exec_time=0 error_code=0

SET timestamp 1456762860

SET @ @ session.pseudoplastics thread readreadability idling 947514pxxxxx

CREATE TEMPORARY TABLE ibbackup_binlog_marker (an INT) ENGINE = INNODB

/ *! * /

# at 1534712

# 160301 0:21:00 server id 3809805896 end_log_pos 1534760 CRC32 0x70ca2249 GTID [commit=yes]

SET @ @ SESSION.GTID_NEXT= '44d3c576-ca26-11e3mura90aMuia0369f38458apur1413Compact accounts /

# at 1534760

# 160301 0:21:00 server id 3809805896 end_log_pos 1534841 CRC32 0x5dad491c Query thread_id=947514 exec_time=0 error_code=0

SET timestamp 1456762860

BEGIN

/ *! * /

# at 1534841

# 160301 0:21:00 server id 3809805896 end_log_pos 1534961 CRC32 0xb5f7ab48 Query thread_id=947514 exec_time=0 error_code=0

SET timestamp 1456762860

INSERT INTO ibbackup_binlog_marker VALUES (1)

/ *! * /

# at 1534961

# 160301 0:21:00 server id 3809805896 end_log_pos 1535035 CRC32 0x9baea0da Query thread_id=947514 exec_time=0 error_code=0

SET timestamp 1456762860

COMMIT

/ *! * /

When checking the relaylog of the main database, it can be sent that these transactions are successfully synchronized and are not lost. Then why do you prompt for errors that do not exist in the table? Is there any reason why the table statement is not executed?

Let's first take a look at the GTID of the main library. The maximum transaction number synchronized from the slave library is 7383:

* * 1. Row *

Variable_name: enforce_gtid_consistency

Value: ON

* 2. Row * *

Variable_name: gtid_executed

Value: 258cacac-ca16-11e3-a8a1-a0369f35d966:1-160665110

44d3c576-ca26-11e3-a90a-a0369f38458a:1-5:7-19:21-1294Partition 1322,13331335-1346Will 1348-1359Rd 1361-1372RV 1374-1412Rd 1414-1425Rd 1427-14381Rd 1453-14641Rd 1466-147814801491Rd 1493-1504Rd 1506-151730Rd 1532-154345-1556R 1558-1570R 1572-1583Rod 1585-1596Rod 1598-1609Rod 1611-1622R 1624-1635Rd 1648Rd 1650-1662Rd 1664-1675Rd 1677-1688Rd 1690-1703-1714Rd 1716-1727Rd 1729-1740Rd 1756-1767Rd 1782-17803Rd 1808-181919Rd 1834-1846Rd 1848-1859Rd 1861-1872Rd 1874-1885t1887-18981RU 1900-1924141926-1938Met 1940-1951Rol 1953-1964purr 1966-1977MAV 1979-2003Rod 2018-20303Rd 2018-203022 2043Rose 2045-2056Rd 2058-2069Rd 2084-2082Rd 2084-20952128Rd 2110-2122122124-2135Rd 2137-2147Rd 2150-2161Rd 2163-2174Rd 2176-2187Rd 2189-2200Rd 2202-2214Rd 2216-222727Rl2229-2240R2242-2253Rd 2255-22668-2279R2281-22922294-2306Rich 2308-2319Rod 2321-2332Rod 2334-2345Rod 2347-2358Rod 2360-2371Rod 2373-2384R 2386-2398Rich 2400-241113 2413-2482413 2437, 2439-2450, 2452-2463, 2465-2476, 2478-2490, 2492-2503, 2505-2516, 2518-7383:

Take a look at the library again and find that the maximum only goes to 1420!

* * 1. Row *

Variable_name: enforce_gtid_consistency

Value: ON

* 2. Row * *

Variable_name: gtid_executed

Value: 258cacac-ca16-11e3-a8a1-a0369f35d966:109528488-160665725

44d3c576-ca26-11e3-a90a-a0369f38458a:1-1420

The question is: why is the GTID of the slave library smaller than that of the master library? It should be that some students have done the reset master operation from the library and reset the GTID!

We know that when the slave database applies the log of the main database, it will check the GTID to determine whether the corresponding transaction has been executed. If the corresponding GTID is already recorded in the repository, the corresponding transaction has been executed and does not need to be executed repeatedly. On the contrary, it needs to be implemented.

As you can see from the GTID record of the main library above, GTID:1412 has already been executed. But we can boldly speculate that the 1412 that has been executed is not 1412 of the binlog of the slave library above, that is, this is the GTID that was used a long time ago. When the new 1412 transaction (table statement) from the library is synchronized to the master database, the master library finds that the GTID already has an execution record, so it skips to execute the 1413 insert statement directly, thus causing an error in Table doesn't exist.

Once you find the cause, you can determine the solution:

Because this is information synchronized from the slave library to the master library, while the slave library is read-only, only temporary information for backup is generated. So these transactions can be skipped. However, since the GTID of the slave library is smaller than that of the master library, transaction 1413 is skipped, and there will be more similar problems later. Therefore, it is also necessary to push the transaction number on the slave library to the same level as the master library, so that the transaction numbers generated from the slave library will no longer conflict with those on the master library. Pushing up the transaction number can be achieved by executing an empty transaction:

Set gtid_next='44d3c576-ca26-11e3-a90a-a0369f38458a:1421

Begin;commit

Set gtid_next='44d3c576-ca26-11e3-a90a-a0369f38458a:1422

Begin;commit

...

Set gtid_next='44d3c576-ca26-11e3-a90a-a0369f38458a:7383

Begin;commit

Set gtid_next='AUTOMATIC'

At the same time, the same processing is done on the main database to fill in the "gaps" caused by the transactions skipped above. After processing, the auto_position can be set to 1:

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Retrieved_Gtid_Set: 44d3c576-ca26-11e3-a90a-a0369f38458a:7384-7396

Executed_Gtid_Set: 258cacac-ca16-11e3-a8a1-a0369f35d966:1-161808236

44d3c576-ca26-11e3-a90a-a0369f38458a:1-7383

Auto_Position: 1

1 row in set (0.00 sec)

So far, the problem has been dealt with.

When it was checked the next day, it was found that the synchronization was normal, but the GTID of the main database application was skipped. Transaction 7388 was not executed in the main database:

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Retrieved_Gtid_Set: 44d3c576-ca26-11e3-a90a-a0369f38458a:7384-7396

Executed_Gtid_Set: 258cacac-ca16-11e3-a8a1-a0369f35d966:1-161808236

44d3c576-ca26-11e3-a90a-a0369f38458a:1-7387 7389-7396

Auto_Position: 1

1 row in set (0.00 sec)

Check the main library relaylog and find that 7388 and 7389 have been obtained, but 7388 seems to have skipped execution, so why did 7389 not report an error this time?

/ *! * /

# at 2300

# 160303 3:06:55 server id 3809805896 end_log_pos 7759399 CRC32 0x844b31b3 GTID [commit=yes]

SET @ @ SESSION.GTID_NEXT= '44d3c576-ca26-11e3mura90amura0369f38458apur7388mm /

# at 2348

# 160303 3:06:55 server id 3809805896 end_log_pos 7759544 CRC32 0x19df06b2 Query thread_id=966014 exec_time=0 error_code=0

SET timestamp 1456945615

CREATE TEMPORARY TABLE ibbackup_binlog_marker (an INT) ENGINE = INNODB

/ *! * /

# at 2493

# 160303 3:06:55 server id 3809805896 end_log_pos 7759592 CRC32 0x0641a4d4 GTID [commit=yes]

SET @ @ SESSION.GTID_NEXT= '44d3c576-ca26-11e3mura90amura0369f38458apur7389mm /

# at 2541

# 160303 3:06:55 server id 3809805896 end_log_pos 7759673 CRC32 0x76269211 Query thread_id=966014 exec_time=0 error_code=0

SET timestamp 1456945615

BEGIN

/ *! * /

# at 2622

# 160303 3:06:55 server id 3809805896 end_log_pos 7759793 CRC32 0x7627ea96 Query thread_id=966014 exec_time=0 error_code=0

SET timestamp 1456945615

INSERT INTO ibbackup_binlog_marker VALUES (1)

/ *! * /

# at 2742

# 160303 3:06:55 server id 3809805896 end_log_pos 7759867 CRC32 0xb23b3a62 Query thread_id=966014 exec_time=0 error_code=0

SET timestamp 1456945615

COMMIT

Check the binlog of the main library.

It can be found that 7388 is indeed skipped, but 7389, which is supposed to be an insert, has become an empty transaction!

# at 192527598

# 160303 3:06:55 server id 3809805896 end_log_pos 192527646 CRC32 0x68163992 GTID [commit=yes]

SET @ @ SESSION.GTID_NEXT= '44d3c576-ca26-11e3mura90amura0369f38458apur7387mm /

# at 192527646

# 160303 3:06:55 server id 3809805896 end_log_pos 192527793 CRC32 0xf6c14291 Query thread_id=966014 exec_time=0 error_code=0

Use `mysql` / *! * /

SET timestamp 1456945615

DROP TABLE IF EXISTS `ibbackup_binlog_ Marker` / * generated by server * /

/ *! * /

# at 192527793

# 160303 3:06:55 server id 3809805896 end_log_pos 192527841 CRC32 0x092b349d GTID [commit=yes]

SET @ @ SESSION.GTID_NEXT= '44d3c576-ca26-11e3mura90amura0369f38458apur7389mm /

# at 192527841

# 160303 3:06:55 server id 3809805896 end_log_pos 192527914 CRC32 0x2df7ca6b Query thread_id=966014 exec_time=0 error_code=0

SET timestamp 1456945615

BEGIN

/ *! * /

# at 192527914

# 160303 3:06:55 server id 3809805896 end_log_pos 192527988 CRC32 0x1f722f7c Query thread_id=966014 exec_time=0 error_code=0

SET timestamp 1456945615

COMMIT

/ *! * /

# at 192527988

# 160303 3:06:55 server id 3809805896 end_log_pos 192528036 CRC32 0xf9656531 GTID [commit=yes]

SET @ @ SESSION.GTID_NEXT= '44d3c576-ca26-11e3Mui a90aMuia0369f38458aVR 7390mm /

This problem is rather awkward. Take a look at the document. In the case of row format, mysql deals with temporary tab as follows:

1. No tempory table replication (this should mean creation)

2. As you can see from the binlog above, for the dml operation of temporary tab, an empty transaction is used instead of replication.

Corresponding documentation:

RBL, RBR, and temporary tables. As noted in Section 16.4.1.22, "Replication and Temporary"

Tables ", temporary tables are not replicated when using row-based format. When mixed format is in

Effect, "safe" statements involving temporary tables are logged using statement-based format. For more

Information, see Section 16.1.2.1, "Advantages and Disadvantages of Statement-Based and Row-Based"

Replication ".

MySQL documentation: 17.1.3.4 Restrictions on Replication with GTIDs

Temporary tables. CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE statements are not supported inside transactions when using GTIDs (that is, when the server was started with the-- enforce-gtid-consistency option). It is possible to use these statements with GTIDs enabled, but only outside of any transaction, and only with autocommit=1.

Note: the reset slave operation does not have any impact on the gtid, so you cannot keep the master and slave GTID consistent in this way:

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

< 省略部分信息 >

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: 73d1e54a-c3a5-11e3-bea2-005056b4006e:3-5

Executed_Gtid_Set: 73d1e54a-c3a5-11e3-bea2-005056b4006e:1-5 Magi a95348e2mure7aamur11e2Mura42fLive001b785aa468purl 1-5

Auto_Position: 1

1 row in set (0.00 sec)

Mysql >

Mysql >

Mysql >

Mysql >

Mysql > show global variables like'% gtid%'

+-+

| | Variable_name | Value |

+-+

| | enforce_gtid_consistency | ON |

| | gtid_executed | 73d1e54a-c3a5-11e3-bea2-005056b4006e:1-5, a95348e2-e7aa-11e2-a42f-001b785aa468:1-5 | |

| | gtid_mode | ON |

| | gtid_owned |

| | gtid_purged |

| | simplified_binlog_gtid_recovery | OFF |

+-+

6 rows in set (0.00 sec)

Mysql > select * from test.aa

+-+ +

| | id | name |

+-+ +

| | 1 | a |

| | 2 | a |

| | 1 | NULL |

| | 2 | NULL |

| | 3 | NULL |

+-+ +

5 rows in set (0.00 sec)

Mysql > stop slave

Query OK, 0 rows affected (0.06 sec)

Mysql > reset slave

Query OK, 0 rows affected (0.11 sec)

Mysql > show global variables like'% gtid%'

+-+

| | Variable_name | Value |

+-+

| | enforce_gtid_consistency | ON |

| | gtid_executed | 73d1e54a-c3a5-11e3-bea2-005056b4006e:1-5, a95348e2-e7aa-11e2-a42f-001b785aa468:1-5 | |

| | gtid_mode | ON |

| | gtid_owned |

| | gtid_purged |

| | simplified_binlog_gtid_recovery | OFF |

+-+

6 rows in set (0.00 sec)

This is the end of the article on "what if reset master leads to inconsistency between master and slave GTID". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

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.