MENU


Replication with GTIDs

Transaction-based replication using global transaction identifiers (GTIDs) allows for seamless failover, in which one of the slaves is promoted to a master when the master crashes.

A GTID is represented as:
GTID = source_id:transaction_id
which uniquely identifies a transaction across the entire replication setup. source_id, which is usually the server’s server_uuid, refers to the originating server. transaction_id is an integer that increases by one every time a transaction executes within the master. For example, a GTID looks like:
3E11FA47-71CA-11E1-9E33-C80AA9429562:23

In the binary log, each transaction is associated with a GTID. During a replication process when the slave reads a GTID from the master, it retains the same GTID after committing the corresponding transaction. The slave does not generate a new GTID. In other words, a GTID remains the same throughout the replication setup after the transaction was first committed on the master.
The following steps describe how to set up transaction-based replication:

Step 1: Make the master and slave read-only.
SET @@global.read_only = ON;

Then, allow the slave to catch up with the master.
Step 2: Stop both servers. In the command line:
mysqladmin –uusername –p shutdown

Step 3: Restart both servers with GTIDs enabled. For the master, enter in the command line:
mysqld_safe --gtid_mode=ON --log-bin
--log-slave-updates --enforce-gtid-consistency &

For the slave, add the option --skip-slave-start:
mysqld_safe --gtid_mode=ON --log-bin
--log-slave-updates --enforce-gtid-consistency
--skip-slave-start &

Step 4: Connect the slave to the master. 

CHANGE MASTER TO
MASTER_HOST = host,
MASTER_PORT = port,
MASTER_USER = user,
MASTER_PASSWORD = password,
MASTER_AUTO_POSITION =1;
START SLAVE;
Step 5: Disable read-only mode for the master.
SET @@global.read_only = OFF;

To see the GTIDs in the binary log, enter SHOW MASTER STATUS, SHOW SLAVE STATUS, or SHOW BINLOG EVENTS. You can read the system variable gtid_exceuted. Moreover, the log file can be read using mysqlbinlog --base-64-output=DECODE-ROWS in the command line. You should notice that consecutive GTIDs may be collapsed into the range form:
3E11FA47-71CA-11E1-9E33-C80AA9429562:20-23
3E11FA47-71CA-11E1-9E33-C80AA5698468:40-99
During a failover, the slave sends to the master the range of GTIDs that have been committed. The master then sends all other transactions that have not been committed on the salve, along with the corresponding GTIDs.
When a slave has just been set up, executing the entire transaction history from the master can be time consuming. To speed things up, you can manually copy the data and transactions to the slave: However, sometimes it is not feasible to copy the entire binary log files because of their sheer sizes. To solve this problem, you can commit an empty transaction on the slave for each transaction identifier contained in the master’s gtid_executed system variable, like this: 

SET GTID_NEXT='aaa-bbb-ccc-ddd:N';
BEGIN;
COMMIT;
SET GTID_NEXT='AUTOMATIC';
After all transaction identifiers have been reinstated like this, run the following commands: 

FLUSH LOGS;
PURGE BINARY LOGS TO 'master-bin.00000N';
FLUSH LOGS creates a new binary log. PURGE BINARY LOGS purges the empty transactions, which allows the slave to catch up with the master in time.
Instead of committing empty transactions and using the PURGE BINARY LOGS statement, you can also set the system variable gtid_purged on the slave directly, based on the value of gtid_executed on the master. gtid_purged contains the set of all transactions that have been purged from the master’s binary log.

Note that the following are not supported by GTID-based replication: