184.108.40.206 Provisioning a Multi-Source Replica for GTID-Based Replication
If the sources in the multi-source replication topology have existing data, it can save time to provision the replica with the relevant data before starting replication. In a multi-source replication topology, copying the data directory cannot be used to provision the replica with data from all of the sources, and you might also want to replicate only specific databases from each source. The best strategy for provisioning such a replica is therefore to use mysqldump to create an appropriate dump file on each source, then use the mysql client to import the dump file on the replica.
If you are using GTID-based replication, you need to pay attention to the
SET @@GLOBAL.gtid_purged statement that mysqldump places in the dump output. This statement transfers the GTIDs for the transactions executed on the source to the replica, and the replica requires this information. However, for any case more complex than provisioning one new, empty replica from one source, you need to check what effect the statement will have in the replica's MySQL release, and handle the statement accordingly. The following guidance summarizes suitable actions, but for more details, see the mysqldump documentation.
In MySQL 5.6 and 5.7, the
SET @@GLOBAL.gtid_purged statement written by mysqldump replaces the value of
gtid_purged on the replica. Also in those releases that value can only be changed when the replica's record of transactions with GTIDs (the
gtid_executed set) is empty. In a multi-source replication topology, you must therefore remove the
SET @@GLOBAL.gtid_purged statement from the dump output before replaying the dump files, because you will not be able to apply a second or subsequent dump file including this statement. As an alternative to removing the
SET @@GLOBAL.gtid_purged statement, if you are provisioning the replica with two partial dumps from the same source, and the GTID set in the second dump is the same as the first (so no new transactions have been executed on the source in between the dumps), you can set mysqldump's
--set-gtid-purged option to
OFF when you output the second dump file, to omit the statement.
For MySQL 5.6 and 5.7, these limitations mean all the dump files from the sources must be applied in a single operation on a replica with an empty
gtid_executed set. You can clear a replica's GTID execution history by issuing
RESET MASTER on the replica, but if you have other, wanted transactions with GTIDs on the replica, choose an alternative method of provisioning from those described in Section 220.127.116.11, “Using GTIDs for Failover and Scaleout”.
In the following provisioning example, we assume that the
SET @@GLOBAL.gtid_purged statement needs to be removed from the files and handled manually. We also assume that there are no wanted transactions with GTIDs on the replica before provisioning starts.
To create dump files for a database named
source1and a database named
source2, run mysqldump for
mysqldump -u<user> -p<password> --single-transaction --triggers --routines --set-gtid-purged=ON --databases db1 > dumpM1.sql
Then run mysqldump for
mysqldump -u<user> -p<password> --single-transaction --triggers --routines --set-gtid-purged=ON --databases db2 > dumpM2.sql
cat dumpM1.sql | grep GTID_PURGED | cut -f2 -d'=' | cut -f2 -d$'\'' cat dumpM2.sql | grep GTID_PURGED | cut -f2 -d'=' | cut -f2 -d$'\''
The result in each case should be a GTID set, for example:
source1: 2174B383-5441-11E8-B90A-C80AA9429562:1-1029 source2: 224DA167-0C0C-11E8-8442-00059A3C7B00:1-2695
Remove the line from each dump file that contains the
SET @@GLOBAL.gtid_purgedstatement. For example:
sed '/GTID_PURGED/d' dumpM1.sql > dumpM1_nopurge.sql sed '/GTID_PURGED/d' dumpM2.sql > dumpM2_nopurge.sql
Use the mysql client to import each edited dump file into the replica. For example:
mysql -u<user> -p<password> < dumpM1_nopurge.sql mysql -u<user> -p<password> < dumpM2_nopurge.sql
On the replica, issue
RESET MASTERto clear the GTID execution history (assuming, as explained above, that all the dump files have been imported and that there are no wanted transactions with GTIDs on the replica). Then issue a
SET @@GLOBAL.gtid_purgedstatement to set the
gtid_purgedvalue to the union of all the GTID sets from all the dump files, as you recorded in Step 2. For example:
mysql> RESET MASTER; mysql> SET @@GLOBAL.gtid_purged = "2174B383-5441-11E8-B90A-C80AA9429562:1-1029, 224DA167-0C0C-11E8-8442-00059A3C7B00:1-2695";
If there are, or might be, overlapping transactions between the GTID sets in the dump files, you can use the stored functions described in Section 18.104.22.168, “Stored Function Examples to Manipulate GTIDs” to check this beforehand and to calculate the union of all the GTID sets.