• GreatSQL community original content is not allowed to use without authorization, please contact xiaobian and indicate the source.

GTID overview

MySQL5.6 adds a new replication mode based on GTID, which consists of UUID and transaction ID, and has the following features.

  • GTID transactions are globally unique and each transaction corresponds to a GTID value.
  • A GTID value is executed only once on the same MySQL instance.

Advantages of GTID over traditional replication

  • The master-slave construction is more convenient, and there is no need to manually specify the position position.
  • A replication cluster has a unified id, facilitating identification and management.
  • Failover is easier, and you don’t have to find log_file and log_Pos positions like traditional replication.
  • Under normal circumstances, GTID is continuous without voids, which can better ensure data consistency and zero loss.
  • Compared with ROW replication mode, data security is higher and switching is easier.
  • More secure than traditional replication, a GTID is executed only once on a MySQL instance to avoid data confusion or primary/secondary inconsistency caused by repeated execution.

What are the limitations of GTID

  • In a replication group, GTID must be enabled.
  • MySQL5.6 GTID needs to be restarted.
  • Sql_slave_skip_counte is not supported. Traditional replication can use this command to skip transactions.
  • It is not allowed to update tables of a transaction engine and non-transaction engine at the same time in a SQL, such as InnoDB and MyISAM.
  • Create TEMPORARY Table and DROP TEMPORARY Table statements are not supported.
  • Create table is not supported… Select statement replication.

The working principle of GTID is briefly introduced

  • When the master node updates data, the GTID information is generated before the transaction and recorded in the binlog.
  • The I/O thread of the slave node writes the binlog to the local relay log.
  • Then the SQL thread reads the GTID from the relay log, sets the value of gTID_next to the GTID, and compares the binlog on the slave side to see whether there are records.
  • If there is a record, it indicates that the transaction of the GTID has been run and the slave ignores it.
  • If there is no record, the slave performs the transaction corresponding to the GTID and records it in the binlog.

How do I enable GTID replication

  • In addition to the binlog parameters required for traditional replication, the following parameters must be enabled for GTID synchronization. The synchronization between the primary and secondary nodes must be enabled.
Gtid_mode =on # Enable GTID invincible-gtid -consistency=on # Synchronize this parameter log-slave-updates=1 # 5.6 This parameter needs to be enabledCopy the code

View GTID parameters

[root@GreatSQL][(none)]>show variables like '%gtid%'; +----------------------------------+------------------------------------------------------------------------------------ -+ | Variable_name | Value | +----------------------------------+------------------------------------------------------------------------------------ -+ | binlog_gtid_simple_recovery | ON | | enforce_gtid_consistency | ON | | gtid_executed | 613743f5-8b1c-11ec-9922-00155dcff911:1-14 | | gtid_executed_compression_period | 0 | | gtid_mode | ON | | gtid_next | AUTOMATIC | | gtid_owned | | | gtid_purged | | | session_track_gtids | OFF | +----------------------------------+------------------------------------------------------------------------------------ -+ 9 rows in set (0.00 SEC)Copy the code
  • Brief Description of Parameters
Parameter names Meaning is introduced
binlog_gtid_simple_recovery This parameter controls the value of GTIDs automatically found when the MySQL service is restarted or started.
enforce_gtid_consistency This parameter is mandatory to allow only replication transaction-safe transactions, see the specific restrictions in the steps above.
gtid_executed GTID information that has been executed.
gtid_executed_compression_period When GTID is enabled, the server periodically performs compression on the mysql.gtid_executed table. The compression rate can be controlled by setting the gtid_executed_compression_period system variable to control the number of transactions allowed before the table is compressed. If the value is set to 0, no compression is performed.
gtid_mode Whether to enable the GTID mode
gtid_next Represents the next GTID information to be executed
gtid_owned This parameter includes global and session. Global indicates that all servers have GTIDs, and session level indicates that all GTIDs owned by the current client.
gtid_purged Purge GTIDs and purged GTIDs will be included in gtid_executed.
session_track_gtids This parameter controls the GTIDs used for capture and the tracker returned in OK PACKE.

Differences between GTID and traditional replication statements

# traditional copy change master to Master_host = "127.0.0.1", master_port = 3310, MASTER_USER = 'sync', MASTER_PASSWORD = 'GreatSQL', MASTER_LOG_FILE = '000005' log - bin.  MASTER_LOG_POS=4111; # GTID copy change master to Master_host = "127.0.0.1", master_port = 3310, MASTER_USER = 'sync', MASTER_PASSWORD = 'GreatSQL', MASTER_AUTO_POSITION = 1Copy the code

GTID synchronization Changes the pos point of the traditional replication from manually specified binlog to the pos point of the traditional replication from MASTER_AUTO_POSITION=1.

GTID synchronization status simple resolution

In addition to the traditional view of binlog and POS values, GTID mode can be more intuitive view of the execution of a transaction.

[root@GreatSQL][(none)]>show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.6.215
                  Master_User: sync
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000001
          Read_Master_Log_Pos: 2425
               Relay_Log_File: mgr2-relay-bin.000002
                Relay_Log_Pos: 2634
        Relay_Master_Log_File: binlog.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2425
              Relay_Log_Space: 2842
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 2153306
                  Master_UUID: 613743f5-8b1c-11ec-9922-00155dcff911
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 613743f5-8b1c-11ec-9922-00155dcff911:1-10
            Executed_Gtid_Set: 613743f5-8b1c-11ec-9922-00155dcff911:1-10,
652ade08-8b1c-11ec-9f62-00155dcff90a:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set, 1 warning (0.01 sec)

ERROR:
No query specified
Copy the code
  • GTID Indicates key phase parameters
Parameter names Meaning is introduced
Retrieved_Gtid_Set Slave Indicates the GTIDs received by the Master node
Executed_Gtid_Set Slave GTIDs executed on the Slave node
Auto_Position Automatically gets position, displayed as 1

conclusion

  • Space is limited to write so much for the time being, next week will continue on the master and slave copy related content, welcome to follow up, in addition limited by personal ability and experience, the content is inevitable mistakes, if there are mistakes welcome to point out corrections in the comments area.

Enjoy GreatSQL 🙂

Article recommendation:

GreatSQL Quarterly (2021.12.26) mp.weixin.qq.com/s/FZ_zSBHfl…

Technology sharing | sysbench mp.weixin.qq.com/s/m16LwXWy9 up.the pressure measuring tool usage…

Failure analysis | Linux disk IO utilization rate is high, the analysis of the correct posture mp.weixin.qq.com/s/7cu_36jfs…

Technology sharing | flashbacks in MySQL implementation and improvement of mp.weixin.qq.com/s/6jepwEE0D…

How a # 20, an index pushdown data filtering mp.weixin.qq.com/s/pt6mr3Ge1…

About GreatSQL

GreatSQL is a MySQL branch maintained by Wanli Database, which focuses on improving the reliability and performance of MGR. It supports InnoDB parallel query feature and is a MySQL branch version suitable for financial applications.

Gitee: gitee.com/GreatSQL/Gr…

Making: github.com/GreatSQL/Gr…

Bilibili: space.bilibili.com/1363850082/…

Wechat &QQ Group: you can search to add GreatSQL Community Assistant wechat friends, send verification information “add group” to join GreatSQL/MGR communication wechat group

QQ group: 533341697 wechat assistant: WanliDBC

This article is published by OpenWrite!