17.1.5.2 Enabling GTID Transactions Online

This section describes how to enable GTID transactions, and
optionally auto-positioning, on servers that are already online
and using anonymous transactions. This procedure does not require
taking the server offline and is suited to use in production.
However, if you have the possibility to take the servers offline
when enabling GTID transactions that process is easier.

Before you start, ensure that the servers meet the following
pre-conditions:

All servers in your topology must use
MySQL 5.7.6 or later. You cannot enable GTID transactions
online on any single server unless all
servers which are in the topology are using this version.

The following procedure can be paused at any time and later
resumed where it was, or reversed by jumping to the corresponding
step of
Section 17.1.5.3, “Disabling GTID Transactions Online”,
the online procedure to disable GTIDs. This makes the procedure
fault-tolerant because any unrelated issues that may appear in the
middle of the procedure can be handled as usual, and then the
procedure continued where it was left off.

Note

It is crucial that you complete every step before continuing to
the next step.

To enable GTID transactions:

On each server, execute:

SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN;

Let the server run for a while with your normal workload and
monitor the logs. If this step causes any warnings in the log,
adjust your application so that it only uses GTID-compatible
features and does not generate any warnings.

Important

This is the first important step. You must ensure that no
warnings are being generated in the error logs before going
to the next step.

On each server, execute:

SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;

On each server, execute:

SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;

It does not matter which server executes this statement first,
but it is important that all servers complete this step before
any server begins the next step.

On each server, execute:

SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;

It does not matter which server executes this statement first.

On each server, wait until the status variable
ONGOING_ANONYMOUS_TRANSACTION_COUNT is
zero. This can be checked using:

SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT';

Note

On a replication slave, it is theoretically possible that
this shows zero and then nonzero again. This is not a
problem, it suffices that it shows zero once.

Wait for all transactions generated up to step 5 to replicate
to all servers. You can do this without stopping updates: the
only important thing is that all anonymous transactions get
replicated.

If you use binary logs for anything other than replication,
for example point in time backup and restore, wait until you
do not need the old binary logs having transactions without
GTIDs.

For instance, after step 6 has completed, 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.

Ideally, wait for the server to purge all binary logs that
existed when step 6 was completed. Also wait for any backup
taken before step 6 to expire.

Important

This is the second important point. It is vital to
understand that binary logs containing anonymous
transactions, without GTIDs cannot be used after the next
step. After this step, you must be sure that transactions
without GTIDs do not exist anywhere in the topology.

On each server, execute:

SET @@GLOBAL.GTID_MODE = ON;

On each server, add gtid-mode=ON to
my.cnf.

You are now guaranteed that all transactions have a GTID
(except transactions generated in step 5 or earlier, which
have already been processed). To start using the GTID protocol
so that you can later perform automatic fail-over, execute the
following on each slave. Optionally, if you use multi-source
replication, do this for each channel and include the
FOR CHANNEL
channel clause: