Rechercher dans le manuel MySQL

17.1.4.2 Provisioning a Multi-Source Replication Slave for GTID-Based Replication

Table des matières

If the masters in the multi-source replication topology have existing data, it can save time to provision the slave 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 slave with data from all of the masters, and you might also want to replicate only specific databases from each master. The best strategy for provisioning such a slave is therefore to use mysqldump to create an appropriate dump file on each master, then use the mysql client to import the dump file on the slave.

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 master to the slave, and the slave requires this information. However, for any case more complex than provisioning one new, empty slave from one master, you need to check what effect the statement will have in the slave's MySQL release, and handle the statement accordingly. The following guidance summarizes suitable actions, but for more details, see the mysqldump documentation.

The behavior of the SET @@GLOBAL.gtid_purged statement written by mysqldump is different in releases from MySQL 8.0 compared to MySQL 5.6 and 5.7. In MySQL 5.6 and 5.7, the statement replaces the value of gtid_purged on the slave, and also in those releases that value can only be changed when the slave'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. Also note that for MySQL 5.6 and 5.7, this limitation means all the dump files from the masters must be applied in a single operation on a slave with an empty gtid_executed set. You can clear a slave's GTID execution history by issuing RESET MASTER on the slave, but if you have other, wanted transactions with GTIDs on the slave, choose an alternative method of provisioning from those described in Section 17.1.3.5, “Using GTIDs for Failover and Scaleout”.

From MySQL 8.0, the SET @@GLOBAL.gtid_purged statement adds the GTID set from the dump file to the existing gtid_purged set on the slave. The statement can therefore potentially be left in the dump output when you replay the dump files on the slave, and the dump files can be replayed at different times. However, it is important to note that the value that is included by mysqldump for the SET @@GLOBAL.gtid_purged statement includes the GTIDs of all transactions in the gtid_executed set on the master, 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 slave that contains any of the same GTIDs (for example, another partial dump from the same master, or a dump from another master that has overlapping transactions), any SET @@GLOBAL.gtid_purged statement in the second dump file will fail, and must therefore be removed from the dump output.

For masters from MySQL 8.0.17, as an alternative to removing the SET @@GLOBAL.gtid_purged statement, you may set mysqldump's --set-gtid-purged option to COMMENTED to include the statement but commented out, so that it is not actioned when you load the dump file. If you are provisioning the slave with two partial dumps from the same master, and the GTID set in the second dump is the same as the first (so no new transactions have been executed on the master 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.

In the following provisioning example, we assume that the SET @@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 slave before provisioning starts.

  1. To create dump files for a database named db1 on master1 and a database named db2 on master2, run mysqldump for master1 as follows: 

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

    Then run mysqldump for master2 as follows: 

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

  2. Record the gtid_purged value that mysqldump added to each of the dump files. For example, for dump files created on MySQL 5.6 or 5.7, you can extract the value like this: 

    cat dumpM1.sql | grep GTID_PURGED | cut -f2 -d'=' | cut -f2 -d$'\''
cat dumpM2.sql | grep GTID_PURGED | cut -f2 -d'=' | cut -f2 -d$'\''

    From MySQL 8.0, where the format has changed, 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: 

    master1:   2174B383-5441-11E8-B90A-C80AA9429562:1-1029
master2:   224DA167-0C0C-11E8-8442-00059A3C7B00:1-2695

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

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

  4. Use the mysql client to import each edited dump file into the slave. For example: 

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

  5. On the slave, issue RESET MASTER 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 slave). Then issue a SET @@GLOBAL.gtid_purged statement to set the gtid_purged value to the union of all the GTID sets from all the dump files, as you recorded in Step 2. For example: 

    1. mysql> RESET MASTER;
    2. 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 17.1.3.7, “Stored Function Examples to Manipulate GTIDs” to check this beforehand and to calculate the union of all the GTID sets.


Rechercher dans le manuel MySQL

Traduction non disponible

Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.

Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-replication-multi-source-provision-slave.html

L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.

Références

  1. Consulter le document html Langue du document :en Manuel MySQL : https://dev.mysql.com/

Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.

Table des matières Haut