Saturday, November 2, 2019

Manage InnoDB Cluster using MySQL Shell Extensions

At times, when playing with different InnoDB Clusters for testing (I usually deploy all Group Replication instances on the same host on different ports) I find myself stopping the group and doing operations on every instance (e.g. a static reconfiguration). Or I may need to shutdown all instances at once. Scripting is the usual approach, but in addition, MySQL Shell offers a very nice (and powerful) way to integrate custom scripts into the Shell itself to manage an InnoDB Cluster. This is the purpose of MySQL Shell extensions, to create new custom reports and functions and have the flexibility to manage one or more instances at once. I found particularly practical the new plugin feature, introduced in MySQL Shell 8.0.17, that can aggregate reports and functions under the same umbrella: the plugin.

As an example of the things that are possible, I have modified Rene's great example so to stop Group Replication in a shot from MySQL Shell, and it's particularly easy, check the following script.

  1. Create directory  ~/.mysqlsh/plugins/ext/idc/
  2. Create there init.js script as follows, it will be loaded at MySQL Shell startup.

// Get cluster object, only if session is created
function get_cluster(session, context) {
  if (session) {
    try {
      return dba.getCluster();
    } catch (err) {
      throw "A session to a cluster instance is required: " + err.message
  } else {
    throw "A session must be established to execute this " + context

function stop_cluster() {
  var cluster = get_cluster(shell.getSession(), "function");
  var data = cluster.status();
  var topology = data.defaultReplicaSet.topology;
  var sess = shell.getSession()
  var uri = sess.getUri()
  var user = (uri.split('//')[1]).split('@')[0]

  // iterate through members in the cluster
  for (index in topology) {
if (topology[index].status=="ONLINE")
print("\n-----> " + topology[index].address + " is ONLINE and will be evicted from GR\n")

var sess = shell.connect(user + "@" + topology[index].address)
var result = sess.runSql("STOP GROUP_REPLICATION;")

//print(JSON.stringify(result, null, 4))

  // Reconnect original session

// Creates the extension object
var idc = shell.createExtensionObject()

// Adds the desired functionality into it
shell.addExtensionObjectMember(idc, "stopCluster", stop_cluster, {
  brief: "Stops GR on all nodes, secondaries first, primary instance the last.",
  details: [
    "This function will stop GR on all nodes.",
    "The information is retrieved from the cluster topology."]});

// Register the extension object as a global object
shell.registerGlobal("idc", idc, {
  brief:"Utility functions for InnoDB Cluster."})

The script defines stop_cluster function that is invoked with idc.stopCluster() and:

  1. Get the cluster object from the session (session against any member must be created beforehand)
  2. Fetch topology from cluster object
  3. Iterate through members belonging to the topology and get the address
  4. For every member, establish a session using same session user (e.g. root or whatever, it is a prerequisite to administer a cluster with the same user)
  5. Send command to stop Group Replication
  6. After iterating through all members, reset the original session
The script also creates an extension object, registers it as global object and adds the function so it can be invoked as follows:

It is also possible to restart the cluster with the built-in dba global object, with function dba.rebootClusterFromCompleteOutage(); 

So in short, it is possible to start and stop the cluster with one command and from the same MySQL Shell session. This is only a quick skeleton (can be improved e.g. like stopping GR starting by secondary instances, and the primary at last) to connect to instances and do operations, there is no limit to the number of things that are possible. Read more on LeFred's blog here

1 comment:

  1. I am completely agreed with this article and I simply need say that this article is extremely nice and exceptionally enlightening article. I will try to read your blog more. I learn something tougher on distinct blogs every day. This is a unique post from your side and looking it thoroughly it seems that you done a great job by writing this beautiful post. I do trust you may well be more convincing. Incredible post keeps up posting such great information. Try to check out this best essay writing service for any kind of academic writing work