Skip to end of metadata
Go to start of metadata


The following topics describe configuring ClustrixDB as a Slave to a MySQL Master.

Creating and Maintaining a Slave Configuration

ClustrixDB supports multiple Slave processes, each with its own configuration. Because this functionality exceeds that provided by MySQL, ClustrixDB extends replication syntax to support a named Slave configuration. Each replication Slave operates independently. If one Slave encounters an error and stops, other Slaves are unaffected. Ensure that Slaves do not update the database in a conflicting manner. To determine where replication for a specified slave is running, query the nodeid column in the system.mysql_Slave_stats table.

For compatibility with MySQL, the standard syntax operates on a Slave named "default."

For example, operating on the "default" Slave, you can issue the following commands. Use the logon information specifically established for the replication process when specifying MASTER_USER and MASTER_PASSWORD.

Icon

Note the use of the CHANGE MASTER command in the preceding example. This command always refers to the default Slave and is included for compatibility with MySQL. CHANGE MASTER is an alias for CHANGE SLAVE 'default' .


To define named Slaves for multiple Slave instances, use the following syntax. The logon information for MASTER_USER and MASTER_PASSWORD should be that of the established replication user.


The following code creates a new Slave configuration entry with the specified Master settings. You use the specified Slave-name in commands that you issue to control or monitor this Slave instance.


To update the configuration of the Slave, specifying settings such as host name, port, log sequence, and log filename, issue the following command:

Starting and Stopping a Slave Process

To start a specified Slave, issue the following command:

If the Slave is already running, this command has no effect. To start any Slaves that are configured on this cluster and not already running, issue the following command. To load-balance replication traffic, Slaves are started in a round-robin fashion across the nodes in the cluster.

To stop a specified Slave, issue the following command:

If the Slave is not running, this command has no effect.

To stop all running Slaves, issue the following command:

Alternatively, ClustrixDB also supports the legacy MySQL Syntax for SLAVE START and SLAVE STOP.

Displaying the Status of a Slave


To display the status of a specified Slave, issue the following command.

To display the status of all Slaves, issue the following command.

Skipping Statements for a Stopped Slave

To skip one or more pending replication statements (for example, when dealing with a bad query), issue the following command:

where N is the number of statements to skip. The Slave skips the specified number of statements and attempts to execute the following statements. If statement N+1 fails for any reason, the Slave remains in the state that it was in before the SKIP command was issued. For example, if there are three consecutive failing queries in a row, SKIP 1 or SKIP 2 has no effect on the Slave position, but SKIP 3 enables replication to resume.

Specifying Ignored and Included Databases

The ClustrixDB replication Slave supports two modes:

  • Replicate everything, skipping specified databases (default)
  • Replicate only specified databases, skipping everything else

The following examples, using databases named "one", "two" and "three", illustrate the modes. In the following example, statements for database "one" and "three" are executed. Statements for database "two" are skipped.


In the following example, statements for database "one" are executed. Statements for databases "two" and "three" are skipped.

Icon
  • These "ignore" and "include" directives affect named Slave instances globally.
  • Only the current database of a statement determines whether it is executed or skipped as part of this policy.
  • Rows where the allow column matches the policy variable are treated as if they are not present (that is, according to the policy).
  • Stop the Slave manually before changing policies. This configuration is read when the Slave starts (and periodically when its buffer fills).

Controlling Slave Behavior on Errors

ClustrixDB offers several ways to control slave behavior on errors:

  • Inserting error codes into system.mysql_slave_skip_errors
  • Setting the slave_exec_mode global variable
  • Setting the rbr_loose_fks global variable
Inserting error codes into system.mysql_slave_skip_errors

You can configure the slave not stop on certain errors by inserting the desired error code into the table system.mysql_slave_skip_errors. This applies to all slaves. There is no per-slave configurability.
Note: Insert the mysql_error_code, NOT the result_code into mysql_slave_skip_errors.


To obtain a list of available error codes, run the following query:

Icon

 The only errors supported for Row-Based_Replication (RBR) are 1451 and 1452 (Cannot delete or update parent row, Cannot add or update child row, respectively).

To insert an error code into system.mysql_slave_skip_errors, stop the slaves and then run a query such as the following that inserts error code 1062 into the table:


Logging Skipped Errors


You can set global variable mysql_slave_log_skipped_statements to toggle logging the skipped statements to sprout.log.

Setting the slave_exec_mode global variable

MySQL row-based replication specifies the expected (pre-update) contents of each row to be updated or deleted. In case the row specified does not exactly match the row present on ClustrixDB, there are three modes of operation, determined by the setting of the global variable slave_exec_mode:

  • STRICT: In this mode, as long as the PRIMARY KEY or first UNIQUE key of the row matches, the update/delete is applied. This is the default setting, and also matches MySQL's default behavior.
  • EXACT: In this mode, if the row specified does not exactly match (for all columns) a row present in the table, the slave will error (with "Row Not Found"), and stop. Note that this mode is unique to ClustrixDB.
  • IDEMPOTENT: In this mode, "duplicate key" and "row not found" errors are ignored, meaning that operations resulting in such errors are not applied, but the slave is not stopped. This mode is only appropriate for multi-master configurations, and should be used with caution.

 

Icon

IDEMPOTENT mode is similar to inserting error codes 1032 and 1062 into system.mysql_slave_skip_errors, but the system.mysql_slave_skip_errors approach applies until hitting an error and then stops, whereas IDEMPOTENT mode will not stop. 

The setting takes effect immediately.

Setting the rbr_loose_fks global variable

To mitigate possible performance issues with Foreign Keys (FKs) and RBR, you can set global variable rbr_loose_fks to true to configure the slave to ignore RESTRICT FKs, but still apply any CASCADE actions (update/delete). The idea here is that if the master and slave are consistent, the master should reject any writes that would cause a conflict, and so the slave need not perform its own FK checking. CASCADEs are different, because they do not result in errors, rather additional operations beyond that directly requested (i.e. updating/deleting child/parent rows).

Replicating Identically-Named Databases

To replicate identically-named databases from different Masters, you can create rewrite rules that map one database name to another. To remap database names, add a rule of the form (Slave_name, from_db, to_db) to the table system.mysql_Slave_rewrite_db. The following example remaps "db1234" to "otherdb."

Icon
  • You can define only one remapping for any Slave/database combination.
  • You must stop the Slave before creating or modifying a rule for it.
  • You cannot perform cross-database updates.

Deleting a Slave Configuration

To delete a previously-configured Slave, issue the following command:

Icon

You must stop the Slave before deleting it.

Replicating from MySQL 5.7

ClustrixDB does not provide support for MySQL GTIDs and ignores GTID info it encounters in binlogs. 

When replicating from MySQL 5.7, the gtid and server_uuid variables will be ignored when encountered in the binlog.

Errors in the  clustrix.log relative to server_uuid may be ignored. 

Additional information is available at MySQL 5.7 Replication and GTIDs.

 

  • No labels