MySQL GTID Replication

This blog explains how to set-up a GTID based Master-Slave replication.

About GTID

A global transaction identifier (GTID) is a unique identifier created and associated with each transaction committed on the server of origin (master).

GTID is a represented as a pair of coordinates separated by a colon `:`

{source_id:transaction_id}

Where ,

source_id : server's UUID.

transaction_id : sequence number

An GTID looks like

68e889d2-6c60-11e7-9ae5-080027c91509:1

Why GTID?

• Setting up MySQL replication is so simple now! (forget binlog & position)
• Consistency is guaranteed between master and slaves.
• Fail-over process become much easier.

Master side configuration

Do a mysql restart for the changes to take place

[root@gtid1 ~]# service mysqld restart

Stopping mysqld:                                           [  OK  ]

Starting mysqld:                                           [  OK  ]

[root@gtid1 ~]#

Create a mysql replication user for the slave to connect with master

GRANT REPLICATION SLAVE ON *.* TO 'gtid_repl'@'gtid2' IDENTIFIED BY 's3cret';

Note : gtid1 --> Master

gtid2 --> Slave

Slave side configuration

Do a mysql restart for the changes to take place

[root@gtid2 ~]# service mysqld restart

Stopping mysqld:                                           [  OK  ]

Starting mysqld:                                           [  OK  ]

[root@gtid2 ~]# 

Execute the change master to command

CHANGE MASTER TO MASTER_HOST='gtid1',

  MASTER_PORT=3306,

  MASTER_USER=gtid_repl,     

  MASTER_PASSWORD='s3cret',  

  MASTER_AUTO_POSITION=1;

Start the replication

START SLAVE;

Validate the replication from below command

SHOW SLAVE STATUS\G

Verification :

 On gtid1

On gtid2

Explanation for variables:-

server-id	Unique replication id (must if the server is involved in a replication environment)
log-bin	Enabling binary logging to log all statements that change data
binlog-format	Type of binary logging format in which server needs to store.
gtid-mode	Enable or disable gtid mode
enforce_gtid_consistency	It prevents executing the non-transactionally safe statements
log_slave_updates	Tells the slave to write the updates performed by its SQL thread to its binary log. For gtid we need it to be enabled on both servers else mysql won’t start. [ERROR] --gtid-mode=ON or UPGRADE_STEP_1 or UPGRADE_STEP_2 requires --log-bin and --log-slave-updates