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

This article shares with you the content of the sample analysis of MYSQL_GTID. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

I. Overview of GTID

GTID is a new feature of MYSQL5.6. GTID (Global Transaction Identifier) is a global transaction identifier, which is used to uniquely identify database instance transactions. It is mainly composed of source_id and transaction_id, that is, GTID = source_id:transaction_id. Where source_id is the unique identification of the database instance automatically generated by the database startup, which is saved in auto.cnf, and transaction_id is the sequence number of the transaction execution.

Second, the advantages and disadvantages of GTID

Advantages:

Replication is more secure, and a transaction is executed only once on each instance

Failover is simple, and master-slave relationships can be established by setting up MASTER_AUTO_POSITION=1 instead of master_log_file and master_log_pos

The earliest committed instance of a transaction can be determined according to GTID

Disadvantages:

In group replication, you must uniformly enable GTID or disable GTID

Copying create table table_name select is not supported. From table_name_xx

Create temporary table and drop temporary table are not supported

Sql_slave_skip_counter is not supported, but can be skipped through set global gtid_next=''

Both the slave library and the master library must set log_slave_updates

Third, the working principle of GTID

1. When master updates data, GTID will be generated before the transaction and recorded in the binlog log.

2. The iUnip thread on the slave side writes the changed binlog to the local relay log.

3. The SQL thread gets the GTID from the relay log, and then compares whether the binlog on the slave side has a record.

4. If there is a record, the transaction of the GTID has been executed, and slave will ignore it.

5. If there is no record, slave will execute the transaction of the GTID from relay log and record it to binlog.

6. During the parsing process, it will determine whether there is a primary key, if not, use a secondary index, and if not, use a full scan.

IV. GTID on and off

Gtid_mode=ON (required)

Log_bin=ON (required)

Log-slave-updates=ON (required)

Enforce-gtid-consistency (required)

Log-bin = / home/mysql/mysql-bin (required)

Binlog_format = MIXED (mixed or row is required)

# #

Change master to master_host = 'ipaddr',master_port = 3306 mastery user =' username',master_password='password',master_auto_position = 1

5. Applicable scenarios for GTID

1. Build a highly available architecture to facilitate the new slave library to reassign the master library after the master-slave switch (for example, the structure of one master and two slaves, A for mater,B, Slave,C for Slave,A, and B for Slave,A downtime, C reassigns the master library to B)

2. Create table table_name select * from table_name/create temporary table/update T1 where T2 where is not often used. The situation of this kind of sentence

VI. Parameters related to GTID

All GTID executed by parameter commentgtid_executed, which can be reset via reset master

Gtid_purged discards the GTID. After setting it, slave will no longer request these GTIDs from master, and if Executed_Gtid_Set is empty, you can set this value to gtid_modegtid mode gtid_nextsession-level variables. The next gtidenforce_gtid_consistency running gtidenforce_gtid_consistency guarantees GTID security parameters. Thank you for reading! This is the end of this article on "sample Analysis of MYSQL_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, you can 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.