I was recently given the chance to try out MySQL Cluster by Oracle/MySQL. I've never tried configuring one before, and I thought was never going to find any need to. I suppose I may have told myself, who would've thought I'd be doing one now.
First things first, register for an Oracle Account on the Oracle Network. Simply visit http://edelivery.oracle.com. This should redirect you to a page where it will prompt to see if you'd like to login (if you happened to have an Oracle Account) or create a new one.
I already have a personal Oracle Account account which I had used my gmail e-mail address as it's username.
Once you've logged yourself in, the Oracle's E-Delivery portal will have a search text field, enter "Mysql cluster" and this search field will automatically return you all the available text match it could find and simply pick MySQL Cluster 7.4.1.1. To the right of the search text field is the pull down list that allows to select which platform or rather which binary format you'd like to download. I've picked x86_64 for my test.
Below the search text field, you should be able to see MySQL Cluster 7.4.1.1 as the selected item, click on the Continue button.
Expand the MySQL Cluster 7.4.1.1 item by clicking on the tiny triangle to the left of the select item. This will again prompt you to check the check box, I don't know it's asking you to confirm for the second time, I think it's unnecessary but hey, it's Oracle. You will see another Continue button, and I think you can already see the obvious pattern, especially on what to do next.
A popup box will appear with the list of available package format such as Debian, SuSE, Oracle/Linux and the ever handy tar ball format.
I'm lazy and since I'm on CentOS 6.8, I've picked RPM for Oracle Linux / RHEL 6 x86 (64-bit). Download accordingly, follow the next instructions.
Once download is complete, and if you're on x86_64 on CentOS 6.8 like I do, you will probably end up with a zip file called V138059-01.zip. Unzip this zip file and you will get a set of RPMs :
MySQL-Cluster-client-advanced-7.4.11-1.el6.x86_64.rpm
MySQL-Cluster-devel-advanced-7.4.11-1.el6.x86_64.rpm
MySQL-Cluster-embedded-advanced-7.4.11-1.el6.x86_64.rpm
MySQL-Cluster-server-advanced-7.4.11-1.el6.x86_64.rpm
MySQL-Cluster-shared-advanced-7.4.11-1.el6.x86_64.rpm
MySQL-Cluster-shared-compat-advanced-7.4.11-1.el6.x86_64.rpm
MySQL-Cluster-test-advanced-7.4.11-1.el6.x86_64.rpm
First things, first I have a rough diagram of how my MySQL cluster design looks like.
[Image here]
mgmt1 - Management Node 1
sql1 - SQL/API Node 1
sql2 - SQL/API Node 2
data1 - Data Node 1
data2 - Data Node 2
Management Node
First things first, on the designated management host, uninstall mysql-libs. mysql-libs would have some conflicting files when you'll attempt to install MySQL-Cluster-server-advanced.
# rpm -e --nodeps mysql-libs
Install the MySQL-Cluster-server-advanced, MySQL-Cluster-shared-advanced, and MySQL-Cluster-shared-compat-advanced RPM packages.
# rpm -Uvh MySQL-Cluster-server-advanced-7.4.11-1.el6.x86_64.rpm MySQL-Cluster-shared-compat-advanced-7.4.11-1.el6.x86_64.rpm MySQL-Cluster-shared-advanced-7.4.11-1.el6.x86_64.rpm
I have selected minimal install for every Linux install I've done so I can pick the packages that I would like to install. Always update to the latest kernel and all it's subsequent package updates.
wget - so I can grab packages from whichever site I'd like to
zip/unzip - to be able to unzip the MySQL Cluster zip file containing the RPM packages
numactl - can't remember why I needed this, aparrently it is a prerequisite
man - for obvious reasons, man pages
openssh-clients - didn't I say I picked minimal install earlier?
ntp - the ntp client, for system time synching
ntpdate - an ntp utility, out of habit I feel like I need for some reason
python-paramiko - a prerequisite if you intend to use MySQL Cluster Autoinstall (ndb_setup.py)
Create a directory called /var/lib/mysql-cluster/49
# mkdir -p /var/lib/mysql-cluster/49
Create a text file and call it config.ini under the directory /var/lib/mysql-cluster/49, with the following contents. The number 49 is the designated NDB node ID for the management host. This is all up to you.
================== config.ini begins =================
[NDB_MGMD DEFAULT]
Portnumber=1186
[NDB_MGMD]
NodeId=49
HostName=mgmt1
DataDir=/var/lib/mysql-cluster/49/
Portnumber=1186
[TCP DEFAULT]
SendBufferMemory=2M
ReceiveBufferMemory=2M
[NDBD DEFAULT]
BackupMaxWriteSize=1M
BackupDataBufferSize=16M
BackupLogBufferSize=4M
BackupMemory=20M
BackupReportFrequency=10
MemReportFrequency=30
LogLevelStartup=15
LogLevelShutdown=15
LogLevelCheckpoint=8
LogLevelNodeRestart=15
DataMemory=1M
IndexMemory=1M
MaxNoOfTables=4096
MaxNoOfTriggers=3500
NoOfReplicas=2
StringMemory=25
DiskPageBufferMemory=64M
SharedGlobalMemory=20M
LongMessageBuffer=32M
MaxNoOfConcurrentTransactions=16384
BatchSizePerLocalScan=512
FragmentLogFileSize=64M
NoOfFragmentLogFiles=16
RedoBuffer=32M
MaxNoOfExecutionThreads=2
StopOnError=false
LockPagesInMainMemory=1
TimeBetweenEpochsTimeout=32000
TimeBetweenWatchdogCheckInitial=60000
TransactionInactiveTimeout=60000
HeartbeatIntervalDbDb=15000
HeartbeatIntervalDbApi=15000
[NDBD]
NodeId=1
HostName=data1
DataDir=/var/lib/mysql-cluster/1/
[NDBD]
NodeId=2
HostName=data2
DataDir=/var/lib/mysql-cluster/2/
[MYSQLD DEFAULT]
[MYSQLD]
NodeId=55
HostName=sql1
[MYSQLD]
NodeId=56
HostName=sql2
[API]
NodeId=50
HostName=sql1
[API]
NodeId=52
HostName=sql2
================== config.ini ends =================
Data Nodes
On the designated data nodes, install the MySQL-Cluster-server-advanced, MySQL-Cluster-shared-advanced, and MySQL-Cluster-shared-compat-advanced RPM packages.
#
rpm -Uvh MySQL-Cluster-server-advanced-7.4.11-1.el6.x86_64.rpm
MySQL-Cluster-shared-compat-advanced-7.4.11-1.el6.x86_64.rpm
MySQL-Cluster-shared-advanced-7.4.11-1.el6.x86_64.rpm
Create a directory called /var/lib/mysql-cluster/1
# mkdir -p /var/lib/mysql-cluster/1
SQL Nodes
While on the designated sql nodes, install the same MySQL-Cluster-server-advanced, MySQL-Cluster-shared-advanced, and MySQL-Cluster-shared-compat-advanced RPM packages.
#
rpm -Uvh MySQL-Cluster-server-advanced-7.4.11-1.el6.x86_64.rpm
MySQL-Cluster-shared-compat-advanced-7.4.11-1.el6.x86_64.rpm
MySQL-Cluster-shared-advanced-7.4.11-1.el6.x86_64.rpm
Create a directory called /var/lib/mysql-cluster/55
# mkdir -p /var/lib/mysql-cluster/55
Initialize the necessary mysql databases and tables.
# mysql_install_db --basedir="/usr" --datadir="/var/lib/mysql-cluster/56"
Create a my.cnf under the /var/lib/mysql-cluster/56 directory
================== my.cnf begins =================
[mysqld]
log-error=mysqld.56.err
datadir="/var/lib/mysql-cluster/56/"
tmpdir="/var/lib/mysql-cluster/56/tmp"
basedir="/usr/"
port=3306
ndbcluster=on
ndb-nodeid=55
ndb-connectstring=mgmt1:1186,
socket="/var/lib/mysql-cluster/56/mysql.socket"
================== my.cnf ends =================
Edit the /etc/init.d/mysql, locate the directive other_args and amend accordingly as follows:
other_args="--ndbcluster --ndb-nodeid=56 --ndb-connectstring=mgmt1:1186"
Once all the configurations are done (in the following order) start the Management node, Data Nodes and followed by SQL Nodes.
Starting the Management Node
To start the Management Node run the following
# ndb_mgmd -f /var/lib/mysql-cluster/49/config.ini --initial
Starting the Data Nodes
To start the Data Nodes run the following instead:
# ndb_mgmd --ndb-node=1 --ndb-connectstring=mgmt1:1186 --initial
Starting the SQL Nodes
Lastly, start the SQL node by starting the MySQL server.
# /etc/init.d/mysql start
The Management Node client
From the Management Node, run the Management Node client, run show
# ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=1 @192.168.56.121 (mysql-5.6.29 ndb-7.4.11, Nodegroup: 0, *)
id=2 @192.168.56.122 (mysql-5.6.29 ndb-7.4.11, Nodegroup: 0)
[ndb_mgmd(MGM)] 1 node(s)
id=49 @192.168.56.101 (mysql-5.6.29 ndb-7.4.11)
[mysqld(API)] 4 node(s)
id=50 (not connected, accepting connect from sql1)
id=52 (not connected, accepting connect from sql2)
id=55 @192.168.56.111 (mysql-5.6.29 ndb-7.4.11)
id=56 @192.168.56.112 (mysql-5.6.29 ndb-7.4.11)
As you may notice my API nodes are not connected, and I am still looking for answers on this for the time being.
I guess I'll need to do some more reading on MySQL Cluster as it seems. The documentations are rather vague and even the instructions are not very clear to say the least.
Once all the services are up, and you'd have every intention to start creating your databases and tables using the NDB Cluster Storage engine. Simply change the table engine type to ndbcluster and you'll be pretty much on your way.
