Today: July 13, 2020 11:07 am

MySQL 5.7 and Innodb cluster : Must read series – Part 2

6. Consistency levels

Default behaviour is EVENTUAL consistency for innodb cluster. What does this mean ?

Eventual Consistency :

The transaction does not wait for preceding transactions to be applied before executing, neither does it wait for other members to apply its changes.This was the behaviour of Group Replication before 8.0.14.

So if you are on 5.7 the only consistency level option available is EVENTUAL   which would result in stale reads sometimes

Version 8 brings you other options like AFTER,BEFORE,BEFORE_ON_PRIMARY_FAILOVER and BEFORE_AND_AFTER . You have great flexibility to change consistency level to suit your needs

7. Group replication exit state action

In 5.7 when a member of cluster is kicked out it will go into super_read_only mode and enter the ERROR state. But with version 8 you can set the group_replication_exit_state_action to ABORT_SERVER or READ_ONLY .

READ_ONLY behaviour is similar to what is described above(default in 5.7) . Abort_server(new in 8) inturn will log that occurrence and shut itself down cleanly and gracefully .

Abort_server is really helpful to prevent any kind of stale reads or split-brain situation . Lets say you are using cloud servers and one of the VM is separated due to network segmentation for a brief period of time , abort_server could prevent split-brain situation .

8. Router API calls

From 8.0.17 it is possible to query the MySQL Router via its REST API and get a lot of useful information.

Below are some URLs supported. As you can see you can get router health , app connections , blocked hosts etc.. easily now

  • /routes/{routeName}/health
  • /routes/{routeName}/destinations
  • /routes/{routeName}/connections
  • /routes/{routeName}/blockedHosts
  • /routes
  • /router/status

9. Reporting framework :

Built-in reports and user-defined reports that have been registered with MySQL Shell can be run in any interactive MySQL Shell mode (JavaScript, Python, or SQL) using the \show or \watch command, or called using the shell.reports object from JavaScript or Python scripts. The \show command or \watch command with no parameters list all the available built-in and user-defined reports.

For example, the following command uses the built-in report query to display the statement counter variables and refresh the results every 0.5 seconds:

\watch query –interval=0.5 show global status like ‘Com%’

NOTE : You can write your own custom reports easily from version 8

10. Several cluster commands missing in 5.7

In version 8 you can use Cluster.listRouters() to check the routers configured . Also you have Cluster.>options() using which you can alter the behaviour of cluster when it is live

For example , you can set below options on a live cluster :

  • clusterName: string value to define the cluster name.
  • exitStateAction: string value indicating the group replication exit state action.
  • memberWeight: integer value with a percentage weight for the automatic primary election on failover.
  • failoverConsistency: string value indicating the consistency guarantees for primary failover in single primary mode.
  • expelTimeout: integer value to define the time period in seconds that cluster members should wait for a non-responding member before evicting it from the cluster.

Example :

We can change expelTimeout of cluster as illustrated below :

Share this Post :