MySQL 5.7 Reference Manual Including MySQL NDB Cluster 7.5 and NDB Cluster 7.6
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 in the replica. This is true regardless of whether
the replica 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 source cluster.
(The binary log itself contains updates from all MySQL servers in
the cluster to be replicated.) This table is defined as follows:
CREATE TABLE `ndb_binlog_index` ( `Position` BIGINT(20) UNSIGNED NOT NULL, `File` VARCHAR(255) NOT NULL, `epoch` BIGINT(20) UNSIGNED NOT NULL, `inserts` INT(10) UNSIGNED NOT NULL, `updates` INT(10) UNSIGNED NOT NULL, `deletes` INT(10) UNSIGNED NOT NULL, `schemaops` INT(10) UNSIGNED NOT NULL, `orig_server_id` INT(10) UNSIGNED NOT NULL, `orig_epoch` BIGINT(20) UNSIGNED NOT NULL, `gci` INT(10) UNSIGNED NOT NULL, `next_position` bigint(20) unsigned NOT NULL, `next_file` varchar(255) NOT NULL, PRIMARY KEY (`epoch`,`orig_server_id`,`orig_epoch`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Prior to NDB 7.5.2, this table always used the
MyISAM
storage engine. If you are
upgrading from an earlier release, you can use
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.19, “CREATE TABLESPACE Statement”, and
Section 13.1.18, “CREATE TABLE Statement”, as well as
Section 14.6.3, “Tablespaces”.
The size of the ndb_binlog_index
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; this
relationship can be represented by the formula shown here:
[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 sources. The
SELECT
statement used to find the
closest binary log position to the highest applied epoch on the
replica in a multi-source setup (see
Section 20.6.10, “NDB Cluster Replication: Bidrectional 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 source has been running
with --ndb-log-empty-epochs=ON
. You
can improve multi-source failover times by adding an index to
these columns, as shown here:
ALTER TABLE mysql.ndb_binlog_index ADD INDEX orig_lookup USING BTREE (orig_server_id, orig_epoch);
Adding this index provides no benefit when replicating from a
single source to a single replica, 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 20.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 source 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 source to the replica. Unlike the case with
ndb_binlog_index
, the data in this table is not
specific to any one SQL node in the (replica) cluster, and so
ndb_apply_status
can use the
NDBCLUSTER
storage engine, as shown here:
CREATE TABLE `ndb_apply_status` ( `server_id` INT(10) UNSIGNED NOT NULL, `epoch` BIGINT(20) UNSIGNED NOT NULL, `log_name` VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, `start_pos` BIGINT(20) UNSIGNED NOT NULL, `end_pos` BIGINT(20) UNSIGNED NOT NULL, PRIMARY KEY (`server_id`) USING HASH ) ENGINE=NDBCLUSTER DEFAULT CHARSET=latin1;
The ndb_apply_status
table is populated only on
replicas, which means that, on the source, this table never
contains any rows; thus, there is no need to allot any
DataMemory
or
IndexMemory
to
ndb_apply_status
there.
Because this table is populated from data originating on the
source, it should be allowed to replicate; any replication
filtering or binary log filtering rules that inadvertently prevent
the replica from updating ndb_apply_status
, or
that prevent the source 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 are maintained by the NDB
binary
log (binlog) injector thread. This keeps the source
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 replica I/O
thread transfers the events from the source's binary log to
the replica's relay log.
Even though ndb_binlog_index
and
ndb_apply_status
are created and maintained
automatically, 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
source. This can be also be accomplished using the
SHOW BINLOG EVENTS
statement on
either the source or replica SQL node. (See
Section 13.7.5.2, “SHOW BINLOG EVENTS Statement”.)
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 replica, 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 20.6.11, “NDB Cluster Replication Conflict Resolution”.