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 |
