Disabling GTID in MySQL replication

There could be situations where we have to disable GTID in our replication setup.
We can follow below steps to do so. No need to change any GTID related parameters. We just have to change slave configuration and bounce slave. No need to touch master.

Step 1) Confirm that you are using GTID

If you check processlist on master, you can see GTID is enabled

master> show processlist \G
*************************** 1. row ***************************
     Id: 51
   User: replicate
   Host: master-1.db.example.com:43420
     db: NULL
Command: Binlog Dump GTID
   Time: 81316
  State: Master has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL

Check the “command” above and it says “Binlog Dump GTID

 

Step 2) Stop slave, reset master_auto_position to 0


mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_auto_position=0;
Query OK, 0 rows affected (0.00 sec)

Step 3) Check slave status for master log file and position

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: master-1.db.example.com
                  Master_User: replicate
                  Master_Port: 3306
                Connect_Retry: 5
              Master_Log_File: bin_log.000003
          Read_Master_Log_Pos: 191
               Relay_Log_File: relay_log.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: bin_log.000003
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
       ...
       ...
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set: 8c593930-b4da-11e3-9e2d-b499ba05473c:1-7,
cc4192b6-a60d-11e4-8301-80c16e2272cc:1-168
                Auto_Position: 0
1 row in set (0.01 sec)

 

Step 4) Use change master to point to master log file and position


mysql> change master to master_host='master-1.db.example.com' , master_port = 3306 , master_user = 'replicate' , master_password='welcome' , master_log_file = 'bin_log.000003' , master_log_pos = 191, master_connect_retry = 5;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                              |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1759 | Sending passwords in plain text without SSL/TLS is extremely insecure.                                                                                                                                                                                                               |
| Note  | 1760 | Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

 

Step 5) start slave


mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

show slave status will still show GTIDs but its not using GTIDs now

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: master-1.db.example.com
                  Master_User: replicate
                  Master_Port: 3306
                Connect_Retry: 5
              Master_Log_File: bin_log.000003
          Read_Master_Log_Pos: 465
               Relay_Log_File: relay_log.000002
                Relay_Log_Pos: 586
        Relay_Master_Log_File: bin_log.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
              ...
              ...
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 8c593930-b4da-11e3-9e2d-b499ba05473c:8
            Executed_Gtid_Set: 8c593930-b4da-11e3-9e2d-b499ba05473c:1-8,
cc4192b6-a60d-11e4-8301-80c16e2272cc:1-168
                Auto_Position: 0
1 row in set (0.00 sec)

 

We can confirm that GTID is not getting used by checking processlist on master

master>show processlist \G
*************************** 1. row ***************************
     Id: 1
   User: root
   Host: localhost
     db: deo
Command: Query
   Time: 0
  State: init
   Info: show processlist
*************************** 2. row ***************************
     Id: 3
   User: replicate
   Host: master-1.db.example.com:59631
     db: NULL
Command: Binlog Dump
   Time: 24
  State: Master has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL
2 rows in set (0.00 sec)

If you check “command” for row 2, you will notice GTID is gone.

Hope this helps !!

Enabling GTID in MySQL

What is GTID ?

GTID stands for Global Transaction Identifier. This is a new feature introduced in 5.6 version.

I have given a link in reference section of this article which explains the concept of GTID.

Global transaction identifier is a unique transaction ID assigned to every transaction that happens in MySQL database. This ID has specific importance in maintaining replication slave. Using this ID we can easily track which transactions has been applied to slave and which are yet to be applied. This also prevents applying same transaction again to slave.

Here is how it works. When the master commits a transaction, it generates an identifier consisting of two components:

  • The first component is the server’s server_uuid (server_uuid is a global variable introduced in MySQL 5.6 which holds a UUID, i.e. a 128-bit random number generated so that the probability that two servers in the world have the same server_uuid is so small that we can assume every UUID is unique).
  • The second component is an integer; it is 1 for the first transaction committed on the server, 2 for the second transaction, and so on. Much like an autoincrement column, it is incremented by one for each commit.

The first component ensures that two transactions generated on different servers have different GTIDs. The second component ensures that two transactions generated on the same server have different GTIDs.

In text, we write the GTID as “UUID:N”, e.g., 22096C54-FE03-4B44-95E7-BD3C4400AF21:4711.

The GTID is written as an event to the binary log prior to the transaction

The binary log including the GTID is replicated to the slave. When the slave reads the GTID, it makes sure that the transaction retains the same GTID after it has been committed on the slave. Thus, the slave does not generate a new GTID;

Just to give a brief idea about binary logs – Binary logs are basically logs written for every transactions. They are like the archive logs/online redo logs in Oracle database terminology. I also see GTID is kind of a SCN number in Oracle database terminology.

Steps to enable GTID

You need to set following parameters either in /etc/my.cnf or while starting MySQL instance to enable GTID.

gtid_mode=ON
enforce_gtid_consistency=true (Starting with MySQL 5.6.9, –disable-gtid-unsafe-statements is now named –enforce-gtid-consistency) 
log_bin=/usr/local/mysql/log/bin_log 
log_slave_updates=true

If you are starting the instances using above parameters as arguments inline with mysqld_safe binary, you need to replace underscore(_) with hypen(-) in above parameters and include double hypens at the beginning as below

--gtid-mode=ON
--enforce-gtid-consistency=true (Starting with MySQL 5.6.9, –disable-gtid-unsafe-statements is now named –enforce-gtid-consistency) 
--log-bin=/usr/local/mysql/log/bin_log 
--log-slave_updates=true

I have updated /etc/my.cnf with above parameters and bounced MySQL instance

I can see GTID is now enabled


mysql> show global variables like '%GTID%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| enforce_gtid_consistency | ON |
| gtid_executed | |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
+--------------------------+-------+

mysql> show variables like '%gtid_next%';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| gtid_next | AUTOMATIC |
+---------------+-----------+

mysql> select @@global.gtid_executed;
+------------------------+
| @@global.gtid_executed |
+------------------------+
| |
+------------------------+
1 row in set (0.00 sec)

I will now import a database named “deo” into this MySQL instance.

Exporting deo database from my desktop server MySQL instance:

advait.desktop$ mysqldump --databases deo -u root -p > deo.sql
Enter password:
advait.desktop$

Importing deo database into my new MySQL instance:

/usr/local/mysql/bin/mysql -u root < deo.sql

If I check in my new MySQL instance, I can see deo database


mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| deo |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)

If I check the GTID value now


mysql> select @@global.gtid_executed;
+-------------------------------------------+
| @@global.gtid_executed |
+-------------------------------------------+
| 2441a23c-b4ae-11e3-9d0b-80c16e2272cc:1-21 |
+-------------------------------------------+
1 row in set (0.00 sec)

It means that 21 transactions has happened.

References:

GTID concepts – http://dev.mysql.com/doc/refman/5.6/en/replication-gtids-concepts.html

http://svenmysql.blogspot.in/2012/10/failover-and-flexible-replication.html

Hope this helps !!