How To Setup GTID Replication In MySQL 5.6

What is GTID?

GTID (Global Transaction Identifier) is a unique identifier created and associated with each transaction committed on the server of origin. It consists of two parts separated by a column:

GTID = source_id:transaction_id

Where,

source_id : Server’s UUID
transaction_id : Sequence number

GTID Benefits

Easy to setup MySQL replication.
Consistency is guaranteed between master and salves.
Fail-over process become easy.
Automatic fail over script is not a pain now.
Simple to determine inconsistency.

Setting Up Replication Using GTIDs

MySQL master side configurations
MySQL slave side configurations

Master Side Configuration

Add the following variables to /etc/my.cnf.

# vim /etc/my.cnf [mysqld] log-bin = mysql-bin server-id = 1 relay-log = relay-log-slave gtid-mode =ON enforce-gtid-consistency binlog_format = MIXED log_slave_updates

Save and close file.

Restart MySQL to apply the configuration changes:

# service mysql restart

Now create a MySQL user to be used by the slave.

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user_name'@'slave_ip' IDENTIFIED BY 'password';

Slave Side Configuration

Add the following variables to /etc/my.cnf.

# vim /etc/my.cnf [mysqld] log_bin = mysql-bin server_id = 2 binlog_format = ROW skip_slave_start gtid_mode = on enforce_gtid_consistency log_slave_updates

Save and close file.

Restart MySQL to apply the configuration changes:

# service mysql restart

Now run the CHANGE MASTER TO command:

mysql> CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_PORT=3306, MASTER_USER='slave_user_name', MASTER_PASSWORD='password', MASTER_AUTO_POSITION=1;

Start the replication

mysql> start slave;

Check The Replication

Now check the replicatin status by running below command:

mysql> show slave status\G; Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.5 Master_User: repleuser Master_Port: 3306 . . . Slave_IO_Running: Yes Slave_SQL_Running: Yes . . . Retrieved_Gtid_Set: b9b4712a-df64-11e3-b391-60672090eb04:1-2 Executed_Gtid_Set: b9b4712a-df64-11e3-b391-60672090eb04:1-2 Auto_Position: 1

Add New Slave To A GTID Replication

Follow the below steps to add a new slave to GTID replication:

1. Teka master server backup using mysqldump.

mysql> mysqldump -u root -p --all-databases –flush-privileges --single-transaction --flush-logs --triggers --routines --events –hex-blob > /path/to/backupdir/full_backup-$TIMESTAMP.sql

2. Edit the new slave’s /etc/my.cnf file as described above.
3. Now restore the master backup file on the slave server.
4. Execute the CHANGE MASTER TO command with MASTER_AUTO_POSITION=1
5. Start MySQL slave.