Wednesday 19 July 2017

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