Rechercher dans le manuel MySQL
22.6.4 NDB Cluster Replication Schema and Tables
Replication in NDB Cluster makes use of a number of dedicated
tables in the mysql
database on each MySQL
Server instance acting as an SQL node in both the cluster being
replicated and the replication slave (whether the slave is a
single server or a cluster). These tables are created during the
MySQL installation process, and include a table for storing the
binary log's indexing data. Since the
ndb_binlog_index
table is local to each MySQL
server and does not participate in clustering, it uses the
InnoDB
storage engine. This means that it must
be created separately on each mysqld
participating in the master cluster. (However, the binary log
itself contains updates from all MySQL servers in the cluster to
be replicated.) This table is defined as follows:
If you are upgrading from an old release (prior to NDB 7.5.2),
perform the MySQL upgrade procedure and ensure that the system
tables are upgraded. (As of MySQL 8.0.16, start the server with
the --upgrade=FORCE
option. Prior to MySQL
8.0.16, invoke mysql_upgrade with the
--force
and
--upgrade-system-tables
options after starting the server.) The system table upgrade
causes an ALTER
TABLE ... ENGINE=INNODB
statement to be executed for
this table. Use of the MyISAM
storage engine
for this table continues to be supported for backward
compatibility.
ndb_binlog_index
may require additional disk
space after being converted to InnoDB
. If
this becomes an issue, you may be able to conserve space by
using an InnoDB
tablespace for this table,
changing its ROW_FORMAT
to
COMPRESSED
, or both. For more information,
see Section 13.1.21, “CREATE TABLESPACE Syntax”, and
Section 13.1.20, “CREATE TABLE Syntax”, as well as
Section 15.6.3, “Tablespaces”.
The size of this table is dependent on the number of epochs per
binary log file and the number of binary log files. The number of
epochs per binary log file normally depends on the amount of
binary log generated per epoch and the size of the binary log
file, with smaller epochs resulting in more epochs per file. You
should be aware that empty epochs produce inserts to the
ndb_binlog_index
table, even when the
--ndb-log-empty-epochs
option is
OFF
, meaning that the number of entries per
file depends on the length of time that the file is in use; that
is,
[number of epochs per file] = [time spent per file] / TimeBetweenEpochs
A busy NDB Cluster writes to the binary log regularly and
presumably rotates binary log files more quickly than a quiet one.
This means that a “quiet” NDB Cluster with
--ndb-log-empty-epochs=ON
can
actually have a much higher number of
ndb_binlog_index
rows per file than one with a
great deal of activity.
When mysqld is started with the
--ndb-log-orig
option, the
orig_server_id
and
orig_epoch
columns store, respectively, the ID
of the server on which the event originated and the epoch in which
the event took place on the originating server, which is useful in
NDB Cluster replication setups employing multiple masters. The
SELECT
statement used to find the
closest binary log position to the highest applied epoch on the
slave in a multi-master setup (see
Section 22.6.10, “NDB Cluster Replication: Multi-Master and Circular Replication”) employs
these two columns, which are not indexed. This can lead to
performance issues when trying to fail over, since the query must
perform a table scan, especially when the master has been running
with --ndb-log-empty-epochs=ON
. You
can improve multi-master failover times by adding an index to
these columns, as shown here:
Adding this index provides no benefit when replicating from a
single master to a single slave, since the query used to get the
binary log position in such cases makes no use of
orig_server_id
or
orig_epoch
.
See Section 22.6.8, “Implementing Failover with NDB Cluster Replication”, for more
information about using the next_position
and
next_file
columns.
The following figure shows the relationship of the NDB Cluster
replication master server, its binary log injector thread, and the
mysql.ndb_binlog_index
table.
An additional table, named ndb_apply_status
, is
used to keep a record of the operations that have been replicated
from the master to the slave. Unlike the case with
ndb_binlog_index
, the data in this table is not
specific to any one SQL node in the (slave) cluster, and so
ndb_apply_status
can use the
NDBCLUSTER
storage engine, as shown here:
The ndb_apply_status
table is populated only on
slaves, which means that, on the master, this table never contains
any rows; thus, there is no need to allow for
DataMemory
or
IndexMemory
to be allotted
to ndb_apply_status
there.
Because this table is populated from data originating on the
master, it should be allowed to replicate; any replication
filtering or binary log filtering rules that inadvertently prevent
the slave from updating ndb_apply_status
or the
master from writing into the binary log may prevent replication
between clusters from operating properly. For more information
about potential problems arising from such filtering rules, see
Replication and binary log filtering rules with replication between NDB
Clusters.
The ndb_binlog_index
and
ndb_apply_status
tables are created in the
mysql
database because they should not be
explicitly replicated by the user. User intervention is normally
not required to create or maintain either of these tables, since
both ndb_binlog_index
and the
ndb_apply_status
are maintained by the
NDB
binary log (binlog) injector
thread. This keeps the master mysqld process
updated to changes performed by the
NDB
storage engine. The
NDB
binlog
injector thread receives events directly from the
NDB
storage engine. The
NDB
injector is responsible for
capturing all the data events within the cluster, and ensures that
all events which change, insert, or delete data are recorded in
the ndb_binlog_index
table. The slave I/O
thread transfers the events from the master's binary log to the
slave's relay log.
However, it is advisable to check for the existence and integrity
of these tables as an initial step in preparing an NDB Cluster for
replication. It is possible to view event data recorded in the
binary log by querying the
mysql.ndb_binlog_index
table directly on the
master. This can be also be accomplished using the
SHOW BINLOG EVENTS
statement on
either the replication master or slave MySQL servers. (See
Section 13.7.6.2, “SHOW BINLOG EVENTS Syntax”.)
You can also obtain useful information from the output of
SHOW ENGINE NDB
STATUS
.
When performing schema changes on
NDB
tables, applications should
wait until the ALTER TABLE
statement has returned in the MySQL client connection that
issued the statement before attempting to use the updated
definition of the table.
If the ndb_apply_status
table does not exist on
the slave, ndb_restore re-creates it.
Conflict resolution for NDB Cluster Replication requires the
presence of an additional mysql.ndb_replication
table. Currently, this table must be created manually. For
information about how to do this, see
Section 22.6.11, “NDB Cluster Replication Conflict Resolution”.
Nederlandse vertaling
U hebt gevraagd om deze site in het Nederlands te bezoeken. Voor nu wordt alleen de interface vertaald, maar nog niet alle inhoud.Als je me wilt helpen met vertalingen, is je bijdrage welkom. Het enige dat u hoeft te doen, is u op de site registreren en mij een bericht sturen waarin u wordt gevraagd om u toe te voegen aan de groep vertalers, zodat u de gewenste pagina's kunt vertalen. Een link onderaan elke vertaalde pagina geeft aan dat u de vertaler bent en heeft een link naar uw profiel.
Bij voorbaat dank.
Document heeft de 26/06/2006 gemaakt, de laatste keer de 26/10/2018 gewijzigd
Bron van het afgedrukte document:https://www.gaudry.be/nl/mysql-rf-mysql-cluster-replication-schema.html
De infobrol is een persoonlijke site waarvan de inhoud uitsluitend mijn verantwoordelijkheid is. De tekst is beschikbaar onder CreativeCommons-licentie (BY-NC-SA). Meer info op de gebruiksvoorwaarden en de auteur.
Referenties
Deze verwijzingen en links verwijzen naar documenten die geraadpleegd zijn tijdens het schrijven van deze pagina, of die aanvullende informatie kunnen geven, maar de auteurs van deze bronnen kunnen niet verantwoordelijk worden gehouden voor de inhoud van deze pagina.
De auteur Deze site is als enige verantwoordelijk voor de manier waarop de verschillende concepten, en de vrijheden die met de referentiewerken worden genomen, hier worden gepresenteerd. Vergeet niet dat u meerdere broninformatie moet doorgeven om het risico op fouten te verkleinen.