InnoDB Cluster in a Nutshell Part 3: MySQL Shell

July 20, 2018

Author

Francisco Bordenave

Insight for DBAs

Insight for Developers

MySQL

Share this Post:

MySQL InnoDB Cluster MySQL Shell Welcome to the third part of this series. I’m glad you’re still reading, as hopefully this means you find this subject interesting at least. Previously we presented the first two components of MySQL InnoDB Cluster: Group Replication and MySQL Router and now we will discuss the last component, MySQL Shell.

MySQL Shell

This is the last component in the cluster and I love it. Oracle have created this tool to centralize cluster management, providing a friendly, command-line based user interface.

The tool can be defined as an advanced MySQL shell, which is much more powerful than the well known MySQL client. With the capacity to work with both relational and document (JSON) data, the tool provides an extended capability to interact with the database from a single place.

MySQL Shell is also able to understand different languages:

JavaScript (default) which includes several built-in functions to administer the cluster—create, destroy, restart, etc.—in a very easy way.

Python it provides an easy way to write Python code to interact with the database. This is particularly useful for developers who don’t need to have SQL skills or run applications to test code.

SQL to work in classic mode to query database as we used to do with the old MySQL client.

A very interesting feature provided with MySQL Shell is the ability to establish different connections to different servers/clusters from within the same shell. There is no need to exit to connect to a different server, just issuing the command connect will make this happen. As DBA, I find this pretty useful when handling multiple clusters/servers.

Some of the features present in this tool:

Capacity to use both Classic and X protocols.

Online switch mode to change languages (JavaScript, Python and SQL)

Auto-completion of commands using tab, a super expected feature in MySQL client.

Colored formatting output that also supports different formats like Table, Tab-separated and Json formats.

Batch mode that processes batches of commands allowing also an interactive mode to print output according each line is processed.

Some sample commands

Samples of new tool and execution modes:

 #switch modes<br>sql<br>js<br>py<br><br>#connect to instance<br>connect user@host:[port]<br><br>#create a cluster (better to handle through variables)<br>var cluster=dba.createCluster('percona')<br><br>#add instances to cluster<br>cluster.addInstance('root@192.168.70.2:3306')<br><br>#check cluster status<br>cluster.status()<br><br>#using another variable<br>var cluster2=dba.getCluster('percona')<br>cluster.status()<br><br>#get cluster structure<br>cluster.describe()<br><br>#rejoin instance to cluster - needs to be executed locally to the instance<br>cluster.rejoinInstance()<br><br>#rejoin instance to cluster - needs to be executed locally to the instance<br>cluster.rejoinInstance()<br><br>#recover from lost quorum<br>cluster.forceQuorumUsingPartitionOf('root@localhost:3306')<br><br>#recover from lost quorum<br>cluster.rebootClusterFromCompleteOutage()<br><br>#destroy cluster<br>cluster.dissolve({force:true});

1

#switch modes sql js py #connect to instance connect user@host:[port] #create a cluster (better to handle through variables) var cluster=dba.createCluster('percona') #add instances to cluster cluster.addInstance('[email protected]:3306') #check cluster status cluster.status() #using another variable var cluster2=dba.getCluster('percona') cluster.status() #get cluster structure cluster.describe() #rejoin instance to cluster - needs to be executed locally to the instance cluster.rejoinInstance() #rejoin instance to cluster - needs to be executed locally to the instance cluster.rejoinInstance() #recover from lost quorum cluster.forceQuorumUsingPartitionOf('root@localhost:3306') #recover from lost quorum cluster.rebootClusterFromCompleteOutage() #destroy cluster cluster.dissolve({force:true});

Personally, I think this tool is a very good replacement for the classic MySQL client. Sadly, mysql-server installations do not include MySQL shell by default, but it is worth getting used to. I recommend you try it.

Conclusion

We finally reached the end of this series. I hope you have enjoyed this short introduction to what seems to be Oracle’s bid to have a built-in High Availability solution based on InnoDB. It may become a good competitor to Galera-based solutions. Still, there is a long way to go, as the tool was only just released as GA (April 2018). There are a bunch of things that need to be addressed before it becomes consistent enough to be production-ready. In my personal opinion, it is not—yet. Nevertheless, I think it is a great tool that will eventually be a serious player in the HA field as it’s an excellent, flexible and easy to deploy solution.