Dost Muhammad Khalil (OCP)
[email protected]
+92 311 4848475
Configuring GTID Based Replication on MySQL 8
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ Master SetUp $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
show variables like 'server_id';
show variables like 'log_bin';
show variables like 'binlog_format';
show variables like 'gtid_mode';
show variables like 'enforce_gtid_consistency';
show variables like 'log_slave_updates';
--If the variables are not set, append the below in my.cnf file on master.
server-id = 1 ##Should be unique throughout the replication topology.
log-bin = mysql-bin ##Enable binary logging.
binlog_format = row ##Setting binary log format, row is recommended.
gtid-mode=ON ##Enabling GTID mode.
enforce-gtid-consistency ##
log-slave-updates ##
systemctl restart mysqld ##Restarting Master.
NOTE: On Production for restart you will need proper approval for it and all because the application team will stop
the traffic and when give you the green signal then you should run the stop or restart command and also open the
logfile in another tab to properly monitor the activity.
--After restart check the variables and verify all are set.
show variables like 'server_id';
show variables like 'log_bin';
show variables like 'binlog_format';
show variables like 'gtid_mode';
show variables like 'enforce_gtid_consistency';
show variables like 'log_slave_updates';
create user 'replica_user'@'%' identified by 'Replica123#'; ##Create user for Replication
ALTER USER 'replica_user'@'%' IDENTIFIED WITH mysql_native_password BY 'Replica123#'; ##Altering user.
Grant replication slave on *.* to 'replica_user'@'%'; ##Granting privileges to replication user.
FLUSH PRIVILEGES;
show master status ;
Dost Muhammad Khalil (OCP)
[email protected]+92 311 4848475
show global variables like 'gtid_executed';
NOTE: As can be seen the current binary logfile is mysql-bin.00001 and gtid_executed=1-4
mysqldump --all-databases -flush-privileges --single-transaction --flush-logs --triggers --routines --events -hex-blob --
host=127.0.0.1 --port=3306 --user=root --password=Mysql123# > mysqlbackup_dump.sql
##This will take backup of complete MySQL using mysqldump utility. It should be kept in mind that in production
environment we may have data in terabytes so in such case we can’t proceed with mysqldump utility because it is logical
and takes time so in that scenario we will go with physical backup utility like mysqlbackup or percona xtra backup.
$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ Replica SetUp $$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
--If the replica server is completely fresh just append the following in my.cnf file. Values can be changed according to
your desire and requirement.
server-id = 2
log-bin = mysql-bin
relay-log = relay-log-server
relay-log = relay-log-server
read-only = ON
gtid-mode=ON
enforce-gtid-consistency
log-slave-updates
systemctl restart mysqld ##Restarting Replica.
source mysqlbackup_dump.sql; ##Login to MySQL & Import the taken backup.
show global variables like 'gtid_executed';
##Checking the GTID_EXECUTED it should be noted that if your master is recieving transactions and can't be stopped
then this will return small value than the value returned by this statement on master which can be automatically set
once replica is started.
CHANGE MASTER TO ##Execute on Slave. Set the values accordingly.
MASTER_HOST = '192.168.231.182',
MASTER_PORT = 3306,
MASTER_USER = 'replica_user',
Dost Muhammad Khalil (OCP)
[email protected]+92 311 4848475
MASTER_PASSWORD = 'Replica123#',
MASTER_AUTO_POSITION = 1;
Show slave Status\G; ##This will give you info about replication process. If
facing any error then execute the below query on slave it will give the exact reason for that.
select * from performance_schema.replication_applier_status_by_worker\G;
START SLAVE;
Now perform some transactions on Master and verify on slave.
In such replication setup you will have to play with GTID so make sure you have strong understanding of GTID.
There are a lot to do with this replication topology explore with your self.
