MySQL Based on Gtid replication

GTID Replication:
In MySQL 5.6.5 or more, MySQL has added a Gtid-based replication method. The GTID ensures that each transaction committed on the primary database has a unique ID in the cluster, which strengthens the consistency of the database master, failback, and fault tolerance.

What is Gtid? ：
GTID (global Transaction ID) is the number of a committed transaction and is a globally unique number. GTID is made up of Uuid+tid. The UUID is a unique identifier for a MySQL instance, and TID represents the number of transactions that have been committed on that instance and is monotonically incremented as the transaction commits.
What is a UUID?
MySQL 5.6 replaced the original 32-bit server-id with a 128-bit server-uuid. Server-id is a manual configuration based on my.cnf, which is prone to conflict, and the UUID guarantees that the generated server-uuid avoids conflict issues.
To view the UUID:
Show variables like "%server_uuid%";
3ff69404-0b11-11e8-a5c9-000c29713b71
View Gtid:
3ff69404-0b11-11e8-a5c9-000c29713b71:1
Gtid form:
3ff69404-0b11-11e8-a5c9-000c29713b71:1
3ff69404-0b11-11e8-a5c9-000c29713b71:5 #一组连续的事物

How the Gtid replication implementation works

1. When master updates the data, it generates Gtid before the transaction, which is recorded in the Binlog log together;
2. The slave IO thread writes the changed binlog to the local relay log;
3. SQL thread obtains gtid from relay log, and then compares slave binlog for records;
4. If there is a record stating that the Gtid's transaction has been performed, slave will ignore it;
5. If there is no record, slave executes the Gtid transaction from the relay log and logs it 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.

Show Binlog events in ' mysql-bin.000003 ';
| mysql-bin.000003 | 2152 | Gtid | 1283306 | 2217 | SET @ @SESSION. gtid_next= ' 3ff69404-0b11-11e8-a5c9-000c29713b71:12 ' |
| mysql-bin.000003 | 2217 | Query | 1283306 | 2290 | BEGIN |
| mysql-bin.000003 | 2290 | Table_map | 1283306 | 2339 | table_id:145 (GTIDB.GTITB) |
| mysql-bin.000003 | 2339 | Write_rows | 1283306 | 2379 | table_id:145 Flags:stmt_end_f |
| mysql-bin.000003 | 2379 | Xid | 1283306 | 2410 | COMMIT/* xid=588 */|

Configure Gtid Master-slave replication:
Master configuration:

• Gtid-mode:gtid-mode=on
• Enforce-gtid-consistency:enforce-gtid-consistency=on
• Log-slave-updates:log-slave-updates:on
• Server-id 1003306, (recommended IP after two digits plus database port number)
• Binlog_format:binlog_format=row
• Log-bin:log-bi=on

To create a copy account:
Grant Replication Slave on * * to ' repl ' @ '% ' identified by ' password ';
Flush privileges;
To view the master status:
Show Master Status\g;

Slave configuration:

• Gtid-mode:gtid-mode=on
• Enforce-gtid-consistency:enforce-gtid-consistency=on
• Log-slave-updates:log-slave-updates:on
• Server-id 1013306, (recommended IP after two digits plus database port number)
• Binlog_format:binlog_format=row
• Log-bin:log-bi=on

Connect Master data:
Connect to Main library:
Change master to
master_host= ' 192.168.100.100 ',
master_user= ' Repl ',
Master_ Password= ' PASSWORD ',
master_port=3306,
Master_auto_position=1;
Turn on sync:
start slave;
View slave status:
show slave status\g;
Slave_io_running:yes
Slave_sql_running:yes
Retrieved_gtid_set:3ff69404-0b11-11e8-a5c9-000c29713b71:1-3
Executed_gtid_set:3ff69404-0b11-11e8-a5c9-000c29713b71:1-3

Gtid Related parameters:
Show global variables like '%gtid% ';
Show variables like '%gtid_next% ';
Gtid_executed: The Gtid collection executed on the current instance; It actually contains all the transactions that were recorded in the Binlog. Therefore, a transaction executed after setting set sql_log_bin=0 does not generate a Binlog event and is not recorded in gtid_executed. Perform reset master to empty the variable.
Gtid_purged:binlog cannot reside permanently on the service and needs to be cleaned up periodically (through Expire_logs_days can control the periodic cleanup interval), or it will run out of disk sooner or later. The gtid_purged is used to record a collection of binlog transactions that have been cleared, which is a subset of the gtid_executed. The variable can be set manually only if gtid_executed is empty, and the same value gtid_executed to and gtid_purged are updated at the same time. Gtid_executed is null means either Gtid-based replication has not been initiated before, or reset MASTER has been executed. When you perform reset master, the gtid_purged is also empty, that is, always keep gtid_purged as a subset of gtid_executed.

Gtid_next: A session-level variable that indicates how the next gtid is generated.

• AUTOMATIC: Automatically generates the next Gtid, which is assigned a minimum number of gtid that have not been executed on the current instance.
• ANONYMOUS: Executing a transaction after setting does not produce gtid.
• Explicitly specified gtid: You can specify an arbitrary form of a valid Gtid value, but not the gtid that is already contained in the current gtid_executed, or the next time the transaction is executed.

MySQL Based on Gtid replication