What is GTID ?
GTID stands for Global Transaction Identifier. This is a new feature introduced in 5.6 version.
I have given a link in reference section of this article which explains the concept of GTID.
Global transaction identifier is a unique transaction ID assigned to every transaction that happens in MySQL database. This ID has specific importance in maintaining replication slave. Using this ID we can easily track which transactions has been applied to slave and which are yet to be applied. This also prevents applying same transaction again to slave.
Here is how it works. When the master commits a transaction, it generates an identifier consisting of two components:
- The first component is the server’s server_uuid (server_uuid is a global variable introduced in MySQL 5.6 which holds a UUID, i.e. a 128-bit random number generated so that the probability that two servers in the world have the same server_uuid is so small that we can assume every UUID is unique).
- The second component is an integer; it is 1 for the first transaction committed on the server, 2 for the second transaction, and so on. Much like an autoincrement column, it is incremented by one for each commit.
The first component ensures that two transactions generated on different servers have different GTIDs. The second component ensures that two transactions generated on the same server have different GTIDs.
In text, we write the GTID as “UUID:N”, e.g., 22096C54-FE03-4B44-95E7-BD3C4400AF21:4711.
The GTID is written as an event to the binary log prior to the transaction
The binary log including the GTID is replicated to the slave. When the slave reads the GTID, it makes sure that the transaction retains the same GTID after it has been committed on the slave. Thus, the slave does not generate a new GTID;
Just to give a brief idea about binary logs – Binary logs are basically logs written for every transactions. They are like the archive logs/online redo logs in Oracle database terminology. I also see GTID is kind of a SCN number in Oracle database terminology.
Steps to enable GTID
You need to set following parameters either in /etc/my.cnf or while starting MySQL instance to enable GTID.
gtid_mode=ON enforce_gtid_consistency=true (Starting with MySQL 5.6.9, –disable-gtid-unsafe-statements is now named –enforce-gtid-consistency) log_bin=/usr/local/mysql/log/bin_log log_slave_updates=true If you are starting the instances using above parameters as arguments inline with mysqld_safe binary, you need to replace underscore(_) with hypen(-) in above parameters and include double hypens at the beginning as below --gtid-mode=ON --enforce-gtid-consistency=true (Starting with MySQL 5.6.9, –disable-gtid-unsafe-statements is now named –enforce-gtid-consistency) --log-bin=/usr/local/mysql/log/bin_log --log-slave_updates=true
I have updated /etc/my.cnf with above parameters and bounced MySQL instance
I can see GTID is now enabled
mysql> show global variables like '%GTID%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | enforce_gtid_consistency | ON | | gtid_executed | | | gtid_mode | ON | | gtid_owned | | | gtid_purged | | +--------------------------+-------+ mysql> show variables like '%gtid_next%'; +---------------+-----------+ | Variable_name | Value | +---------------+-----------+ | gtid_next | AUTOMATIC | +---------------+-----------+ mysql> select @@global.gtid_executed; +------------------------+ | @@global.gtid_executed | +------------------------+ | | +------------------------+ 1 row in set (0.00 sec)
I will now import a database named “deo” into this MySQL instance.
Exporting deo database from my desktop server MySQL instance:
advait.desktop$ mysqldump --databases deo -u root -p > deo.sql Enter password: advait.desktop$
Importing deo database into my new MySQL instance:
/usr/local/mysql/bin/mysql -u root < deo.sql
If I check in my new MySQL instance, I can see deo database
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | deo | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec)
If I check the GTID value now
mysql> select @@global.gtid_executed; +-------------------------------------------+ | @@global.gtid_executed | +-------------------------------------------+ | 2441a23c-b4ae-11e3-9d0b-80c16e2272cc:1-21 | +-------------------------------------------+ 1 row in set (0.00 sec)
It means that 21 transactions has happened.
References:
GTID concepts – http://dev.mysql.com/doc/refman/5.6/en/replication-gtids-concepts.html
http://svenmysql.blogspot.in/2012/10/failover-and-flexible-replication.html
Hope this helps !!
Persistent Storage Solutions 