MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide

17.1.3.5 Disabling GTID Transactions

If you have enabled GTIDs in MySQL 5.6 and want to downgrade to a MySQL release that does not support GTIDs, you must carry out this procedure to disable GTIDs before downgrading. In MySQL 5.6, you must take the servers offline in order to disable GTIDs.

  1. On each replica, disable auto-positioning by running the following statements:

    
    STOP SLAVE;
    CHANGE MASTER TO MASTER_AUTO_POSITION = 0, MASTER_LOG_FILE = file, \
    MASTER_LOG_POS = position;
    START SLAVE;
     
     
  2. On each server, stop updates by running the following statement:

    SET @@GLOBAL.READ_ONLY = ON;
  3. Wait for all ongoing transactions to commit or roll back. Then, wait for a safe period of time, depending on your deployment, for all transactions that currently exist in any binary log to replicate to all replicas. It is extremely important that you make sure all replicas have processed all updates before continuing.

    If you use binary logs for anything other than replication, for example to do point in time backup and restore, wait until you do not need the old binary logs containing GTID transactions. Ideally, wait for the server to purge all binary logs, and wait for any existing backup to expire.

    Important

    It is important to understand that logs containing GTID transactions cannot be used on servers where GTIDs are disabled. Before proceeding, you must be sure that GTID transactions do not exist anywhere in the topology.

  4. Stop each server using mysqladmin as shown here, where username is the user name for a MySQL user having sufficient privileges to shut down the server:

    shell> mysqladmin -uusername -p shutdown
    

    Then supply this user's password at the prompt.

  5. On each server, set gtid_mode=OFF and enforce_gtid_consistency=OFF in my.cnf.

  6. Restart each server in read-only mode, using mysqld_safe or another mysqld startup script, and specifying the option --read_only=ON on the command line. Starting the servers in read-only mode prevents unwanted or accidental updates from being performed on any server.

  7. Take a new backup at this point, so that you are not left without a usable backup. Existing backups that were made before you disabled GTIDs can no longer be used on these servers now that you have disabled GTIDs. For instance, you can execute FLUSH LOGS on the server where you are taking backups. Then either explicitly take a backup or wait for the next iteration of any periodic backup routine you may have set up.

  8. On each server, re-enable updates by running the following statement:

    SET @@GLOBAL.READ_ONLY = OFF;

If you want to downgrade to an earlier version of MySQL, you can do so now, using the normal downgrade procedure.