Updated June 2, 2023
Definition of MySQL InnoDB Cluster
MySQL InnoDB Cluster can be said as a completely great availability resolution delivered by MySQL which consists of only one and multiple master abilities and failover recognition. Here, by implementing the AdminAPI comprised of MySQL Shell, one can easily organize and administer at least three instances of MySQL server as an InnoDB Cluster. MySQL InnoDB Cluster consists of three modules, which are as follows:
- MySQL Group Replication – A collection of the database server duplicating each other having fault tolerance.
- MySQL Router – Inquiry router to the vigorous database nodes.
- MySQL Shell – As a client, helper, and configuration tool.
Syntax
MySQL InnoDB Cluster utilizes the AdminAPI, included in the MySQL Shell, to implement an outstanding availability solution by grouping at least three instances of MySQL servers. Every instance of MySQL server executes MySQL Group Replication.
To access the AdminAPI in MySQL Shell, you use a global variable called “dba” along with its associated methods. This procedure of the dba variable allows a user to configure, deploy, and administer the InnoDB clusters. Suppose we use the method dba.createCluster() to create an InnoDB cluster.
Note that MySQL Shell permits connecting to the servers using a socket connection. Whereas AdminAPI needs TCP connections to an instance of the server. So, we should not use socket connections while using AdminAPI. You can use the command dba.help() method to get online help for AdminAPI.
We must apply a format object for online support on a particular approach.help(‘methodname’), explained in the below illustration:
dba.help('getClsuter') // Fetches a cluster from metadata storeSyntax:
<Dba>.getCluster([name])WHERE
The name defines the parameter to state the name of the cluster to be resumed.
If the name is not detailed, it will return the default cluster.
But if the name is stated and no cluster with the defined term is found, it will raise an error.
How does InnoDB Cluster work in MySQL?
MySQL is an object-relational and open-source type of database management system available with specific features like scalable and extensible. It also maintains data replication through multiple data centers.
MySQL 5.7 version supports MySQL InnoDB Cluster, which is an Oracle high accessibility solution and can be installed over MySQL for holding multiple master abilities and spontaneous failover. This solution comprises three constituents:
1. Group Replication
A collection of database servers is defined where MySQL databases are replicated through multiple nodes, including the fault tolerance feature. Suppose any alterations in the data happen in the MySQL databases, then it routinely duplicates to the secondary nodes of the server. This idea of Group Replication in MySQL is identical to the concept of the AlwaysOn availability group.
2. MySQL Router
This helps make client applications aware of the cluster topology and the PRIMARY instance whenever a failover happens. MySQL Router regulates this functionality by routing data requests to the appropriate and accessible MySQL server instance. It operates as a proxy that effectively conceals the presence of multiple MySQL database servers. This concept is identical to the Virtual Network name concept of the Windows Server failover cluster.
3. MySQL Shell
This is a kind of tool used for configuration to implement, connect, deploy, and manage the MySQL InnoDB cluster. MySQL Shell consists of an Admin API with a dba global variable to deploy and accomplish the InnoDB Cluster.
Features
Let’s discuss a few other features delivered by this solution:
1. Distinct Primary or Multiple Primary Modes
It means that the cluster can control either with only a writer and more than one reader, the default setup and recommended one, or with more than one writer where entire nodes can admit write transactions. Due to the conflict firmness, the latter is provided at the cost of a performance penalty.
2. Spontaneous Failure Recognition
Here, an internal component can detect a failed node which can be either a crash or any network problems and also agrees to eliminate it from the cluster mechanically. Suppose a member does not connect with the cluster and gets inaccessible; it will not receive transactions. This assures that this type of situation has not obstructed the cluster data.
3. Fault Tolerance
It defines the strategy which the cluster implements to maintain failing members. Since it is also based on the majority kept by the other two members, whereas a cluster requires three members to support one node. If there is a larger number of nodes, then there will also be a larger number of failing nodes supported by the cluster. In a cluster, the number of members or nodes is presently restricted to 7, where four or more active nodes store the majority. We can say that a cluster of seven will support and maintain up to three failing nodes.
Examples of MySQL InnoDB Cluster
Let us view working with MySQL InnoDB Cluster and regulating shared administration tasks explained as follows:
1. Eliminating Instances from the MySQL InnoDB Cluster
When you want to remove the instances at any time from a cluster, then you can do so. You can perform this action by utilizing the method “cluster.removeInstance(instance)” as written:
Cluster.removeInstance('root@localhost:3360')This method removes the instance from the metadata of all online cluster members and the instance itself. This action ensures that the eliminated instance does not cause any issues with transactions that require its involvement. In that case, the AdminAPI will wait for a few seconds as configured by MySQL Shell with the dba.gtidWaitTimeout option, whose default value is the 60s.
2. Running an InnoDB Cluster
If a user wants to run a cluster, you must connect to a read-write instance, such as the Primary, in only one primary cluster using the command cluster.dissolve(). This will remove whole metadata along with the cluster’s configuration and restrict Group Replication on the instances. At the same time, the instances do not detach any duplicated data.
You will find no way to undo this process of dissolving or running an InnoDB cluster, but it can be created again using the command:
dba.createCluster()The cluster.dissolve() method will only configure instances of a server that are ONLINE or reachable
3. Altering a Cluster’s Topology
An InnoDB cluster executes in a single primary mode by default. Here, the cluster consists of only one primary server, which takes read and write (R/W) queries, and the remaining instances in the cluster take only read(R/O) queries. For this, the operational command is:
cluster.switchToSinglePrimaryMode({instance}).When configuring the cluster to run in multi-primary mode, all instances within the cluster are designated as primaries, allowing them to handle both read and write (R/W) queries. For this, we will use the command:
cluster.switchToMultiPrimaryMode().Conclusion
MySQL InnoDB is a popular storage engine in MySQL and MariaDB database management systems. To guarantee high availability, MySQL created the InnoDB Cluster, which enables easy configuration and administration of at least three MySQL server instances that operate as an InnoDB cluster. It is important to note that InnoDB Cluster does not support or maintain MySQL NDB cluster, as the NDB storage engine governs it. Additionally, MySQL server version 8.0 does not include some NDB cluster code programs.
Recommended Articles
We hope that this EDUCBA information on “MySQL InnoDB Cluster” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
