MySQL Cluster

Modified: 28 Apr 2022 01:26 UTC

The Joyent MySQL Cluster image is a SmartOS base64 image pre-configured with MySQL Cluster, a highly scalable, real time, ACID compliant database.

You can find more information about MySQL Cluster at their website.

Overview

MySQL Cluster comes pre-installed and configured as three SMF services to control each management node (ndb_mgmd), data node (ndbd), and api node (mysqld). The instance comes with default configurations in place to easily get a cluster up and running.

Directory layout

The directory and file layout is as follows:

Directory/File Description
/opt/local/sbin Holds MySQL server binaries
/opt/local/etc/my.cnf The defaults configuration file for MySQL Cluster nodes. Default values are accepted from this file for each type of node.
/opt/local/etc/mysql-cluster.ini The configuration file for the MySQL Cluster management node ndb_mgmd
/var/mysql MySQL Cluster data directory which also holds log files as ndb*.log.

Services layout

The SMF service layout is as follows:

SMF Service Description
svc:/pkgsrc/mysql-cluster:ndb_mgmd MySQL Cluster management node service
svc:/pkgsrc/mysql-cluster:ndbd MySQL Cluster data node service
svc:/pkgsrc/mysql-cluster:mysqld MySQL Cluster api node service

Configuring MySQL Cluster

By default all MySQL cluster services come disabled. Configuring the cluster starts with setting up the management node, and then adding in data nodes and api nodes.

In this example we'll use these hosts for each type of node:

IP/Host Description Size
192.168.0.5 MySQL Cluster management node 2GB instance
192.168.0.6 MySQL Cluster data node 2GB instance
192.168.0.7 MySQL Cluster data node 2GB instance
192.168.0.8 MySQL Cluster api node 2GB instance

You can find additional documentation on configuring MySQL Cluster at the MySQL Cluster reference documentation:

Management node (ndb_mgmd)

The management node is the node that controls the cluster which all nodes connect to. It uses the /opt/local/etc/mysql-cluster.ini file to determine how many data nodes the cluster will have (replicas), how much memory to use (for data, indexes), and where to find the data nodes and api nodes. It also has a client (ndb_mgm) which you can connect to see the status of the cluster.

By default the configuration comes semi populated, but needs a few adjustments. We need to adjust how many data nodes in the cluster (replicas), how much memory we'll use for data and indexes, and where all of the nodes are located. Because everything must fit in memory the DataMemory IndexMemory settings are very important, and you must have enough memory to support your data, indexes or adding new nodes will fail. You must also adjust NoOfReplicas accordingly or ndb_mgmd will fail to start. Here's the configuration we'll use:

/opt/local/etc/mysql-cluster.ini

    [ndbd default]
    # Options affecting ndbd processes on all data nodes:
    NoOfReplicas=2      # Number of replicas
    DataMemory=1000M    # How much memory to allocate for data storage
    IndexMemory=300M    # 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 Cluster setup.

    [ndb_mgmd]
    # Management process options:
    hostname=192.168.0.5           # Hostname or IP address of MGM node
    datadir=/var/mysql             # Directory for MGM node log files

    [ndbd]
    # Options for data node "A":
                                   # (one [ndbd] section per data node)
    hostname=192.168.0.6           # Hostname or IP address
    datadir=/var/mysql             # Directory for this data node's data files

    [ndbd]
    # Options for data node "B":
    hostname=192.168.0.7           # Hostname or IP address
    datadir=/var/mysql             # Directory for this data node's data files

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

After this is in place we start the management service with:

    # svcadm enable pkgsrc/mysql-cluster:ndb_mgmd

We can connect to the management cluster and see the status with ndb_mgm show command:

    # ndb_mgm
    -- NDB Cluster -- Management Client --
    ndb_mgm> show
    Connected to Management Server at: 192.168.0.5:1186
    Cluster Configuration
    ---------------------
    [ndbd(NDB)]     2 node(s)
    id=2 (not connected, accepting connect from 192.168.0.6)
    id=3 (not connected, accepting connect from 192.168.0.7)

    [ndb_mgmd(MGM)] 1 node(s)
    id=1    @192.168.0.5  (mysql-5.6.15 ndb-7.3.4)

    [mysqld(API)]   1 node(s)
    id=4 (not connected, accepting connect from 192.168.0.8)

You can find additional documentation on management nodes at the MySQL Cluster reference documentation:

Data node (ndbd)

Data nodes store copies or replicas of the data across the cluster. The data nodes need to be on their own instances, and use the configuration file /opt/local/etc/my.cnf which only require a couple of settings. The first setting is ndbcluster which tells it to use the NDBCLUSTER engine and the second setting is ndb-connectstring which tells the data node where to connect to for the management node. By default this file already comes populated correctly, we just need to modify ndb-connectstring under the ndbd section to connect to our management node 192.168.0.5 In this example here's the configuration we'll use:

/opt/local/etc/my.cnf

    [mysqld]
    ndbcluster
    bind-address=192.168.0.6
    ndb-connectstring=

    [ndbd]
    bind-address=192.168.0.6
    ndb-connectstring=192.168.0.5

    [ndb_mgmd]
    configdir=/var/mysql
    bind-address=192.168.0.6

    [ndb_mgm]
    ndb-connectstring=192.168.0.6

We can also adjust this setting with the svccfg command:

    # svccfg -s pkgsrc/mysql-cluster:ndbd setprop ndbd/ndb_connectstring = "192.168.0.5"
    # svcadm refresh pkgsrc/mysql-cluster:ndbd

And verify it has been set with:

    # svcprop pkgsrc/mysql-cluster:ndbd | grep ndb_connectstring
    ndbd/ndb_connectstring astring 192.168.0.5

Be aware that setting it with svccfg will take precedence over using the configuration file. If you want to use the configuration file instead then set this value back to empty.

Now we can start the data service with:

    # svcadm enable pkgsrc/mysql-cluster:ndbd

This process must be repeated on the second data node 192.168.0.7.

From the management node, in the management client (ndb_mgm) you should see the node has connected by running 'show'.

You can find additional documentation on data nodes at the MySQL Cluster reference documentation:

API node (mysqld)

Api or SQL nodes are the nodes that access the cluster data. This is a traditional MySQL server (mysqld) that uses the ndbcluster engine, and where your application should connect to. To connect you will need to login using root user and the mysql_pw metadata for the password. The configuration file Api nodes use is /opt/local/etc/my.cnf and all we need to modify is the ndb-connectstring setting under the mysqld section to tell it the management node 192.168.0.5. Below is the example:

/opt/local/etc/my.cnf

    [mysqld]
    ndbcluster
    bind-address=192.168.0.8
    ndb-connectstring=192.168.0.5

    [ndbd]
    bind-address=192.168.0.8
    ndb-connectstring=

    [ndb_mgmd]
    configdir=/var/mysql
    bind-address=192.168.0.8

    [ndb_mgm]
    ndb-connectstring=192.168.0.8

We can also adjust this setting with the svccfg command:

    # svccfg -s pkgsrc/mysql-cluster:mysqld setprop mysqld/ndb_connectstring = "192.168.0.5"
    # svcadm refresh pkgsrc/mysql-cluster:mysqld

And verify it has been set with:

    # svcprop pkgsrc/mysql-cluster:mysqld | grep ndb_connectstring
    mysqld/ndb_connectstring astring 192.168.0.5

Be aware that setting it with svccfg will take precedence over using the configuration file. If you want to use the configuration file instead then set this value back to empty.

Now we can start the data service with:

    # svcadm enable pkgsrc/mysql-cluster:mysqld

From the management node, in the management client (ndb_mgm) you should see the node has connected by running 'show'.

To login to the mysqld:

    # mysql -uroot -p$(mdata-get mysql_pw)

You can find additional documentation on api/sql nodes at the MySQL Cluster reference documentation:

Determine amount memory your DB needs for MySQL Cluster

To determine exactly how much memory your database will need to use for MySQL Cluster (NDBCLUSTER engine) you can use ndb_size.pl. This script will need access to the MySQL database in which you are running it on. You may need to pkgin install perl and some dependencies.

You can find additional documentation on ndb_size.pl at the MySQL Cluster reference documentation:

Monitoring MySQL Cluster

Management client (ndb_mgm)

From the management node (ndb_mgmd) you can monitor the MySQL Cluster using the management client ndb_mgm. 'help' will list all available commands to monitor the status of the cluster:

    # ndb_mgm
    -- NDB Cluster -- Management Client --
    ndb_mgm> help
    ---------------------------------------------------------------------------
     NDB Cluster -- Management Client -- Help
    ---------------------------------------------------------------------------
    HELP                                   Print help text
    HELP COMMAND                           Print detailed help for COMMAND(e.g. SHOW)
    SHOW                                   Print information about cluster
    CREATE NODEGROUP <id>,<id>...          Add a Nodegroup containing nodes
    DROP NODEGROUP <NG>                    Drop nodegroup with id NG
    START BACKUP [NOWAIT | WAIT STARTED | WAIT COMPLETED]
    START BACKUP [<backup id>] [NOWAIT | WAIT STARTED | WAIT COMPLETED]
    START BACKUP [<backup id>] [SNAPSHOTSTART | SNAPSHOTEND] [NOWAIT | WAIT STARTED | WAIT COMPLETED]
                                           Start backup (default WAIT COMPLETED,SNAPSHOTEND)
    ABORT BACKUP <backup id>               Abort backup
    SHUTDOWN                               Shutdown all processes in cluster
    CLUSTERLOG ON [<severity>] ...         Enable Cluster logging
    CLUSTERLOG OFF [<severity>] ...        Disable Cluster logging
    CLUSTERLOG TOGGLE [<severity>] ...     Toggle severity filter on/off
    CLUSTERLOG INFO                        Print cluster log information
    <id> START                             Start data node (started with -n)
    <id> RESTART [-n] [-i] [-a] [-f]       Restart data or management server node
    <id> STOP [-a] [-f]                    Stop data or management server node
    ENTER SINGLE USER MODE <id>            Enter single user mode
    EXIT SINGLE USER MODE                  Exit single user mode
    <id> STATUS                            Print status
    <id> CLUSTERLOG {<category>=<level>}+  Set log level for cluster log
    PURGE STALE SESSIONS                   Reset reserved nodeid's in the mgmt server
    CONNECT [<connectstring>]              Connect to management server (reconnect if already connected)
    <id> REPORT <report-type>              Display report for <report-type>
    QUIT                                   Quit management client

    <severity> = ALERT | CRITICAL | ERROR | WARNING | INFO | DEBUG
    <category> = STARTUP | SHUTDOWN | STATISTICS | CHECKPOINT | NODERESTART | CONNECTION | INFO | ERROR | CONGESTION | DEBUG | BACKUP | SCHEMA
    <report-type> = BACKUPSTATUS | MEMORYUSAGE | EVENTLOG
    <level>    = 0 - 15
    <id>       = ALL | Any database node id

    For detailed help on COMMAND, use HELP COMMAND.

Memory usage (ndbd)

You can determine how much memory your cluster is using for data and indexes by running REPORT MEMORYUSAGE on each node id through the ndb_mgm client:

    # ndb_mgm
    -- NDB Cluster -- Management Client --
    ndb_mgm> 1 report memoryusage
    Node 1: Data usage is 0%(5 32K pages of total 6400)
    Node 1: Index usage is 0%(5 8K pages of total 6432)

Memory usage (system)

Monitoring the processes memory usages is easy with project support.

MySQL Cluster memory usage can be monitored under the 'mysql' project using prstat -J.

    # prstat -J
    PROJID    NPROC  SWAP   RSS MEMORY      TIME  CPU PROJECT
         0       16   72M   44M   0.0%   0:00:58 0.0% system
       924        2   362M  214M   0.0%   0:00:00 0.0% mysql

Metadata keys

With the MySQL Cluster image are metadata keys that can be pre-defined during provision to provide additional customization to the instance.

Metadata Key Description
mysql_server_id MySQL server id to set
mysql_password MySQL root password to set
ndb_connectstring Sets ndb_connectstring to IP specified

Version 15.x.x

15.1.1
Name mysql-cluster
Description A SmartOS 64-bit image pre-configured and optimized as a MySQL Cluster 7.3.8 server.
UUID a3b1c304-1050-11e5-9ac6-632e326bd0fa
Based on base-64 15.1.1
Pkgsrc 2015Q1

What's New In This Image

Software Included In This Image

Software installed as pkgsrc packages:

bzip2-1.0.6nb1
curl-7.42.0
db4-4.8.30
dtracetools-0.7
gcc47-libs-4.7.4nb1
gtar-base-1.28nb1
htop-1.0.1
mysql-cluster-7.3.8nb2
nodejs-0.12.4
openssl-1.0.2a
perl-5.20.2
pkgin-0.8.0
postfix-2.11.4
python27-2.7.9nb1
smtools-20150312
sqlite3-3.8.10
sudo-1.7.10p9
wget-1.16.3
zoneinit-1.6.8

Software installed as npm packages:

json@9.0.3
manta@1.5.1
smartdc@7.3.1

Version 14.x.x

14.4.0
Name mysql-cluster
Description A SmartOS 64-bit image pre-configured and optimized as a MySQL Cluster 7.3.7 server.
UUID c0afda48-d489-11e4-a31e-d76f6d8f8f0d
Based on base-64-lts 14.4.0
Pkgsrc 2014Q4
What's new in this image
Software included in this image

Software installed as pkgsrc packages:

bzip2-1.0.6nb1
curl-7.39.0nb1
db4-4.8.30
dtracetools-0.7
gcc47-libs-4.7.3nb7
gtar-base-1.28
htop-1.0.1
mysql-cluster-7.3.7
nodejs-0.12.1
openssl-1.0.1k
perl-5.20.1
pkgin-0.7.0
postfix-2.11.3
python27-2.7.9
smtools-20150312
sqlite3-3.8.7.4
sudo-1.7.10p9
wget-1.16.1
zoneinit-1.6.8

Software installed as npm packages:

json@9.0.3
manta@1.5.1
smartdc@7.3.1

14.3.0
Name mysql-cluster
Description A SmartOS 64-bit image pre-configured and optimized as a MySQL Cluster 7.3.6 server.
UUID 28fbb45a-7bde-11e4-8b97-0b85d363b8f2
Based on base64 14.3.0
Pkgsrc 2014Q3
What's new in this image
Software included in this image

Software installed as pkgsrc packages:

    bzip2-1.0.6nb1
    curl-7.38.0
    db4-4.8.30
    gcc47-libs-4.7.3nb7
    gtar-base-1.28
    mysql-cluster-7.3.6
    nodejs-0.10.33
    openssl-1.0.1j
    perl-5.20.0nb2
    pkgin-0.6.4nb7
    postfix-2.11.1nb1
    python27-2.7.8nb1
    smtools-20141114
    sqlite3-3.8.6
    sudo-1.7.10p8
    wget-1.16
    zoneinit-1.6.8

Software installed as npm packages

    manta@1.4.5
    smartdc@7.3.0

14.2.0
Name mysql-cluster
Description A SmartOS 64-bit image pre-configured and optimized as a MySQL Cluster 7.3.6 server.
UUID ff422fc4-27a8-11e4-8050-f7ce39aa8165
Based on Base Image
Pkgsrc 2014Q2
What's new in this image
Software included in this image

Software installed as pkgsrc packages:

    bzip2-1.0.6nb1
    curl-7.37.0nb1
    db4-4.8.30
    gcc47-libs-4.7.3nb4
    gtar-base-1.27.1nb1
    mysql-cluster-7.3.6
    nodejs-0.10.30
    openssl-1.0.1h
    perl-5.20.0nb1
    pkgin-0.6.4nb7
    postfix-2.11.1nb1
    python27-2.7.7nb2
    smtools-20140728
    sqlite3-3.8.5
    sudo-1.7.10p8
    wget-1.15nb2
    zoneinit-1.6.8

Software installed as npm packages

    jsontool@7.0.2
    manta@1.4.2
    smartdc@7.3.0

14.1.0
Name mysql-cluster
Description A SmartOS 64-bit image pre-configured and optimized as a MySQL Cluster 7.3.4 server.
UUID 1eed39b6-f7cc-11e3-aacb-7bffef5bf8b6
Based on base64 14.1.0
Pkgsrc 2014Q1
What's new in this image
Software included in this image

Software installed as pkgsrc packages:

    bzip2-1.0.6nb1
    curl-7.36.0
    db4-4.8.30
    duo-unix-1.9.7
    gcc47-libs-4.7.3nb3
    gtar-base-1.27.1nb1
    mysql-cluster-7.3.4nb1
    nodejs-0.10.29
    openssl-1.0.1g
    perl-5.18.2nb1
    pkgin-0.6.4nb5
    postfix-2.11.0nb1
    python27-2.7.6nb3
    smtools-20140612
    sqlite3-3.8.4.1
    sudo-1.7.10p8
    wget-1.15nb1
    zoneinit-1.6.7

Software installed as npm packages

    jsontool@7.0.2
    manta@1.2.7
    smartdc@7.2.1