As discussed in this previous article about MySQL NDB Cluster overview, it is built on the NDBCLUSTER storage engine for MySQL and provides high availability and scalability. This article explains installation of MySQL NDB Cluster on Linux environment. The detailed documentation is available at the official MySQL Developer Documentation page.

To avoid a single point of failure, the example cluster is installed on separate hosts that represent each of the four nodes needed to run the cluster. So this is a four node, four host cluster that we are setting up. Instead of different hosts, virtual hosts also can be used for this setup. Each node/host is assigned separate IP addresses on the Ethernet Network as follows:

Management node (mgmd) - 192.168.0.10
SQL node (mysqld) – 192.168.0.20
Data node “A” (ndbd) – 192.168.0.30
Data node “B” (ndbd) – 192.168.0.40

NDB Cluster setup on multi-computer Ethernet network

Installing the NDB Cluster Binary Release

Install the NDB Cluster binary from the MySQL NDB Cluster download page. At the time of this article, NDB Cluster 7.5 binary archive is available as mysql-cluster-gpl-7.5.8-linux-i686-glibc23.tar.gz. Download it to /var/tmp.

SQL Node setup

A user named mysql on group mysql is needed on the system. Verify these are present after login as root user and if not, manually create them.

Unpack the archive to /usr/local and create a symbolic link named mysql to the mysql directory found inside the package.

 shell> cd /var/tmp
 shell> tar -C /usr/local -xzvf mysql-cluster-gpl-7.5.8-linux2.6.tar.gz
 shell> ln -s /usr/local/mysql-cluster-gpl-7.5.8-linux2.6-i686 /usr/local/mysql

Now the symbolic link mysql points to the NDB Cluster Directory mysql-cluster-gpl-7.5.8-linux2.6-i686 inside /usr/local.

After changing to the mysql directory, initialize the MySQL installation (mysqld) by creating the data directory and populating the mysql system table.

With –initialize option a random password will be generated to the MySQL root user. If no password generation is needed during this stage, use –initialize-insecure option instead of –initialize and set password later. The database base directory and data directory also can be specified during initialization:

 shell> mysqld --initialize --user=mysql
 --basedir=/usr/local/mysql
 --datadir=/usr/local/mysql/data

Set permission for root and mysql users over the database directories and assign the base directory to the group mysql:

 shell> chown -R root .
 shell> chown -R mysql data
 shell> chgrp -R mysql .

In order for mysqld to start during system startup, add the startup script to /etc/rd.d/init.d scripts, make it executable and add it as startup service.

 shell> cp support-files/mysql.server /etc/rc.d/init.d/
 shell> chmod +x /etc/rc.d/init.d/mysql.server
 shell> chkconfig --add mysql.server

Thus mysql.server starts the SQL node. For each host machine containing SQL nodes, above steps are to be repeated.

Data node setup

Data nodes do not need the mysqld binary. To setup the data node, either the single-threaded NDB Cluster data node executable ndbd or multi-threaded executable ndbmtd is required. These binaries are also included in the NDB Cluster binary downloaded above. Download and unpack the binary in the data node host. The data node binaries will be inside the bin directory of the unpacked cluster binary directory. From the downloaded location (assuming as /var/tmp), move the data node binaries to the /usr/local/bin location for binaries. Perform the below operations as root user.

 shell> cd /var/tmp
 shell> tar -zxvf mysql-5.7.19-ndb-7.5.8-linux-i686-glibc23.tar.gz
 shell> cd mysql-5.7.19-ndb-7.5.8-linux-i686-glibc23
 shell> cp bin/ndbd /usr/local/bin/ndbd
 shell> cp bin/ndbmtd /usr/local/bin/ndbmtd

Delete the directory that contains the unpacked NDB cluster archive.

Now make both the data node binaries (ndbd and ndbmtd) executable:

shell> cd /usr/local/bin
shell> chmod +x ndb*

Repeat the above steps for each data node host. Note that the MySQL data directory is /usr/local/mysql/data.

Management node setup

Management node does not need the mysqld binary. It needs only the NDB Cluster management server ndb_mgmd. The management client named ndb_mgm is highly desirable to use the management node. Both binaries are located in the bin folder inside the NDB Cluster binary downloaded (inside /var/tmp) as mentioned above. As root user, unpack the cluster binary, copy the management node and client binaries to /ust/local/bin and make them executable.

 shell> cd /var/tmp
 shell> tar -zxvf mysql-5.7.19-ndb-7.5.8-linux2.6-i686.tar.gz
 shell> cd mysql-5.7.19-ndb-7.5.8-linux2.6-i686
 shell> cp bin/ndb_mgm* /usr/local/bin

Delete the directory that contains the unpacked NDB cluster archive.

Now make both the management node and client binaries executable:

 shell> cd /usr/local/bin
 shell> chmod +x ndb_mgm*

NDB Cluster Initial Configuration

After downloading and installing the SQL, Data and Management nodes, now it is time to configure the NDB Cluster.

Data and SQL Nodes Configuration

Each of the data and SQL nodes need the configuration file my.cnf to specify the connection string to connect to the management node and another parameter that enables the NDBCLUSTER storage engine on this MySQL Server. Create the my.cnf file, if not already exists, or open with vi editor and add the configuration parameters.

shell> vi /etc/my.cnf

Below shown is the part of the my.cnf file that holds the NDB Cluster related parameters.

[mysqld]

# Options for mysqld process:
ndbcluster # run NDB storage engine
[mysql_cluster]
# Options for NDB Cluster processes:
ndb-connectstring=192.168.0.10 # location of management server

Note: the IP address of management node above is as per this example setup only and needs to be replaced by the actual IP address in your case.

In our example, these my.cnf settings are to be added to Data Node A, Data Node B and SQL Node hosts.

Management Node Configuration

The Management Node needs a config.ini file that specifies the following information:

1. How many replicas (data nodes) to be managed in the cluster.
2. How much memory is to be allocated for data and indexes on each data node.
3. IP address or Domain Names (with DNS Resolution) of Data and SQL Nodes in the cluster.
4. Data directories of each Data Node to save database data.

Create the configuration directory for the Management Node and create config.ini file inside it, running as root user.

shell> mkdir /var/lib/mysql-cluster
shell> cd /var/lib/mysql-cluster
shell> vi config.ini

For our example setup, the config.ini of Master Node should read as shown below after writing the settings:

[ndbd default]

# Options affecting ndbd processes on all data nodes:
NoOfReplicas=2 # Number of replicas
DataMemory=80M # How much memory to allocate for data storage
IndexMemory=18M # How much memory to allocate for index storage
                  # For DataMemory and IndexMemory, we have used the
                  # default values. Since the "world" database takes up
                  # only about 500KB, this should be more than enough for
                  # this example NDB Cluster setup.
ServerPort=2202 # This the default value; however, you can use any
                  # port that is free for all the hosts in the cluster
                  # Note1: It is recommended that you do not specify the port
                  # number at all and simply allow the default value to be used
                  # instead
                  # Note2: The port was formerly specified using the PortNumber
                  # TCP parameter; this parameter is no longer available in NDB
                  # Cluster 7.5.

[ndb_mgmd]

# Management process options:
HostName=192.168.0.10 # Hostname or IP address of MGM node
DataDir=/var/lib/mysql-cluster # Directory for MGM node log files

[ndbd el_id='59a5076ed1de1']

# Options for data node "A":
# (one [ndbd el_id='59a5076ed1e2b'] section per data node)
HostName=192.168.0.30 # Hostname or IP address
NodeId=2 # Node ID for this data node
DataDir=/usr/local/mysql/data # Directory for this data node's data files

[ndbd el_id='59a5076ed1e68']

# Options for data node "B":
HostName=192.168.0.40 # Hostname or IP address
NodeId=3 # Node ID for this data node
DataDir=/usr/local/mysql/data # Directory for this data node's data files

[mysqld]

# SQL node options:
HostName=192.168.0.20 # Hostname or IP address
# (additional mysqld connections can be
# specified for this node for various
# purposes such as running ndb_restore)

Note: the IP addresses are for this example setup only and need to be replaced by your actual IP addresses or Domain names.

The above are the minimal settings for the my.cnf and config.ini files.

Initial Startup of the NDB Cluster

The nodes must be started in the following order:

1. Management Nodes
2. Data Nodes
3. SQL Nodes.

Start the management node process ndb_mgmd in the management node host. During the initial startup, the node must be supplied as parameter, the location of its configuration file, config.ini, through the option -f or –config-file:

shell> ndb_mgmd -f /var/lib/mysql-cluster/config.ini

Next start the data nodes, ndbd processes, at the data node hosts:

shell> ndbd

Now start the SQL node process, mysqld, on the SQL node host:

shell> cd mysql_installation_directory
shell> bin/mysqld_safe &

OR

shell> mysql.server start
In the example, mysql_installation_directory is /usr/local/mysql

If the setup is correct and everything has gone well, the cluster will be operational now. Use the ndb_mgm client to test the cluster. A running cluster should produce the below output for this example:

shell> ndb_mgm<>
-- NDB Cluster -- Management Client --
ndb_mgm> SHOW

Connected to Management Server at: localhost:1186

Cluster Configuration

---------------------
 [ndbd(NDB)] 2 node(s)
 id=2 @192.168.0.30 (Version: 5.7.19-ndb-7.5.8, Nodegroup: 0, *)
 id=3 @192.168.0.40 (Version: 5.7.19-ndb-7.5.8, Nodegroup: 0)
 [ndb_mgmd(MGM)] 1 node(s)
 id=1 @192.168.0.10 (Version: 5.7.19-ndb-7.5.8)
 [mysqld(API)] 1 node(s)
 id=4 @192.168.0.20 (Version: 5.7.19-ndb-7.5.8)

Here the [mysqld(API) ] reference is for the SQL Node process mysqld and it is acting as the NDB Cluster API node to accept data access requests from clients.

Now you can create tables using the NDBCLUSTER storage engine:

CREATE TABLE tbl_name (col_name column_definitions) ENGINE=NDBCLUSTER;

Alter existing tables to NDBCLUSTER storage engine, if any:

ALTER TABLE tbl_name ENGINE=NDBCLUSTER;

To shutdown the cluster, issue below command on the management node host:

shell> ndb_mgm -e shutdown

To restart the cluster, issue below command on the management node host:

shell> ndb_mgmd -f /var/lib/mysql-cluster/config.ini

In data node hosts:

shell> ndbd

In SQL node hosts, from the mysql installation directory:

shell> bin/mysqld_safe &

OR

shell> mysql.server start