The following topics describe configuring Clustrix as a Slave to a MySQL Master.
Clustrix supports multiple Slave processes, each with its own configuration. Because this functionality exceeds that provided by MySQL, Clustrix 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.
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 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:
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:
To display the status of a specified Slave, issue the following command.
To display the status of all Slaves, issue the following command.
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.
The Clustrix replication Slave supports two modes:
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.
Clustrix offers several ways to control slave behavior on 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 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:
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.
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 Clustrix, there are three modes of operation, determined by the setting of the global variable slave_exec_mode:
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.
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).
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."
To delete a previously-configured Slave, issue the following command:
You must stop the Slave before deleting it.