Replication in MySQL Cluster makes use of a number of dedicated tables in the
mysqldatabase 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 by the mysql_install_db script, and include a table for storing the binary log's indexing data. Since the
ndb_binlog_indextable is local to each MySQL server and does not participate in clustering, it uses the
MyISAMstorage engine. This means that it must be created separately on each mysqld participating in the master cluster. (However, the binlog 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` BIGINT(20) UNSIGNED NOT NULL, `updates` BIGINT(20) UNSIGNED NOT NULL, `deletes` BIGINT(20) UNSIGNED NOT NULL, `schemaops` BIGINT(20) UNSIGNED NOT NULL, PRIMARY KEY (`epoch`) ) ENGINE=MYISAM DEFAULT CHARSET=latin1;
Prior to MySQL 5.1.14, the
ndb_binlog_indextable was known as
binlog_index, and was kept in a separate
clusterdatabase, which in MySQL 5.1.7 and earlier was known as the
cluster_replicationdatabase. Similarly, the
ndb_schematables were known as
schema, and were also found in the
cluster_replication) database. However, beginning with MySQL 5.1.14, all MySQL Cluster replication tables reside in the
Information about how this change affects upgrades from MySQL Cluster 5.1.13 and earlier to 5.1.14 and later versions can be found in Section C.1.40, “Changes in MySQL 5.1.14 (05 December 2006)”.
Beginning with MySQL Cluster NDB 6.3.2, this table has been changed to facilitate 3-way replication recovery. Two columns
orig_epochhave been added to this table; when mysqld is started with the
--ndb-log-origoption, these 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. In addition, the table's primary key now includes these two columns. The modified table definition is shown here:
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, PRIMARY KEY (`epoch`,`orig_server_id`,`orig_epoch`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
gcicolumn was added in MySQL Cluster NDB 6.2.6 and MySQL Cluster NDB 6.3.2.
The following figure shows the relationship of the MySQL Cluster replication master server, its binlog injector thread, and the
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_statuscan use the
NDB Clusterstorage 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;
This table is populated only on slaves; on the master, no
DataMemoryis allocated to it. However, the table is populated from the master. For this reason, this table must be replicated and any replication filtering or binary log filtering rules that prevent this prevent replication between clusters from operating properly. For more information about potential problems arising from such filtering rules, see Section 17.6.3, “Known Issues in MySQL Cluster Replication”.
end_poscolumns were added in MySQL 5.1.18.
If you are using MySQL Cluster replication, see Section 184.108.40.206, “MySQL Cluster 5.1 and MySQL Cluster NDB 6.x/7.x Upgrade and Downgrade Compatibility” before upgrading to MySQL 5.1.18 or later from an earlier version.
ndb_apply_statustables are created in the
mysqldatabase because they should not be replicated. No user intervention is normally required to create or maintain either of them. Both the
ndb_apply_statustables are maintained by the
NDBinjector thread. This keeps the master mysqld process updated to changes performed by the
NDBstorage engine. The
NDBbinlog injector thread receives events directly from the
NDBstorage engine. The
NDBinjector 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_indextable. 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 a MySQL Cluster for replication. It is possible to view event data recorded in the binary log by querying the
mysql.ndb_binlog_indextable directly on the master. This can be also be accomplished using the
SHOW BINLOG EVENTSstatement on either the replication master or slave MySQL servers. (See Section 220.127.116.11, “
SHOW BINLOG EVENTSSyntax”.)
You can also obtain useful information from the output of
SHOW ENGINE NDB STATUS.
ndb_schematable is used to track schema changes made to
NDBtables. It is defined as shown here:
CREATE TABLE ndb_schema ( `db` VARBINARY(63) NOT NULL, `name` VARBINARY(63) NOT NULL, `slock` BINARY(32) NOT NULL, `query` BLOB NOT NULL, `node_id` INT UNSIGNED NOT NULL, `epoch` BIGINT UNSIGNED NOT NULL, `id` INT UNSIGNED NOT NULL, `version` INT UNSIGNED NOT NULL, `type` INT UNSIGNED NOT NULL, PRIMARY KEY USING HASH (db,name) ) ENGINE=NDB DEFAULT CHARSET=latin1;
Unlike the two tables previously mentioned in this section, the
ndb_schematable is not visible either to MySQL
SHOWstatements, or in any
INFORMATION_SCHEMAtables; however, it can be seen in the output of ndb_show_tables, as shown here:
ndb_show_tables -t 2id type state logging database schema name 4 UserTable Online Yes mysql def ndb_apply_status 5 UserTable Online Yes ndbworld def City 6 UserTable Online Yes ndbworld def Country 3 UserTable Online Yes mysql def NDB$BLOB_2_3 7 UserTable Online Yes ndbworld def CountryLanguage 2 UserTable Online Yes mysql def ndb_schema NDBT_ProgramExit: 0 - OK
SELECT * FROM mysql.ndb_schema WHERE name='City' \G*************************** 1. row *************************** db: ndbworld name: City slock: query: alter table City engine=ndb node_id: 4 epoch: 0 id: 0 version: 0 type: 7 1 row in set (0.00 sec)
This can sometimes be useful when debugging applications.
When performing schema changes on
NDBtables, applications should wait until the
ALTER TABLEstatement has returned in the MySQL client connection that issued the statement before attempting to use the updated definition of the table.
The ndb_schema table was added in MySQL 5.1.8.
Beginning with MySQL 5.1.14, if either of the
ndb_schematables does not exist on the slave, it is created by ndb_restore. (Bug#14612)
Conflict resolution for MySQL Cluster Replication requires the presence of an additional
mysql.ndb_replicationtable. Currently, this table must be created manually. For details, see Section 17.6.11, “MySQL Cluster Replication Conflict Resolution”.