When the MySQL engineers and product management teams decided to develop ground breaking high availability features and a new way to store unstructured data, they knew they were on to something that would change the MySQL world in dramatic fashion.
In this section, we look at two high availability features that are poised to change MySQL high availability in a new and dramatic way. We will also see how the new structured storage mechanism will change what you can store and indeed how you can interact with MySQL to store data for applications where data can change allowing your application to adapt without having to rebuild the storage layers.
Let’s begin with the MySQL 8 high availability solutions.
MySQL Group Replication
If you have used MySQL replication, you are no doubt very familiar with how to leverage it when building high availability solutions. Indeed, it is likely you have discovered a host of ways to improve availability in your applications with MySQL replication.
What Is Replication? And How Does it Work?
MySQL replication is an easy-to-use feature and yet a complex and major component of the MySQL server. This section presents a bird’s-eye view of replication for the purpose of explaining how it works and how to set up a simple replication topology. For more information about replication and its many features and commands, see the online MySQL reference manual.
Replication requires two or more servers. One server must be designated as the origin or master. The master role means all data changes (writes) to the data are sent to the master and only the master. All other servers in the topology maintain a copy of the master data and are by design and requirement read-only servers. Thus, when your sensors send data for storage, they send it to the master. Applications you write to use the sensor data can read it from the slaves.
The copy mechanism works using a technology called the binary log that stores the changes in a special format, thereby keeping a record of all the changes. These changes are then shipped to the slaves and executed there. Thus, once the slave executes the changes (called events), the slave has an exact copy of the data.
The master maintains a binary log of the changes, and the slave maintains a copy of that binary log called the relay log. When a slave requests data changes from the master, it reads the events from the master and writes them to its relay log; then another thread in the slave executes those events from the relay log. As you can imagine, there is a slight delay from the time a change is made on the master to the time it is made on the slave. Fortunately, this delay is almost unnoticeable except in topologies with high traffic (lots of changes).
Moreover, it has become apparent that the more your high availability needs and your solution expands (grows in sophistication), the more you need to employ better ways to manage the loss of nodes, data integrity, and general maintenance of the clusters (groups of servers replicating data - sometimes called replicasets). In fact, most high availability solutions have outgrown the base master and slaves topology evolving into tiers consisting of clusters of servers. Some have replicated a portion of the data for faster throughput and for compartmental storage. All of these have led many to discover they need more from MySQL replication. Oracle has answered these needs and more with Group Replication.
Group Replication was released as GA in December 2016 and is bundled with the server in the form of a plugin. Although it is a GA release, I list it here as a paradigm-shifting feature because of the promise it provides for allowing MySQL high availability to grow well beyond the confines of the original MySQL replication feature and thus empower MySQL 8 to become an important component in high availability database solutions.
Note
Group Replication makes the topology eventually synchronous replication (among the nodes belonging to the same group) a reality, whereas the existing MySQL Replication feature is asynchronous (or at most semi-synchronous). Therefore, better high availability guaranties can be provided, because transactions are delivered to all members in the same order (despite being applied at its own pace in each member after being accepted).
Group Replication does this via a distributed state machine with strong coordination among the servers assigned to a group. This communication allows the servers to coordinate replication automatically within the group. More specific, groups maintain membership so that the data replication among the servers is always consistent at any point in time. Even if servers are removed from the group, when they are added, the consistency is initiated automatically. Further, there is also a failure detection mechanism for servers that go offline or become unreachable. Figure 1 shows how you would use Group Replication with our applications to achieve high availability.
Figure 1 Using Group Replication with applications for high availability (Courtesy of Oracle)
Note that Group Replication can be used with the MySQL Router to allow your applications to have a layer of isolation from the cluster. We will see a bit about the router when we examine the InnoDB Cluster.
Another important distinction between Group Replication and standard replication is that all of the servers in the group can participate in updating the data with conflicts resolved automatically. Yes, you no longer have to carefully craft your application to send writes (updates) to a specific server! However, you can configure Group Replication to allow updates by only one server (called the primary) with the other servers acting as secondary servers or as a backup (for failover).
All of these capabilities and more are made possible using three specific technologies built into Group Replication: group membership, failure detection, and fault tolerance.
- Group membership: This manages whether servers are active (online) and participating in the group. Also, ensures every server in the group has a consistent view of the membership set. That is, every server knows the complete list of servers in the group. When servers are added to the group, the group membership service reconfigures the membership automatically.
- Failure detection: A mechanism that is able to find and report which servers are offline (unreachable) and assumed to be dead. The failure detector is a distributed service that allows all servers in the group to test the condition of the presumed dead server and in that way, the group decides if a server is unreachable (dead). This allows the group to reconfigure automatically by coordinating the process of excluding the failed server.
- Fault tolerance: This service uses an implementation of the Paxos distributed algorithm to provide distributed coordination among the servers. In short, the algorithm allows for automatic promotion of roles within the group to ensure the group remains consistent (data is consistent and available) even if a server (or several) fail or leave the group. As with similar fault tolerance mechanisms, the number of failures (servers that fail) is limited. Currently, Group Replication fault tolerance is defined as n = 2f + 1, where n is the number of servers needed to tolerate f failures. For example, if you want to tolerate up to 5 servers failing, you need at least 11 servers in the group.
Although Group Replication is a plugin, it is bundled with the server installation today with MySQL 5.7 (starting with the 5.7.17 release) as well as MySQL 8.
Tip
Rather than demonstrate Group Replication by itself, we will see just how powerful this feature is when we explore another new feature named InnoDB Cluster in the following section. As you will see in the demonstration of InnoDB Cluster, Group Replication is easy to use and when part of InnoDB Cluster, both technologies change the way we use MySQL replication in a most dramatic way.
InnoDB Cluster
Another new and emerging feature is called InnoDB Cluster. It is designed to make high availability easier to setup, use, and maintain. InnoDB Cluster works with the X AdminAPI via the MySQL Shell and the Admin API, Group Replication, and the MySQL Router to take high availability and read scalability to a new level. That is, it combines new features in InnoDB for cloning data with Group Replication and the MySQL Shell and MySQL Router to provide a new way to setup and manage high availability.
Note
In this use case, the cluster is setup with a single primary (think master in standard replication parlance), which is the target for all write (updates). Multiple secondary servers (slaves) maintain replicas of the data, which can be read from and thus enable reading data without burdening the primary thus enabling read out scalability (but all servers participate in consensus and coordination). The incorporation of Group Replication means the cluster is fault tolerant and group membership is managed automatically. The MySQL router caches the metadata of the InnoDB Cluster and performs high availability routing to the MySQL server instances making it easier to write applications to interact with the cluster.
You may wonder what makes this different from a readout scalability setup with standard replication. At a high level, it may seem that the solutions are solving the same use case. However, with InnoDB Cluster, you can create, deploy, and configure servers in your cluster from the MySQL Shell providing a complete high availability solution that can be managed easily. That is, you can use the InnoDB Cluster AdminAPI via the shell to create and administer an InnoDB Cluster programmatically using either JavaScript or Python.
Let us now see these new technologies in action. What follows is a demonstration of deploying three servers, configuring them as a cluster via Group Replication using JavaScript commands in the new MySQL Shell. Although that sounds like a lot of effort, it really isn’t and in fact is really easy.
Note
Let’s begin by starting the shell and deploying three servers using the AdminAPI. In this case, we will use the deploySandboxInstance() method in the dba object to create new instances for each server. All of these will run on our localhost. Listing 1 demonstrates how to deploy three servers. I highlight the commands used to help identify the commands from the messages.
$ mysqlsh
MySQL Shell 8.0.11
Copyright (c) 2016, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type '\help' or '\?' for help; '\quit' to exit.
MySQL JS > dba.deploySandboxInstance(3307)
A new MySQL sandbox instance will be created on this host in
/Users/cbell/mysql-sandboxes/3307
Please enter a MySQL root password for the new instance:
Deploying new MySQL instance...
Instance localhost:3307 successfully deployed and started.
Use shell.connect('root@localhost:3307'); to connect to the instance.
MySQL JS > dba.deploySandboxInstance(3308)
A new MySQL sandbox instance will be created on this host in
/Users/cbell/mysql-sandboxes/3308
Please enter a MySQL root password for the new instance:
Deploying new MySQL instance...
Instance localhost:3308 successfully deployed and started.
Use shell.connect('root@localhost:3308'); to connect to the instance.
MySQL JS > dba.deploySandboxInstance(3309)
A new MySQL sandbox instance will be created on this host in
/Users/cbell/mysql-sandboxes/3309
Please enter a MySQL root password for the new instance:
Deploying new MySQL instance...
Instance localhost:3309 successfully deployed and started.
Use shell.connect('root@localhost:3309'); to connect to the instance.
MySQL JS >Listing 1 Creating Local Server Instances
Note that the text explains that we are using a sandbox , which is a term applied to running servers on the localhost in a special directory: the mysql-sandboxes folder in the user home. In particular in this case, we use /Users/cbell/mysql-sandboxes. Note that we now have three servers running on ports 3307, 3308, and 3309. Note also that the shell will prompt you for the new password.
Tip
The next thing we need to do is setup a new cluster. We do this with the createCluster() method in the dba object. But first, we must connect to the server we want to make our primary server. Listing 2 demonstrates how to create the cluster. Note that this is a continuation of our shell session and demonstrates how to create a new cluster.
MySQL JS > \connect root@localhost:3307
Creating a session to 'root@localhost:3307'
Enter password:
Your MySQL connection id is 12
Server version: 8.0.11 MySQL Community Server (GPL)
No default schema selected; type \use <schema> to set one.
MySQL localhost:3307 ssl JS > my_cluster = dba.createCluster('my_cluster')
A new InnoDB cluster will be created on instance 'root@localhost:3307'.
Creating InnoDB cluster 'my_cluster' on 'root@localhost:3307'...
Adding Seed Instance...
Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
At least 3 instances are needed for the cluster to be able to withstand up to
one server failure.
<Cluster:my_cluster>Listing 2 Creating a Cluster in InnoDB Cluster
Note that we named the cluster my_cluster and used a variable of the same name to store the object returned from the createCluster() method. Note that the first server we connected has become the primary (master).
Next, we add the other two server instances to complete the cluster using the addInstance() of our new my_cluster object. These servers automatically become secondary servers (slaves) in the group. Listing 3 shows how to add the instances to the cluster.
MySQL localhost:3307 ssl JS > my_cluster = dba.getCluster('my_cluster')
<Cluster:my_cluster>
MySQL localhost:3307 ssl JS > my_cluster.addInstance('root@localhost:3308')
A new instance will be added to the InnoDB cluster. Depending on the amount of data on the cluster this might take from a few seconds to several hours.
Please provide the password for 'root@localhost:3308':
Adding instance to the cluster ...
The instance 'root@localhost:3308' was successfully added to the cluster.
MySQL localhost:3307 ssl JS > my_cluster.addInstance('root@localhost:3309')
A new instance will be added to the InnoDB cluster. Depending on the amount of data on the cluster this might take from a few seconds to several hours.
Please provide the password for 'root@localhost:3309':
Adding instance to the cluster ...
The instance 'root@localhost:3309' was successfully added to the cluster.Listing 3 Adding Instances to the Cluster
Once the cluster is created and the instances are added, we can get the status of the cluster using the status() method of our my_cluster object as shown in Listing 4.
MySQL localhost:3307 ssl JS > my_cluster.status()
{
"clusterName": "my_cluster",
"defaultReplicaSet": {
"name": "default",
"primary": "localhost:3307",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"localhost:3307": {
"address": "localhost:3307",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"localhost:3308": {
"address": "localhost:3308",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"localhost:3309": {
"address": "localhost:3309",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
}
}
}
}
MySQL localhost:3307 ssl JS > \exit
Bye!Listing 4 Getting the Status of the Cluster
At this point, we’ve seen how InnoDB Cluster can setup servers and add them to the group. What you do not see behind the scenes is all of the Group Replication mechanisms - you get them for free! How cool is that?
Now that we have a cluster, there is one more thing we need to do to enable applications to use the fault tolerance features of Group Replication. That is, we need to be able to connect to the cluster and interact with MySQL even if one of the servers fails. Note that because we only have three servers, we can only tolerate one failure. For example, solving for f in the number of faults tolerated by Group Replication, we get 3 = 2f + 1 or f = 1.
We must now use MySQL Router to manage the connections for our application. Although we don’t have an application to demonstrate, we can see this in action using the shell. Now let’s see how easy it is to set up the router. Listing 5 shows how to start the router in bootstrap mode. Note that by connecting to the cluster, the router automatically gets the members of the group. Recall from the previous section, this is one of the tenets of Group Replication via the membership service.
& mysqlrouter --bootstrap localhost:3307 --user=cbell
Please enter MySQL password for root:
Bootstrapping system MySQL Router instance...
MySQL Router has now been configured for the InnoDB cluster 'my_cluster'.
The following connection information can be used to connect to the cluster.
Classic MySQL protocol connections to cluster 'my_cluster':
- Read/Write Connections: localhost:6446
- Read/Only Connections: localhost:6447
X protocol connections to cluster 'my_cluster':
- Read/Write Connections: localhost:64460
- Read/Only Connections: localhost:64470
& mysqlrouter &Listing 5 Setting Up the MySQL Router
Okay, now we have the router running. Our applications can use the features of the router to automatically reroute our application connections should something happen to one of the servers in the cluster.
Let’s see a short demonstration of this feature. In this case, we will use the shell to connect to the cluster via the router on port 6446 as shown in Listing 6. We use this port because the router is used to forward connections automatically. That is, if the server we’re connected to goes down - for instance the one on port 3307 - we do not have to restart our application to reconnect to a server on another port. Thus, the router, routes the communications for us. Let’s see this in action.
Listing 6 demonstrates connecting to the cluster via the router. We switch to SQL mode in the shell and use an SQL command to see the port of the server where we’re connected. We then switch back to JavaScript and use the AdminAPI to kill the instance. We then attempt to issue the SQL command again and now notice that, once the shell has automatically reconnected, we are now connected to another server. Cool!
$ mysqlsh --uri root@localhost:6446 --sql
Creating a session to 'root@localhost:6446'
Enter password:
Your MySQL connection id is 47
Server version: 8.0.11 MySQL Community Server (GPL)
No default schema selected; type \use <schema> to set one.
MySQL Shell 8.0.11
Copyright (c) 2016, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type '\help' or '\?' for help; '\quit' to exit.
MySQL localhost:6446 ssl SQL > SELECT @@port;
+--------+
| @@port |
+--------+
| 3307 |
+--------+
1 row in set (0.00 sec)
MySQL localhost:6446 ssl SQL > \js
Switching to JavaScript mode...
MySQL localhost:6446 ssl JS > dba.killSandboxInstance(3307)
The MySQL sandbox instance on this host in
/Users/cbell/mysql-sandboxes/3307 will be killed
Killing MySQL instance...
Instance localhost:3307 successfully killed.
MySQL localhost:6446 ssl JS > \sql
Switching to SQL mode... Commands end with ;
MySQL localhost:6446 ssl SQL > SELECT @@port;
ERROR: 2006 (HY000): MySQL server has gone away
The global session got disconnected.
Attempting to reconnect to 'root@localhost:6446'..
The global session was successfully reconnected.
MySQL localhost:6446 ssl SQL > SELECT @@port;
+--------+
| @@port |
+--------+
| 3308 |
+--------+
1 row in set (0.00 sec)
MySQL localhost:6446 ssl SQL > \quit
Bye!Listing 6 Fault Tolerance Demonstration
Note that although the shell had lost the connection it automatically reconnected so that we can retry the command. Very nice.
Finally, let’s discover how to put the instance that failed back into service. In this case, we simulate recovering a downed server adding it back to the cluster where Group Replication ensures that the new server becomes consistent by applying any missing transactions. Listing 7 shows the commands you can use to recover the server.
$ mysqlsh --uri root@localhost:6446
MySQL localhost:6446 ssl JS > dba.startSandboxInstance(3307)
The MySQL sandbox instance on this host in
/Users/cbell/mysql-sandboxes/3307 will be started
Starting MySQL instance...
Instance localhost:3307 successfully started.
MySQL localhost:6446 ssl JS > my_cluster = dba.getCluster('my_cluster')
<Cluster:my_cluster>
MySQL localhost:6446 ssl JS > my_cluster.rejoinInstance('root@localhost:3307')
Rejoining the instance to the InnoDB cluster. Depending on the original
problem that made the instance unavailable, the rejoin operation might not be
successful and further manual steps will be needed to fix the underlying
problem.
Please monitor the output of the rejoin operation and take necessary action if the instance cannot rejoin.
Please provide the password for 'root@localhost:3307':
Rejoining instance to the cluster ...
The instance 'root@localhost:3307' was successfully rejoined on the cluster.
The instance 'localhost:3307' was successfully added to the MySQL Cluster.
MySQL localhost:6446 ssl JS > \q
Bye!Listing 7 Recovering a Lost Server
It is clear that using the shell to setup and manage a cluster is a lot easier than setting up and managing a standard Group Replication setup. In particular, you don’t have to manually configure replication! Better still, should a server fail, you don’t have to worry about reconfiguring your application or the topology to ensure the solution remains viable - InnoDB Cluster does this automatically for you.
To learn more about InnoDB Cluster, see the online documentation.
Summary
MySQL has come a long way since the days when developers downloaded the code, modified it, and put it into use on their rapidly developed platforms. As one who has watched and participated in its evolution, it is with some pride that I look back on the bad old days and see just how far MySQL has come.
The journey hasn’t been easy. The engineering team alone has weathered two acquisitions (Sun Microsystems and Oracle) in rapid succession and a host of smaller team development and minor personnel changes. Through all of this, the engineering team continued to improve features and add new technologies remaining dedicated to making MySQL the best possible solution.
Users also have grown in how they use MySQL from stand alone, single database server installations to massive high availability server farms. Through all of this, the MySQL product has remained poised for something greater. Now, with MySQL 8.0, Oracle has shown its hand and it’s loaded with top-notch technologies. Indeed, the MySQL world is poised to discover new ways to leverage MySQL in a yet unknown variety of methods. I am certain by the time you read this blog you will have your own ideas of how to revamp your use of MySQL.
In this blog, we explored some of the highlights of the new MySQL server version 8.0. We discovered those features originally introduced in earlier versions that have been adapted to the new paradigm that is version 8.0, features that are new, and those new features that are truly revolutionary such as the document store, Group Replication, and InnoDB Cluster.
