Understand Mysql's GTID-based replication mode

mysql tutorialThe column introduces what is the GTID-based replication mode of Mysql

Recommended (free): mysql tutorial

##GTID definition

GTID (Global Transaction Identifier) ​​Global transaction identifier. GTID is a major improvement on master-slave replication introduced in version 5.6. Compared with the previous version of master-slave replication based on Binlog file Position, GTID-based master-slave replication has higher data consistency and more robust master-slave data replication. Switchover and failover are less error-prone and rarely require human intervention.

Representation method

GTID = server_uuid:transaction_id

The GTID is usually recorded in the MySQL system variable
@@GLOBAL In .gtid_executed and system table mysql.gtid_executed, the system variable @@GLOBAL.gtid_executed is in memory and belongs to non-persistent storage, while the system table mysql.gtid_executed belongs to persistent storage. storage.

The advantages of GTID over traditional replication

Easier to build master-slave replication
1. Easier implementation For failover (master-slave switching), there is no need to find the
2. log_file and log_pos
GTID step by step as before. The GTID is continuous and has no holes, ensuring data consistency. Zero lost.
3. The replication cluster has a unified way to identify the replication location, which brings convenience to cluster management

GTID restrictions

Mixing engines such as Innodb and myisam in one transaction will result in multiple GTIDS
2. CREATE TABLE…..SELECT cannot be used
3. CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE cannot be used within a transaction

Master-slave replication flow chart

##GTID life cycle

When a transaction is executed and committed on a main database , then this transaction will be assigned a gtid associated with the main library uuid, and this gtid will be written to the binlog file of the main library.

1. When the binlog file reaches the maximum size and is rotated, or MySQL Server is shut down, the transaction GTID in the previous binlog file will be written to the
mysql.gtid_executed
2. table. When a transaction is submitted, the gtid of the transaction will be quickly added to the system variable
@@GLOBAL.gtid_executed
3. , but the system table mysql.gtid_executed will not, because some gtid is still there In binlog, you need to wait until binlog rotation or mysqlServer is shut down before writing to mysql. gtid_executedIn the table.The binlog on the main library is transferred to the slave library through the master-slave replication protocol, and written to the relay log (relay log) of the slave library, and read from the slave library For the gtid and corresponding transaction information in the relay log, set
gtid_next
4. to the gtid value, so that the slave library uses the gtid value to apply its corresponding transaction If multiple threads concurrently apply the same For a transaction, such as multiple threads setting gtid_next to the same value, MySQL Server only allows one of the threads to execute.
gtid_owned
5. The system variable records who owns the GTID.

Traditional replacement GTID replication mode

Configure GTID

1. All server settings
global.read_only
2. parameters, waiting for the master and slave servers Synchronization completed;

   mysql> SET @@global.read_only = ON;

   Restart the master and slave servers in sequence;
3. Use change master to update the master and slave configuration;

   mysql> CHANGE MASTER TO
   MASTER_HOST = host,
   MASTER_PORT = port,
   MASTER_USER = user,
   MASTER_PASSWORD = password,
   MASTER_AUTO_POSITION = 1;

   5. Enable replication

   mysql> START SLAVE;

   6, Verify master-slave replication

   mysql> show slave status \G

The above is the detailed content of Understand Mysql's GTID-based replication mode. For more information, please follow other related articles on the PHP Chinese website!