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

This article introduces the relevant knowledge of "Mysql 5.7 Gtid Internal actual case Analysis". Many people will encounter such a dilemma in the operation of actual cases. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

I. trigger conditions

In this case, I have tested four versions.

Percona Mysql 5.7.14

Official Community Mysql 5.7.17

Percona Mysql 5.7.19

Percona Mysql 5.7.15

Percona Mysql 5.7.14 and the official community Mysql 5.7.17 have this problem. Other versions are unknown

Known as percona Mysql 5.7.14 or the official community Mysql 5.7.17.

Mysqldump backup does not use the-F,-- flush-logs option

Gtid, open it.

Second, fault description

This failure is mainly caused by the newly built Gtid master-slave library. If you restart the master-slave library after a certain period of time, the error will be reported as follows:

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.' Third, fault analysis

Why do you find something wrong after restart, and then find that this Gtid thing is no longer in the binlog of the main library? it should have been a long time ago. In fact, we need to go back to the file from mysqldump how to initialize Gtid and the mysql.gtid_ executed table.

When mysqldump does not use-- set-gtid-purged, it is bound to be included in the script from dump.

-- GTID state at the beginning of the backup SET @ @ GLOBAL.GTID_PURGED='e859a28b-b66d-11e7-8371-000c291f347d:1-41'

Such a statement that sets the GTID_PURGED contains all the Gtid transactions that have been executed on the main library. We know from the source and summary sections in section 5 that at least three changes have been made to this statement (there are only three visible in DBA):

Writing of mysql.gtid_executed table

Modification of gtid_executed variable

Modification of gtid_purged variable

After completing this step, the mysql.gtid_executed table actually contains all the executed Gtid transactions, but then we see that the dump script contains the following statements

210-- Table structure for table `gtid_ executed`211-- 212213 DROP TABLE IF EXISTS `gtid_ executed`; 214 / *! 40101 SET @ saved_cs_client = @ @ character_set_client * /; 215 / *! 40101 SET character_set_client = utf8 * / 216 CREATE TABLE `First number of interval.', executed` (217 `source_ uuid` char (36) NOT NULL COMMENT 'uuid of the source where the transaction was originally executed.', 218`interval_ start` bigint (20) NOT NULL COMMENT' First number of interval.', 219` interval_ end` bigint (20) NOT NULL COMMENT 'Last number of interval.', 220 PRIMARY KEY (`source_ uuid`, `interval_ start`) ENGINE=InnoDB DEFAULT CHARSET=utf8; 2222 / *! 40101 SET character_set_client = @ saved_cs_client * / 223 224-225-Dumping data for table `gtid_ executed`226-227228 LOCK TABLES `gtid_ executed`WRITE; 229 / *! 40000 ALTER TABLE `gtid_ executed`DISABLE KEYS * /; 230INSERT INTO `gtid_ executed` VALUES ('e859a28b-b66d-11e7-8371-000c291f347dFeiguo 332); 231 / *! 40000 ALTER TABLE `gtid_ executed` ENABLE KEYS * /

Obviously here we will reinitialize the mysql.gtid_executed of the library when we source to:

'e859a28b-b66d-11e7-8371-000c291f347dchimera 1Phone32

The actual Gtid that has been executed is:

'e859a28b-b66d-11e7-8371-000c291f347d:1-41'

For example, in section 5 above, after analyzing the source code, we summarize as follows:

When the mysql.gtid_ execute table is modified, all Gtid that have been executed up to the last binlog file are saved when the binlog is switched (rotate). It is not updated in real time.

Therefore, the time table does not fully contain all the executed Gtid transactions, and in the source code analysis in section 6 above, we know that two Gtid persistence media must be read when the Gtid module starts:

Mysql.gtid_executed

Binlog

To judge the collection of Gtid, it is obviously impossible to include this Gtid thing in binlog from the library, so such an operation procedure leads to an error report after the database from the database, and the correct step here is not to rebuild and import mysql.gtid_executed at all, which I found to be the case in percona Mysql 5.7.15 and percona Mysql 5.7.19. But in order to prevent this problem, I added two steps after importing data from the database in the built Gtid:

Reset master;set global gtid_purged='e859a28b-b66d-11e7-8371-000c291f347d:1-41'

These two steps are to reinitialize the mysql.gtid_ executed table and make it correct.

You can also add the-F,-- flush-logs option to avoid this problem when mysqldump, but-F will add the following MDL LOCK:

2017-12-18T08:16:39.580985Z 6 Query FLUSH / *! 40101 LOCAL * / TABLES2017-12-18T08:16:39.612598Z 6 Query FLUSH TABLES WITH READ LOCK2017-12-18T08:16:39.613406Z 6 Refresh/root/mysql5.7.14/percona-server-5.7.14-7/sql/mysqld, Version: 5.7.14-7-debug-log (Source distribution). Started with:Tcp port: 13001 Unix socket: / root/mysql5.7.14/percona-server-5.7.14-7/mysql-test/var/tmp/mysqld.1.sockTime Id Command Argument2017-12-18T08:16:39.965847Z 6 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ2017-12-18T08:16:39.966298Z 6 Query START TRANSACTION / *! 40100 WITH CONSISTENT SNAPSHOT * / 2017-12-18T08:16:39 .966792Z 6 Query SHOW VARIABLES LIKE 'gtid\ _ mode'2017-12-18T08:16:39.969587Z 6 Query SELECT @ @ GLOBAL.GTID_EXECUTED2017-12-18T08:16:39.970216Z 6 Query SHOW STATUS LIKE' binlog_snapshot_%'2017-12-18T08:16:39.975242Z 6 Query UNLOCK TABLES

This lock is a GLOBAL-level MDL_SHARED (S) lock, which will not be obtained until the end of the SELECT/DML/DDL statement you say, and will block all SELECT/DML/DDL, although the holding time is as follows:

Mysql > flush tables with read lock Query OK 0 rows affected (0.01sec) 2017-08-03T18:19:11.603911Z 3 [Note] (acquire_lock) THIS MDL LOCK acquire oklines 2017-08-03T18:19:11.603947Z 3 [Note] (> MDL PRINT) Thread id is 3: 2017-08-03T18:19:11.603971Z 3 [Note] (- > MDL PRINT) Namespace is:GLOBAL 2017-08-03T18:19:11.603994Z 3 [Note] (- > MDL PRINT) Mdl type is:MDL_SHARED (s) 2017-08-03T18:19:11.604045Z 3 [Note] (- > MDL PRINT) Mdl duration is:MDL_EXPLICIT 2017-08-03T18:19:11.604073Z 3 [Note] (- > MDL PRINT) Mdl status is:EMPTY 2017-08-03T18:19:11.604133Z 3 [Note] (acquire_lock) THIS MDL LOCK acquire ok!

Of course, friends who want to know more about MDL LOCK can refer to my article.

Http://blog.itpub.net/7728585/viewspace-2143093/

MYSQL METADATA LOCK (MDL LOCK) Learning (1) theoretical knowledge and lock type testing

4. Fault simulation

After knowing the reason, it is also good to simulate that the version I use is Community version 5.7.17, and the building process is the step mentioned above. It's just that the mysql.gtid_ execute table is reinitialized without using the reset master and set gtid_ purged tables after importing the data. After the construction is completed, the normal state of several things is as follows:

Mysql > show slave status\ gateway * 1. Row * * Master_Log_File: binlog.000002 Read_Master_Log_Pos: 5077 Relay_Log_File: test1-relay-bin.000002 Relay _ Log_Pos: 2498 Relay_Master_Log_File: binlog.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Exec_Master_Log_Pos: 5077 Relay_Log_Space: 2705 Last_IO_Errno: 0 Last_IO_Error: Seconds_Behind_Master: 0 Retrieved_Gtid_Set: E859a28b-b66d-11e7-8371-000c291f347d:42-49 Executed_Gtid_Set: e859a28b-b66d-11e7-8371-000c291f347d:1-49 Auto_Position: 1

But at this point we found that there was a problem with the mysql.gtid_ executed table as follows:

Mysql > select * from gtid_executed +-- + | source_uuid | interval_start | interval_end | + -+ | e859a28b-b66d-11e7-8371-000c291f347d | 1 | 32 | | e859a28b-b66d-11e7-8371-000c291f347d | 42 | 42 | | e859a28b-b66d-11e7-8371-000c291f347d | 43 | 43 | | e859a28b-b66d-11e7-8371-000c291f347d | 44 | 44 | | e859a28b-b66d-11e7-8371-000c291f347d | 45 | 45 | e859a28b-b66d-11e7-8371-000c291f347d | 46 | 46 | | e859a28b-b66d-11e7-8371-000c291f347d | 47 | 47 | e859a28b-b66d-11e7-8371-000c291f347d | 48 | 48 | | e859a28b-b66d-11e7-8371-000c291f347d | 49 | 49 | +-+

It is easy to find that there is no persistence between 33 and 41. If we use purge binary logs to to clean up the logs of the main library at this time, there will be a problem, and if we don't clean up, there will be a re-execution of Gtid transactions. We do clean-up simulation of online errors. The main library executes:

Mysql > show binary logs +-+-+ | Log_name | File_size | +-+-+ | binlog.000001 | 9974 | | binlog.000002 | 5121 | binlog.000003 | 194 | +-+-+ 3 Rows in set (0.01 sec) mysql > purge binary logs to 'binlog.000003' Query OK, 0 rows affected mysql > show binary logs;+-+-+ | Log_name | File_size | +-+-+ | binlog.000003 | 194 | +-+-+ 1 row in set (0.00 sec)

Error reappears after slave database restart:

Mysql > show slave status\ gateway * 1. Row * * Slave_IO_State: Master_Host: 192.168.190.62 Master_User: repl Master_Port : 3308 Connect_Retry: 60 Master_Log_File: binlog.000003 Read_Master_Log_Pos: 194 Relay_Log_File: test1-relay-bin.000005 Relay_Log_Pos: 4 Relay_Master_Log_File: binlog.000003 Slave_IO_Running: No Slave_SQL_Running: Yes Exec _ Master_Log_Pos: 194 Relay_Log_Space: 194 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.' Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Retrieved_Gtid_Set: e859a28b-b66d-11e7-8371-000c291f347d:42-49 Executed_Gtid_Set: e859a28b-b66d-11e7-8371-000c291f347d:1-32:42-49 Auto_Position: 1

We find that the things that I thread tries to get the 33-41 Gtid things of the main database can no longer be obtained, in fact, even if they can be obtained, it will cause the re-execution of things. We can see that there are two consecutive intervals in Executed_Gtid_Set:

Executed_Gtid_Set: e859a28b-b66d-11e7-8371-000c291f347d:1-32:42-49 "Mysql 5.7 Gtid Internal actual case study" ends here. Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.