Shulou(Shulou.com)05/31 Report--

Editor to share with you what are the differences between MySQL GTID and MariaDB GTID, I believe that most people still do not understand, so share this article for your reference, I hope you will learn a lot after reading this article, let's go to know it!

GTID is the full name of Global Transaction Identifier, which can simplify the master-slave switching and Failover of MySQL. GTID is used to uniquely identify a transaction in binlog. When a transaction commits, when MySQL Server writes binlog, it first writes a special Binlog Event of type GTID_Event, specifies the GTID of the next transaction, and then writes the Binlog of the transaction. During the master-slave synchronization, both the GTID_Event and the Binlog of the transaction are passed to the slave library, and the slave library uses the same GTID to write the binlog when executing, so that after the master-slave synchronization, the location of the slave library synchronization can be determined by GTID.

In MySQL 5.6, each transaction on the database server is assigned a unique transaction identifier, which is a 64-bit non-zero value assigned according to the order in which the transaction is committed. GTID has two parts. The first part refers to the server UUID. This UUID is a random string of 32 characters. This value is taken from the auto.cnf file located in the mysql data directory. The second part is the sequence.

For example:

On the slave library, GTID is a single expression:

Fba30f4d-5815-11e8-9beb-000c2900351f:10

It is important to understand that if the transaction is copied from the master to the slave side, the binary location of the transaction will change, because on the slave side, the transaction needs to be written to slave's own binary log, the write location is different, but the global transaction identifier is the same.

For parameters of GTID

L gtid_executed records the UUID of the currently executed GTID. The most important reason why the parameter log_slave_updates must be configured in MySQL 5.6 is that when slave is restarted, it is impossible to know the GTID location where the current slave has been run, because the variable gtid_executed is a memory value.

L gtid_mode is used to control whether the GTID mode is on / off.

L gtid_owned is a read-only variable, and its content depends on its scope. When used with the session session level, the list contains all GTID; owned by this client. When used with the global level, it contains a list of all GTID and their owners.

L gtid_purged, which represents the GTID collection dropped by purge

Example:

View binlog

/ *! 50530 SET @ @ SESSION.PSEUDOSLAVE models 1 / session / session .Max.Placement delayedthreads0Threads @ @ session. 50003 SET @ OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER / * / # at 4, 181009 23:02:36 server id 1 end_log_pos 120 CRC32 0x07eff3b3 Start: binlog v 4, server v 5.6.40-log created 181009 23:02:36 at startup# Warning: this binlog is either in use or was not closed properly.Rollback # at 120 "181009 23:02:36 server id 1 end_log_pos 151CRC32 0xb62fd2d2 Previous-GTIDs# [empty] # at 151" 181010 4:37:11 server id 1 end_log_pos [commit=yes] SET @ @ SESSION.GTID_NEXT= 'fba30f4d-5815-11e8-9bebWhat 000c2900351frig / at 1999 181010 4:37:11 server id 1 end_log_pos 271 CRC32 0xa6b6773e Query thread_id=5 exec_time=0 error_code=0SET Timestamp 1539160631 / SET @ @ session.foreign_key_checks=1, @ @ session.sql_auto_is_null=0, @ @ session.unique_checks=1, @ @ session.session .sqlalarm modewords 1075838976, @ session. Session. Incrementations offsets1Thread switch set @ @ session.auto_increment_increment=1, @ @ session. Autoincrement increment offsets1Thread utf8 * / *! / SET @ @ session.session collection timestamps namespace namespaces 0pxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx # at 271 "181010 4:37:11 server id 1 end_log_pos 336 CRC32 0x0f828e75 Table_map: `test`.`tx _ albert` mapped to number 7 examples at 336" 181010 4:37:11 server id 1 end_log_pos 398 CRC32 0xd023df0a Write_rows: table id 75 flags: STMT_END_F### INSERT INTO `test`.`tx _ albert` # SET### @ 1 # # 2='enmo'### @ 3% 5 # @ 4='F'### @ 5='BeiJing'### @ 6='BJ'# at 398181010 4:37:11 server id 1 end_log_pos 429 CRC32 0x7117fe15 Xid = 45COMMIT # at 429 "181010 4:38:24 server id 1 end_log_pos 477 CRC32 0x8b8da3af GTID [commit=yes] SET @ @ SESSION.GTID_NEXT= 'fba30f4d-5815-11e8-9beblue 000c2900351f Wheeler 2" at 477 "181010 4:38:24 server id 1 end_log_pos 549 CRC32 0xe2c69fee Query thread_id=5 exec_time=0 error_code=0SET timestamp 1539160704 # at 549 181010 4:38:24 server id 1 end_log_pos 614 CRC32 0xa52a4212 Table_map: `test`.`tx _ albert` mapped to number 7 at 614 181010 4:38:24 server id 1 end_log_pos 676 CRC32 0x12c7c08f Write_rows: table id 75 flags: STMT_END_F### INSERT INTO `test`.`tx _ albert` # SET### @ 1 "4" # @ 2='sed'### @ 3 "34" @ 5='GuiYang'### @ 6='DBA'# at 676 181010 4:38:24 server id 1 end_log_pos 707 CRC32 0xab6aa483 Xid = 46COMMIT SET @ @ SESSION.GTID_NEXT= 'AUTOMATIC' / * added by mysqlbinlog * / / *! * /; DELIMITER; # End of log file SET @ @ SESSION.GTID_NEXT=' fba30f4d-5815-11e8-9bebMuth000c2900351f

Where 'fba30f4d-5815-11e8-9bebmur000c2900351f' is server UUID,'1' is sequence, that is, the submitted sequence

# 181010 4:37:11 server id 1 end_log_pos 429 CRC32 0x7117fe15 Xid = 45

MySQL toggles GTID mode on / off through the global variable gtid_mode. But gtid_mode is read-only and can be added to the configuration file, and then restart mysqld to turn on GTID mode. Since GTID needs to be written to binary logs, GTID is used, and binary logging needs to be enabled as well. The relevant configuration items are as follows:

MariaDB database, as a branch of MySQL, is the same as MySQL in some features. MariaDB is fully compatible with MySQL, including API and the command line, while in terms of storage engine, it uses XtraDB as an alternative to MySQL InnoDB, while XtraDB is also compatible with InnoDB. GTID replication appears in MariaDB version 10 and consists of The Domain ID,server ID, transaction sequence number. In MariaDB version 10, GTID replication mode is enabled by default. Each Event Group will receive a GTID_EVENT when it is written to Binlog, which can be seen using MariaDB's mysqlbinlog tool or SHOW BINLOG EVENTS command. At the same time, there is no need to set any parameters of GTID in MariaDB version 10, let alone the need to set log_slave_updates=1 on slave, as in MySQL 5.6.This will increase the pressure of slave.

MariaDB supports hot swap of GTID. Unlike the MySQL5.6/5.7 version, modifying the GTID mode requires modifying the corresponding GTID parameters and rebooting.

Example:

CHANGE MASTER TO master_use_gtid = {slave_pos | current_pos | no}

L slave_pos, is to configure Slave to use GTID mode. When Slave connects to Master, Master replicates Binlog for Slave starting from the last GTID

L current_pos, this setting does not need to know whether the current instance is Master or Slave, but for slave, it may cause replication errors if it exists in other transactions that have nothing to do with replication. Of course, you can set @ @ GLOBAL.gtid_strict_mode=1 on slave, that is, turn on GTID strict mode.

L no, that is, the traditional replication mode

View slave replication:

MariaDB [(none)] > show slave status\ G show processlist * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.34 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-bin.000002 Read_Master_Log_Pos: 993 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 644 Relay_Master_Log_File: mariadb-bin.000002 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: 993 Relay_Log_Space: 944 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: 0Master_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: 1000 Master_SSL_Crl: Master_SSL _ Crlpath: Using_Gtid: Slave_Pos Gtid_IO_Pos: 0-1000-4 Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative1 row in set (1000 sec)

You can see that the current GTID transport used by slave is Using_Gtid: Slave_Pos, and the obtained pos location is 0-1000-4.

"0", the first place is Domain ID, which is a 32-bit unsigned integer

"1000", the second place is Server ID, which is the same as the meaning of Server ID in traditional active and standby replication, and is also a 32-bit unsigned integer. So the Server ID for each instance must be unique in a replication topology

"4", the third place is the transaction sequence number (Sequence Number). This is a 64-bit unsigned integer. Each newly generated Event Group records to Binlog will generate a new monotonously increasing sequence number.

The above is all the contents of the article "what are the differences between MySQL GTID and MariaDB GTID". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.