Rechercher dans le manuel MySQL
17.1.4.2 Provisioning a Multi-Source Replication Slave for GTID-Based Replication
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.
To create dump files for a database named
db1onmaster1and a database nameddb2onmaster2, run mysqldump formaster1as follows:mysqldump -u<user> -p<password> --single-transaction --triggers --routines --set-gtid-purged=ON --databases db1 > dumpM1.sqlThen run mysqldump for
master2as follows:mysqldump -u<user> -p<password> --single-transaction --triggers --routines --set-gtid-purged=ON --databases db2 > dumpM2.sqlRecord the
gtid_purgedvalue 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-2695Remove 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.sqlUse 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.sqlOn the slave, 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 slave). Then issue aSET @@GLOBAL.gtid_purgedstatement to set thegtid_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;
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.
Deutsche Übersetzung
Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.
Vielen Dank im Voraus.
Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-replication-multi-source-provision-slave.html
Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.
Referenzen
Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.
