MySQL setting Gtid-based replication

The concept of Gtid

GTID (full name global transaction identifier) is the unique identifier for the transaction. The format is as follows:
GTID = source_id:transaction_id
SOURCE_ID: Identifies the source server, usually the server_uuid of the server
TRANSACTION_ID: Sequence number ordered by the order of transactions committed on the server.
For example:

60f9111a-cdba-11e7-b354-005056a30507:1

Add the following information in the configuration file to enable Gtid mode

[Mysqld]
Gtid_mode=on

After you enable Gtid mode, you can view the Gtid collection that has been executed by using the following statement.

Mysql>show Master Status

The application of Gtid in replication

• In the main library: When a transaction executes a commit on the main library, the transaction is assigned a gtid,gtid written to the log before the transaction is written to the primary repository's binary log.
• Get the binary log of the main library from the library and store it in the trunk log from the library (relay log). Read Gtid from the library and set the value of Gtid_next to this gtid. This tells from the library that the next transaction must use this Gtid record.
• Verify from the library that Gtid is already in use in its own binary log. If not used, the Gtid is written from the library, the transaction is applied, and the transaction is logged to the binary log from the library.
• Since the Gtid_next is non-empty, the library does not generate a new gtid for the transaction, but instead uses the Gtid obtained from the main library.

Setting up Gtid-based replication

And normal replication is not very different, just need the main library and from the library to open Gitd_mode and enforce-gitd-consistency. If the library is version 5.6, additional--log-bin and--log-slave-updates are required in the main library and from the library. (regardless of version, the main library must have Log-bin turned on)
This is because after MySQL5.7.5, Gtids is stored in the gtid_executed table in the MySQL system library.

1. Modify the configuration file and restart the database
   Add the following in MY.CNF

   [Mysqld]
   Gtid_mode=on
   Enforce-gtid-consistency
   Log-bin
   Log-slave-updates=1

2. Create the account used for replication on the main library

   Create user ' repl ' @ ' hostname ';
   Grant REPLICATION SLAVE on * * to ' repl ' @ ' hostname ';

3. Synchronizing data from the main library and from the library, if required
   Synchronizing data with mysqldump or other tools

4. To set up Gtids-based replication from the library, use the following statement

   Mysql> Change MASTER to
   Master_host = HOST,
   Master_port = PORT,
   Master_user = USER,
   Master_password = PASSWORD,
   Master_auto_position = 1;

5. Check Replication status

   Mysql> Show Salve Status\g

MySQL Settings Gtid-based replication