In a previous post I was talking about How to Setup MySQL Replication using the classic method (based on binary logs information). In this article I‘ll go through the transaction-based replication implementation using GTID in different scenarios.

The following topics will be covered in this blog:

• What is the concept of GTID protocol?
• GTID Replication Implementation
  • Fresh Installations
  • Adding New Slave
• Migration from classic replication to GTID replication
  • How to perform the migration?

  • Is online migration from classic to GTID replication available?
  • Workaround ?

    ?

• GTID Benefits

What is the concept of GTID protocol?

GTID is a Global Transaction IDentifier which introduced in MySQL 5.6.5. It‘s not only unique on the server it was originated but it‘s unique among all servers in a replication setup. 
GTID also guarantee consistency because once a transaction is committed on a server, any other transaction having the same GTID will be ignored, i.e. a committed transaction on a master will be applied only once on the slaves.

GTID consists of two parts separated by a column {source_id:transactions_id}.

WHERE

• source_id: Normally the server‘s UUID on which the transaction originates. e.g. "b9b4712a-df64-11e3-b391-60672090eb04" .
• transaction_id: A sequence number determining the order of the committed transaction.

The following is the GTID for the third transaction on a server having the uuid "b9b4712a-df64-11e3-b391-60672090eb04": 
b9b4712a-df64-11e3-b391-60672090eb04:3

As a new protocol in MySQL there is a set of new related variables, the following are the most important ones (IMHO):

• gtid-mode: ON|OFF to enable or disable GTID, this is not a Boolean variable (0 and 1 are not acceptable).
• enforce-gtid-consistency: prevent executing the non transactionally safe statements, like: 
  
  • CREATE TABLE .. SELECT.
  • CREATE TEMPORARY TABLE (inside a transaction).
  • Statements that update nontransactional tables inside a transaction.
• gtid_purged: The set of transactions that have been purged from the binary logs.
• gtid_executed: The set of transactions which already executed on that server.
• gtid_next: The GTID which will be used for the next transaction.

GTID Replication Implementation

Fresh Installations

Fresh installation means that there‘s no data yet in the master or in other words, we are building a replication setup from scratch.

The implementation process is divided into two parts:

MASTER‘S SIDE CONFIGURATION:

• Add the following variables to the MySQL configuration file (my.cnf):

  [mysqld]
  server-id=1		
  log-bin=mysql-bin
  binlog_format=ROW
  gtid-mode=on
  enforce-gtid-consistency
  log-slave-updates

• Restart MySQL so that configuration changes take place:

  shell> service mysql restart

• Create a MySQL user to be used by the slave:

  SQL> GRANT REPLICATION SLAVE ON *.* TO ‘slave_user_name‘@‘slave_ip‘ IDENTIFIED BY ‘s3cret‘;

SLAVE‘S SIDE CONFIGURATION:

• Add the following variables to the my.cnf file:

  [mysqld]
  server-id=2
  log-bin=mysql-bin
  binlog_format=ROW
  relay_log=relay-log
  skip-slave-start
  gtid-mode=on
  enforce-gtid-consistency
  log-slave-updates
  

• Restart MySQL so that configuration changes take place:

  shell> service mysql restart

• Set the master information on the slave‘s:

  Unlike the classic method, we don‘t need the master‘s binary log information and only what we need is to specify MASTER_AUTO_POSITION=1 instead:

  SQL> CHANGE MASTER TO
      -> MASTER_HOST=‘master_ip‘,
      -> MASTER_PORT=3306,
      -> MASTER_USER=‘slave_user_name‘,
      -> MASTER_PASSWORD=‘s3cret‘,
      -> MASTER_AUTO_POSITION=1;

• Start replication:

  SQL> START SLAVE;

• Check the replication status:

  SQL> show slave status\G
                 Slave_IO_State: Waiting for master to send event
                    Master_Host: 127.0.0.1
                    Master_User: gtid_repl
                    Master_Port: 3320
                  Connect_Retry: 60
                Master_Log_File: mysql-bin.000007
            Read_Master_Log_Pos: 191
                 Relay_Log_File: relay-log.000004
                  Relay_Log_Pos: 401
          Relay_Master_Log_File: mysql-bin.000007
               Slave_IO_Running: Yes
              Slave_SQL_Running: Yes
                Replicate_Do_DB: 
  			.
  			.
  			.
  			.
  
             Retrieved_Gtid_Set: b9b4712a-df64-11e3-b391-60672090eb04:1-2
              Executed_Gtid_Set: b9b4712a-df64-11e3-b391-60672090eb04:1-2
                  Auto_Position: 1

Adding New Slave

It‘s a very simple process to add a new slave to a running replication (or setup replication with existing data) where GTID is being used:

• Backup the master server

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

• On the new slave, use the same MySQL configuration as described above (except the server id which should be unique) and restart it.
• Restore the backup file taken from the master.
• Use change master to with MASTER_AUTO_POSITION=1
• Start the slave.

Is it so simple like that!! How did the slave know the backup position?

What if some transactions were executed on the master after that backup?

Actually, when GTID is enabled, mysqldump includes the last transaction ID (GTID) at the time of taking the backup:

--
-- GTID state at the beginning of the backup 
--

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

After restoring the backup the variable GTID_EXECUTED will be equal to GTID_PURGED (the above value) and when the slave starts it first sends the range of GTIDs it has executed (GTID_EXECUTED) to the master so that the master can sends back every missing transaction which was not applied yet on the slave.

Migration from classic replication to GTID replication

How to perform the migration?

To migrate an already running replication using the classical method to GTID replication, the following steps should be done:

• Ensure that all servers (master and slaves) are in the same point by setting the master server as read only (SET GLOBAL read_only=ON;) and wait until all slaves catch up the master‘s data.
• Shutdown MySQL on all servers and add the GTID variables to the configuration files.
• Beside the GTID variables, add read-only to the master‘s configuration and skip-slave-start to the slaves configurations.
• Start MySQL service on all servers.
• Issue the change master command with MASTER_AUTO_POSITION=1 on all slaves and then start them.
• Make the master writable again by SET GLOBAL read_only=OFF; (don‘t forget to remove/hash it from the master‘s my.cnf file as well).

Is online migration from classic to GTID replication available?

At the time of writing this article, the online migration is not applicable - as you can see from the above steps - we have to shutdown ALL servers at the same time and that is because of two reasons:

• GTID can NOT be enabled online because GTID_MODE is a read only variable (having this variable to be dynamic is already inOracle‘s plan).
• Replication can NOT be established between two or more servers having different values for GTID_MODE, i.e. either GTID is enabled on ALL servers or disabled on ALL servers.

Workaround ?

?

There‘s a feature request (by MySQL Devs team at Booking.com) to have an extra GTID mode (ANONYMOUS_IN-GTID_OUT) which allows a slave to receives anonymous transactions (transactions from master having GTID_MODE = OFF which do not have GTIDs) and assigns GTIDs for those transactions. In this case, this slave could be used as an intermediate server between master having GTID disabled and slaves having GTID enabled (it will be slave for the master and master for the other slaves)

The online migration steps would be:

• Restart a slave (lets name it slaveA) using the GTID_MODE = ANONYMOUS_IN-GTID_OUT.
• Rolling restart to the other slaves to use the normal GTID_MODE=ON and pointing them to slaveA as a new master.
• Point the application to write to slaveA instead of the old master.
• Restart the old master to use GTID_MODE=ON and having slaveA as a master.

Note: This is not yet available in Oracle binaries

More information on this could be find here.

GTID Benefits

• Simplifies the setup of MySQL replication as master‘s binary logs information is not needed anymore (binary log file name and position).
• Consistency is guaranteed between master and slave as the committed transaction on the master will be applied only once on the slave.
• Simple to determine whether masters and slaves are consistent or not.
• Fail-over process is much easier. When the master fail to operate, no need to calculate a slave‘s binary logs information before promoting it to be new master. MASTER_AUTO_POSITION=1 will do the job as all transactions in all servers inside the replication have the same GTID.
• Automatic fail-over scripts is now much easier to implement.

In a future post, I will write about how to troubleshoot GTID replication.