Rechercher dans le manuel MySQL

22.6.4 NDB Cluster Replication Schema and Tables

Table des matières

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 the replication slave (whether the slave 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 master cluster. (However, the binary log itself contains updates from all MySQL servers in the cluster to be replicated.) This table is defined as follows:

  1. CREATE TABLE `ndb_binlog_index` (
  2.     `Position` BIGINT(20) UNSIGNED NOT NULL,
  3.     `File` VARCHAR(255) NOT NULL,
  4.     `epoch` BIGINT(20) UNSIGNED NOT NULL,
  5.     `inserts` INT(10) UNSIGNED NOT NULL,
  6.     `updates` INT(10) UNSIGNED NOT NULL,
  7.     `deletes` INT(10) UNSIGNED NOT NULL,
  8.     `schemaops` INT(10) UNSIGNED NOT NULL,
  9.     `orig_server_id` INT(10) UNSIGNED NOT NULL,
  10.     `orig_epoch` BIGINT(20) UNSIGNED NOT NULL,
  11.     `gci` INT(10) UNSIGNED NOT NULL,
  12.     `next_position` bigint(20) unsigned NOT NULL,
  13.     `next_file` varchar(255) NOT NULL,
  14.     PRIMARY KEY (`epoch`,`orig_server_id`,`orig_epoch`)
  15. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Note

If you are upgrading from an old release (prior to NDB 7.5.2), perform the MySQL upgrade procedure and ensure that the system tables are upgraded. (As of MySQL 8.0.16, start the server with the --upgrade=FORCE option. Prior to MySQL 8.0.16, invoke 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.21, “CREATE TABLESPACE Syntax”, and Section 13.1.20, “CREATE TABLE Syntax”, as well as Section 15.6.3, “Tablespaces”.

The size of this 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; that is, 

[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 masters. The SELECT statement used to find the closest binary log position to the highest applied epoch on the slave in a multi-master setup (see Section 22.6.10, “NDB Cluster Replication: Multi-Master 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 master has been running with --ndb-log-empty-epochs=ON. You can improve multi-master failover times by adding an index to these columns, as shown here:

  1. ALTER TABLE mysql.ndb_binlog_index
  2.     ADD INDEX orig_lookup USING BTREE (orig_server_id, orig_epoch);

Adding this index provides no benefit when replicating from a single master to a single slave, 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 22.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 master server, its binary log injector thread, and the mysql.ndb_binlog_index table.

Figure 22.34 The Replication Master 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 three areas, and receiver and injector threads connect NdbCluster table handler and mutex. The bottom area lists four data nodes (ndbd). They all have events 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 injector thread points to a binlog and also the third area in this image: the ndb_binlog_index table, a table described in the surrounding text.

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 NDBCLUSTER storage engine, as shown here:

  1. CREATE TABLE `ndb_apply_status` (
  2.     `server_id`   INT(10) UNSIGNED NOT NULL,
  3.     `epoch`       BIGINT(20) UNSIGNED NOT NULL,
  4.     `log_name`    VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  5.     `start_pos`   BIGINT(20) UNSIGNED NOT NULL,
  6.     `end_pos`     BIGINT(20) UNSIGNED NOT NULL,
  7.     PRIMARY KEY (`server_id`) USING HASH
  8. ) ENGINE=NDBCLUSTER   DEFAULT CHARSET=latin1;

The ndb_apply_status table is populated only on slaves, which means that, on the master, this table never contains any rows; thus, there is no need to allow for DataMemory or IndexMemory to be allotted to ndb_apply_status there.

Because this table is populated from data originating on the master, it should be allowed to replicate; any replication filtering or binary log filtering rules that inadvertently prevent the slave from updating ndb_apply_status or the master 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 ndb_binlog_index and the ndb_apply_status are maintained by the NDB binary log (binlog) 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 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 master. This can be also be accomplished using the SHOW BINLOG EVENTS statement on either the replication master or slave MySQL servers. (See Section 13.7.6.2, “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:

  1. CREATE TABLE ndb_schema (
  2.     `db` VARBINARY(63) NOT NULL,
  3.     `name` VARBINARY(63) NOT NULL,
  4.     `slock` BINARY(32) NOT NULL,
  5.     `query` BLOB NOT NULL,
  6.     `node_id` INT UNSIGNED NOT NULL,
  7.     `epoch` BIGINT UNSIGNED NOT NULL,
  8.     `id` INT UNSIGNED NOT NULL,
  9.     `version` INT UNSIGNED NOT NULL,
  10.     `type` INT UNSIGNED NOT NULL,
  11.     PRIMARY KEY USING HASH (db,name)
  12. ) 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:

  1. mysql> SELECT * FROM mysql.ndb_schema WHERE name='city' \G
  2. *************************** 1. row ***************************
  3.      db: ndbworld
  4.    name: city
  5.   slock:
  6.   query: alter table City engine=ndb
  7. node_id: 4
  8.   epoch: 0
  9.      id: 0
  10. version: 0
  11.    type: 7
  12. 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.

If the ndb_apply_status table or the ndb_schema table does not exist on the slave, ndb_restore re-creates the missing table or tables (Bug #14612).

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 22.6.11, “NDB Cluster Replication Conflict Resolution”.


Rechercher dans le manuel MySQL

Traduction non disponible

Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.

Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-mysql-cluster-replication-schema.html

L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.

Références

  1. Consulter le document html Langue du document :en Manuel MySQL : https://dev.mysql.com/

Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.

Table des matières Haut