In the Setting up Basic Master-Slave Replication in MySQL 8 blog, we went through the process of setting up two MySQL servers on the same box for the purposes of replication. In that scenario, the Master handled write operations, while the Slave performed the preponderance of read operations. While this relatively simple topology may suffice for small to medium businesses, for more intensive applications, this basic design can be scaled to a cluster, by employing the NDB Storage Engine. In most cases you would still have (at least) one Master database server that handles write operations and at least a couple of Slaves to handle reads.
Having gone over a variety of ways to optimize the performance of your MySQL cluster, the time has come to put that theory into practice by setting up a MySQL cluster. To better approximate real-life usage, we'll split up the master and slaves across a couple of physical servers.
"If you fail to plan, you are planning to fail!" - Benjamin Franklin
Before diving into creating our server cluster, let's quickly go over the steps that we'll be taking. Consider this to be a summary of each section that will follow throughout this blog.
We'll assume that you've already got a usable instance of MySQL running. That will become the Master in our set up.
Configure the Master Server
As with a straight Master-Slave configuration, the Master server will handle all write operations as well as propagate data across the cluster of slaves. This will ensure data consistency across the entire cluster, which in our case will consist of three servers. Typically, it's a good idea to make this server a little more robust than your slaves. This can be achieved through selective optimization following the procedure outlined in the Master Optimization section of the MySQL 8 Slave Cluster Configuration Basics blog.
Preparing/Optimizing the Server for Replication
It is well worth noting that the default installation of MySQL does not permit Master-Slave replication out of the box. Therefore, we need to configure a few options to enable replication:
sudo service mysql restart
In Windows, you can stop the MySQL server by executing this command:
C:\> "C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqladmin" -u root shutdown
Note that, if the MySQL root user account has a password, you need to invoke mysqladmin with the -p option and supply the password when prompted.
Then, start up the mysqld process using this command:
C:\> "C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld"
Bear in mind that the path to mysqld may vary depending on the install location of MySQL on your system.
Create a User Account for Replication
Your slave servers will require a user account that allows them to request the data to replicate from the master. As such, this account will require access to whichever database(s) you want to replicate to your slaves.
Obtain Information from the Master
Before your slaves can start replicating data from your databases, they need to know the log file of the master server that records changes, as well as the location in the log file of the most recent change. Do the following on the master:
Configure the Slaves
We are now ready to initialize replication on the slaves. This step is divided into two parts: configuring the slaves and enabling them for replication.
Configuring the Slaves
The following instruction must be performed on each of your slave servers in order to prepare them for replication:
Enabling the Slaves
This step involves settin up the slave to communicate with the master by configuring the slave with the necessary connection information.
Execute the following command in order to update the master information:
Start the Slaves
To activate the slaves, we'll use the START SLAVE command. Although it comes with a number of optional parameters, we'll stick with its basic form here:
Perform Basic Checks on Replication Process
You should always perform a basic check to ensure that replication is working correctly. Execute the following command in order to verify the replication is up and running. Here is some sample output, using the "\G" statement terminator instead of a semi-***, which produces a more readable vertical layout:
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Slave_SQL_Running_State: Reading event from the relay log
Congratulations. Your MySQL master-slave cluster replication is now successfully set up.
Cluster Replication vs. Group Replication
In late 2016, Oracle, the parent company of MySQL, introduced its own Group Replication implementation that was built from the ground up as a new solution. Although MySQL Group Replication shares many very similar concepts to Galera, a plugin that offered both asynchronous and virtually synchronous replication, it is not based on it. Rather, Group Replication was designed to be a significant improvement over Oracle's previous offering, Fabric.
The architectures are different, but the goals are similar. Both do an excellent job of High Availability in that any single node crashing can be recovered from, usually automatically. Both also provide a high degree of read scaling, although Group Replication promises to provide more write scaling. Where Group Replication differs from Cluster Replication is that the former is more slanted towards Fault Tolerance than the latter. Group Replication employs a built-in group membership service that keeps the view of the group consistent and available for all servers at any given point in time. Servers can leave and join the group and the view is updated accordingly. Sometimes servers can leave the group unexpectedly, in which case the failure detection mechanism detects this and notifies the group that the view has changed. This is all automatic.
True fault tolerance is achievable only if you are willing to put nodes in at least 3 separate geographic locations (to protect against floods, tornados, earthquakes, etc). Both allow for that. Use the rule of surviving any single-point-of-failure, and include "datacenter" as a point-of-failure.
We'll explore Group Replication in greater detail in a future blog.