This section describes a process for configuring and starting GTID-based replication in MySQL 5.6. This is a “cold start” procedure that assumes either that you are starting the replication master for the first time, or that it is possible to stop it; for information about provisioning replication slaves using GTIDs from a running master, see Section 126.96.36.199, “Using GTIDs for Failover and Scaleout”.
The key steps in this startup process for the simplest possible GTID replication topology—consisting of one master and one slave—are as follows:
If replication is already running, synchronize both servers by making them read-only.
Stop both servers.
Restart both servers with GTIDs, binary logging, and slave update logging enabled, and with statements that are unsafe for GTID-based replication disabled. In addition, the servers should be started in read-only mode, and the slave SQL and I/O threads should be prevented from starting on the slave.
The mysqld options necessary to start the servers as described are discussed in the example that follows later in this section.
Instruct the slave to use the master as the replication data source and to use auto-positioning, and then start the slave.
The SQL statements needed to accomplish this step are described in the example that follows later in this section.
Disable read-only mode on both servers, so that they can once again accept updates.
We now present a more detailed example. We assume two servers already running as master and slave, using MySQL's “classic” file-based replication protocol.
Most of the steps that follow require the use of the MySQL
root account or another MySQL user account
that has the
requires either the
SUPER privilege or the
Step 1: Synchronize the servers.
Make the servers read-only. To do this, enable the
read_only system variable by
executing the following statement on both servers:
SET @@global.read_only = ON;
Then, allow the slave to catch up with the master. It is extremely important that you make sure the slave has processed all updates before continuing.
Step 2: Stop both servers.
Stop each server using mysqladmin as shown
username is the user
name for a MySQL user having sufficient privileges to shut
down the server:
Then supply this user's password at the prompt.
Step 3: Restart both servers with GTIDs enabled. To enable binary logging with global transaction identifiers, each server must be started with GTID mode, binary logging, slave update logging enabled, and with statements that are unsafe for GTID-based replication disabled. In addition, you should prevent unwanted or accidental updates from being performed on either server by starting both in read-only mode. This means that both servers must be started with (at least) the options shown in the following invocation of mysqld_safe:
mysqld_safe --gtid_mode=ON --log-bin --log-slave-updates --enforce-gtid-consistency &
In addition, you should start the slave with the
--skip-slave-start option along
with the other server options specified in the example just
Although it may appear that
--gtid-mode is a boolean, it is
not (in fact, its values are enumerated). Use one of the values
OFF only, when
setting this option. Using a numeric value such as 0 or 1 can
lead to unexpected results.
For more information about the
options, see Section 188.8.131.52, “Global Transaction ID Options and Variables”.
Step 4: Direct the slave to use the master.
Instruct the slave to use the master as the replication data
source, and to use GTID-based auto-positioning rather than
file-based positioning. You can do this by executing a
CHANGE MASTER TO statement on
the slave, using the
option to tell the slave that transactions will be identified
You may also need to supply appropriate values for the master's host name and port number as well as the user name and password for a replication user account which can be used by the slave to connect to the master; if these have already been set prior to Step 1 and no further changes need to be made, the corresponding options can safely be omitted from the statement shown here.
CHANGE MASTER TO>
MASTER_AUTO_POSITION = 1;
MASTER_LOG_FILE option nor the
MASTER_LOG_POS option may be used with
MASTER_AUTO_POSITION set equal to 1.
Attempting to do so causes the
MASTER TO statement to fail with an error. (If you
need to revert from GTID-based replication to replication based
on files and positions, you must use one or both of these
options together with
0 in the
CHANGE MASTER TO
Assuming that the
CHANGE MASTER TO statement
has succeeded, you can then start the slave, like this:
Step 5: Disable read-only mode. Allow the master to begin accepting updates once again by running the following statement:
SET @@global.read_only = OFF;
GTID-based replication should now be running, and you can begin (or resume) activity on the master as before. Section 184.108.40.206, “Using GTIDs for Failover and Scaleout”, discusses creation of new slaves when using GTIDs.