Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Mail Systems
Eclipse Documentation

How To Guides
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Problem Solutions




16.7.4. Replication Schema and Tables

Replication in MySQL Cluster makes use of a number of dedicated tables in a separate cluster_replication 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). This database, which is created during the MySQL installation process by the mysql_install_db script, contains a table for storing the binary log's indexing data. As the binlog_index table is local to each MySQL server and does not participate in clustering, it uses the MyISAM storage engine, and so must be created separately on each mysqld participating in the master cluster. This table is defined as follows:

CREATE TABLE `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` BIGNINT(20) UNSIGNED NOT NULL,
          PRIMARY KEY (`epoch`)

The following figure shows the relationship of the MySQL Cluster replication master server, its binlog injector thread, and the cluster_replication.binlog_index table.

The replication master cluster, the
          binlog-injector thread, and the
          binlog_index table

An additional table, named 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 binlog_index, the data in this table is not specific to any one SQL node in the (slave) cluster, and so apply_status can use the NDB Cluster storage engine, as shown here:

CREATE TABLE `apply_status` (
    `server_id` INT(10) UNSIGNED NOT NULL,
    `epoch`     BIGINT(20) UNSIGNED NOT NULL,
    PRIMARY KEY  USING HASH (`server_id`)

The binlog_index and apply_status tables are created in a separate database because they should not be replicated. No user intervention is normally required to create or maintain either of them. Both the binlog_index and the 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 changing, inserting, or deleting data are recorded in the binlog_index table. The slave I/O thread will transfer the 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 cluster_replication.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.

  Published under the terms of the GNU General Public License Design by Interspire