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