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

What is GTID (Global transaction identifiers):

MySQL-5.6.2 is supported and improved after MySQL-5.6.10. GTID is divided into two parts. One is that the UUid,UUID of the service is stored in the auto.cnf file of the mysql data directory.

This is a very important document and cannot be deleted. This part will not change. The other part is the transaction ID, which increases at a time as the transaction increases, as shown in the figure below.

+-- +

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-- +

| | binlog.000029 | 23556 | 724afcc2-29d6-11e4-9902-000c290c0121:1-362 |

+-- +

GTID:724afcc2-29d6-11e4-9902-000c290c0121:1-362,

UUID:724afcc2-29d6-11e4-9902-000c290c0121

TransactionId:1-362

GTID does not change throughout the replication architecture, even among multiple serial owners.

For example: ServerA-- > ServerB-> ServerC

GTID is the same in both ServerA and ServerB,ServerC.

Second, 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.

3. Advantages of GTID:

1. A transaction corresponds to a unique ID, and a GTID is executed only once on a server.

2.GTID is a method used to replace traditional replication. The biggest difference between GTID replication and ordinary replication mode is that there is no need to specify a binary file name and location.

3. Reduce manual intervention and service failure time. When the host is down, upgrade one standby machine to the host through software.

So how does GTID replication automatically synchronize and automatically correspond to locations?

For example: ServerC ServerB

Host ServerA

Standby: ServerB,ServerC

When the host ServerA hangs, ServerB executes all the transactions passed from ServerA

ServerC delay a little bit. At this point, ServerB needs to be promoted to the host, and Server C will continue to be the standby.

When ServerC links to ServerC, first find the latest GTID from ServerA in your binary file

Then send the GTID to ServerB, and after ServerB gets the GTID, it starts from the next GTID of this GTID

Start sending transactions to ServerC. This self-finding replication location mode reduces the possibility of transaction loss and the time it takes to recover from failures.

Fourth, the restrictions of GTID:

1. Non-transaction engine is not supported

two。 Create table is not supported. Copy the select statement (the main database reports an error directly)

Principle: (two sql are generated, one is the DDL creation table SQL, and the other is the sql in which the insert into inserts the data.

Because DDL causes auto-commit, this sql requires at least two GTID, but in GTID mode, only one GTID can be generated for this sql)

3. A SQL is not allowed to update both a transaction engine table and a non-transaction engine table

4. In a replication group, you must either enable GTID or turn off GTID.

5. Restart is required to enable GTID (except 5.7)

6. When GTID is enabled, the original traditional replication method is no longer used.

7. Not supported for create temporary table and drop temporary table statements

8. Sql_slave_skip_counter is not supported

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.