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

Brief introduction

GTID is a new feature of MySQL 5.6. its full name is Global Transaction Identifier, which simplifies the master-slave handover and Failover of MySQL. GTID is used to uniquely identify a transaction in binlog. When a transaction commits, when MySQL Server writes binlog, it first writes a special Binlog Event of type GTID_Event, specifies the GTID of the next transaction, and then writes the Binlog of the transaction. During the master-slave synchronization, both the GTID_Event and the Binlog of the transaction are passed to the slave library, and the slave library uses the same GTID to write the binlog when executing, so that after the master-slave synchronization, the location of the slave library synchronization can be determined by GTID. In other words, whether it is cascading or one-master-multi-slave situation, you can automatically find points through GTID, instead of finding points through File_name and File_position as before.

Representation of GTID

MySQL 5.6 uses both server_uuid and transaction_id to form a GTID. That is: GTID = server_uuid:transaction_id

Server_uuid is a read-only variable of MySQL Server, which is stored in auto.cnf in the data directory and can be viewed directly through the cat command. MySQL first starts to create auto.cnf files and generate server_uuid (MySQL uses machine network cards, current time, random numbers, etc. to splice into a 128bit uuid, which can be considered unique in the universe, and uuid generated using the same algorithm will not conflict in the next hundred years). Later, when MySQL starts again, it does not repeatedly generate uuid, but uses uuid in auto.cnf. You can also view the server_uuid through the MySQL client using the following command, and what you see is actually the hexadecimal code of the server_uuid, a total of 16 bytes (the horizontal lines in the uuid are only for easy viewing and have no practical significance).

1 2 3 4 5 6 7mysql > show global variables like 'server_uuid' +-+-+ | Variable_name | Value | +-+-+ | Server_uuid | b3485508-883f-11e5-85fb-e41f136aba3e | +-+-- + 1 row in set (0.00 sec)

In the same cluster, the server_uuid of each MySQL instance must be unique, otherwise synchronization will result in continuous interruptions and reconnections of IO threads. When restoring data from a backup, be sure to delete the auto.cnf from the var directory and let MySQL generate its own uuid when it starts.

Another part of the GTID is transaction_id, and the transaction_id under the same server_uuid is generally incremented. If a transaction is executed through a user thread, MySQL uses its own server_uuid when generating the GTID, and then increments another transaction_id as the GTID for the transaction. Of course, if the transaction is generated when the relay-log is played back through the SQL thread, then the GTID is directly used in the binlog. Don't worry about not having GTID in binlog in MySQL 5.6.If GTID mode is turned on from the slave library, the master library must also be turned on, otherwise the IO thread will be interrupted when the connection is established. GTID 5.6 has very strict requirements on the cluster environment of MySQL, either master-slave GTID mode is enabled or GTID mode is turned off.

As mentioned just now, the transaction_id under the same server_uuid is generally incremented. Isn't it incrementing in some cases? The answer is yes. MySQL supports setting the Session-level variable gtid_next to specify the GTID of the next transaction in the format 'server_uuid:transaction_id'. You can then change it back to AUTOMATIC (default)

12 3 4 5 6 7 8mysql > set gtid_next = 'b694c8b2-883f-11e5-85 fbmure41f136aba3eV12000005'; Query OK, 0 rows affected (0.00 sec) mysql > begin; Query OK, 0 rows affected (0.00 sec) mysql > commit; Query OK, 0 rows affected (0.00 sec) mysql > set gtid_next = AUTOMATIC; Query OK, 0 rows affected (0.00 sec)

Gtid_next is generally set to add 1, which is used to skip a transaction when master-slave synchronization. However, if the transaction_id under the current server_uuid is not contiguous after gtid_next is set, then the trickery will also appear. After changing back to AUTOMATIC, when another transaction executes, MySQL generates transaction_id instead of continuing to grow at the current maximum transaction_id, but filling the gap (using the smallest missing transaction_id as the next gtid). In this way, even for the same server_uuid, the order of transactions cannot be determined by the size of the transaction_id.

The advantage of using server_uuid:transaction_id to form a GTID is that because server_uuid is unique, it does not cause GTID conflicts even if multiple nodes in a cluster have writes at the same time.

The use of GTID

MySQL toggles GTID mode on / off through the global variable gtid_mode. But gtid_mode is read-only and can be added to the configuration file, and then restart mysqld to turn on GTID mode. The relevant configuration items are as follows:

1 2 3 4 5gtid-mode = ON enforce_gtid_consistency = 1 log-slave-updates = 1 log-bin = mysql-bin log-bin-index = mysql-bin.index

The configuration mode is gtid_mode=ON/OFF. What is surprising is that the type of gtid_mode is enumerated, and the enumerated values can be ON and OFF, so you should control gtid_mode through ON or OFF, and do not configure it to 0 or 1, otherwise the result may not meet your expectations. When gtid_mode is enabled, log-bin and log-slave-updates must also be enabled, otherwise MySQL Server refuses to start. In addition, enforce-gtid-consistency must also be turned on, otherwise MySQL Server refuses to start. Enforce-gtid-consistency is because after opening grid_mode, many MySQL's SQL and GTID are not compatible. For example, when opening the ROW format, CREATE TABLE. SELECT, two different transactions will be formed in binlog, and GTID cannot be unique. It is also not allowed to update the MyISAM table in a transaction.

As mentioned just now, when GTID mode is turned on, all MySQL Server in the cluster must be configured with gtid_mod = ON at the same time, otherwise they cannot be synchronized.

Once synchronized using GTID mode, the master-slave switch can use GTID to find points automatically, by specifying MASTER_AUTO_POSITION=1 when CHANGE MASTER. The command is as follows:

1 2 3 45 6mysql > CHANGE MASTER TO\-> MASTER_HOST ='',\-> MASTER_PORT = 3306,\-> MASTER_USER = 'test',\-> MASTER_PASSWORD ='',\-> MASTER_AUTO_POSITION = 1

You can also see Auto_Position: 1 through SHOW SLAVE STATUS, indicating that START SLAVE will use GTID to find points automatically in the future. FileName and FilePosition are also supported in principle after GTID is enabled, but it is not recommended. If you have to use it, specify MASTER_AUTO_POSITION=0 when you CHANGE MASTER

MySQL can view the execution of GTID through several variables.

1 2 3 4 5 6 7 8 9 10mysql > show global variables like 'gtid_%' +-+ | Variable_name | Value | + -+-+ | gtid_executed | b694c8b2-883f- 11e5-85fb-e41f136aba3e:1-10114525 11e5 12000000-12000005 | | gtid_mode | ON | | gtid_owned | b694c8b2-883f-11e5-85fb-e41f136aba3e:10114523#10:10114525#6:10114521#5:10114524#8:10114522#4 | | gtid_purged | b694c8b2-883f-11e5-85fb-e41f136aba3e:1-8993295 | + -+ 4 rows in set (0.00 sec)

There are four variables, of which gtid_mode has been introduced, and the other three variables have the following meanings

Gtid_executed: this is both a Global-level variable and an Session-level variable, which is read-only. A Global-level gtid_executed represents a collection of GTID that has been executed by the current instance. A Session-level gtid_executed is generally empty.

Gtid_owned: this is both a Global-level variable and an Session-level variable, which is read-only. The gtid_owned at the Global level represents the GTID that the current instance is executing, and the corresponding thread id. A Session-level gtid_owned is generally empty.

Gtid_purged: this is a Global-level variable that can be modified dynamically. We know that binlog can be dropped by purge. Gtid_purged represents the collection of GTID in the current instance that has been dropped by purge. Obviously, gtid_purged is a subset of gtid_executed. However, gtid_purged can not be modified at will, and gtid_purged can only be set dynamically when @ @ global.gtid_executed is empty.

GTID related Binlog

As can be seen from the previous introduction, GTID can uniquely identify a transaction in binlog. To understand the principle of finding GTID, you must know the format of Binlog. First, take a look at a Binlog.

12 34 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 26 27 28 30 31 32 33 34 35 3 at 120 # 151222 9:07:58 server id 1026872634 end_log_pos 247 CRC32 0xedf993a8 Previous-GTIDs # b3485508-883f-11e5-85fb-e41f136aba3e:1-14 # b694c8b2-883f-11e5-85fb-e41f136aba3e:1-10115960 at 12000000-12000005 # at 247 # 151222 9:08:03 server id 1026872625 end_log_pos 295 CRC32 0xc3d3d8ee GTID [commit = yes] SET @ @ SESSION.GTID_NEXT= 'b694c8b2-883f-11e5-85fbMeie41f136aba3erel 10115961' / *! * / # at 1026872625 # 151222 9:08:03 server id 1026872625 end_log_pos 370 CRC32 0x0a32d229 Query thread_id=18 exec_time=1 error_code=0 BEGIN / *! * /; # at 370 # 151222 9:08:03 server id 1026872625 end_log_pos 480 CRC32 0x3c0e094f Query thread_id=18 exec_time=1 error_code=0 use `db` / *! * /; SET TIMESTAMP = 14507464483 update tb set val = val + 1 where id=1 / *! * / # at 480 # 151222 9:08:03 server id 1026872625 end_log_pos 511CRC32 0x5772f16b Xid = 6813913 COMMIT / *! * /; # at 511 # 151222 9:10:19 server id 1026872625 end_log_pos 559 CRC32 0x3ac30191 GTID [commit = yes] SET @ @ SESSION.GTID_NEXT= 'b694c8b2-883f-11e5-85fbcopyright e41f136aba3eVane 10115962' / *! * /; # at 559 # 151222 9:10:19 server id 1026872625 end_log_pos 634 CRC32 0x83a74912 Query thread_id=18 exec_time=0 error_code=0 SET TIMESTAMP = 1450746619CPU BEGIN / *! * /; # at 634 # 151222 9:10:19 server id 1026872625 end_log_pos 744 CRC32 0x581f6031 Query thread_id=18 exec_time=0 error_code=0 SET TIMESTAMP = 1450746619 update tb set val = val + 1 where id=1 / *! * / # at 744 # 151222 9:10:19 server id 1026872625 end_log_pos 775 CRC32 0x793f8e34 Xid = 6813916 COMMIT / *!

This Binlog is intercepted at the offset of file 120 (the first Binlog Event after Format_description_log_event). As you can see, the first Binlog Event is of type Previous-GTIDs, which exists in every binlog file. When GTID is turned on, each binlog file has one and only one Previous-GTIDs, and the location is at the first Binlog Event after Format_description_log_event. It means a collection of GTID that has been executed before the current Binlog file and can be used as an index reference. Using this Binlog Event, it is easy to quickly determine whether the GTID is in the current binlog file.

Let's take a look at how gtid_purged and gtid_executed are constructed. MySQL opens the oldest binlog file at startup and reads the Previous-GTIDs in it, so it is @ @ global.gtid_purged. MySQL opens the latest binlog file at startup, reads the Previous-GTIDs in it, constructs a gtid_set, and then iterates through the latest binlog file, adding every gtid encountered to the gtid_set. When the file traversal is complete, the gtid_set is @ @ global.gtid_executed.

As mentioned earlier, @ @ global.gtid_executed can be set dynamically only if @ @ global.gtid_purged is empty. So you can clear @ @ global.gtid_executed through RESET MASTER. This is similar to the command in Ares: set binlog_group_id=XXX, master_server_id=YYY with reset; (binlog will be deleted)

By parsing the binlog file above, we can also see that before each transaction, there is a GTID_log_event that specifies the value of GTID. Overall, the format of an MySQL binlog is roughly as follows:

GTID, find some principles.

We know that when GTID mode is not turned on, the location of execution is identified from the library (File_name and File_pos) tuples. In the case of START SLAVE, the slave library first sends a BINLOG_DUMP command to the master database, specifying File_name and File_pos in the BINLOG_DUMP command, and the master library sends binlog from this location.

If MASTER_AUTO_POSITION=1 is specified when GTID mode is on. When START SLAVE, the slave library calculates the union of Retrieved_Gtid_Set and Executed_Gtid_Set (which can be seen through SHOW SLAVE STATUS), and then sends the GTID union to the master library. The master library uses the GTID collection requested from the slave library to compare with its own gtid_executed, sending all transactions missing from the GTID collection of the slave library to the slave library. What if the GTID missing from the slave library has already been pruge by the master library? A 1236 error was reported from the library and the IO thread was interrupted.

The principle of finding a point through GTID is rather strange, it puts too much emphasis on the consistency of GTID sets in master-slave binlog, weakening the sequence of Binlog execution.

Consider the following situation: a cluster is already using GTID mode to synchronize. Xiao Ming wants to add a slave database to the cluster, and the data is the same as the master database, but there is no binlog, that is, the @ @ global.gtid_executed of the new slave database is empty. But when you CHANGE MASTER, you can find the correct synchronization point through File_name and File_pos, and then START SLAVE, everything is fine. After a while, Xiaoming felt that he could re-CHANGE MASTER and then START SLAVE by MASTER_AUTO_POSITION = 1. In this case, as soon as the master library sees that there is so much binlog missing from the library GTID, and then sends all the missing binglog to the slave library again, then a tragedy occurs.

In order to solve this problem, after Xiaoming has finished the slave library, he should execute reset master; set global gtid_purged = 'xxxxx', on the slave library to set the missing GTID collection to purged, and then he can use MASTER_AUTO_POSITION=1 to find points automatically.

Thus it can be seen that after opening GTID, Binlog is as important as data files, requiring not only the consistency of master-slave data, but also the consistency of GTID sets in master-slave Binlog.

Considerations for GTID

1) after GTID is enabled, you cannot skip transactions using sql_slave_skip_counter. As mentioned earlier, when using GTID to find something, the master library will send the missing GTID of the slave library to the slave library, so skip is useless. In order to detect the problem in advance, MySQL directly forbids the use of set global sql_slave_skip_counter = x in gtid mode. The right thing to do is to set grid_next= 'zzzz' (' zzzz' is the transaction to be skipped), then execute BIGIN;COMMIT to generate an empty transaction, occupy this GTID, and then START SLAVE, you will find that the GTID of the next transaction has been executed, and you will skip the transaction

2) if a GTID has already been executed and a duplicate GTID is encountered, it will be skipped from the library directly, which can be regarded as the idempotency of GTID execution.

3) use restrictions: https://dev.mysql.com/doc/refman/5.6/en/replication-gtids-restrictions.html

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.