• MySQL 5.1 Reference Manual :: 17 MySQL Cluster NDB 6.X/7.X :: 17.6 MySQL Cluster Replication :: 17.6.4 MySQL Cluster Replication Schema and Tables
  • 17.6.4. MySQL Cluster Replication Schema and Tables

    Replication in MySQL 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 by the mysql_install_db script, 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 MyISAM storage 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;
    

    Important

    Prior to MySQL 5.1.14, the ndb_binlog_index table was known as binlog_index, and was kept in a separate cluster database, which in MySQL 5.1.7 and earlier was known as the cluster_replication database. Similarly, the ndb_apply_status and ndb_schema tables were known as apply_status and schema, and were also found in the cluster (earlier cluster_replication) database. However, beginning with MySQL 5.1.14, all MySQL Cluster replication tables reside in the mysql system database.

    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_server_id and orig_epoch have been added to this table; when mysqld is started with the --ndb-log-orig option, 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;
    

    The gci column 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 mysql.ndb_binlog_index table.

    The replication master cluster, the
        binlog-injector thread, and the
        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 NDB Cluster 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;
    

    This table is populated only on slaves; on the master, no DataMemory is 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”.

    The log_name, start_pos, and end_pos columns were added in MySQL 5.1.18.

    Important

    If you are using MySQL Cluster replication, see Section 17.2.6.2, “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.

    The ndb_binlog_index and ndb_apply_status tables are created in the mysql database because they should not be replicated. No user intervention is normally required to create or maintain either of them. Both the ndb_binlog_index and the ndb_apply_status tables are maintained by the NDB 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 a MySQL 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 12.4.5.3, “SHOW BINLOG EVENTS Syntax”.)

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

    The ndb_schema table is used to track schema changes made to NDB tables. 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_schema table is not visible either to MySQL SHOW statements, or in any INFORMATION_SCHEMA tables; however, it can be seen in the output of ndb_show_tables, as shown here:

    shell> ndb_show_tables -t 2
    id    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
    

    It is also possible to SELECT from this table in mysql and other MySQL client applications, as shown here:

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

    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.

    The ndb_schema table was added in MySQL 5.1.8.

    Beginning with MySQL 5.1.14, if either of the ndb_apply_status or ndb_schema tables 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_replication table. Currently, this table must be created manually. For details, see Section 17.6.11, “MySQL Cluster Replication Conflict Resolution”.