Skip to main content
Pure Technical Services

MySQL NDB Cluster Implementation and Best Practices

Currently viewing public documentation. Please login to access the full scope of documentation.

MySQL NDB Cluster (MySQL Cluster) is a deployment type for MySQL providing high availability and scalability. MySQL Cluster makes use of the NDB/NDBCLUSTER storage engine for MySQL, and separates processes across disparate systems (nodes). There are three different node types:

  • Management Nodes - Performs configuration and monitoring of the cluster. This node type will start or restart the cluster. The ndb_mgmd process is responsible for the node type.
  • Data Nodes - These nodes store database data. Tables are sharded across all of the data nodes in the cluster resulting in the ability to handle load balancing, replication, failover, and self-healing. The ndbd (single threaded) or ndbmtd (multi-threaded) process is responsible for this node type.   
  • SQL Node - These nodes connect to the data nodes to store and retrieve data. The mysqld process is used to operate this node type. 

More information about MySQL Cluster can be found in the reference documentation

The operating system requirements are similar to a standalone MySQL Implementation. 

Implementing MySQL Cluster on FlashArray

Regardless of the deployment operating system, the following steps will showcase a recommended configuration for MySQL Cluster on FlashArray.

Installing MySQL Cluster

The installation process for the respective operating systems can be found in the MySQL documentation. 

Prepare Storage for MySQL Cluster 

For each of the node types being deployed the following storage should be provisioned from FlashArray.

Node Type  Storage  Requirement  Volumes 
Management Node

The management node will store event logs in the form of Cluster Logs and Node Logs. Ensure that the volume size is sufficient to store events as long as they need to be retained. 

It is possible to set a specific LogDestination but it is recommended that the logs are sent to the same destination as the datadir.  

Per Management Node:

1 x min 128Gb volume. 

Data Node

Data nodes store persisted database information. Each Data node needs to have (at minimum) a single volume with sufficient space for checkpoint data and redo logs. 

It is recommended to have a separate volume/location for backups. 

Per Data Node: 

1 x data storage volume (1.5x the amount of database data to be stored).

1 x backup volume (Number of retained backups x database data size)

The backup volume can be an NFS volume exported from FlashArray or FlashBlade.

SQL Node 

SQL Nodes only require enough storage capacity for executables and local log files. 

An SQL Node can store InnoDB data locally but this guide assumes only NDB clustering is required.  

Per SQL Node:

1 x min 50Gb volume. 

As a MySQL cluster operates a Shared-Nothing architecture, the volumes only need to be connected to the respective node types.

Volumes are not shared between nodes thus each node type needs to have its own volume(s).

Once each of the nodes has the relevant storage connected to them, ensure each contains a file system and is mounted at the required locations. 

Linux Example file system locations 

XFS and EXT4 are both acceptable file systems for FlashArray volumes holding NDB data. 

  • Management Node: /var/lib/mysql-cluster (DataDir)
  • Data Node:  /usr/local/mysql/data (DataDir)
  • Data Node:  /usr/local/mysql/backup(BackupDataDir)
  • SQL Node: /var/lib/mysql (dataDir)

Microsoft Windows Example File System Locations 

The NTFS filesystem is recommended for FlashArray volumes holding NDB data on Microsoft WIndows.

  • Management Node: C:\Users\user1\my_cluster\ndb_data (DataDir)
  • Data Node:  c:\Users\user1\my_cluster\ndb_data (DataDir)
  • Data Node:  c:\Users\user1\my_cluster\ndb_backup (BackupDataDir)
  • SQL Node: c:\\Users\\user1\\my_cluster\\mysqld_data (dataDir)

Initial MySQL Cluster Configuration 

On the management node, create a file (config.ini) on the management node directory intended for datadir

An example starting configuration can be found in the MySQL documentation.  

The following sections and variables can be used for a basic deployment:

Parameter Name Description Default Value Suggested Value
[ndb_mgmd default]\DataDir The Data Directory on the management node. N/A N/A
[ndb_mgmd]\HostName

The hostname of the management node.

For multiple management servers duplicate the [ndb_mgmd] section. 

N/A N/A
[ndbd default]\NoOfReplicas

The number of fragment replicas. A minimum of two is required to guarantee the availability of data.  

2 Min 2 NoOfReplicas for redundancy. Max 4. 
[ndbd default]\DataMemory This is the amount of memory available to data being stored in the NDB cluster. 98M > 1Gb
[ndbd default]\
NoOfFragmentLogFiles
Sets the number of REDO log files per node.  16 If checkpointing is slow increase this value. 
[ndbd default]\ODirect

If this parameter is enabled then NDB will attempt to use O_DIRECT writes for storage operations. 

false Recommended for Linux systems when using EXT4.
[ndbd default]\
RealTimeScheduler
If enabled data node threads will be done using real-time scheduling.  false Enabling real-time scheduling can increase Data Node throughput.
[ndbd default]\
TimeBetweenGlobalCheckpoints
Defines the time between global checkpoints in milliseconds.  2000  < 3000 
[ndbd default]\
RedoBuffer
Sets the size of the buffer in which the REDO log is written. 32MB Increase this value for systems with frequent updates to data. 

[ndbd default]\

MaxNoOfExecutionThreads

Only if using ndbmtd affects the number of execution threads on a Data Node.  2 Depending on CPU > 16
[ndbd default]\MaxDiskDataLatency

Controls the maximum allows mean latency for disk access. 

Recommendation for FlashArray and controlling IO Subsystem pressure: QOS.

0 0
[ndbd]\
HostName
The Hostname or IP Address of the Data Node. N/A N/A
[ndbd]\
NodeId
(Optional) The Node ID of the Data Node. N/A N/A
[ndbd]\
DataDir
The Data directory where NDB data will be stored on a Data Node.  N/A N/A
[ndbd]\BackupDataDir The directory in which backups are placed. DataDir/FilesystemPath

Path to FlashArray MySQL Cluster Backup Volume for Node for NFS share for Node. 

Each Node should have its own Backup directory. 

[mysqld]\
HostName
The Hostname of a SQL Node.  N/A N/A

This is an example config.ini file: 

[ndb_mgmd default]
# Directory for management node log files
DataDir=/var/lib/mysql-cluster

[ndb_mgmd]
# Management Node IP Address
HostName=10.21.227.36

[ndbd default]
NoOfReplicas=2      # Number of replicas
DataMemory=368G     # Memory allocate for data storage
TimeBetweenGlobalCheckpoints=3000

[ndbd]
# Data Node
HostName=10.21.227.39
NodeId=2
DataDir=/usr/local/mysql/data
BackupDataDir=/usr/local/mysq/backup

[ndbd]
# Data Node 
HostName=10.21.227.40
NodeId=3
DataDir=/usr/local/mysql/data
BackupDataDir=/usr/local/mysq/backup

[mysqld]
# SQL Node 
HostName=10.21.227.37

[mysqld]
#SQL Node 
HostName=10.21.227.38

 Initial Startup of the MySQL Cluster 

Once the config.ini has been created the cluster can be started.

Step 1. On a Management Node execute the ndb_mgmd command with the confi.ini file path:

ndb_mgmd -f /path/to/config.ini
[root@DB-06 ~]# ndb_mgmd -f /var/lib/mysql-cluster/config.ini
MySQL Cluster Management Server mysql-8.0.23 ndb-8.0.23 

Step 2. On the Data Nodes execute the ndbd or ndbmtd command to start the respective process after setting the option file with the ndb connection string:

In Linux the option file is located at /etc/my.cnf and in Microsoft Windows it is located at C:\ProgramData\MySQL\MySQL Server <version>\my.ini

[mysql_cluster]
# Options for NDB Cluster processes:
ndb-connectstring=10.21.227.36 # location of management server
ndbd
ndbmtd
[root@DB-09 ~]# ndbmtd
2021-03-05 00:55:20 [ndbd] INFO     -- Angel connected to '10.21.227.36:1186'
2021-03-05 00:55:20 [ndbd] INFO     -- Angel allocated nodeid: 2

Ensure the firewall is not blocking the ndbd, ndbmtd, or ndb_mgmd process on the respective node. 

Data Nodes communicate with the Management Node on port 118. Ensure this port is open for in the firewall. 

 Step 3. Start the MySQL process on the SQL Nodes after setting the option file properties for the ndb cluster.

[mysqld]
# Options for mysqld process:
ndbcluster                      # run NDB storage engine
ndb-use-exact-count=0
ndb-index-stat-enable=0
ndb-force-send=1
optimizer-switch=engine_condition_pushdown=on
default_storage_engine=ndbcluster
port=3306
max_allowed_packet=1073741824
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysql_cluster]
# Options for NDB Cluster processes:
ndb-connectstring=10.21.227.36 # location of management server 

Once the option file has been created/edited on the SQL nodes, the MySQL cluster can be started.

Microsoft Windows - Run the following command in a PowerShell terminal (for those running MySQL 8 the service name is mysql80, for mysql 5.7 it is mysql57):

net start MySQL80

Linux - Run the following command in a terminal:

systemctl start mysqld