Throughout this section, we use the following abbreviations or symbols for referring to the master and slave clusters, and to processes and commands run on the clusters or cluster nodes:

A replication channel requires two MySQL servers acting as replication servers (one each for the master and slave). For example, this means that in the case of a replication setup with two replication channels (to provide an extra channel for redundancy), there will be a total of four replication nodes, two per cluster.

Replication of a MySQL Cluster as described in this section and those following is dependent on row-based replication. This means that the replication master MySQL server must be started with --binlog-format=ROW, as described in Section 17.6.6, "Starting MySQL Cluster Replication (Single Replication Channel)". For general information about row-based replication, see Section 16.1.2, "Replication Formats".

Each MySQL server used for replication in either cluster must be uniquely identified among all the MySQL replication servers participating in either cluster (you cannot have replication servers on both the master and slave clusters sharing the same ID). This can be done by starting each SQL node using the --server-id=id option, where id is a unique integer. Although it is not strictly necessary, we will assume for purposes of this discussion that all MySQL Cluster binaries are of the same release version.

It is generally true in MySQL Replication that both MySQL servers (mysqld processes) involved must be compatible with one another with respect to both the version of the replication protocol used and the SQL feature sets which they support (see Section 16.4.2, "Replication Compatibility Between MySQL Versions"). It is due to such differences between the binaries in the MySQL Cluster and MySQL Server 5.5 distributions that MySQL Cluster Replication has the additional requirement that both mysqld binaries come from a MySQL Cluster distribution. The simplest and easiest way to assure that the mysqld servers are compatible is to use the same MySQL Cluster distribution for all master and slave mysqld binaries.

We assume that the slave server or cluster is dedicated to replication of the master, and that no other data is being stored on it.

This section discusses known problems or issues when using replication with MySQL Cluster NDB 7.2.

Loss of master-slave connection. A loss of connection can occur either between the replication master SQL node and the replication slave SQL node, or between the replication master SQL node and the data nodes in the master cluster. In the latter case, this can occur not only as a result of loss of physical connection (for example, a broken network cable), but due to the overflow of data node event buffers; if the SQL node is too slow to respond, it may be dropped by the cluster (this is controllable to some degree by adjusting the MaxBufferedEpochs and TimeBetweenEpochs configuration parameters). If this occurs, it is entirely possible for new data to be inserted into the master cluster without being recorded in the replication master's binary log. For this reason, to guarantee high availability, it is extremely important to maintain a backup replication channel, to monitor the primary channel, and to fail over to the secondary replication channel when necessary to keep the slave cluster synchronized with the master. MySQL Cluster is not designed to perform such monitoring on its own; for this, an external application is required.

The replication master issues a "gap" event when connecting or reconnecting to the master cluster. (A gap event is a type of "incident event," which indicates an incident that occurs that affects the contents of the database but that cannot easily be represented as a set of changes. Examples of incidents are server crashes, database resynchronization, (some) software updates, and (some) hardware changes.) When the slave encounters a gap in the replication log, it stops with an error message. This message is available in the output of SHOW SLAVE STATUS, and indicates that the SQL thread has stopped due to an incident registered in the replication stream, and that manual intervention is required. See Section 17.6.8, "Implementing Failover with MySQL Cluster Replication", for more information about what to do in such circumstances.

Circular replication. MySQL Cluster Replication supports circular replication, as shown in the next example. The replication setup involves three MySQL Clusters numbered 1, 2, and 3, in which Cluster 1 acts as the replication master for Cluster 2, Cluster 2 acts as the master for Cluster 3, and Cluster 3 acts as the master for Cluster 1, thus completing the circle. Each MySQL Cluster has two SQL nodes, with SQL nodes A and B belonging to Cluster 1, SQL nodes C and D belonging to Cluster 2, and SQL nodes E and F belonging to Cluster 3.

Circular replication using these clusters is supported as long as the following conditions are met:

This type of circular replication setup is shown in the following diagram:

In this scenario, SQL node A in Cluster 1 replicates to SQL node C in Cluster 2; SQL node C replicates to SQL node E in Cluster 3; SQL node E replicates to SQL node A. In other words, the replication line (indicated by the red arrows in the diagram) directly connects all SQL nodes used as replication masters and slaves.

It should also be possible to set up circular replication in which not all master SQL nodes are also slaves, as shown here:

In this case, different SQL nodes in each cluster are used as replication masters and slaves. However, you must not start any of the SQL nodes using --log-slave-updates. This type of circular replication scheme for MySQL Cluster, in which the line of replication (again indicated by the red arrows in the diagram) is discontinuous, should be possible, but it should be noted that it has not yet been thoroughly tested and must therefore still be considered experimental.

MySQL Cluster replication and primary keys. In the event of a node failure, errors in replication of NDB tables without primary keys can still occur, due to the possibility of duplicate rows being inserted in such cases. For this reason, it is highly recommended that all NDB tables being replicated have primary keys.

MySQL Cluster Replication and Unique Keys. In older versions of MySQL Cluster, operations that updated values of unique key columns of NDB tables could result in duplicate-key errors when replicated. This issue is solved for replication between NDB tables by deferring unique key checks until after all table row updates have been performed.

Deferring constraints in this way is currently supported only by NDB. Thus, updates of unique keys when replicating from NDB to a different storage engine such as MyISAM or InnoDB are still not supported.

The problem encountered when replicating without deferred checking of unique key updates can be illustrated using NDB table such as t, is created and populated on the master (and replicated to a slave that does not support deferred unique key updates) as shown here:

CREATE TABLE t ( p INT PRIMARY KEY, c INT, UNIQUE KEY u (c))   ENGINE NDB;INSERT INTO t VALUES (1,1), (2,2), (3,3), (4,4), (5,5);

The following UPDATE statement on t succeeded on the master, since the rows affected are processed in the order determined by the ORDER BY option, performed over the entire table:

UPDATE t SET c = c - 1 ORDER BY p;

However, the same statement failed with a duplicate key error or other constraint violation on the slave, because the ordering of the row updates was done for one partition at a time, rather than for the table as a whole.

Restarting with --initial. Restarting the cluster with the --initial option causes the sequence of GCI and epoch numbers to start over from 0. (This is generally true of MySQL Cluster and not limited to replication scenarios involving Cluster.) The MySQL servers involved in replication should in this case be restarted. After this, you should use the RESET MASTER and RESET SLAVE statements to clear the invalid ndb_binlog_index and ndb_apply_status tables, respectively.

Replication from NDB to other storage engines. It is possible to replicate an NDB table on the master to a table using a different storage engine on the slave, taking into account the restrictions listed here:

The next few paragraphs provide additional information about each of the issues just described.

Multiple masters not supported when replicating NDB to other storage engines. For replication from NDB to a different storage engine, the relationship between the two databases must be a simple master-slave one. This means that circular or master-master replication is not supported between MySQL Cluster and other storage engines.

In addition, it is not possible to configure more than one replication channel when replicating between NDB and a different storage engine. (However, a MySQL Cluster database can simultaneously replicate to multiple slave MySQL Cluster databases.) If the master uses NDB tables, it is still possible to have more than one MySQL Server maintain a binary log of all changes; however, for the slave to change masters (fail over), the new master-slave relationship must be explicitly defined on the slave.

Replicating NDB to a slave storage engine that does not perform binary logging. If you attempt to replicate from a MySQL Cluster to a slave that uses a storage engine that does not handle its own binary logging, the replication process aborts with the error Binary logging not possible ... Statement cannot be written atomically since more than one engine involved and at least one engine is self-logging (Error 1595). It is possible to work around this issue in one of the following ways:

Replication from NDB to a nontransactional storage engine. When replicating from NDB to a nontransactional storage engine such as MyISAM, you may encounter unnecessary duplicate key errors when replicating INSERT ... ON DUPLICATE KEY UPDATE statements. You can suppress these by using --ndb-log-update-as-write=0, which forces updates to be logged as writes (rather than as updates).

In addition, when replicating from NDB to a storage engine that does not implement transactions, if the slave fails to apply any row changes from a given transaction, it does not roll back the rest of the transaction. (This is true when replicating tables using any transactional storage engine-not only NDB-to a nontransactional storage engine.) Because of this, it cannot be guaranteed that transactional consistency will be maintained on the slave in such cases.

Replication and binary log filtering rules with replication between MySQL Clusters. If you are using any of the options --replicate-do-*, --replicate-ignore-*, --binlog-do-db, or --binlog-ignore-db to filter databases or tables being replicated, care must be taken not to block replication or binary logging of the mysql.ndb_apply_status, which is required for replication between MySQL Clusters to operate properly. In particular, you must keep in mind the following:

You should also remember that each replication rule requires the following:

If you are replicating a MySQL Cluster to a slave that uses a storage engine other than NDB, the considerations just given previously may not apply, as discussed elsewhere in this section.

MySQL Cluster Replication and IPv6. Currently, the NDB API and MGM API do not support IPv6. However, MySQL Servers-including those acting as SQL nodes in a MySQL Cluster-can use IPv6 to contact other MySQL Servers. This means that you can replicate between MySQL Clusters using IPv6 to connect the master and slave SQL nodes as shown by the dotted arrow in the following diagram:

However, all connections originating within the MySQL Cluster-represented in the preceding diagram by solid arrows-must use IPv4. In other words, all MySQL Cluster data nodes, management servers, and management clients must be accessible from one another using IPv4. In addition, SQL nodes must use IPv4 to communicate with the cluster.

Since there is currently no support in the NDB and MGM APIs for IPv6, any applications written using these APIs must also make all connections using IPv4.

Attribute promotion and demotion. MySQL Cluster Replication includes support for attribute promotion and demotion. The implementation of the latter distinguishes between lossy and non-lossy type conversions, and their use on the slave can be controlled by setting the slave_type_conversions global server system variable.

For more information about attribute promotion and demotion in MySQL Cluster, see Row-based replication: attribute promotion and demotion.

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 binary log 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` 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=MyISAM DEFAULT CHARSET=latin1;

The next_position and next_file columns were added in MySQL Cluster NDB 7.2.6; see Section 17.6.8, "Implementing Failover with MySQL Cluster Replication", for more information about using these columns.

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.

The following figure shows the relationship of the MySQL Cluster replication master server, its binlog injector thread, and the mysql.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 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;

This table is populated only on slaves; on the master, no DataMemory is allocated to it. Since 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 MySQL 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 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 13.7.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 2id type state logging database schema   name4 UserTable Online   Yes mysql def  ndb_apply_status5 UserTable Online   Yes ndbworld def  City6 UserTable Online   Yes ndbworld def  Country3 UserTable Online   Yes mysql def  NDB$BLOB_2_37 UserTable Online   Yes ndbworld def  CountryLanguage2 UserTable Online   Yes mysql def  ndb_schemaNDBT_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=ndbnode_id: 4  epoch: 0 id: 0version: 0   type: 71 row in set (0.00 sec)

This can sometimes be useful when debugging applications.

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 MySQL 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 17.6.11, "MySQL Cluster Replication Conflict Resolution".

Preparing the MySQL Cluster for replication consists of the following steps:

This section outlines the procedure for starting MySQL Cluster replication using a single replication channel.

It is also possible to use two replication channels, in a manner similar to the procedure described in the next section; the differences between this and using a single replication channel are covered in Section 17.6.7, "Using Two Replication Channels for MySQL Cluster Replication".

It is also possible to improve cluster replication performance by enabling batched updates. This can be accomplished by setting the slave_allow_batching system variable on the slave mysqld processes. Normally, updates are applied as soon as they are received. However, the use of batching causes updates to be applied in 32 KB batches, which can result in higher throughput and less CPU usage, particularly where individual updates are relatively small.

Batching can be turned on and off at runtime. To activate it at runtime, you can use either of these two statements:

SET GLOBAL slave_allow_batching = 1;SET GLOBAL slave_allow_batching = ON;

If a particular batch causes problems (such as a statement whose effects do not appear to be replicated correctly), slave batching can be deactivated using either of the following statements:

SET GLOBAL slave_allow_batching = 0;SET GLOBAL slave_allow_batching = OFF;

You can check whether slave batching is currently being used by means of an appropriate SHOW VARIABLES statement, like this one:

mysql> SHOW VARIABLES LIKE 'slave%';+---------------------------+-------+| Variable_name | Value |+---------------------------+-------+| slave_allow_batching  | ON || slave_compressed_protocol | OFF   || slave_load_tmpdir | /tmp  || slave_net_timeout | 3600  || slave_skip_errors | OFF   || slave_transaction_retries | 10 |+---------------------------+-------+6 rows in set (0.00 sec)

In a more complete example scenario, we envision two replication channels to provide redundancy and thereby guard against possible failure of a single replication channel. This requires a total of four replication servers, two masters for the master cluster and two slave servers for the slave cluster. For purposes of the discussion that follows, we assume that unique identifiers are assigned as shown here:

Setting up replication with two channels is not radically different from setting up a single replication channel. First, the mysqld processes for the primary and secondary replication masters must be started, followed by those for the primary and secondary slaves. Then the replication processes may be initiated by issuing the START SLAVE statement on each of the slaves. The commands and the order in which they need to be issued are shown here:

As mentioned previously, it is not necessary to enable binary logging on replication slaves.

In the event that the primary Cluster replication process fails, it is possible to switch over to the secondary replication channel. The following procedure describes the steps required to accomplish this.

Once the secondary replication channel is active, you can investigate the failure of the primary and effect repairs. The precise actions required to do this will depend upon the reasons for which the primary channel failed.

If the failure is limited to a single server, it should (in theory) be possible to replicate from M to S', or from M' to S; however, this has not yet been tested.

This section discusses making backups and restoring from them using MySQL Cluster replication. We assume that the replication servers have already been configured as covered previously (see Section 17.6.5, "Preparing the MySQL Cluster for Replication", and the sections immediately following). This having been done, the procedure for making a backup and then restoring from it is as follows:

To perform a backup and restore on a second replication channel, it is necessary only to repeat these steps, substituting the host names and IDs of the secondary master and slave for those of the primary master and slave replication servers where appropriate, and running the preceding statements on them.

For additional information on performing Cluster backups and restoring Cluster from backups, see Section 17.5.3, "Online Backup of MySQL Cluster".

#!/user/bin/perl -w#  file: reset-slave.pl#  Copyright � MySQL AB#  This program is free software; you can redistribute it and/or modify#  it under the terms of the GNU General Public License as published by#  the Free Software Foundation; either version 2 of the License, or#  (at your option) any later version.#  This program is distributed in the hope that it will be useful,#  but WITHOUT ANY WARRANTY; without even the implied warranty of#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the#  GNU General Public License for more details.#  You should have received a copy of the GNU General Public License#  along with this program; if not, write to:#  Free Software Foundation, Inc.#  59 Temple Place, Suite 330#  Boston, MA 02111-1307 USA##  Version 1.1######################## Includes ###############################use DBI;######################## Globals ################################my  $m_host='';my  $m_port='';my  $m_user='';my  $m_pass='';my  $s_host='';my  $s_port='';my  $s_user='';my  $s_pass='';my  $dbhM='';my  $dbhS='';####################### Sub Prototypes ##########################sub CollectCommandPromptInfo;sub ConnectToDatabases;sub DisconnectFromDatabases;sub GetSlaveEpoch;sub GetMasterInfo;sub UpdateSlave;######################## Program Main ###########################CollectCommandPromptInfo;ConnectToDatabases;GetSlaveEpoch;GetMasterInfo;UpdateSlave;DisconnectFromDatabases;################## Collect Command Prompt Info ##################sub CollectCommandPromptInfo{  ### Check that user has supplied correct number of command line args  die "Usage:\n   reset-slave >master MySQL host< >master MySQL port< \n   >master user< >master pass< >slave MySQL host< \n   >slave MySQL port< >slave user< >slave pass< \n   All 8 arguments must be passed. Use BLANK for NULL passwords\n"   unless @ARGV == 8;  $m_host  =  $ARGV[0];  $m_port  =  $ARGV[1];  $m_user  =  $ARGV[2];  $m_pass  =  $ARGV[3];  $s_host  =  $ARGV[4];  $s_port  =  $ARGV[5];  $s_user  =  $ARGV[6];  $s_pass  =  $ARGV[7];  if ($m_pass eq "BLANK") { $m_pass = '';}  if ($s_pass eq "BLANK") { $s_pass = '';}}###############  Make connections to both databases #############sub ConnectToDatabases{  ### Connect to both master and slave cluster databases  ### Connect to master  $dbhM = DBI->connect( "dbi:mysql:database=mysql;host=$m_host;port=$m_port", "$m_user", "$m_pass")  or die "Can't connect to Master Cluster MySQL process!  Error: $DBI::errstr\n";  ### Connect to slave  $dbhS = DBI->connect(  "dbi:mysql:database=mysql;host=$s_host",  "$s_user", "$s_pass") or die "Can't connect to Slave Cluster MySQL process! Error: $DBI::errstr\n";}################  Disconnect from both databases ################sub DisconnectFromDatabases{  ### Disconnect from master  $dbhM->disconnect  or warn " Disconnection failed: $DBI::errstr\n";  ### Disconnect from slave  $dbhS->disconnect  or warn " Disconnection failed: $DBI::errstr\n";}######################  Find the last good GCI ##################sub GetSlaveEpoch{  $sth = $dbhS->prepare("SELECT MAX(epoch) FROM mysql.ndb_apply_status;")  or die "Error while preparing to select epoch from slave: ", $dbhS->errstr;  $sth->execute  or die "Selecting epoch from slave error: ", $sth->errstr;  $sth->bind_col (1, \$epoch);  $sth->fetch;  print "\tSlave Epoch =  $epoch\n";  $sth->finish;}#######  Find the position of the last GCI in the binary log ########sub GetMasterInfo{  $sth = $dbhM->prepare("SELECT   SUBSTRING_INDEX(File, '/', -1), Position FROM mysql.ndb_binlog_index WHERE epoch > $epoch ORDER BY epoch ASC LIMIT 1;")  or die "Prepare to select from master error: ", $dbhM->errstr;  $sth->execute  or die "Selecting from master error: ", $sth->errstr;  $sth->bind_col (1, \$binlog);  $sth->bind_col (2, \$binpos);  $sth->fetch;  print "\tMaster binary log =  $binlog\n";  print "\tMaster binary log position =  $binpos\n";  $sth->finish;}##########  Set the slave to process from that location #########sub UpdateSlave{  $sth = $dbhS->prepare("CHANGE MASTER TO MASTER_LOG_FILE='$binlog', MASTER_LOG_POS=$binpos;")  or die "Prepare to CHANGE MASTER error: ", $dbhS->errstr;  $sth->execute   or die "CHANGE MASTER on slave error: ", $sth->errstr;  $sth->finish;  print "\tSlave has been updated. You may now start the slave.\n";}# end reset-slave.pl

It is possible to use MySQL Cluster in multi-master replication, including circular replication between a number of MySQL Clusters.

Circular replication example. In the next few paragraphs we consider the example of a replication setup involving three MySQL Clusters numbered 1, 2, and 3, in which Cluster 1 acts as the replication master for Cluster 2, Cluster 2 acts as the master for Cluster 3, and Cluster 3 acts as the master for Cluster 1. Each cluster has two SQL nodes, with SQL nodes A and B belonging to Cluster 1, SQL nodes C and D belonging to Cluster 2, and SQL nodes E and F belonging to Cluster 3.

Circular replication using these clusters is supported as long as the following conditions are met:

This type of circular replication setup is shown in the following diagram:

In this scenario, SQL node A in Cluster 1 replicates to SQL node C in Cluster 2; SQL node C replicates to SQL node E in Cluster 3; SQL node E replicates to SQL node A. In other words, the replication line (indicated by the red arrows in the diagram) directly connects all SQL nodes used as replication masters and slaves.

It is also possible to set up circular replication in such a way that not all master SQL nodes are also slaves, as shown here:

In this case, different SQL nodes in each cluster are used as replication masters and slaves. However, you must not start any of the SQL nodes using --log-slave-updates. This type of circular replication scheme for MySQL Cluster, in which the line of replication (again indicated by the red arrows in the diagram) is discontinuous, should be possible, but it should be noted that it has not yet been thoroughly tested and must therefore still be considered experimental.

mysql> SET GLOBAL SLAVE_EXEC_MODE = 'IDEMPOTENT';

Using NDB-native backup and restore to initialize a slave MySQL Cluster. When setting up circular replication, it is possible to initialize the slave cluster by using the management client BACKUP command on one MySQL Cluster to create a backup and then applying this backup on another MySQL Cluster using ndb_restore. However, this does not automatically create binary logs on the second MySQL Cluster's SQL node acting as the replication slave. In order to cause the binary logs to be created, you must issue a SHOW TABLES statement on that SQL node; this should be done prior to running START SLAVE.

This is a known issue which we intend to address in a future release.

Multi-master failover example. In this section, we discuss failover in a multi-master MySQL Cluster replication setup with three MySQL Clusters having server IDs 1, 2, and 3. In this scenario, Cluster 1 replicates to Clusters 2 and 3; Cluster 2 also replicates to Cluster 3. This relationship is shown here:

In other words, data replicates from Cluster 1 to Cluster 3 through 2 different routes: directly, and by way of Cluster 2.

Not all MySQL servers taking part in multi-master replication must act as both master and slave, and a given MySQL Cluster might use different SQL nodes for different replication channels. Such a case is shown here:

MySQL servers acting as replication slaves must be run with the --log-slave-updates option. Which mysqld processes require this option is also shown in the preceding diagram.

The need for failover arises when one of the replicating clusters goes down. In this example, we consider the case where Cluster 1 is lost to service, and so Cluster 3 loses 2 sources of updates from Cluster 1. Because replication between MySQL Clusters is asynchronous, there is no guarantee that Cluster 3's updates originating directly from Cluster 1 are more recent than those received through Cluster 2. You can handle this by ensuring that Cluster 3 catches up to Cluster 2 with regard to updates from Cluster 1. In terms of MySQL servers, this means that you need to replicate any outstanding updates from MySQL server C to server F.

On server C, perform the following queries:

mysqlC> SELECT @latest:=MAX(epoch) -> FROM mysql.ndb_apply_status -> WHERE server_id=1;mysqlC> SELECT -> @file:=SUBSTRING_INDEX(File, '/', -1), -> @pos:=Position -> FROM mysql.ndb_binlog_index -> WHERE orig_epoch >= @latest -> AND orig_server_id = 1 -> ORDER BY epoch ASC LIMIT 1;

Copy over the values for @file and @pos manually from server C to server F (or have your application perform the equivalent). Then, on server F, execute the following CHANGE MASTER TO statement:

mysqlF> CHANGE MASTER TO -> MASTER_HOST = 'serverC' -> MASTER_LOG_FILE='@file', -> MASTER_LOG_POS=@pos;

Once this has been done, you can issue a START SLAVE statement on MySQL server F, and any missing updates originating from server B will be replicated to server F.

The CHANGE MASTER TO statement also supports an IGNORE_SERVER_IDS option which takes a comma-separated list of server IDs and causes events originating from the corresponding servers to be ignored. For more information, see Section 13.4.2.1, "CHANGE MASTER TO Syntax", and Section 13.7.5.35, "SHOW SLAVE STATUS Syntax".

When using a replication setup involving multiple masters (including circular replication), it is possible that different masters may try to update the same row on the slave with different data. Conflict resolution in MySQL Cluster Replication provides a means of resolving such conflicts by permitting a user-defined resolution column to be used to determine whether or not an update to the row on a given master should be applied on the slave.

Some types of conflict resolution supported by MySQL Cluster (NDB$OLD(), NDB$MAX(), NDB$MAX_DELETE_WIN()) implement this user-defined column as a "timestamp" column (although its type cannot be TIMESTAMP, as explained later in this section); epoch-based conflict resolution functions introduced in MySQL Cluster NDB 7.2.1 (NDB$EPOCH() and NDB$EPOCH_TRANS()) compare the order in which epochs are replicated. Different methods can be used to compare resolution column values on the slave when conflicts occur, as explained later in this section; the method used can be set on a per-table basis.

Requirements. Preparations for conflict resolution must be made on both the master and the slave. These tasks are described in the following list:

When using the functions NDB$OLD(), NDB$MAX(), and NDB$MAX_DELETE_WIN() for timestamp-based conflict resolution, we often refer to the column used for determining updates as a "timestamp" column. However, the data type of this column is never TIMESTAMP; instead, its data type should be INT (INTEGER) or BIGINT. The "timestamp" column should also be UNSIGNED and NOT NULL.

The NDB$EPOCH() and NDB$EPOCH_TRANS() functions discussed later in this section work by comparing the relative order of replication epochs applied on a primary and secondary MySQL Cluster, and do not make use of timestamps.

Master column control. We can see update operations in terms of "before" and "after" images-that is, the states of the table before and after the update is applied. Normally, when updating a table with a primary key, the "before" image is not of great interest; however, when we need to determine on a per-update basis whether or not to use the updated values on a replication slave, we need to make sure that both images are written to the master's binary log. This is done with the --ndb-log-update-as-write option for mysqld, as described later in this section.

Logging Full or Partial Rows (--ndb-log-updated-only Option)

For purposes of conflict resolution, there are two basic methods of logging rows, as determined by the setting of the --ndb-log-updated-only option for mysqld:

It is usually sufficient-and more efficient-to log updated columns only; however, if you need to log full rows, you can do so by setting --ndb-log-updated-only to 0 or OFF.

--ndb-log-update-as-write Option: Logging Changed Data as Updates

The setting of the MySQL Server's --ndb-log-update-as-write option determines whether logging is performed with or without the "before" image. Because conflict resolution is done in the MySQL Server's update handler, it is necessary to control logging on the master such that updates are updates and not writes; that is, such that updates are treated as changes in existing rows rather than the writing of new rows (even though these replace existing rows). This option is turned on by default; in other words, updates are treated as writes. (That is, updates are by default written as write_row events in the binary log, rather than as update_row events.) To turn off the option, start the master mysqld with --ndb-log-update-as-write=0 or --ndb-log-update-as-write=OFF.

Conflict resolution control. Conflict resolution is usually enabled on the server where conflicts can occur. Like logging method selection, it is enabled by entries in the mysql.ndb_replication table.

The ndb_replication system table. To enable conflict resolution, it is necessary to create an ndb_replication table in the mysql system database on the master, the slave, or both, depending on the conflict resolution type and method to be employed. This table is used to control logging and conflict resolution functions on a per-table basis, and has one row per table involved in replication. ndb_replication is created and filled with control information on the server where the conflict is to be resolved. In a simple master-slave setup where data can also be changed locally on the slave this will typically be the slave. In a more complex master-master (2-way) replication schema this will usually be all of the masters involved. Each row in mysql.ndb_replication corresponds to a table being replicated, and specifies how to log and resolve conflicts (that is, which conflict resolution function, if any, to use) for that table. The definition of the mysql.ndb_replication table is 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=NDBPARTITION BY KEY(db,table_name);

The columns in this table are described in the next few paragraphs.

db. The name of the database containing the table to be replicated.

table_name. The name of the table to be replicated.

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

binlog_type. The type of binary logging to be employed. This is determined as shown in the following table:

conflict_fn. The conflict resolution function to be applied. This function must be specified as one of those shown in the following list:

These functions are described in the next few paragraphs.

NDB$OLD(column_name). If the value of column_name is the same on both the master and the slave, then the update is applied; otherwise, the update is not applied on the slave and an exception is written to the log. This is illustrated by the following pseudocode:

if (master_old_column_value == slave_current_column_value)  apply_update();else  log_exception();

This function can be used for "same value wins" conflict resolution. This type of conflict resolution ensures that updates are not applied on the slave from the wrong master.

NDB$MAX(column_name). If the "timestamp" column value for a given row coming from the master is higher than that on the slave, it is applied; otherwise it is not applied on the slave. This is illustrated by the following pseudocode:

if (master_new_column_value > slave_current_column_value)  apply_update();

This function can be used for "greatest timestamp wins" conflict resolution. This type of conflict resolution ensures that, in the event of a conflict, the version of the row that was most recently updated is the version that persists.

NDB$MAX_DELETE_WIN(column_name). This is a variation on NDB$MAX(). Due to the fact that no timestamp is available for a delete operation, a delete using NDB$MAX() is in fact processed as NDB$OLD. However, for some use cases, this is not optimal. For NDB$MAX_DELETE_WIN(), if the "timestamp" column value for a given row adding or updating an existing row coming from the master is higher than that on the slave, it is applied. However, delete operations are treated as always having the higher value. This is illustrated in the following pseudocode:

if ( (master_new_column_value > slave_current_column_value) ||  operation.type == "delete")  apply_update();

This function can be used for "greatest timestamp, delete wins" conflict resolution. This type of conflict resolution ensures that, in the event of a conflict, the version of the row that was deleted or (otherwise) most recently updated is the version that persists.

NDB$EPOCH(). The NDB$EPOCH() function, available beginning with MySQL Cluster NDB 7.2.1, tracks the order in which replicated epochs are applied on a slave MySQL Cluster relative to changes originating on the slave. This relative ordering is used to determine whether changes originating on the slave are concurrent with any changes that originate locally, and are therefore potentially in conflict.

Most of what follows in the description of NDB$EPOCH() also applies to NDB$EPOCH_TRANS(). Any exceptions are noted in the text.

NDB$EPOCH() is asymmetric, operating on one MySQL Cluster in a two-cluster circular replication configuration (sometimes referred to as "active-active" replication). We refer here to cluster on which it operates as the primary, and the other as the secondary. The slave on the primary is responsible for detecting and handling conflicts, while the slave on the secondaryis not involved in any conflict detection or handling.

When the slave on the primary detects conflicts, it injects events into its own binary log to compensate for these; this ensures that the secondary MySQL Cluster eventually realigns itself with the primary and so keeps the primary and secondary from diverging. This compensation and realignment mechanism requires that the primary MySQL Cluster always wins any conflicts with the secondary-that is, that the primary's changes are always used rather than those from the secondary in event of a conflict. This "primary always wins" rule has the following implications:

NDB$EPOCH() and NDB$EPOCH_TRANS() do not require any user schema modifications, or application changes to provide conflict detection. However, careful thought must be given to the schema used, and the access patterns used, to verify that the complete system behaves within specified limits.

Each of the NDB$EPOCH() and NDB$EPOCH_TRANS() functions can take an optional parameter; this is the number of bits to use to represent the lower 32 bits of the epoch, and should be set to no less than

CEIL( LOG2( TimeBetweenGlobalCheckpoints / TimeBetweenEpochs ), 1) 

For the default values of these configuration parameters (2000 and 100 milliseconds, respectively), this gives a value of 5 bits, so the default value (6) should be sufficient, unless other values are used for TimeBetweenGlobalCheckpoints, TimeBetweenEpochs, or both. A value that is too small can result in false positives, while one that is too large could lead to excessive wasted space in the database.

Both NDB$EPOCH() and NDB$EPOCH_TRANS() insert entries for conflicting rows into the relevant exceptions tables, provided that these tables have been defined according to the same exception table schema rules as described elsewhere in this section (see NDB$OLD(column_name)). Note that you need to create any exception table before creating the table with which it is to be used.

As with the other conflict detection functions discussed in this section, NDB$EPOCH() and NDB$EPOCH_TRANS() are activated by including relevant entries in the mysql.ndb_replication table (see The ndb_replication system table). The roles of the primary and secondary MySQL Clusters in this scenario are fully determined by mysql.ndb_replication table entries.

NDB$EPOCH() and NDB$EPOCH_TRANS() status variables. MySQL Cluster NDB 7.2.1 introduces several status variables that can be used to monitor NDB$EPOCH() and NDB$EPOCH_TRANS() conflict detection. You can see how many rows have been found in conflict by NDB$EPOCH() since this slave was last restarted from the current value of the Ndb_conflict_fn_epoch system status variable.

Ndb_conflict_fn_epoch_trans provides the number of rows that have been found directly in conflict by NDB$EPOCH_TRANS(); the number of rows actually realigned, including those affected due to their membership in or dependency on the same transactions as other conflicting rows, is given by Ndb_conflict_trans_row_reject_count.

For more information, see Section 17.3.4.4, "MySQL Cluster Status Variables".

Limitations on NDB$EPOCH(). The following limitations currently apply when using NDB$EPOCH() to perform conflict detection:

NDB$EPOCH_TRANS(). NDB$EPOCH_TRANS() extends the NDB$EPOCH() function, and, like NDB$EPOCH(), is available beginning with MySQL Cluster NDB 7.2.1. Conflicts are detected and handled in the same way using the "primary wins all" rule (see NDB$EPOCH()) but with the extra condition that any other rows updated in the same transaction in which the conflict occurred are also regarded as being in conflict. In other words, where NDB$EPOCH() realigns individual conflicting rows on the secondary, NDB$EPOCH_TRANS() realigns conflicting transactions.

In addition, any transactions which are detectably dependent on a conflicting transaction are also regarded as being in conflict, these dependencies being determined by the contents of the secondary cluster's binary log. Since the binary log contains only data modification operations (inserts, updates, and deletes), only overlapping data modifications are used to determine dependencies between transactions.

NDB$EPOCH_TRANS() is subject to the same conditions and limitations as NDB$EPOCH(), and in addition requires that Version 2 binary log row events are used (--log-bin-use-v1-row-events equal to 0), which adds a storage overhead of 2 bytes per event in the binary log. In addition, all transaction IDs must be recorded in the secondary's binary log (--ndb-log-transaction-id option), which adds a further variable overhead (up to 13 bytes per row).

See NDB$EPOCH().

NULL. Indicates that conflict resolution is not to be used for the corresponding table.

Status information. A server status variable Ndb_conflict_fn_max provides a count of the number of times that a row was not applied on the current SQL node due to "greatest timestamp wins" conflict resolution since the last time that mysqld was started.

The number of times that a row was not applied as the result of "same timestamp wins" conflict resolution on a given mysqld since the last time it was restarted is given by the global status variable Ndb_conflict_fn_old. In addition to incrementing Ndb_conflict_fn_old, the primary key of the row that was not used is inserted into an exceptions table, as explained later in this section.

Exceptions table. To use the NDB$OLD() conflict resolution function, it is also necessary to create an exceptions table corresponding to each NDB table for which this type of conflict resolution is to be employed. This is also true when using NDB$EPOCH() or NDB$EPOCH_TRANS() in MySQL Cluster NDB 7.2.1 and later. The name of this table is that of the table for which conflict resolution is to be applied, with the string $EX appended. (For example, if the name of the original table is mytable, the name of the corresponding exception table name should be mytable$EX.) This table is created as follows:

CREATE TABLE original_table$EX  ( server_id INT UNSIGNED, master_server_id INT UNSIGNED, master_epoch BIGINT UNSIGNED, count INT UNSIGNED, original_table_pk_columns, [additional_columns,] PRIMARY KEY(server_id, master_server_id, master_epoch, count)) ENGINE=NDB;

The first four columns are required. Following these columns, the columns making up the original table's primary key should be copied in the order in which they are used to define the primary key of the original table.

Additional columns may optionally be defined following these columns, but not before any of them; any such extra columns cannot be NOT NULL. The exception table's primary key must be defined as shown. The exception table must use the NDB storage engine. An example that uses NDB$OLD() with an exceptions table is shown later in this section.

Examples. The following examples assume that you have already a working MySQL Cluster replication setup, as described in Section 17.6.5, "Preparing the MySQL Cluster for Replication", and Section 17.6.6, "Starting MySQL Cluster Replication (Single Replication Channel)".