MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
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 source server for the first time, or that it is possible to stop it; for information about provisioning replicas using GTIDs from a running source, see Section 17.1.3.3, “Using GTIDs for Failover and Scaleout”.
The key steps in this startup process for the simplest possible GTID replication topology, consisting of one source and one replica, 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 replica 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 replication SQL and I/O threads should be prevented from starting on the replica.
The mysqld options necessary to start the servers as described are discussed in the example that follows later in this section.
Instruct the replica to use the source as the replication data source and to use auto-positioning. The SQL statements needed to accomplish this step are described in the example that follows later in this section.
Take a new backup. Binary logs containing transactions without GTIDs cannot be used on servers where GTIDs are enabled, so backups taken before this point cannot be used with your new configuration.
Start the replica, then disable read-only mode again on both servers, so that they can accept updates.
In the following example, two servers are already running as source and replica, 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 SUPER
privilege.
mysqladmin shutdown
requires either the SUPER
privilege or the
SHUTDOWN
privilege.
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:
mysql> SET @@GLOBAL.read_only = ON;
Wait for all ongoing transactions to commit or roll back. Then, allow the replica to catch up with the source. It is extremely important that you make sure the replica has 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 transactions without GTIDs. 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 transactions without GTIDs cannot be used on servers where GTIDs are enabled. Before proceeding, you must be sure that transactions without GTIDs do not exist anywhere in the topology.
Step 2: Stop both servers.
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.
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, replica 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:
shell> mysqld_safe --gtid_mode=ON --log-bin --log-slave-updates --enforce-gtid-consistency &
In addition, you should start the replica with the
--skip-slave-start
option along
with the other server options specified in the example just
shown.
gtid_mode
is not a boolean,
but an enumeration. Use one of the values
ON
or 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
gtid_mode
and
enforce_gtid_consistency
system
variables, see Section 17.1.4.5, “Global Transaction ID Options and Variables”.
Depending on your configuration, supply additional options to mysqld_safe or other mysqld startup script.
Step 4: Direct the replica to use the source.
Tell the replica to use the replication source server as the
data source, and to use GTID-based auto-positioning rather
than file-based positioning. Execute a
CHANGE MASTER TO
statement on
the replica, using the MASTER_AUTO_POSITION
option to tell the replica that transactions are identified by
GTIDs.
You may also need to supply appropriate values for the source'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 replica to connect to the source; 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.
mysql>CHANGE MASTER TO
>MASTER_HOST =
>host
,MASTER_PORT =
>port
,MASTER_USER =
>user
,MASTER_PASSWORD =
>password
,MASTER_AUTO_POSITION = 1;
Neither the 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 CHANGE
MASTER TO
statement to fail with an error.
Step 5: Take a new backup. Existing backups that were made before you enabled GTIDs can no longer be used on these servers now that you have enabled GTIDs. Take a new backup at this point, so that you are not left without a usable backup.
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.
Step 6: Start the replica and disable read-only mode. Start the replica like this:
mysql> START SLAVE;
Allow the source to begin accepting updates once again by running the following statement:
mysql> SET @@GLOBAL.read_only = OFF;
GTID-based replication should now be running, and you can begin (or resume) activity on the source as before. Section 17.1.3.3, “Using GTIDs for Failover and Scaleout”, discusses creation of new replicas when using GTIDs.