MySQL Cluster
MySQL NDB Cluster, commonly referred to as MySQL Cluster, is a high-availability, distributed relational database system that integrates the standard MySQL server with an in-memory, shared-nothing storage engine known as NDB (Network Database).[1] This architecture enables real-time data processing across multiple nodes without a single point of failure, supporting automatic data partitioning, replication, and recovery to ensure continuous availability even during hardware failures or node restarts.[1] First publicly released as part of MySQL 5.0.2 in 2004, it has evolved to provide scalable solutions for mission-critical applications requiring low-latency access and fault tolerance.[2] The core of MySQL NDB Cluster lies in its multi-node setup, which includes data nodes responsible for storing and managing data in memory (with optional disk persistence), SQL nodes that run MySQL Server instances to handle queries and transactions, and management nodes that oversee cluster configuration and monitoring.[1] Communication between nodes occurs over TCP/IP, allowing the system to scale horizontally by adding nodes to increase capacity and performance without downtime.[1] Key features include support for ACID-compliant transactions, online backups and restores using native NDB tools, and rolling restarts for upgrades and maintenance, minimizing disruptions.[1] MySQL NDB Cluster is designed for demanding workloads such as telecommunications, financial services, and e-commerce, where it delivers sub-millisecond query response times and linear scalability on commodity hardware.[1] By mirroring data across nodes and enabling automatic failover, it ensures high availability with minimal data loss—typically limited to uncommitted transactions during failures.[1] As of recent releases like NDB 8.4 (an LTS version from 2024), it aligns closely with MySQL Server 8.4, incorporating enhancements in performance, security, and integration with modern development tools.[3]Introduction
Overview
MySQL NDB Cluster, also known as MySQL Cluster, is a distributed database technology that enables shared-nothing clustering for the MySQL database management system using the NDB storage engine. It integrates standard MySQL SQL servers with a network of data nodes to provide in-memory or disk-based storage, designed for high-availability environments with no single points of failure. This architecture allows the system to operate across inexpensive commodity hardware while maintaining data consistency and real-time access.[4][5] As of November 2025, the latest innovation release is NDB Cluster 9.5, building on previous LTS versions like 8.4.[6] The primary use cases for MySQL NDB Cluster involve mission-critical applications in telecommunications, web services, and enterprise systems that demand real-time data processing, such as mobile phone networks, authentication platforms, and online gaming infrastructures serving billions of users daily. It excels in scenarios requiring 99.999% uptime, linear scalability, and fault tolerance, where downtime or data loss could have significant impacts.[5][4] In distinction from standard MySQL, which relies on storage engines like InnoDB for single-instance or basic replication setups, MySQL NDB Cluster employs a fully distributed model that separates SQL processing from data storage across multiple nodes. Its basic operational model automatically partitions data using hashing algorithms and replicates it across nodes—typically up to four replicas per partition—to achieve horizontal scaling and resilience against node failures. This enables seamless addition of nodes for increased capacity without interrupting operations.[7][4]Key Features and Benefits
MySQL NDB Cluster delivers high availability through its shared-nothing architecture, which eliminates single points of failure by distributing data and processing across multiple independent nodes. Automatic failover occurs in under one second using heartbeating mechanisms to detect node failures, enabling self-healing with automatic restarts and resynchronization without manual intervention. This design supports 99.999% uptime, making it suitable for mission-critical applications requiring continuous operation.[7] The system provides auto-sharding for horizontal scalability, automatically partitioning data across nodes to handle growing workloads efficiently. Nodes can be added online without downtime, allowing seamless expansion while maintaining performance through multi-master replication that distributes writes across the cluster. This linear scalability ensures that throughput increases proportionally with additional hardware resources.[1][7] In in-memory mode, MySQL NDB Cluster achieves real-time data consistency with low latency, typically under 5 milliseconds for SQL reads and writes with synchronous replication, providing immediate visibility of changes across all connected SQL nodes. It enforces ACID-compliant transactions, including support for foreign key constraints, ensuring reliability for online transaction processing (OLTP) workloads. Additionally, NDB Cluster supports Disk Data tables, which store table data on disk while maintaining indexes in memory, enabling the handling of larger datasets for both transactional and analytical workloads without compromising ACID compliance.[1][7][8] MySQL NDB Cluster enhances cost efficiency by running on commodity hardware without the need for expensive shared storage solutions like SAN. This approach leverages inexpensive servers in a distributed setup, reducing infrastructure costs while supporting deployment in cloud, Docker, or on-premises environments. Synchronous replication across nodes further bolsters data redundancy without adding significant overhead.[1][7]Architecture
Core Components
MySQL Cluster, also known as NDB Cluster, consists of several fundamental components that work together to provide a distributed, high-availability database system. These include management nodes, data nodes, SQL nodes, and API nodes, each serving distinct roles in configuration, storage, query processing, and application access. The system employs a shared-nothing design where data nodes independently manage their resources to ensure scalability and fault isolation. Data nodes form the backbone of data storage and management in MySQL Cluster. They run the NDB storage engine processes, either as the single-threadedndbd daemon or the multi-threaded ndbmtd daemon, which handle distributed transactions, data replication, checkpointing, node recovery, and online backups across the cluster.[9] Multiple data nodes are required to achieve redundancy, with the minimum number determined by the replication factor and partitioning scheme—for instance, at least four data nodes for two replicas per partition.[10] These nodes store data in memory for high performance while supporting disk-based persistence for durability.[11]
SQL nodes act as front-end interfaces for applications, utilizing standard MySQL Server instances (mysqld) configured with the NDBCLUSTER storage engine to process SQL queries and connect to the cluster data.[10] They translate SQL statements into operations on the underlying NDB engine, enabling traditional relational database access without requiring applications to handle distribution logic directly. SQL nodes can be scaled independently by adding more MySQL servers, supporting load balancing for read and write operations.
Management nodes, implemented by the ndb_mgmd daemon, serve as the central coordinators for the cluster. They read and distribute configuration information from files like config.ini, manage node startups and shutdowns, perform arbitration during failures, maintain activity logs, and facilitate backups and status monitoring.[12] Typically, one or more management nodes are deployed for redundancy, and they must be started before other nodes can join the cluster.[13]
API nodes provide flexible access to cluster data beyond SQL, encompassing any application or process that connects to the NDB engine using the native NDB API, MGM API, or other interfaces for NoSQL-style operations or custom integrations.[10] SQL nodes are a specialized subset of API nodes, but API nodes also include tools like ndb_restore or user-developed applications that directly perform transactions, scans, or updates on distributed data.[14]
Inter-component communication in MySQL Cluster relies on transporters, which are configurable transport mechanisms facilitating signal exchange between nodes. The primary transporter uses TCP/IP over Ethernet for reliable, low-latency data transfer between data nodes, management nodes, and API nodes, with support for multiple parallel transporters per node pair to enhance throughput.[15] Additional options like shared memory (SHM) transporters enable faster local communication when nodes reside on the same host. These transporters ensure coordinated operations, such as transaction commits and failure detection, across the distributed architecture.
Data Distribution and Sharding
MySQL Cluster employs horizontal partitioning to distribute data across multiple data nodes, enabling scalability in a shared-nothing architecture. Tables using the NDB storage engine are automatically partitioned using KEY partitioning, which applies a hashing function—specifically MD5 for NDB Cluster—to the table's primary key to determine row placement. This hash-based sharding ensures even distribution of rows into discrete partitions without requiring manual configuration by the user. If no explicit primary key is defined, the NDB engine generates a hidden primary key to facilitate this process.[16] Each NDB table is divided into a configurable number of partitions, with the default determined by the cluster's configuration: the product of the number of data nodes and the number of local data manager (LDM) threads per node. For single-threaded ndbd processes, this typically equals the number of data nodes; for multithreaded ndbmtd processes, it scales with the configured execution threads to optimize parallelism. These partitions are balanced across the data nodes within node groups, where a node group comprises one or more nodes that collectively store replicas of each partition. The maximum number of partitions per table is limited to 8 times the product of LDM threads and node groups, supporting large-scale deployments.[17] The auto-sharding process in MySQL Cluster operates transparently, partitioning tables upon creation and routing inserts, updates, and deletes based on the primary key hash to the appropriate partition owner. No user intervention is needed for initial distribution, as the NDB kernel manages hash map allocation and load balancing automatically. When nodes are added or removed, the cluster supports online reconfiguration, redistributing partitions across the updated set of data nodes to maintain balance and availability. This rebalancing occurs dynamically without downtime, leveraging the cluster's management layer to migrate fragment replicas as needed. For redundancy, each partition—also known as a fragment—maintains multiple fragment replicas equal to the configured NoOfReplicas parameter (typically 2), distributed across nodes in the same node group. The node holding the primary replica for a given partition acts as the partition owner, handling writes and coordinating synchronization to backup replicas on other nodes within the group. This ownership model ensures data consistency while allowing reads from any replica, with the primary facilitating fault-tolerant operations.[17]Replication and Fault Tolerance
MySQL NDB Cluster employs synchronous replication to maintain data consistency and high availability across its distributed architecture. Data is partitioned into fragments, each of which is replicated synchronously across multiple data nodes within a node group, typically using two replicas per fragment as configured by the NoOfReplicas parameter set to 2. This setup, often referred to as 2-safe replication, ensures that transactions are committed only after acknowledgment from both replicas, providing durability against the failure of a single node without data loss. To manage transaction consistency, especially during failures, MySQL NDB Cluster uses an epoch-based protocol. Transactions are grouped into discrete epochs, which represent synchronized points in time across the cluster; a global epoch is advanced only after all participating nodes confirm the completion of operations within the current epoch. This mechanism enables the creation of consistent snapshots for read operations and facilitates recovery by allowing nodes to replay or discard incomplete epochs from failed transactions. Node failures are handled through automatic recovery processes that minimize downtime. When a data node fails, the surviving replica in its node group immediately assumes responsibility for the affected partitions, ensuring continued availability without interrupting ongoing operations. Upon restart, the failed node automatically redistributes partitions by copying data from surviving replicas, restoring full redundancy. MySQL NDB Cluster also supports rolling restarts, where nodes are restarted sequentially without cluster-wide shutdown, allowing for maintenance, configuration updates, or software upgrades while preserving service continuity.[18] Failure detection and coordination rely on a heartbeat mechanism among data nodes, which monitors node liveness and triggers recovery actions if responses cease. Management nodes play a critical role in arbitration, detecting potential split-brain scenarios through heartbeat analysis and deciding which subset of nodes forms the authoritative cluster partition; this prevents inconsistent operations by potentially shutting down non-viable partitions. Arbitration can be configured to use external resources like disk files for added reliability in complex deployments.[19] For tolerance against network partitions, MySQL NDB Cluster's arbitration process evaluates the viability of surviving node communities based on majority quorum and connectivity. In multi-site configurations, synchronous replication ensures intra-site fault tolerance, while asynchronous replication between geographically distributed clusters provides options for disaster recovery and load balancing across sites without compromising local performance. This shared-nothing isolation further enhances resilience by limiting failure propagation.[19]Storage Models
MySQL NDB Cluster primarily employs an in-memory storage model where table data, indexes, and logs are stored in RAM across data nodes to enable low-latency access and high-throughput operations. This approach leverages the NDB storage engine's design for real-time applications, ensuring that all active data remains readily available without disk I/O overhead during normal query processing. For durability, optional disk persistence is implemented through redo logging, which captures transaction changes to local disk files on each node.[20][7] To accommodate datasets larger than available memory, NDB Cluster supports disk data tables, which store non-indexed columns on local disk per data node rather than in RAM, eliminating the need for shared storage systems. These tables are created using tablespaces and log file groups, where column data resides in data files and undo information in dedicated log files, allowing scalability beyond memory constraints while maintaining cluster-wide distribution. Only non-indexed columns can be placed on disk; indexed columns and primary keys must remain in memory to support efficient query performance.[8][21] Checkpointing in NDB Cluster provides crash recovery by periodically writing committed transaction states from memory to disk in a process known as local checkpoints (LCPs), complemented by global checkpoints (GCPs) that coordinate across nodes. This mechanism balances memory efficiency with persistence, as LCPs flush data fragments asynchronously to redo log files, enabling node restarts to replay logs and recover to the last consistent state without data loss. Parameters such as TimeBetweenLocalCheckpoints and TimeBetweenGlobalCheckpoints govern the frequency, typically set to trigger after a configurable volume of writes, ensuring recovery times remain sub-second in most configurations.[22] The shared-nothing principle underpins NDB Cluster's storage architecture, with each data node independently managing its own memory and local disk resources, free from dependencies on centralized storage like SANs or NFS. This design enhances fault isolation, as failures in one node's storage do not propagate to others, supporting linear scalability by adding nodes without resource contention. Data nodes, as the primary storage hosts, exemplify this by partitioning and replicating data locally across replicas within node groups.[20][23] In hybrid mode, NDB Cluster combines in-memory storage for indexes and frequently accessed data with disk-based storage for larger row data, optimizing cost and performance for diverse workloads. Tables can be configured with STORAGE DISK for non-indexed columns or STORAGE MEMORY for full in-RAM retention, allowing applications to scale economically by offloading bulk data to affordable disk while keeping hot paths in memory. This flexibility is managed through SQL DDL statements during table creation, integrating seamlessly with the cluster's distributed nature.[21][7]APIs and Access Methods
MySQL Cluster provides access to its data through a variety of APIs, enabling both relational and non-relational interactions with the underlying NDB storage engine. The primary SQL interface utilizes standard MySQL servers configured as SQL nodes, which connect to the cluster via the NDB storage engine to execute relational queries using the conventional MySQL protocol. This allows applications to perform standard SQL operations such as SELECT, INSERT, UPDATE, and DELETE, with the cluster handling data distribution and replication transparently.[24] For NoSQL access, MySQL Cluster offers native APIs that bypass the SQL layer for higher performance in key-value and object-oriented scenarios. The NDB API, implemented in C++, serves as the low-level, object-oriented interface directly to the data nodes, supporting indexed operations, full-table scans, transactions, and event handling for real-time applications. ClusterJ, part of the MySQL NDB Cluster Connector for Java, provides a higher-level Java API resembling object-relational mapping frameworks like JPA, enabling session-based data object management and query execution. Additionally, the Memcached API integrates a key-value store interface, allowing memcached-compatible clients to perform atomic get/set operations on cluster data with automatic persistence and sharding support.[25][26][7] RESTful access is facilitated through integrations such as the former MySQL NoSQL Connector for JavaScript (Node.js), which provided adapters for building web applications with direct NDB access, though it has been deprecated in NDB Cluster 9.0 and removed in subsequent versions; current implementations often rely on general MySQL connectors or third-party tools for HTTP-based interactions. A key strength of MySQL Cluster is its multi-API support, where the same dataset can be accessed concurrently via SQL or NoSQL interfaces without requiring extract-transform-load (ETL) processes, supporting polyglot application development across languages like C++, Java, and scripting environments. This unified data access promotes flexibility in hybrid workloads.[27][28] Transaction models in MySQL Cluster emphasize reliability and performance tailored to the API. The SQL API delivers full ACID (atomicity, consistency, isolation, durability) compliance for relational transactions, leveraging two-phase commit protocols across distributed nodes. NoSQL APIs, such as NDB and Memcached, offer optimized ACID transactions with features like unique key operations and partitioned scans, ensuring low-latency consistency while supporting schemaless key-value stores; for instance, each key-value pair is typically stored in a single row for efficient retrieval. These models maintain sharding transparency, where applications interact with the cluster as a single logical database without managing partitions explicitly.[7][24]Management and Deployment
Cluster Management Tools
MySQL Cluster provides several native tools for managing the cluster's lifecycle, including deployment, configuration, monitoring, and maintenance. These tools enable administrators to handle operations such as starting and stopping nodes, tuning parameters, and ensuring high availability without requiring external orchestration systems. The primary command-line interface is the NDB Cluster Management Client (ndb_mgm), which interacts with the management node to control cluster-wide activities. Complementing this is the MySQL NDB Cluster Manager (MCM), a higher-level automation tool that simplifies initial setup and ongoing administration through scripted processes. Configuration is managed via dedicated files that define cluster topology and node behaviors.[29] The NDB Cluster Management Client, known as ndb_mgm, serves as the core command-line tool for real-time cluster administration. It connects to the management server (ndb_mgmd) on the default port 1186 and supports commands for starting and stopping data nodes (ndbd or ndbmtd), SQL nodes (mysqld), and the entire cluster. For instance, theSTART node_id command brings specified data nodes online, while STOP node_id halts them, with options like -f for forced stops that abort ongoing transactions to prevent hangs. The SHUTDOWN command terminates all nodes safely. Configuration changes, such as adding or removing node groups, are handled via CREATE NODEGROUP and DROP NODEGROUP commands, which require the cluster to be in a compatible state with no active data in affected groups. ndb_mgm is not required for cluster operation but is essential for administrative tasks like these.[30]
MySQL NDB Cluster Manager (MCM) automates many manual steps in cluster deployment and management, acting as a CLI-based agent and client system. For initial setup, MCM's client (mcm_client) allows creating a cluster deployment by specifying hosts, node types, and parameters via commands like create cluster --package=package_name --processhosts=ndbd@host1,mysqld@host2 cluster_name, which automatically distributes binaries, configures nodes, and starts the cluster. It includes parameter tuning based on workload types (e.g., low/medium/high write loads for web or real-time applications), optimizing settings like memory allocation and replication factors to match hardware capabilities. MCM supports rolling out configuration updates across nodes without full downtime, using its agent to propagate changes sequentially. This tool is particularly useful for on-premises environments, reducing setup time from hours to minutes compared to manual configuration.[31]
Monitoring in MySQL Cluster relies heavily on ndb_mgm's real-time commands for assessing health and performance. The SHOW command displays cluster overview, including node IDs, types, statuses (e.g., STARTED, NO_CONTACT), and versions, while STATUS node_id provides detailed states for specific nodes. For performance insights, REPORT MEMORY node_id outputs usage statistics, such as data memory (e.g., 5% utilized) and index memory allocation, helping identify bottlenecks in transactions or storage. Transaction monitoring includes commands like ENTER [SINGLE USER MODE](/page/Single-user_mode) to isolate access for maintenance, restricting operations to a single SQL node. These commands enable proactive issue detection, such as hung processes or memory exhaustion, by polling node states continuously. MCM enhances monitoring by automatically checking OS and process levels across hosts, alerting on dead or unresponsive nodes.[32]
Cluster-wide settings are defined in two key configuration files: config.ini for the management server and my.cnf for SQL nodes. The config.ini file, read by ndb_mgmd, specifies global parameters in sections like [NDBD DEFAULT] for data node defaults (e.g., MaxNoOfTables, MaxNoOfOrderedIndexes) and [MGM] for management node details, including host IPs and arbitration ranks. It supports up to 255 nodes and must be identical across all management servers for redundancy. Individual node overrides appear in [ndbd], [mgmd], or [mysqld] sections. The my.cnf file configures SQL nodes with the [mysqld] section, enabling the NDB storage engine via ndbcluster or --ndbcluster startup option, and setting connection strings like ndb-connectstring=mgmt_host:1186 to link to the management node. Changes to these files require rolling restarts to apply without downtime. The management node role facilitates this by storing and distributing the configuration to all participants.[33]
Rolling upgrades, supported natively through ndb_mgm and MCM, allow version updates without full cluster downtime by performing sequential restarts. The process begins with management nodes: stop ndb_mgmd, upgrade binaries, and restart. Data nodes follow in groups, using RESTART node_id -i for initial restarts that clear local recovery logs, ensuring compatibility across versions (e.g., from 8.0 to 8.4). SQL nodes are upgraded last, as they can tolerate mixed versions better. MCM automates this via upgrade cluster commands, verifying compatibility and handling binary distribution. No schema changes or backups are permitted during the process to avoid inconsistencies, and all nodes of one type must complete before proceeding to the next. This method maintains availability, with the cluster remaining operational as long as a majority of data nodes (NoOfReplicas) are active. Compatibility is ensured between minor releases, but major upgrades may require specific sequences.[34]
Kubernetes Integration
The MySQL NDB Operator is a Kubernetes operator designed to automate the deployment, management, scaling, and failover of MySQL NDB Clusters within Kubernetes environments. It leverages Custom Resource Definitions (CRDs), specifically theNdbCluster resource, to declaratively define the desired cluster state, including the number of management nodes, data nodes, and SQL nodes. The operator continuously monitors the cluster through a reconciliation loop, ensuring that the actual state aligns with the specified configuration while handling tasks such as pod restarts and configuration updates with minimal manual intervention.
For data persistence, the operator employs Kubernetes StatefulSets to manage data nodes, providing stable network identities and ordered deployment/scaling. Each data node pod is associated with a PersistentVolumeClaim (PVC) for local storage volumes, ensuring that NDB data survives pod rescheduling or node failures. This setup supports the in-memory and disk-based storage models of MySQL NDB Cluster, with the operator automatically provisioning storage based on the cluster's data distribution requirements.[35]
Installation of the MySQL NDB Operator is facilitated through official Helm charts, which streamline the process by deploying the CRDs, operator pod, and validating webhook server in a single command. Users add the Helm repository at https://mysql.github.io/mysql-ndb-operator/ and install with configurable parameters such as image pull policies and namespace scoping. The charts support both cluster-wide and namespace-scoped deployments, making it adaptable to various Kubernetes setups.[36]
The operator enables multi-node cluster configurations, supporting up to 255 nodes in total as per NDB Cluster limits, with examples including multiple data nodes for sharding and replication. It enforces default pod anti-affinity rules—using preferredDuringSchedulingIgnoredDuringExecution—to distribute management, data, and SQL node pods across different worker nodes, reducing the risk of correlated failures. Users can customize these via the NdbClusterPodSpec.affinity field, along with nodeSelector for targeted node placement. Additionally, resource management is handled through the NdbClusterPodSpec.resources field, allowing specification of CPU and memory requests and limits for each node type, with defaults calculated by the operator for data nodes based on cluster size.[37][38]
As of 2025, the MySQL NDB Operator integrates with managed Kubernetes services from major cloud providers, including AWS EKS, Google GKE, and Azure AKS, leveraging their standard Kubernetes APIs for automated provisioning in cloud environments. This compatibility allows for scalable, fault-tolerant deployments without custom modifications, as demonstrated in operator releases supporting Kubernetes versions 1.23 and later.[39][40]
Configuration and Operations
MySQL NDB Cluster configuration involves setting parameters in theconfig.ini file to optimize performance, availability, and resource usage across data nodes, management nodes, and API nodes. Key parameters such as NoOfReplicas determine data redundancy, with a default and recommended value of 2 for production environments to balance fault tolerance against overhead from synchronization; increasing it to 3 or 4 enhances availability in high-risk scenarios but raises latency and CPU demands due to additional replica updates.[11] For performance tuning, BatchSize on API nodes (including SQL nodes) sets the default number of records per batch during data transfers, typically tuned to 256 or higher based on workload to reduce network overhead while avoiding excessive memory consumption on receivers.[41] Other related parameters like MaxUIBuildBatchSize control scan batch sizes during unique index builds, adjustable upward (e.g., from default 64) to accelerate initial loads but monitored to prevent impacting concurrent queries.[42]
Online operations in NDB Cluster enable dynamic scaling and maintenance without full downtime. Adding data nodes requires forming a new node group: update the config.ini with new node IDs and parameters, restart the management server, perform a rolling restart of existing data nodes, then start the new nodes to redistribute fragments automatically.[43] Removing nodes involves evacuating data via the management client (STOP command on the node, followed by reconfiguration and rolling restart), ensuring no data loss if NoOfReplicas exceeds 1. Schema changes support zero-downtime via ALTER TABLE with ALGORITHM=INPLACE, allowing concurrent DML for operations like adding columns (if nullable and dynamic) or indexes on NDB tables; a global schema lock briefly prevents other DDL but permits ongoing reads/writes.[44]
Performance monitoring leverages the ndbinfo database, a read-only NDB-backed schema accessible via SQL queries from any connected MySQL Server. Views like cluster_operations track aggregate metrics such as read/write operation rates per second across nodes (e.g., SELECT SUM(reads) FROM cluster_operations;), while counters provides per-node counts for transactions and scans to identify bottlenecks.[45] The memoryusage view monitors DataMemory and IndexMemory utilization (e.g., querying used vs. total percentages) to detect overloads early, with thresholds like 80% usage signaling the need for scaling.
Security configurations emphasize protecting inter-node communications and data access. Node authentication uses TLS mutual authentication in NDB 8.3 and later, where each node verifies peers via X.509 certificates signed by a cluster-specific CA, configured by generating keys with ndb_sign_keys and enabling via --ndb-tls-search-path during rolling restarts.[46] Encryption for transporters secures data in transit over TCP/IP, enforced cluster-wide with RequireTls=true in config.ini sections, supporting cipher suites like TLS_AES_256_GCM_SHA384 for compliance; private keys remain unencrypted for automated restarts but are restricted to read-only permissions (0400).[46]
Best practices for sizing focus on workload analysis to provision memory and nodes appropriately. For DataMemory, estimate total row size multiplied by rows and replicas (e.g., 1 million 100-byte rows with 2 replicas requires ~200MB, plus 30% buffer), ensuring physical RAM exceeds allocated memory by at least 20% to prevent swapping; set MinFreePct=5 to reserve space for restarts.[11] Tune MaxNoOfConcurrentOperations to 250,000 or more for high-throughput updates across multiple nodes, scaling linearly with data nodes. For multi-site setups, deploy active-active configurations using circular replication: configure bidirectional MySQL replication between clusters with unique server IDs (e.g., 1000-series for site A, 2000-series for site B) and IGNORE_SERVER_IDS to break loops, or --log-replica-updates=0 for efficiency; enable conflict resolution via the ndb_replication table with functions like NDB$MAX(timestamp) on update timestamps.[47]
Implementation
Node Types and Processes
MySQL Cluster employs several distinct node types, each implemented as specific processes that handle different aspects of cluster operation. Data nodes are responsible for storing and managing the actual data partitions, while SQL nodes provide the familiar MySQL interface for query execution. Management nodes oversee cluster coordination, and API nodes enable direct data access. These processes interact through a defined startup sequence to ensure reliable initialization and ongoing operation.[10] Data nodes run either the single-threadedndbd daemon or the multi-threaded ndbmtd daemon, both of which handle all data operations for tables using the NDBCLUSTER storage engine, including distributed transactions, node recovery, checkpointing, and backups. The ndbd process operates with a single main thread for asynchronous data reading, writing, and scanning, supported by auxiliary threads for file I/O and network transporters, but it may underutilize multi-core CPUs under heavy loads, potentially consuming up to two CPUs on multi-processor systems.[9] In contrast, ndbmtd is designed for better CPU utilization on multi-core systems, defaulting to single-threaded mode but configurable via parameters like MaxNoOfExecutionThreads to spawn multiple execution threads (e.g., four threads for improved parallelism), allowing it to leverage available cores more effectively without requiring data loss during switches from ndbd.[48] This multi-threaded approach enhances performance for demanding workloads, though both daemons can coexist in the same cluster.[11]
SQL nodes integrate as standard MySQL Server instances (mysqld) configured with the NDBCLUSTER storage engine via the --ndbcluster and --ndb-connectstring options, enabling them to connect to the cluster and treat NDB as a backend storage layer.[10] The NDB engine plugin within mysqld processes SQL statements from clients, parsing them and distributing operations—such as data reads, writes, and scans—across relevant data nodes based on the cluster's partitioning scheme, while supporting load balancing and failover through multiple SQL nodes. This setup allows seamless use of standard MySQL clients and drivers (e.g., PHP, Java) without altering application code.[10]
Management nodes, implemented via the ndb_mgmd process, provide configuration data to other nodes, manage startups and shutdowns, and perform arbitration to maintain cluster integrity during failures.[49] Arbitration involves election protocols where a coordinator (typically a management or SQL node with ArbitrationRank set to 1 for external candidates or 2 for internal) is selected to resolve network partitions and prevent split-brain scenarios; for instance, if a partition lacks quorum, the arbitrator issues a shutdown signal to isolated nodes based on parameters like ArbitrationDelay for voting timeouts.[49] This ensures high availability by prioritizing the partition with the majority of data nodes or an external arbitrator if configured.
API nodes represent lightweight client processes or applications that connect directly to the cluster for data access, bypassing the SQL layer for lower latency in specialized use cases.[10] These include tools like ndb_restore for backups and custom applications using the NDB API (C++), ClusterJ (Java), or Node.js connectors, which establish connections to data nodes via the NDB transport layer to perform transactions, scans, and event handling without invoking a full mysqld instance.[25] Such direct access supports high-throughput scenarios where SQL processing overhead is undesirable.
The startup sequence for cluster processes follows a phased protocol to ensure orderly initialization and integration. Management nodes start first, loading the configuration file and awaiting connections on designated ports. Data and API nodes then initialize by obtaining a node ID, allocating memory and ports, and entering phased startup: Phase 0 activates core blocks like NDBFS; Phase 1 establishes transporter connections, inter-block communications, and heartbeats for failure detection (e.g., via periodic signals between nodes); subsequent phases (2–9) handle schema setup, log file creation, checkpoint synchronization, node group formation, arbitrator election, and index rebuilding, with API nodes joining post-Phase 7.[50] For joining nodes, the protocol synchronizes data from existing replicas during restarts, using heartbeats to monitor liveness and trigger recovery if failures occur during the process.[50] This sequence supports both initial cluster formation and rolling restarts without downtime.
Networking Requirements
MySQL NDB Cluster relies on robust network infrastructure to ensure reliable inter-node communication, including data replication, heartbeats, and management operations. The default transport protocol is TCP/IP, which supports standard network topologies and is suitable for most deployments. For enhanced performance in low-latency environments, specialized interconnects such as InfiniBand can be utilized to achieve sub-millisecond communication times, though TCP/IP remains the primary and recommended option for production setups.[51][52] Bandwidth requirements specify a minimum of 100 Mbps Ethernet per host to support basic cluster operations, but production environments demand at least 1 Gbps to handle high-throughput workloads without bottlenecks. A dedicated private network or subnet is recommended to isolate cluster traffic and prevent interference from other applications. Latency is critical for optimal performance; round-trip times under 100 microseconds are ideal for local area networks (LANs), while high-latency networks, such as those in wide area network (WAN) environments, may require configuration adjustments to heartbeat intervals and timeouts to avoid false node failure detections.[53][54] Cluster communication employs unicast TCP/IP connections for both heartbeats—used to detect node failures—and data transfers between nodes, with no native support for multicast. Heartbeat intervals are configurable (default 1 second for data nodes) to accommodate varying network conditions, ensuring timely failure detection without unnecessary restarts.[51][11] Firewall configurations must allow inbound and outbound traffic on specific default ports to enable cluster functionality. The management node (ndb_mgmd) uses port 1186 for client connections and inter-node management. Data nodes (ndbd or ndbmtd) communicate via port 2202 for transporter links. SQL nodes (mysqld) expose port 3306 for standard MySQL client access, while their connections to the cluster utilize the management port. Additional dynamic ports may be allocated for TCP transporters between nodes, so monitoring and allowing a range (typically starting from 2200) is advisable in firewalled setups. Ports can be customized in the config.ini file if needed.[55]Backup and Recovery
MySQL NDB Cluster provides native support for online backups through thendb_mgm management client, enabling consistent snapshots of the entire cluster without interrupting ongoing operations or locking tables. The BACKUP command initiates this process, capturing both data and metadata across all data nodes in parallel, with the backup files stored locally on each node by default. These backups are designed for high availability environments, minimizing downtime and supporting large-scale distributed data sets.[56]
The consistency of these online backups is maintained via Local Checkpoints (LCPs) and Global Checkpoints (GCPs), which are integral to NDB's durability mechanisms. An LCP flushes modified data pages from memory to disk on a single data node approximately every few minutes, ensuring local persistence without affecting other nodes. Meanwhile, a GCP synchronizes transaction commits across all nodes every 2-3 seconds by advancing a global epoch and flushing the redo log to disk, providing a cluster-wide consistent point for recovery. During a backup, the system coordinates with the current GCP to include all committed changes up to that point, while ongoing LCPs help manage disk writes efficiently.[57]
Restoration from these native backups is handled by the ndb_restore utility, a command-line tool that must be executed on each data node using the backup files generated by the BACKUP command. It supports selective restoration of specific tables or databases, as well as full cluster recovery, by first restoring metadata (such as table definitions) and then data rows. For point-in-time recovery, especially in replicated setups, the --restore-epoch option applies the backup up to a specific global epoch, allowing integration with binary logs or replication streams for precise rollback. The tool also offers options like --num-slices for parallel processing to accelerate large restores and --rebuild-indexes for optimizing secondary indexes post-restoration. Disk-persisted LCP files from the backup facilitate rapid recovery by providing a baseline state, with in-memory reconstruction handled during node restarts.[58]
In addition to native backups, logical backups are available using mysqldump, which generates portable SQL statements to recreate schemas, data, and objects accessible via the MySQL SQL nodes. This method is particularly useful for exporting NDB tables to non-NDB environments or for schema-only backups, though it operates at the SQL layer and may not capture all NDB-specific features like distribution keys. Options such as --single-transaction ensure consistency for InnoDB-compatible transactions, but for pure NDB tables, it relies on the cluster's inherent ACID properties without additional locking. Logical backups complement native ones for scenarios requiring interoperability or archiving.[59]
For disaster recovery across sites, MySQL NDB Cluster employs asynchronous multi-cluster replication, where one cluster acts as the primary and replicates to a secondary cluster using built-in NDB epoch-based logging. This setup allows failover to the secondary site in case of primary failure, with native backups from the primary serving as a seed for initializing the secondary. Tools like the management client facilitate monitoring replication lag and resuming after recovery, ensuring minimal data loss bounded by the GCP interval. MySQL Enterprise Backup can supplement this for SQL node-specific components, though native NDB mechanisms handle the core distributed data.