Home > Database > Mysql Tutorial > body text

Understand Mysql's GTID-based replication mode

coldplay.xixi
Release: 2020-12-28 10:12:21
forward
2348 people have browsed it

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

Understand Mysql's GTID-based replication mode

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
  3. GTID step by step as before. The GTID is continuous and has no holes, ensuring data consistency. Zero lost.
  4. 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
  1. CREATE TABLE…..SELECT cannot be used
  2. 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
  2. mysql.gtid_executed
  3. table. When a transaction is submitted, the gtid of the transaction will be quickly added to the system variable
  4. @@GLOBAL.gtid_executed
  5. , 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
  6. gtid_next
  7. 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.
  8. gtid_owned
  9. The system variable records who owns the GTID.

Traditional replacement GTID replication mode

Configure GTID
  1. All server settings
  2. global.read_only
  3. parameters, waiting for the master and slave servers Synchronization completed;
    mysql> SET @@global.read_only = ON;
    Copy after login
    Restart the master and slave servers in sequence;
  4. 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;
    Copy after login
    5. Enable replication
    mysql> START SLAVE;
    Copy after login
    6, Verify master-slave replication
    mysql> show slave status \G
    Copy after login

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!

Related labels:
source:learnku.com
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template