Child pages
  • Using Clustrix as a Slave

This is documentation for a previous version of ClustrixDB. Documentation for the latest version can be found here

Skip to end of metadata
Go to start of metadata


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

Creating and Maintaining a Slave Configuration

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.

mysql> CHANGE MASTER TO MASTER_LOG_FILE = 'master_log_name', MASTER_LOG_POS = 1234, MASTER_HOST = 'host_name', MASTER_USER = 'user_name', MASTER_PASSWORD = 'password', MASTER_PORT = 3306;
mysql> START SLAVE;
mysql> STOP SLAVE;
mysql> SHOW SLAVE STATUS;

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:

mysql> CREATE SLAVE '{slave-name}' MASTER_LOG_FILE = '{master_log_name}', MASTER_LOG_POS = {###}[, {MASTER_HOST | MASTER_USER | MASTER_PASSWORD | MASTER_PORT} = {'string' | ###}];


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.

mysql> CREATE SLAVE 'foo' MASTER_LOG_FILE = 'my_master_log', MASTER_LOG_POS = 1234, MASTER_HOST = 'myhost', MASTER_USER = 'myuser', MASTER_PASSWORD = 'swordfish', MASTER_PORT = 3306;
mysql> START SLAVE 'foo';
mysql> STOP SLAVE 'foo';
mysql> SHOW SLAVE 'foo' STATUS;


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

mysql> CHANGE SLAVE 'slave-name' TO {master_option} [, {master_option}]; 

Starting and Stopping a Slave Process

To start a specified Slave, issue the following command:

 mysql> START SLAVE 'slave-name';

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.

mysql> START SLAVE ALL;

To stop a specified Slave, issue the following command:

 mysql> STOP SLAVE 'slave-name';

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

To stop all running Slaves, issue the following command:

mysql> STOP SLAVE ALL;

Displaying the Status of a Slave


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

mysql> SHOW SLAVE STATUS 'slave-name';

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

mysql> SHOW SLAVE STATUS;

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:

mysql> START SLAVE <optional slave name> SKIP N;

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 Clustrix 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.

mysql> SET GLOBAL mysql_default_db_replication_policy = true;
mysql> INSERT INTO mysql_db_replication_policy (dbname, allow) VALUES ('one', true);
mysql> INSERT INTO mysql_db_replication_policy (dbname, allow) VALUES ('two', false);


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

mysql> SET GLOBAL mysql_default_db_replication_policy = false;
mysql> INSERT INTO mysql_db_replication_policy (dbname, allow) VALUES ('one', true);
mysql> INSERT INTO mysql_db_replication_policy (dbname, allow) VALUES ('two', false);
  • 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

Clustrix 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 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:

 mysql> select * from mysql_error_codes natural join error_codes;
+-------------+------------------+---------+-----------------------+--------------------------------------------------+
| result_code | mysql_error_code | family  | code                  | message                                          |
+-------------+------------------+---------+-----------------------+--------------------------------------------------+
|        7168 |             1452 | dml     | DML_FK_INSERT_ERR     | Foreign key constraint violation on insert       | 
|       60417 |             1213 | lockman | LOCKMAN_RC_DEADLOCK   | Lock manager deadlock detected                   | 
|       26645 |             1062 | rigr    | RIGR_RC_DUPLICATE_KEY | Duplicate key in representation                  | 
|       12309 |             1061 | ddl     | DDL_RC_INDEX_CONFLICT | Index name conflict                              | 
|        7172 |             1701 | dml     | DML_FK_TRUNCATE_ERR   | Foreign key contraint on truncate                | 
|       11267 |             1049 | trans   | NO_SUCH_DATABASE      | No such database                                 | 
|       11281 |             1044 | trans   | DB_PERMISSION_DENIED  | Insufficient user permissions to access database | 
|       12307 |             1007 | ddl     | DDL_RC_DB_CONFLICT    | Database name conflict                           | 
|       34816 |             1064 | parser  | ERRCODE_SYNTAX_ERROR  | syntax error                                     | 
|        7171 |             1048 | dml     | DML_NOTNULL_ERR       | NOT NULL constraint violation                    | 
...

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:

mysql> insert into mysql_slave_skip_errors values (1062);
Query OK, 1 row affected (0.02 sec)


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 Clustrix, 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 Clustrix.
  • 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.

 

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."

mysql> INSERT INTO system.mysql_slave_rewrite_db VALUES('slaveA','db1234','otherdb');
Query OK, 1 row affected (0.02 sec)
  • 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:

mysql> DROP SLAVE 'slave-name';

You must stop the Slave before deleting it.



  • No labels