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

This article mainly introduces what is the GTID-based replication mode in Mysql, which has a certain reference value, and interested friends can refer to it. I hope you will gain a lot after reading this article.

GTID definition

GTID (Global Transaction Identifier) global transaction identity. GTID is a major improvement on master-slave replication introduced in version 5.6. compared with the previous version based on Binlog file + Position master-slave replication, GTID-based master-slave replication has higher data consistency, more robust master-slave data replication, master-slave switching and failover are not easy to make mistakes, and rarely require human intervention.

Mode of representation

GTID = server_uuid:transaction_id

Its GTID is usually recorded in the system variable @ @ GLOBAL.gtid_executed of MySQL and the 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.

Advantages of GTID over traditional replication

Easier to build master-slave replication

It is easier to implement failover (master-slave switch) instead of finding log_file and log_pos step by step as before.

GTID is continuous without emptiness, ensuring data consistency and zero loss.

Replication cluster has a unified way to identify replication location, which brings convenience to cluster management.

Limitations of GTID

Mixed use of engines such as Innodb,myisam in a transaction, resulting in multiple GTIDS

CREATE TABLE... .. SELECT cannot be used

CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE cannot be used within a transaction

Master-slave replication flow chart

GTID lifecycle

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

When the binlog file reaches its maximum value, or when the MySQL Server is closed, the transaction GTID from the previous binlog file will be written to the mysql.gtid_ executed table.

When a transaction commits, the gtid of the transaction will be quickly added to the system variable @ @ GLOBAL.gtid_executed, but the system table mysql.gtid_executed will not, because some of the gtid is still in binlog and will not be written to mysql until binlog rotation or mysqlServer is closed. In the gtid_ executed table.

The binlog on the master library is transferred to the slave database through the master-slave replication protocol, and is written to the relay log (relay log) of the slave library. The gtid and the corresponding transaction information in the relay log are read from the library, and the gtid_next is set to the GTID value so that the slave library uses the GTID value to apply its corresponding transaction.

If multiple threads apply the same transaction concurrently, for example, if multiple threads set gtid_next to the same value, MySQL Server only allows one thread to execute, and the gtid_owned system variable records who owns the GTID.

Traditional replacement of GTID replication mode

Configure GTID

All servers set global.read_only parameters and wait for the synchronization between master and slave servers to complete.

Mysql > SET @ @ global.read_only = ON

Restart the master and slave servers in turn

Use change master to update master-slave configuration

Mysql > CHANGE MASTER TOMASTER_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 Thank you for reading this article carefully. I hope the article "what is the GTID-based replication mode in Mysql" shared by the editor will be helpful to you. At the same time, I hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.