This is the third article in our blog series about MySQL NDB Cluster replication, it describes how to extend the single-channel replication from previous post with another set of MySQL servers in order to achieve redundancy.
MySQL NDB Cluster is a highly available RDBMS, designed to be operated with no single point of failure (SPOF). This requires redundancy where the roles of failed components can be taken over by other parts of the system, with minimal disruption to ongoing transaction processing. For asynchronous replication, this requires a dual channel configuration, to retain replication availablity through failures of the MySQL servers implementing a single channel.
The distributed MySQL NDB Cluster database supports having many MySQL servers connected, making it possible to configure several MySQL servers that write binlog and thus making it possible to have binlog redundancy. The basic idea here is that a MySQL Server will write an identical binlog as another MySQL Server configured the same way and connected to NDB for the same period. This is what is used to achieve a highly available record of the changes that have occurred in the cluster.
The preceding diagram shows a logical view of the components required for dual-channel cluster replication with redundancy.
- The MySQL clients use two MySQL servers to read and write data in NDB.
- The binlog MySQL Server writes the changes from NDB to the binary log.
- The second binlog MySQL Server writes the same changes to its binary log.
- The replica MySQL Server fetches and applies the changes to the other cluster.
- The second replica MySQL Server is on standby and ready to start replicating.
- Finally, a local MySQL Server reads the data from the other NDB cluster.
How a redundant binlog MySQL Server works
In the previous article we described how the binlog MYSQL Server is used for writing changes that occur in the NDB cluster to the binary log as a logical function in the cluster.
To achieve binlog redundancy more than one identically configured binlog MySQL Server is used, each one will connect to NDB, subscribe to data changes, and write them to its binary log. Thus the same binary log of changes in NDB is available in several locations. This makes it possible for the replica MySQL Server to choose either one of them as the source for replication. When there is time for maintenance or software version upgrade, the MySQL servers can be stopped one by one and there will still be another one that is writing the binary log and is ready to serve it to the replicas. The same mechanism is used to handle also unplanned failures.
When we say that the MySQL Server has to be identically configured, it means that the settings controlling how the binary log of changes from NDB are written have to be identical. It is for example possible to configure the MySQL Server to only write binary log of some databases or tables and in such case, the binary log will not be identical to another MySQL Server that has included those in its binary log. There are also settings controlling whether all or only updated columns are written to the binary log, while the result at the replica may be the same we advise that such settings are configured identically.
Additional redundancy also provided by NDB
The reason why we call this binlog redundancy is that redundancy already exists on so many levels in MySQL NDB Cluster. One main feature of making sure that the binlog is truly redundant and not affected by the stop, start, or failure of data nodes in the cluster is that the subscriptions for data changes are fault tolerant. When creating a subscription there are several data nodes responsible for sending the change events to the subscriber, and this is coordinated so that only one data node sends the actual change while the others will store the change until it’s been confirmed that the subscriber has received the change event. This functionality makes it possible for the binlog MySQL Server to receive change events for all changes although data nodes leave or join the cluster. While it’s a rare event that a data node fails, it is important to have this functionality to seamlessly be able to stop and start nodes, for example, to upgrade the software version of a cluster while it’s online and operating.
How the redundant replica MySQL Server works
The replica MySQL Server is used for connecting to the source binlog MySQL Server, fetching changes, and applying them to the replica cluster. This needs redundancy to be able to resume replication, either when there is planned maintenance or when a failure occurs causing replication to stop. Redundancy is achieved by configuring more than one replica MySQL Server connected to the replica cluster. There will normally only be one replica MySQL Server actively replicating at a time, while the other ones are on standby and waiting for the command to start replicating.
Compared to the redundant binlog MySQL Server which both are active, the additional replica MySQL Server instances are on standby, normally not doing anything other than being ready to take over with short notice. Depending on the requirements for how quickly the replication should be able to resume, these instances could in a modern cloud environment be shut down or paused assuming they can quickly be started again.
Finding the coordinates where to resume replication
As we have described earlier, each replicated epoch transaction contains the epoch number and the source server_id of the source cluster. This information is transactionally written to the system table mysql.ndb_apply_status in the replica cluster and thus uniquely identifies the epoch number applied from the source. By reading this value, it is possible to query the system table mysql.ndb_binlog_index on the source to find the binlog filename and position corresponding to that position. In replication terminology, we call these the coordinates, and those two values are then provided to the CHANGE REPLICATION SOURCE .. command to indicate where replication should start.
One thing which should be noted here is that although the redundant binlog MySQL servers are writing logically identical binary logs, they are not necessarily physically identical. The same binary log entries may end up in files with different names or at different positions in those files, one common reason for this is log rotation which are performed when a MySQL Server is restarted. By using the applied epoch stored in the replica cluster, and using ndb_binlog_index table on the source binlog MySQL Server allows the logical epoch transaction to be mapped to physical coordinates.
Summary
This article explained how redundant replication between MySQL NDB clusters works, on the source cluster redundant and identical copies of the binary log are created by the binlog MySQL Server, and on the replica one replica MySQL Server is replicating while at least one more is on standby and ready. This is a unique feature of MySQL NDB Cluster replication and lays the foundation for even more advanced replication setups.
More information
For more details on how to configure replication see:
- MySQL Docs - Using Two Replication Channels for NDB Cluster Replication
- MySQL Docs - Implementing Failover with NDB Cluster Replication
MySQL NDB Cluster is open-source and can be downloaded both in source and binary form at MySQL Downloads where you find both the 8.4 LTS version as well as our innovation releases.
The source code is also available at the MySQL Server repository on GitHub and is frequently used by universities when studying the inner details of advanced high-performance distributed databases. Go explore the source yourself and why not submit a pull request with enhancements!