mysql gtid setup_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.

mysqldump knows about GTID

--

-- GTID state at the beginning of the backup

--

SET @@GLOBAL.GTID_PURGED='b9b4712a-df64-11e3-b391-60672090eb04:1-7';

Thanks:)