The following topics describe configuring ClustrixDB as a Slave to a MySQL Master.
- Creating and Maintaining a Slave Configuration
- Starting and Stopping a Slave Process
- Displaying the Status of a Slave
- Skipping Statements for a Stopped Slave
- Specifying Ignored and Included Databases
- Controlling Slave Behavior on Errors
- Replicating Identically-Named Databases
- Deleting a Slave Configuration
- Replicating from MySQL 5.7
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.
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.
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:
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.
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."
Deleting a Slave Configuration
To delete a previously-configured Slave, issue the following command:
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.