This section describes a process for configuring and starting GTID-based replication in MySQL 5.7. 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”. For information about changing GTID mode on servers online, see Section 17.1.5, “Changing Replication Modes on Online Servers”.
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 enabled and the correct options configured.
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.
Enable read mode again on both servers, so that they can accept updates.
In the following example, two servers are already running as master and slave, using MySQL's binary log position-based replication protocol. If you are starting with new servers, see Section 188.8.131.52, “Creating a User for Replication” for information about adding a specific user for replication connections and Section 184.108.40.206, “Setting the Replication Master Configuration” for information about setting the server-id. The following examples show how to use startup options when running mysqld. Alternatively you can store startup options in an option file, see Section 4.2.6, “Using Option Files” for more information.
Most of the steps that follow require the use of the MySQL
root account or another MySQL user account that
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
Then supply this user's password at the prompt.
Step 3: Restart both servers with GTIDs enabled.
To enable GTID-based replication, each server must be started
with GTID mode enabled, by setting the
--gtid-mode option to
ON, and with the
enabled to ensure that only statements which are safe for
GTID-based replication are logged. In addition, you should start
the slave with the
--skip-slave-start option before
configuring the slave settings. For more information on GTID
related options, see
Section 220.127.116.11, “Global Transaction ID Options and Variables”.
It is not mandatory to have binary logging enabled in order to use GTIDs due to the addition of the The mysql.gtid_executed Table in MySQL 5.7.5. This means that you can have slave servers using GTIDs but without binary logging. Masters must always have binary logging enabled in order to be able to replicate. For example, to start a slave with GTIDs enabled but without binary logging, use at least these options:
mysqld --gtid-mode=ON --enforce-gtid-consistency &
In MySQL 5.7.4 and earlier, binary logging is required to use GTIDs and both master and slave servers must be started with at least these options:
mysqld --gtid-mode=ON --log-bin --enforce-gtid-consistency &
Depending on your configuration, supply additional options to mysqld.
Step 4: Direct the slave to use the master.
Tell the slave to use the master as the replication data source,
and to use GTID-based auto-positioning rather than file-based
positioning. Execute a
TO statement on the slave, using the
MASTER_AUTO_POSITION option to tell the slave
that transactions will be identified by GTIDs.
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
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
MASTER_AUTO_POSITION = 0 in the
CHANGE MASTER TO statement.)
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 18.104.22.168, “Using GTIDs for Failover and Scaleout”, discusses creation of new slaves when using GTIDs.