MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
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.
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;
On each server, stop updates by running the following statement:
SET @@GLOBAL.READ_ONLY = ON;
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.
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.
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.
On each server, set
gtid_mode=OFF
and
enforce_gtid_consistency=OFF
in my.cnf
.
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.
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.
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.