In the previous chapter, we learned about MySQL Group Replication. We discussed Group Replication internals and also looked at its step-by-step configuration. We went through all of the important MySQL variables that can be used to fine-tune the performance of Group Replication. In this chapter, we shall focus on MySQL InnoDB, concentrating on the following topics:
InnoDB cluster is a complete, high-availability solution provided by MySQL. InnoDB cluster uses a Group Replication mechanism at its heart to replicate data; on top of that, InnoDB cluster provides a built-in failover mechanism, as illustrated in the following diagram:
To ensure high-availability, a seamless failover mechanism and the ability to load-balance are essential. MySQL provides this with MySQL Router. The router is an important component in InnoDB cluster but is a separate component that needs to be downloaded and installed. The router acts as middleware that provides routing between clients and servers. InnoDB cluster can operate in multi-primary mode or single-primary mode. In the case of primary server failure, applications do not need to manage the failover logic, as it is automatically taken care of by MySQL Router. It acts as lightweight middleware that routes READWRITE and READONLY transactions, as well as appropriately routing data requests to either primary or slave servers. This functionality can help to achieve scalability at the MySQL level.
MySQL Router should be installed near to applications or on the same host as applications. After installation, MySQL Router fetches the metadata or configuration from InnoDB cluster and caches it locally. If the availability of servers on InnoDB cluster changes, router updates its local data to reflect any changes where necessary.
Application connections that are shut down are disconnected from the MySQL server and then moved to a closed state. Note that applications are required to reconnect, to re-establish the connection, as the router will not take care of such a failover.
MySQL Router 8.0 supports both MySQL 5.7 and MySQL 8.0. If you are using an older version, such as 2.0 or 2.1, you must upgrade to MySQL Router 8.0.
MySQL Shell is an advanced client and code editor for MySQL server. You can install MySQL Shell separately. Refer to the official documentation of MySQL Shell for more information.
In addition to existing SQL functionality, MySQL Shell provides scripting capabilities for JavaScript and Python. Shell also uses X DevAPI, which is helpful for developers who want to use MySQL as a document store or RDBMS. Shell also includes X AdminAPI for architects who want to manage MySQL InnoDB cluster and do not want to configure and manage everything manually.
By using X AdminAPI, we can do the following:
- Configure, create, and check the status of groups
- Configure, add members, and remove members
- Create sandbox environments for testing
Shell also provides us with a way to configure colors, to customize the look and feel of an application.
Let's now deep dive into these programs and explore which APIs will be useful for us going forward.
By now, you should have downloaded the latest versions of MySQL server, MySQL Shell, and MySQL Router. Let's first try to configure a sandbox environment before going through the production setup. We'll start with identifying cluster requirements.
![]()
InnoDB cluster uses Group Replication to replicate data across various machines, so we need to make sure that our server instances meet the minimum requirements necessary; they are as follows:
- Python Version 2.7 or higher is required for MySQL Shell to work, and is required for all machines that are part of a group; for Windows, MySQL Shell bundles Python
- MySQL Router, MySQL Shell, and MySQL server should already be downloaded and installed on the servers before configuring InnoDB cluster
- InnoDB must be used as a storage engine for all tables
Please refer to Chapter 7, Group Replication in MySQL 8.0, for more information on the preceding requirements.
Let's now configure a testing scenario to understand these components and how they work.
![]()
First, download the zip file for MySQL Shell for Windows (Windows X86, 64-bit) from the official MySQL website, http://dev.mysql.com/downloads/shell/. Extract it, and you should see the mysqlsh.exe file in the bin folder. Configure the environment and path variables so that they can access mysqlsh.exe from the command prompt.
For Linux distributions, either the MySQL APT repository or the MySQL Yum repository is required. Make sure you select MySQL 8.0 as the release. Please follow the official MySQL documentation for more details.
You can install MySQL Shell with the following command:
sudo apt-get update
sudo apt-get install mysql-shell
If you already have a MySQL APT Repository configuration, use the following commands:
> sudo apt-get update
> sudo apt-get install mysql-apt-config
Make sure you choose MySQL 8.0 as the release series before running the following command:
> sudo apt-get install mysql-shell
For Yum repositories, use the following command. Configure the MySQL Yum repository with the new release package, mysql80-community-release. Once complete, run the following command:
> sudo yum install mysql-shell
![]()
