This tutorial demands a service restart since some flags here presented can not be dynamically changed
What is GTID and why do I need it? Directly from the MySQL documentation (excerpt taken as is with different jargons than used here, for
slave we are using
A global transaction identifier (GTID) is a unique identifier created and associated with each transaction committed on the server of origin (the master). This identifier is unique not only to the server on which it originated, but is unique across all servers in a given replication topology.
GTID assignment distinguishes between client transactions, which are committed on the master, and replicated transactions, which are reproduced on a slave. When a client transaction is committed on the master, it is assigned a new GTID, provided that the transaction was written to the binary log. Client transactions are guaranteed to have monotonically increasing GTIDs without gaps between the generated numbers. If a client transaction is not written to the binary log (for example, because the transaction was filtered out, or the transaction was read-only), it is not assigned a GTID on the server of origin.
In theory you can use replication with only binary logging enabled, however replication with GTID is significantly more reliable. And while some providers don’t require it, at Google Cloud GTID is mandatory.
To represent a GTID a pair of coordinates are used, one is the
server_uuid paired with the
transaction_id which is an integer. Example of valid GTID:
GTID = 8b5dbf2a-45b4-11e8-81bc-42010a800002:25
To understand more how this impacts replication, I recommend reading the section GTID Format and Storage in the MySQL documentation.
Thankfully, to enable it you don’t need to do much, edit your
mysqld.cnf file to support this variables:
server-id = 2 # Or any other number, we recommend to not be 1 log-bin = mysql-bin # Or any other valid value gtid_mode = ON enforce-gtid-consistency = true
Restart the database server to load up the new configuration with
sudo service mysql restart.
Some applications may cause errors due to the
enforce-gtid-consistency flag. That happens because usually the application is trying to do a non-transactional action that also is not possible to replicate inside a transaction.
If you do the following:
START TRANSACTION; CREATE TEMPORARY TABLE `tmp_users` ( id INTEGER ); COMMIT;
It is not a good practice I may add . You will get this error:
ERROR 1787 (HY000): Statement violates GTID consistency: CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can only be executed outside transactional context. These statements are also not allowed in a function or trigger because functions and triggers are also considered to be multi-statement transactions.
What are you basically doing is telling the database to create a connection, which is fine, however the following command is a
CREATE TEMPORARY TABLE. This command is bound to the current connection, and because it won’t have a
transaction_id it won’t be able to replicate the statement. Temporary tables are not replicated.
If your application happens to do that, all you need to do is remove the creation of temporary tables to outside of the transaction. Unfortunately Magento does not do that.
See something wrong in this tutorial? Please don’t hesitate to message me through the comments or the contact page.
3 thoughts on “Configuring GTID and binary logging”
In fact, enabling the GTID can be done online in MySQL 5.7:
Also, beside the “enforce-gtid-consistency” issue – that you have already mentioned – , some other issues are expected too when having the GTID enabled, like errant transactions, injecting an empty transaction , rebuilding a slave, … etc.
For more information, check out my slides below at a previous Percona Live conference about GTID:
LikeLiked by 1 person