MySQL's MySQL master-slave based GTID replication principle Overview

One, what is GTID (Global transaction identifiers):
MySQL-5.6.2 start support, MySQL-5.6.10 perfect, GTID into two parts, part of the service UUID, The UUID is stored in the MySQL data directory in the auto.cnf file,
This is a very important file, cannot be deleted, this part will not change. The other part is the transaction ID, and as the transaction increases, the value is incremented once, such as
+---------------+----------+--------------+------------------+---------------- ----------------------------+
| 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
The transactionid:1-362
Gtid in the entire replication architecture, even if it is not changed from one serial host to the other.
For example: ServerA--->serverb---->SERVERC
Gtid are the same from ServerA, Serverb,serverc.

Second, the working principle of Gtid:
1. When master updates the data, it generates Gtid before the transaction and logs it together into the Binlog log.
2. The slave end of the I/O thread writes the changed binlog to the local relay log.
3. SQL thread gets gtid from relay log, and then compares Binlog on slave side for record.
4, if there is a record, indicating that the Gtid transaction has been executed, slave will be ignored.
5. If there is no record, slave will execute the Gtid transaction from the relay log and log to Binlog.
6, in the parsing process will determine whether there is a primary key, if not a two-level index, if not the full scan.

Third, the advantages of Gtid:
1. A transaction corresponds to a unique ID, and a gtid is executed only once on a server
2.GTID is used to replace the traditional method of replication, gtid replication and normal replication mode is the most different than the need to specify a binary file name and location
3. Reduce manual intervention and reduce the time of service failure, when the host is hung up, the software from a large number of standby machines to upgrade a standby machine for the host

So how does Gtid replication automatically synchronize and automatically correspond to the location?
Example: ServerC <-----ServerA----> ServerB
Host ServerA
Standby machine: Serverb,serverc
When the host ServerA hung up, ServerB executed all the transactions from the ServerA.
ServerC delay a bit. This time the ServerB needs to be promoted to the host, and Server C continues to be the standby machine.
After ServerC link ServerC, first find the latest gtid from ServerA in your binary file,
Then send this gtid to ServerB, ServerB get this gtid, start from this gtid the next Gtid
Start sending transactions to ServerC. This pattern of self-seeking replication locations reduces the likelihood of transaction loss and the time to recover from failure.

Iv. Limitations of Gtid:
1. Non-transactional engines are not supported
2. Do not support CREATE TABLE ... SELECT statement Copy (Main library direct error)
Principle: (generates two SQL, one is the DDL creation table SQL, and the INSERT into inserts the data into the SQL.)
Because DDL causes autocommit, this SQL requires at least two gtid, but in Gtid mode, only one gtid is generated for this SQL)
3. Do not allow one SQL to update both a transaction engine table and a non-transactional engine table
4. In a replication group, you must require a unified open Gtid or a shutdown gtid
5. Restart Gtid required (except 5.7)
6. When Gtid is turned on, it is no longer used as the traditional copy method
7. Not supported for create temporary table and drop temporary table statements
8. Sql_slave_skip_counter not supported

MySQL's MySQL master-slave based GTID replication principle Overview