Setting up MySQL Master- Master Replication with GTID

A global transaction identifier (GTID) is a unique identifier created and associated with each transaction committed on the server of origin (master). This identifier is unique not only to the server on which it originated, but is unique across all servers in a given replication setup. There is a 1-to-1 mapping between all transactions and all GTIDs. A GTID is represented as a pair of coordinates, separated by a colon character (:), as shown here:

GTID = source_id:transaction_idMySQL_gtid-master-master

Advantages :

● Setting up MySQL replication is simple
● Consistency is guaranteed between master and slaves.
● Simple to determine inconsistency
● Fail-over process become much easier.

Step 1 : Configure the Master server 1

To enable GTID-based replication, each server must be started with GTID mode enabled by setting the gtid_mode variable to ON, and with the enforce_gtid_consistency variable enabled to ensure that only statements which are safe for GTID-based replication are logged.

 vi /etc/my.cnf

Add the following parameter under [mysqld] section of my.cnf file

server-id = 1
log-bin = mysql-bin
binlog_format = row
gtid-mode=ON
enforce-gtid-consistency
log-slave-updates

Now start the mysqld server process

 sudo service mysqld start

Step 2 : Create a replication user for Master server 2

create user 'repl_user'@'%' identified by 'XXXXXXXXXX';
Grant replication slave on *.* to 'repl_user'@'%';

Step 3 : Take backup with mysqldump with Master  1

Now we take a backup with mysqldump from the master:

 mysqldump --all-databases -flush-privileges --single-transaction --flush-logs --triggers --routines --events -hex-blob --host=54.89.xx.xx --port=3306 --user=root  --password=XXXXXXXX > mysqlbackup_dump.sql

Step 4 : Configure the Master server 2 

 vi /etc/my.cnf

Add the following parameter under [mysqld] section of my.cnf file

server-id = 2
log-bin = mysql-bin
relay-log = relay-log-server
read-only = ON
gtid-mode=ON
enforce-gtid-consistency
log-slave-updates

Now start the mysqld server process

 sudo service mysqld start

Step 5: Create a replication user for Master server 1

create user 'repl_user'@'%' identified by 'XXXXXXXXXX';
Grant replication slave on *.* to 'repl_user'@'%';

Step 6 : Load the mysqldump in Master server 2 

 mysql>  source mysqlbackup_dump.sql

Step 7 : Execute the Change Master  on Master server 2 

Now we will tell the Master 2 to use Master 1 as a data source

CHANGE MASTER TO
MASTER_HOST = '54.89.xx.xx',
MASTER_PORT = 3306,
MASTER_USER = 'repl_user',
MASTER_PASSWORD = 'XXXXXXXXX',
MASTER_AUTO_POSITION = 1;

Start the slave And then check status.

start slave;
show slave status\G

If every thing fine then move to master 1 to apply change master.

Step 8 : Execute the Change Master  on Master server 1

Now we will tell the Master 2 to use Master 1 as a data source

CHANGE MASTER TO
MASTER_HOST = '58.79.xx.xx',
MASTER_PORT = 3306,
MASTER_USER = 'repl_user',
MASTER_PASSWORD = 'XXXXXXXXX',
MASTER_AUTO_POSITION = 1;

Start the slave And then check status.

start slave;
show slave status\G

Test the Replication

Create a table on both server and check whether the replication is working fine.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Powered by WordPress.com.

Up ↑

%d bloggers like this: