GTID based Asynchronous Replication in MySQL 8

MySQL replication by default is asynchronous. The master writes events to its binary log but does not know whether or when a slave has retrieved and processed them. With asynchronous replication, if the master crashes, transactions that it has committed might not have been transmitted to any slave. Consequently, failover from master to slave in this case may result in failover to a server that is missing transactions relative to the master.

MySQL 8.0 supports different methods of replication.

Traditional Replication Method using Relay logs:

The traditional method is based on replicating events from the master’s binary log and requires the log files and positions in them to be synchronized between master and slave.

GTID Based Replication:

The newer method based on global transaction identifiers (GTIDs) is transactional and therefore does not require working with log files or positions within these files, which greatly simplifies many common replication tasks. Replication using GTIDs guarantees consistency between master and slave if all transactions committed on the master have also been applied on the slave

 In order to use GTID based replication we need to enable following parameters in my.cnf

server_id=[N]
enforce_gtid_consistency = on
gtid_mode = on
log_bin
log_slave_updates

Suppose we a stand-alone server and we would like to set up a slave/ standby to this primary server. Traditionally we mention master binlog name and current position in out change master statement as shown below.

CHANGE MASTER TO
MASTER_HOST='master2.example.com',
MASTER_USER='replication' ,
MASTER_PASSWORD='password',
MASTER_PORT=3306,
MASTER_LOG_FILE='master2-bin.001',
MASTER_LOG_POS=4,
MASTER_CONNECT_RETRY=10;

But using GTID method we don’t need to mention all the above parameters in change master statement, but we only need to mention gtid_purged and MASTER_AUTO_POSITION=1 as shown below.

  
CHANGE MASTER TO
MASTER_HOST='master2.example.com', 
MASTER_USER='replication' , 
MASTER_PASSWORD='password',  
MASTER_AUTO_POSITION=1; 

We also need to gtid_purged after before starting slave which can be obtained from primary server using show variables statement as show below.

mysql> show variables like '%gtid_purged%';
 +---------------+--------------------------------------------------+
 | Variable_name | Value                                            |
 +---------------+--------------------------------------------------+
 | gtid_purged   | 51a7ada5-36f8-11e6-a1e1-005056873fa8:1-620259803 |
 +---------------+--------------------------------------------------+

Now we can set the above gtid_purged on slave.

 SET @@GLOBAL.GTID_PURGED=' 
51a7ada5-36f8-11e6-a1e1-005056873fa8:1-620259803' 

#We can also use parallel threads to multiply replication threads for better performance if needed.

Note: GTID replication is very useful in database migration projects. Watch out for my Next blog Database MySQL 5.7 to MySQL 8 migration using Asynchronous GTID replication.  

Thanks for reading and visiting, Don’t forget to leave your feedback.

Raja Naveed

Leave a Reply

Your email address will not be published. Required fields are marked *