MySQL Cluster
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
- Directory layout
- Services layout
- Configuring MySQL Cluster
- Management node (ndb_mgmd)
- Data node (ndbd)
- API node (mysqld)
- Determine amount memory your DB needs for MySQL Cluster
- Monitoring MySQL Cluster
- Metadata keys
- Version 15.x.x
- Version 14.x.x
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:
- http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-basics.html
- http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-install-configuration.html
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:
- http://dev.mysql.com/doc/refman/5.0/en/mysql-cluster-nodes-groups.html
- http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-install-configuration.html
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:
- http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-basics.html
- http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-install-configuration.html
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
- Base image is base-64-15.1.1 (2015Q1)
- MySQL Cluster version is 7.3.8
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
- Base image is base-64-lts-14.4.0 (2014Q4)
- MySQL Cluster version is 7.3.7
- Metadata keys can be pre-defined during provision to set mysql root password, server-id, and ndb_connectstring
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
- Base image is base64-14.3.0 (2014Q3)
- MySQL Cluster version is 7.3.6
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
- Base image is base64-14.2.0 (2014Q2)
- MySQL Cluster version is 7.3.6
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
- Base image is base64-14.1.0 (2014Q1)
- MySQL Cluster version is 7.3.4
- MySQL Cluster nodes under SMF as:
- ndb_mgmd - pkgsrc/mysql-cluster:ndb_mgmd
- ndbd - pkgsrc/mysql-cluster:ndbd
- api - pkgsrc/mysql-cluster:mysqld
- Includes default configurations for each node type to easily get a cluster running
- ndb_connectstring can be configured via svccfg for ndbd and api nodes
- Includes project support (mysql project) with
prstat -J
- Initial release
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