This is the second article in our series about MySQL NDB Cluster replication, it focuses on the basic concepts which are neccessary to be familiar with in order to build the more advanced configurations with redundancy, improved scalabilty and performance.
The two basic logical components used for MySQL NDB Cluster replication are:
- the binlog MySQL Server which is configured to write a binary log of all changes that occur in NDB.
- the replica MySQL Server which fetch changes from the binlog MySQL Server and applies those to another cluster.
The preceding diagram shows a logical view of the components required for single-channel cluster replication.
- The MySQL clients use the MySQL servers to read and write data in NDB.
- The binlog MySQL Server writes the changes in NDB to the binary log.
- The replica MySQL Server fetches and applies the changes to another cluster.
- Finally, the MySQL Server on the second cluster reads the data from NDB.
The single-channel replication setup is used to replicate data from one cluster to another, neither replication redundancy or updates on the second cluster will be replicated back to the first, this is quite an unusual setup for MySQL NDB Cluster replication but it is the basic building block for the more advanced replication setups and is thus important to understand. Such replication can also be used when there is a need to replicate data to a standalone MySQL Server or MySQL Heatwave, for example, to offload the cluster from analytics queries.
There is no binary log redundancy in the depicted setup, as it just shows the logical components required to get data replicated from one cluster to another. In a highly available setup you want binary log redundancy, how to achieve that is something we will write about in a future article.
How the binlog MySQL Server works
The binlog MySQL Server is used for writing changes that occur in the NDB cluster to the binary log. The functionality is built into the MySQL Server and can be turned on using --ndb-log-bin, this will set up subscriptions on all tables in NDB that should be binlogged, and when data changes, the MySQL Server will get an event describing the change. These change events will be written as transactions in the binary log, where each transaction represents all changes that have occurred during an epoch. Epoch is the mechanism used in NDB for grouping transactions that have been committed together, with default settings, an epoch completes in NDB every 100 milliseconds, and thus each binary log transaction will span all changes during that period. Normally these transactions are large transactions consisting of changes from many clients, and this differs quite significantly from a normal MySQL Server where each client session's transactions are binlogged individually.
We normally refer to these binary log transactions from NDB as epoch transactions and they are identified by an ever-increasing epoch number that is unique in the NDB cluster where the change originated. The epoch number is persisted as part of the binary log transaction by adding an update of the mysql.ndb_apply_status system table. When the binary log transaction is later written to the replica cluster, the mysql.ndb_apply_status table will be updated and thus uniquely identify the exact state of replication from the source cluster. The binary log transaction also contains the MySQL servers server_id, thus making it possible to identify the source cluster where the change originated.
Apart from writing transactions to the binary log, the system table mysql.ndb_binlog_index will be updated with a new row containing the epoch number, the server_id, and its corresponding binary log file name and position. This table can be used for monitoring progress as well as finding where an epoch transaction is located in the binary log, the latter is used when replication at some time needs to be restarted from an exact point in the binary log.
The recommended best practice is to use a dedicated binlog MySQL Server whose only function in the system is to write the binary log, thus it can:
- be configured optimally for the task
- run in a suitable environment
- be shielded from interference by any client load
- be monitored to properly use resources.
For clusters where the data change rate is not so high, it’s possible to let one of the servers used by the clients also write the binary log. However, that is not the recommended best practice, especially in the modern cloud environments where creating and managing instances is simplified.
How the replica MySQL Server works
The replica MySQL Server is responsible for connecting to the binlog MySQL Server, fetching new data changes, and applying them to NDB. This is configured with the usual CHANGE REPLICATION SOURCE .. command which specifies the settings for how to connect to the binlog MySQL Server and where in the binary log to start replicating from. After configuring, the START REPLICA and STOP REPLICA commands make data start or stop flowing through the replication channel. The replica MySQL Server will fetch binary log entries from the source MySQL Server through the replication channel. While receiving each new transaction, it will be applied to NDB. The large epoch transaction sizes allow larger batches of operations to be sent to NDB, thus effectively reducing the number of roundtrips required to apply the transaction. When NDB receives the transaction, either in batch or in full, it will be processed in parallel using all available data nodes thus effectively making use of available resources. This reduces the apply time.
As mentioned previously, the binary log transaction contains an update of the mysql.ndb_apply_status table which will cause an update to this table which exists on the replica cluster. This row thus indicates the exact state of the source cluster as it was when the transaction was committed and the server_id of the binlog MySQL Server which wrote the epoch transaction. This information thus atomically records the state of replication from the source into the replica cluster. The epoch number is also what is used to restart replication from another binlog MySQL Server when using binary log redundancy.
Separation of different functional concerns
As can be seen throughout this article there are two different logical functions each handling its own part of replication. These different functions are are all builtin to the MySQL Server and while it might be tempting to configure the system using a single MySQL Server instance, the recommended best practice is to keep these functions separate in their own instance. Another functional part are the frontend read-write MySQL servers which should be possible to scale horizontally independently from the MySQL servers handling replication. The maximum number of MySQL servers connected to NDB is 250 and thus dedicating a few of these to function as either binlog or replica MySQL Server still makes it possible to have more than enough MySQL servers in the cluster.
Summary
This article explained how data can be replicated from one cluster to another by using well-known and proven MySQL technology. The two basic components to achieve this are the binlog and replica MySQL Server which contains enhanced functionality for working with MySQL NDB Cluster.
More information
For more details on how to configure replication see:
- MySQL Docs - MySQL NDB Cluster replication
- MySQL Docs - MySQL NDB Cluster Single channel replication
- MySQL Docs - MySQL 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 proven GA version 8.0, the just released 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!