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 !!
Persistent Storage Solutions 