MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
There are a number of techniques when using MySQL Replication with Global Transaction Identifiers (GTIDs) in MySQL 5.6.9 and later for provisioning a new replica which can then be used for scaleout, being promoted to source as necessary for failover. In this section, we discuss the four techniques listed here:
Global transaction identifiers were added to MySQL Replication for the purpose of simplifying in general management of the replication data flow and of failover activities in particular. Each identifier uniquely identifies a set of binary log events that together make up a transaction. GTIDs play a key role in applying changes to the database: the server automatically skips any transaction having an identifier which the server recognizes as one that it has processed before. This behavior is critical for automatic replication positioning and correct failover.
The mapping between identifiers and sets of events comprising a given transaction is captured in the binary log. This poses some challenges when provisioning a new server with data from another existing server. To reproduce the identifier set on the new server, it is necessary to copy the identifiers from the old server to the new one, and to preserve the relationship between the identifiers and the actual events This is neccessary for restoring a replica that is immediately available as a candidate to become a new source on failover or switchover.
Simple replication. This is the easiest way to reproduce all identifiers and transactions on a new server; you simply make the new server into the replica of a source that has the entire execution history, and enable global transaction identifiers on both servers. See Section 17.1.3.2, “Setting Up Replication Using GTIDs”, for more information.
Once replication is started, the new server copies the entire binary log from the source and thus obtains all information about all GTIDs.
This method is simple and effective, but requires the replica to read the binary log from the source; it can sometimes take a comparatively long time for the new replica to catch up with the source, so this method is not suitable for fast failover or restoring from backup. This section explains how to avoid fetching all of the execution history from the source by copying binary log files to the new server.
Copying data and transactions to the replica. Playing back the entire transaction history can be time-consuming, and represents a major bottleneck when setting up a new replica. To eliminate this requirement, a snapshot of the data set, the binary logs and the global transaction information the source contains is imported to the replica. The the binary log is played back, after which replication can be started, allowing the replica to become current with any remaining transactions.
There are several variants of this method, the difference being in the manner in which data dumps and transactions from binary logs are transferred to the replica, as outlined here:
Use the mysql client to import a
dump file created with mysqldump.
Use the
--master-data
option
to include binary logging information and
--set-gtid-purged
(available in MySQL 5.6.9 and later) to
AUTO
(the default) or
ON
, to include information about
executed transactions. You should have
gtid_mode=ON
while
importing the dump on the replica. (Bug #14832472)
Stop the replica, copy the contents of the source's data directory to the replica's data directory, then restart the replica.
If gtid_mode
is not
ON
, restart the server with GTID mode
enabled.
Import the binary log using
mysqlbinlog, with the
--read-from-remote-server
and
--read-from-remote-master
options.
Copy the source's binary log files to the
replica. You can make copies from the replica using
mysqlbinlog
--read-from-remote-server
--raw
. These can
be read in to the replica in either of the following
ways:
Update the replica's
binlog.index
file to point to
the copied log files. Then execute a
CHANGE MASTER TO
statement in the mysql client
to point to the first log file, and
START SLAVE
to read
them.
Use mysqlbinlog
>
(without the
file
--raw
option)
to export the binary log files to SQL files that
can be processed by the mysql
client.
See also Section 4.6.8.3, “Using mysqlbinlog to Back Up Binary Log Files”.
This method has the advantage that a new server is available almost immediately; only those transactions that were committed while the snapshot or dump file was being replayed still need to be obtained from the existing source. This means that the replica's availability is not instantanteous, but only a relatively short amount of time should be required for the replica to catch up with these few remaining transactions.
Copying over binary logs to the target server in advance is usually faster than reading the entire transaction execution history from the source in real time. However, it may not always be feasible to move these files to the target when required, due to size or other considerations. The two remaining methods for provisioning a new replica discussed in this section use other means to transfer information about transactions to the new replica.
Injecting empty transactions.
The source's global
gtid_executed
variable
contains the set of all transactions executed on the source.
Rather than copy the binary logs when taking a snapshot to
provision a new server, you can instead note the content of
gtid_executed
on the server from which the
snapshot was taken. Before adding the new server to the
replication chain, simply commit an empty transaction on the
new server for each transaction identifier contained in the
source's gtid_executed
, like this:
SET GTID_NEXT='aaa-bbb-ccc-ddd:N'; BEGIN; COMMIT; SET GTID_NEXT='AUTOMATIC';
Once all transaction identifiers have been reinstated in this
way using empty transactions, you must flush and purge the
replica's binary logs, as shown here, where
N
is the nonzero suffix of the
current binary log file name:
FLUSH LOGS;
PURGE BINARY LOGS TO 'source-bin.00000N
';
You should do this to prevent this server from flooding the
replication stream with false transactions in the event that it
is later promoted to source. (The FLUSH
LOGS
statement forces the creation of a new binary log
file; PURGE BINARY LOGS
purges
the empty transactions, but retains their identifiers.)
This method creates a server that is essentially a snapshot, but in time is able to become a source as its binary log history converges with that of the replication stream (that is, as it catches up with the source or sources). This outcome is similar in effect to that obtained using the remaining provisioning method, which we discuss in the next few paragraphs.
Excluding transactions with gtid_purged.
The source's global
gtid_purged
variable contains
the set of all transactions that have been purged from the
source's binary log. As with the method discussed
previously (see
Injecting empty transactions), you can
record the value of
gtid_executed
on the server
from which the snapshot was taken (in place of copying the
binary logs to the new server). Unlike the previous method,
there is no need to commit empty transactions (or to issue
PURGE BINARY LOGS
); instead,
you can set gtid_purged
on
the replica directly, based on the value of
gtid_executed
on the server
from which the backup or snapshot was taken.
Prior to MySQL 5.6.9,
gtid_purged
was not settable.
(Bug #14797808)
As with the method using empty transactions, this method creates a server that is functionally a snapshot, but in time is able to become a source as its binary log history converges with that of the replication source server or group.