Documentation Home
MySQL 9.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 40.0Mb
PDF (A4) - 40.1Mb
Man Pages (TGZ) - 259.0Kb
Man Pages (Zip) - 366.2Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb


MySQL 9.0 Reference Manual  / ...  / Replication  / Configuring Replication  / MySQL Multi-Source Replication  /  Provisioning a Multi-Source Replica for GTID-Based Replication

19.1.5.2 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, cloning or copying of 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 usemysqldump to create an appropriate dump file on each source, then use themysql client to import the dump file on the replica.

If you are using GTID-based replication, you need to pay attention to theSET @@GLOBAL.gtid_purged statement thatmysqldump 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 has in the version of MySQL used by the replica, and handle the statement accordingly. The following guidance summarizes suitable actions, but for more details, see themysqldump documentation.

SET @@GLOBAL.gtid_purged adds the GTID set from the dump file to the existinggtid_purged set on the replica. The statement can therefore potentially be left in the dump output when you replay the dump files on the replica, and the dump files can be replayed at different times. However, it is important to note that the value that is included bymysqldump for theSET @@GLOBAL.gtid_purged statement includes the GTIDs of all transactions in thegtid_executed set on the source, even those that changed suppressed parts of the database, or other databases on the server that were not included in a partial dump. If you replay a second or subsequent dump file on the replica that contains any of the same GTIDs (for example, another partial dump from the same source, or a dump from another source that has overlapping transactions), anySET @@GLOBAL.gtid_purged statement in the second dump file fails, and must therefore be removed from the dump output.

As an alternative to removing theSET @@GLOBAL.gtid_purged statement, you caninokemysqldump with--set-gtid-purged=COMMENTED to include the statement encased in SQL comments, so that it is not performed when you load the dump file. 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--set-gtid-purged=OFF instead when you export the second dump file, to omit the statement.

In the following provisioning example, we assume that theSET @@GLOBAL.gtid_purged statement cannot be left in the dump output, and must 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.

  1. To create dump files for a database nameddb1 onsource1 and a database nameddb2 onsource2, runmysqldump forsource1 as follows:

    mysqldump -u<user> -p<password> --single-transaction --triggers --routines --set-gtid-purged=ON --databases db1 > dumpM1.sql

    Then runmysqldump forsource2 as follows:

    mysqldump -u<user> -p<password> --single-transaction --triggers --routines --set-gtid-purged=ON --databases db2 > dumpM2.sql

  2. Record thegtid_purged value thatmysqldump added to each of the dump files. You can extract the value like this:

    cat dumpM1.sql | grep GTID_PURGED | perl -p0 -e 's#/\*.*?\*/##sg' | cut -f2 -d'=' | cut -f2 -d$'\''cat dumpM2.sql | grep GTID_PURGED | perl -p0 -e 's#/\*.*?\*/##sg' | 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-1029source2:   224DA167-0C0C-11E8-8442-00059A3C7B00:1-2695

  3. Remove the line from each dump file that contains theSET @@GLOBAL.gtid_purged statement. For example:

    sed '/GTID_PURGED/d' dumpM1.sql > dumpM1_nopurge.sqlsed '/GTID_PURGED/d' dumpM2.sql > dumpM2_nopurge.sql

  4. Use themysql client to import each edited dump file into the replica. For example:

    mysql -u<user> -p<password> < dumpM1_nopurge.sqlmysql -u<user> -p<password> < dumpM2_nopurge.sql

  5. On the replica, issueRESET BINARY LOGS AND GTIDS to 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 aSET @@GLOBAL.gtid_purged statement to set thegtid_purged value to the union of all the GTID sets from all the dump files, as you recorded in Step 2. For example:

    mysql> RESET BINARY LOGS AND GTIDS;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 inSection 19.1.3.8, “Stored Function Examples to Manipulate GTIDs” to check this beforehand and to calculate the union of all the GTID sets.