Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 29.5Mb
PDF (A4) - 29.5Mb
PDF (RPM) - 28.2Mb
HTML Download (TGZ) - 7.1Mb
HTML Download (Zip) - 7.1Mb
HTML Download (RPM) - 6.1Mb
Man Pages (TGZ) - 190.2Kb
Man Pages (Zip) - 305.1Kb
Info (Gzip) - 2.8Mb
Info (Zip) - 2.8Mb
Excerpts from this Manual

MySQL 5.6 Reference Manual  /  ...  /  NDB Cluster Replication Schema and Tables

18.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 in the replica. This is true regardless of whether the replica is a single server or a cluster.

The ndb_binlog_index and ndb_apply_status tables are created in the mysql database. 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.

The ndb_replication table must be created manually. This table can be updated by the user to perform filtering by database or table. See ndb_replication Table, for more information. ndb_replication is also used in NDB Replication conflict detection and resolution for conflict resolution control; see Conflict Resolution Control.

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.3, “SHOW BINLOG EVENTS Statement”.)

You can also obtain useful information from the output of SHOW ENGINE NDB STATUS.

Note

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.

ndb_apply_status Table

ndb_apply_status is used to keep a record of the operations that have been replicated from the source to the replica. If the ndb_apply_status table does not exist on the replica, ndb_restore re-creates it.

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 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.

ndb_binlog_index Table

NDB Cluster Replication uses the ndb_binlog_index table for storing the binary log's indexing data. Since this 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.) 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;
Note

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.18, “CREATE TABLESPACE Statement”, and Section 13.1.17, “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 18.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 18.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.

Figure 18.26 The Replication Source Cluster

Most concepts are described in the surrounding text. This complex image has three main areas. The top area is divided into three sections: MySQL Server (mysqld), NDBCLUSTER table handler, and mutex. A connection thread connects these, and receiver and injector threads connect the NDBCLUSTER table handler and mutex. The bottom area shows four data nodes (ndbd). They all produce events represented by arrows pointing to the receiver thread, and the receiver thread also points to the connection and injector threads. One node sends and receives to the mutex area. The arrow representing the injector thread points to a binary log as well as the ndb_binlog_index table, which is described in the surrounding text.

ndb_replication Table

The ndb_replication table is used to control binary logging and conflict resolution, and acts on a per-table basis. Each row in this table corresponds to a table being replicated, determines how to log changes to the table and, if a conflict resolution function is specified, and determines how to resolve conflicts for that table.

Unlike the ndb_apply_status and ndb_replication tables, the ndb_replication table must be created manually. You can do this by using the SQL statement shown here:

CREATE TABLE mysql.ndb_replication  (
    db VARBINARY(63),
    table_name VARBINARY(63),
    server_id INT UNSIGNED,
    binlog_type INT UNSIGNED,
    conflict_fn VARBINARY(128),
    PRIMARY KEY USING HASH (db, table_name, server_id)
)   ENGINE=NDB
PARTITION BY KEY(db,table_name);

The columns of this table are listed here, with descriptions:

  • db column

    The name of the database containing the table to be replicated. You may employ either or both of the wildcards _ and % as part of the database name. Matching is similar to what is implemented for the LIKE operator.

  • table_name column

    The name of the table to be replicated. The table name may include either or both of the wildcards _ and %. Matching is similar to what is implemented for the LIKE operator.

  • server_id column

    The unique server ID of the MySQL instance (SQL node) where the table resides.

  • binlog_type column

    The type of binary logging to be employed. See text for values and descriptions.

  • conflict_fn column

    The conflict resolution function to be applied; one of NDB$OLD(column_name), NDB$MAX(column_name), NDB$MAX_DELETE_WIN(), NDB$EPOCH(), NDB$EPOCH_TRANS(), NDB$EPOCH2(), NDB$EPOCH2_TRANS(); NULL indicates that conflict resolution is not used for this table.

    See Conflict Resolution Functions, for more information about these functions and their uses in NDB Replication conflict resolution.

    Some conflict resolution functions (NDB$OLD(), NDB$EPOCH(), NDB$EPOCH_TRANS()) require the use of one or more user-created exceptions tables. See Conflict Resolution Exceptions Table.

To enable conflict resolution with NDB Replication, it is necessary to create and populate this table with control information on the SQL node or nodes on which the conflict should be resolved. Depending on the conflict resolution type and method to be employed, this may be the source, the replica, or both servers. In a simple source-replica setup where data can also be changed locally on the replica this is typically the replica. In a more complex replication scheme, such as bidirectional replication, this is usually all of the sources involved. See Section 18.6.11, “NDB Cluster Replication Conflict Resolution”, for more information.

The ndb_replication table allows table-level control over binary logging outside the scope of conflict resolution, in which case conflict_fn is specified as NULL, while the remaining column values are used to control binary logging for a given table or set of tables matching a wildcard expression. By setting the proper value for the binlog_type column, you can make logging for a given table or tables use a desired binary log format, or disabling binary logging altogether. Possible values for this column, with internal values and descriptions, are shown in the following table:

Table 18.58 binlog_type values, with internal values and descriptions

Value Internal Value Description
0 NBT_DEFAULT Use server default
1 NBT_NO_LOGGING Do not log this table in the binary log
2 NBT_UPDATED_ONLY Only updated attributes are logged
3 NBT_FULL Log full row, even if not updated (MySQL server default behavior)
4 NBT_USE_UPDATE (For generating NBT_UPDATED_ONLY_USE_UPDATE and NBT_FULL_USE_UPDATE values only—not intended for separate use)
5 [Not used] ---
6 NBT_UPDATED_ONLY_USE_UPDATE (equal to NBT_UPDATED_ONLY | NBT_USE_UPDATE) Use updated attributes, even if values are unchanged
7 NBT_FULL_USE_UPDATE (equal to NBT_FULL | NBT_USE_UPDATE) Use full row, even if values are unchanged
8 NBT_UPDATED_ONLY_MINIMAL Log update as UPDATE_ROW; log only primary key columns in before image, and only updated columns in after image
9 NBT_UPDATED_FULL_MINIMAL Log update as UPDATE_ROW; log only primary key columns in before image, and all columns other than primary key columns in after image

Binary logging can be set to different formats for different tables by inserting rows into the ndb_replication table using the appropriate db, table_name, and binlog_type column values. The internal integer value shown in the preceding table should be used when setting the binary logging format. The following two statements set binary logging to logging of full rows (NBT_FULL, internal value 3) for table test.a, and to logging of updates only (NBT_UPDATED_ONLY, internal value 2) for table test.b:

# Table test.a: Log full rows
INSERT INTO mysql.ndb_replication VALUES("test", "a", 0, 3, NULL);

# Table test.b: log updates only
INSERT INTO mysql.ndb_replication VALUES("test", "b", 0, 2, NULL);

To disable logging for one or more tables, use 1 (NBT_NO_LOGGING) for binlog_type, as shown here:

# Disable binary logging for table test.t1
INSERT INTO mysql.ndb_replication VALUES("test", "t1", 0, 1, NULL);

# Disable binary logging for any table in 'test' whose name begins with 't'
INSERT INTO mysql.ndb_replication VALUES("test", "t%", 0, 1, NULL);