Global Transaction Identifiers are in!
I am very happy and especially proud to announce that the replication team has delivered global transaction identifiers to MySQL 5.6.5 Development Milestone Release (DMR). It is a very useful, big, impressive and game-changing feature that will make life easier for many of our users. With this feature in place, it is much simpler to track replication progress through the replication topology thus it removes part of the burden of deploying and administering complex multi-tier replication topologies. Actually, as stated before, this feature is an enabler, as it gives the user so much more flexibility when it comes to deploying replication and tracking the data that is replicated. In particular, it is a foundation for reliable and automated fail/switch over with slave promotion, reducing the need for 3rd party HA infrastructure (which adds cost and complexity). In fact, my good colleague Chuck has already written a couple of utilities that build on global transaction identifiers and implement automated fail/switch over. You should have a look at that as well!
Having said that, lets have a look at what is under the hood.
Structure, Life Cycle and Tracking of GTIDs
It has been discussed before what a global transaction identifier(GTID) is. Just to recap, a global transaction identifier is a tuple (SID, GNO). SID is normally the SERVER_UUID and GNO is a sequence number (1 for the first transaction committed on SID, 2 for the second, and so on). Basically, a GTID is a logical identifier that maps into physical coordinates (log file name, file offset). Physical coordinates are likely to be different at each different server. In contrast, global transaction identifiers are not.Now, about the identifier lifecycle… When a transaction is executed for the first time on the master, the master assigns it a GTID. Since SID is the server's UUID, it remains constant for transactions executed on that server. GNO on the other hand, is generated automatically when the transaction commits (it is the smallest number not yet used as GNO for any other transaction with the same SID). GTIDs are persisted in the binary log as a new log event type that holds the actual identifier. The event is called Gtid_log_event. As such, when the group of events for a given transaction is to be written to the binary log, a new Gtid_log_event is also written, preceding the group.
Once the GTID is in the binary log, the identifier flows seamlessly through the replication stream. The Gtid_log_event is read and sent by the dump thread, received and stored in the slave's relay log by the IO thread and read from the relay log by the SQL thread. Nothing new here. However, when the SQL thread executes the transaction, it does not generate a new identifier. Instead, it preserves the same identifier, relaying it to its own binary log. Thus, the server ensures that a replayed transaction gets the same GTID it was assigned on the master. In fact, two properties hold:
- No transaction is re-executed more than once. (Therefore, a before applying a transaction a server checks that it has not applied it before. If it has, it skips it).
- Two different transactions cannot have the same GTID.
To sum up, the current GTIDs implementation is comprised of two major blocks:
- The transaction identifier. It serves a simple and yet very powerful purpose. It uniquely identifies a set of events.
- The state machine that keeps track of which transactions a server has seen. It is a key part of the procedure of switching slaves to a new master as well as keeping the data of a slave consistent by preventing undesirable re-execution of transactions.
CHANGE MASTER Made Easy (Fail-over facilitator)
In a GTIDs enabled topology the master-slave handshake is slightly
different from what it has been until now. When the slave connects to
a new master, the slave will tell the master which GTIDs it has in its
relay or binary log. Therefore, the master can pick which transactions
the slave is missing and send only those to the slave. This is all
automatic and means that the slave does not need to know any non-local
data. Before GTIDs, the slave had to know positions in the master's
binary log. That is not needed now. More importantly, the user does
not have to know anything about the replication positions, thus no
position (re)calculation has to be done when redirecting slaves to a
new master. The user can just issue:
CHANGE MASTER TO MASTER_HOST='...', MASTER_PORT=SOME_PORT, MASTER_USER='...', MASTER_AUTO_POSITION=1;
Restrictions and Design Changes
There are a few restrictions if the user wants to use this new
feature, since some MySQL functionality is not compliant with GTIDs. I
do not want to go into all the gory details, but here is a list of
constructs that are automatically blocked by the server to ensure
robust operation under GTIDs mode, and a brief explanation why:
- Non-transactional updates, such as MyISAM. There are a few cases when using non-transactional tables might result in duplicate GTIDs throughout a replication chain. Either by having transactions including changes to non-transactional tables or by having master and slave with different engine types.
- CREATE TABLE … SELECT . In RBR, CREATE TABLE … SELECT is split into two transactional group of events: one for the CREATE TABLE and one for the row events. Thus, in a replication chain, both group of events could end up getting the same identifier. Since a server skips transactions that it has seen before, the row events would not be applied by a server further down the chain.
- [CREATE|DROP] TEMPORARY TABLE executed inside a transaction. CREATE TEMPORARY TABLE and DROP TEMPORARY table are special statements: they can be executed inside a transaction - there is no implicit commit - but they cannot be rolled back. This is similar to updates to non-transactional tables. There are scenarios in which replication could break and/or parts of transactions might be replayed twice while failing-over to a new master. I will skip the details here, as this is a somewhat convoluted example.
–disable-gtid-unsafe-statements
On the design changes, a note worth mentioning is the fact that the approach changed a bit since the last time I blogged. The current implementation leaves out the planned indexes that would map identifiers to physical coordinates. We are still looking into how to solve all issues around this.
Hands-on
To start making use of GTIDs, the entire replication infrastructure needs to be configured to use GTIDs. In addition, all servers should "speak" GTIDs, meaning that there should not be any transaction, without an identifier, still pending execution. Starting the server with GTIDs ON, requires four switches (two of which you already know from long before):
- –log-bin
- –log-slave-updates
- –gtid-mode=ON
- –disable-gtid-unsafe-statements
There are also a couple of other two new options. One (–disable-gtid-unsafe-statements) was already explained on the "Restrictions" section. The last one, –gtid-mode, it is a simple switch that turns on the GTID feature.
That's it. Starting mysqld with these options gets us a server configured to use GTIDs. That can be checked that by issuing:
mysql> SHOW VARIABLES LIKE '%gtid_mode%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | gtid_mode | ON | +---------------+-------+ 1 row in set (0,01 sec)
OK, great. Now, lets play a bit with it. Lets create a table:
mysql> use test; Database changed mysql> CREATE TABLE t1 (a INT); Query OK, 0 rows affected (0,02 sec) mysql> SHOW BINLOG EVENTS; +-------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +-------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ | master-bin.000001 | 4 | Format_desc | 1 | 117 | Server ver: 5.6.6-m8-debug-log, Binlog ver: 4 | | master-bin.000001 | 117 | Previous_gtids | 1 | 144 | | | master-bin.000001 | 144 | Gtid | 1 | 188 | SET @@SESSION.GTID_NEXT= '4B2CBA63-8082-11E1-BE2D-F0DEF11A08B7:1' | | master-bin.000001 | 188 | Query | 1 | 281 | use `test`; CREATE TABLE t1 (a INT) | +-------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
We can notice a few new things in the binary log already. There is a Previous_gtids event, which I have not mentioned before to not clutter this blog entry. Lets just assume that it is an event that keeps track of transaction identifiers that existed in a set of binary log files previously purged. Anyway, the most interesting part for now is the Gtid event. It precedes the CREATE TABLE and assigns it the unique identifier:
'4B2CBA63-8082-11E1-BE2D-F0DEF11A08B7:1'
Very good! Lets insert a value into the table:
mysql> INSERT INTO t1 VALUES (1); Query OK, 1 row affected (0,01 sec)
This results in the following set of events to be logged into the binary log:
mysql> SHOW BINLOG EVENTS; +-------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +-------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ | master-bin.000001 | 4 | Format_desc | 1 | 117 | Server ver: 5.6.6-m8-debug-log, Binlog ver: 4 | | master-bin.000001 | 117 | Previous_gtids | 1 | 144 | | | master-bin.000001 | 144 | Gtid | 1 | 188 | SET @@SESSION.GTID_NEXT= '4B2CBA63-8082-11E1-BE2D-F0DEF11A08B7:1' | | master-bin.000001 | 188 | Query | 1 | 281 | use `test`; CREATE TABLE t1 (a INT) | | master-bin.000001 | 281 | Gtid | 1 | 325 | SET @@SESSION.GTID_NEXT= '4B2CBA63-8082-11E1-BE2D-F0DEF11A08B7:2' | | master-bin.000001 | 325 | Query | 1 | 400 | BEGIN | | master-bin.000001 | 400 | Query | 1 | 495 | use `test`; INSERT INTO t1 VALUES (1) | | master-bin.000001 | 495 | Xid | 1 | 522 | COMMIT /* xid=11 */ | +-------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ 8 rows in set (0,00 sec)We can see right away that another identifier was assigned to the new transaction. This time:'4B2CBA63-8082-11E1-BE2D-F0DEF11A08B7:2' .
Same SID, 4B2CBA63-8082-11E1-BE2D-F0DEF11A08B7, different GNO, 2.
Now, lets go over to another server and set it as slave of this one. One can do that by simply issuing (note: I am using root user just for demonstration purposes):
mysql> CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=13000, MASTER_USER='root', MASTER_AUTO_POSITION=1; Query OK, 0 rows affected (0,00 sec)
Inspecting SHOW SLAVE STATUS, one can find that everything is setup:
mysql> SHOW SLAVE STATUS\G
(...)
Master_Host: 127.0.0.1
Master_User: root
Master_Port: 13000
(...)
Retrieved_Gtid_Set:
Executed_Gtid_Set:
1 row in set (0,00 sec)
You can see here two new fields, Retrieved_Gtid_Set and Executed_Gtid_Set. The first one is the set of GTIDs pulled from the master. The second one is the set of GTIDs actually executed.
Continuing… Lets start the slave:
mysql> START SLAVE; Query OK, 0 rows affected (0,00 sec)And lets inspect the slave status again:
mysql> SHOW SLAVE STATUS\G
(...)
Master_Host: 127.0.0.1
Master_User: root
Master_Port: 13000
(...)
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
(...)
Retrieved_Gtid_Set: 4B2CBA63-8082-11E1-BE2D-F0DEF11A08B7:1-2
Executed_Gtid_Set: 4B2CBA63-8082-11E1-BE2D-F0DEF11A08B7:1-2
1 row in set (0,00 sec)
Inspecting the RELAY log on the slave, one can find:
mysql> show relaylog events in 'slave-relay-bin.000002'; (...) | slave-relay-bin.000002 | 345 | Gtid | 1 | 188 | SET @@SESSION.GTID_NEXT= '4B2CBA63-8082-11E1-BE2D-F0DEF11A08B7:1' | | slave-relay-bin.000002 | 389 | Query | 1 | 281 | use `test`; CREATE TABLE t1 (a INT) | | slave-relay-bin.000002 | 482 | Gtid | 1 | 325 | SET @@SESSION.GTID_NEXT= '4B2CBA63-8082-11E1-BE2D-F0DEF11A08B7:2' | | slave-relay-bin.000002 | 526 | Query | 1 | 400 | BEGIN | | slave-relay-bin.000002 | 601 | Query | 1 | 495 | use `test`; INSERT INTO t1 VALUES (1) | | slave-relay-bin.000002 | 696 | Xid | 1 | 522 | COMMIT /* xid=11 */ | +------------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------+ 13 rows in set (0,00 sec)
Looking into the slave's BINARY log, once can find:
mysql> SHOW BINLOG EVENTS; +------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ | slave-bin.000001 | 4 | Format_desc | 2 | 117 | Server ver: 5.6.6-m8-debug-log, Binlog ver: 4 | | slave-bin.000001 | 117 | Previous_gtids | 2 | 144 | | | slave-bin.000001 | 144 | Gtid | 1 | 188 | SET @@SESSION.GTID_NEXT= '4B2CBA63-8082-11E1-BE2D-F0DEF11A08B7:1' | | slave-bin.000001 | 188 | Query | 1 | 281 | use `test`; CREATE TABLE t1 (a INT) | | slave-bin.000001 | 281 | Gtid | 1 | 325 | SET @@SESSION.GTID_NEXT= '4B2CBA63-8082-11E1-BE2D-F0DEF11A08B7:2' | | slave-bin.000001 | 325 | Query | 1 | 400 | BEGIN | | slave-bin.000001 | 400 | Query | 1 | 495 | use `test`; INSERT INTO t1 VALUES (1) | | slave-bin.000001 | 495 | Xid | 1 | 522 | COMMIT /* xid=15 */ | +------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+ 8 rows in set (0,00 sec)
Notice how the original identifiers were indeed preserved.
Getting back to the master, one interesting new variable to observe is GTID_DONE. This variable contains the set of logged transactions. One can query it and know which transactions were actually seen/executed by the server:
mysql> SELECT @@GLOBAL.GTID_DONE; +------------------------------------------+ | @@GLOBAL.GTID_DONE | +------------------------------------------+ | 4B2CBA63-8082-11E1-BE2D-F0DEF11A08B7:1-2 | +------------------------------------------+ 1 row in set (0,00 sec)
This is very cool. The same thing can be done on the slave side:
mysql> SELECT @@GLOBAL.GTID_DONE; +------------------------------------------+ | @@GLOBAL.GTID_DONE | +------------------------------------------+ | 4B2CBA63-8082-11E1-BE2D-F0DEF11A08B7:1-2 | +------------------------------------------+ 1 row in set (0,00 sec)
OK, this was just an appetizer! There are a few more variables and additional extensions either to the replication layer as well as to mysqlbinlog that are worth checking out! I will let you uncover those yourself with the help of the great online manual which already documents much of this great feature.
Summary
This post provides a very brief insight on Global Transaction
Identifiers. It is a major replication feature that made it into MySQL
5.6.5 and one that is extremely important for people doing Highly
Available systems based on MySQL replication.
Since it is a big feature and a big change in the replication behavior, I have only covered a small part of it in this post. It is just like a very brief introduction. Anyway, this post touches the very basic parts to get one going as it explains what a GTID and its life cycle is, how to activate it and how to connect servers using GTID protocol (and thus how easy and simple it has become to do a simple slave switchover to new master). In addition it also also mentions some of the restrictions one has to deal with when using this feature.
It concludes with a very short overview of how a server behaves when GTID is turned ON.
I cannot just end this post without referring the readers, again, to the very nice and extremely interesting blog post from Chuck. It presents two new MySQL utilities that automate a couple of the most complex replication administration tasks: switchover and fail over. Such utilities already build on global transaction identifiers and take much of the pain away while doing slave promotion or taking a master down for maintenance, and so forth… And, by the way, MySQL 5.6 is full of new, interesting and very useful MySQL replication features. You can find more details by going through the developer's zone replication article. Go and have a look.
I hope you have a great time trying this new feature out, but more importantly, that it fits nice and perfectly in your own use cases going forward.
Have fun!
Since it is a big feature and a big change in the replication behavior, I have only covered a small part of it in this post. It is just like a very brief introduction. Anyway, this post touches the very basic parts to get one going as it explains what a GTID and its life cycle is, how to activate it and how to connect servers using GTID protocol (and thus how easy and simple it has become to do a simple slave switchover to new master). In addition it also also mentions some of the restrictions one has to deal with when using this feature.
It concludes with a very short overview of how a server behaves when GTID is turned ON.
I cannot just end this post without referring the readers, again, to the very nice and extremely interesting blog post from Chuck. It presents two new MySQL utilities that automate a couple of the most complex replication administration tasks: switchover and fail over. Such utilities already build on global transaction identifiers and take much of the pain away while doing slave promotion or taking a master down for maintenance, and so forth… And, by the way, MySQL 5.6 is full of new, interesting and very useful MySQL replication features. You can find more details by going through the developer's zone replication article. Go and have a look.
I hope you have a great time trying this new feature out, but more importantly, that it fits nice and perfectly in your own use cases going forward.
Have fun!
