Saturday, July 16, 2016

MySQL Cluster

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.

Tuesday, February 9, 2016

Shell specifically bash, how to generate random number between 1 to 4

I've had an assignment of which I thought I should be doing, which was to randomly pick four servers at a time, to pick either one of them and appropriately run script on the selected server. Server hostname format ends with a number between 1 to 4.

i=$(echo $(( ($RANDOM % 4 ) + 1)))
hostname=y3xa10${i}
echo $hostname

To try it out on a bash shell, what I got was basically:

# i=$(echo $(( ($RANDOM % 4 ) + 1))); echo y3xa10${i}
y3xa104
# i=$(echo $(( ($RANDOM % 4 ) + 1))); echo y3xa10${i}
y3xa102
# i=$(echo $(( ($RANDOM % 4 ) + 1))); echo y3xa10${i}
y3xa104
# i=$(echo $(( ($RANDOM % 4 ) + 1))); echo y3xa10${i}
y3xa102
# i=$(echo $(( ($RANDOM % 4 ) + 1))); echo y3xa10${i}
y3xa101
# i=$(echo $(( ($RANDOM % 4 ) + 1))); echo y3xa10${i}
y3xa104
# i=$(echo $(( ($RANDOM % 4 ) + 1))); echo y3xa10${i}
y3xa103
# i=$(echo $(( ($RANDOM % 4 ) + 1))); echo y3xa10${i}
y3xa102

I think this suits my requirement pretty much.

Saturday, October 17, 2015

Life is about making mistakes or a seriea of it

Dad was in town for two weeks before I had dropped him off at the airport.I don't think I have ever had this much conversation with him. Unfortunately my work has been unrelenting in it's effort to keep me from spending more time with my dad. How unfortunate.


We'd literally be spending so much time talking about things, about the world about the economy, about our family and pretty much everything else we could find or start a topic with.

Come to think of it, I had only started talking to him, only recently. I think after so much resentment I had towards him over the years, it was so over-bearingly consuming, that I felt like I had to move on. I had to forgive him and by doing that I hope I had forgiven myself, I was no better than everybody else.

How I sometimes wish I things were better, had I not done this sooner. Mistakes are meant to be made, even if you hadn't wished for it. The best thing you can ever do in such an event is to forgive yourself, and accept that you are suppose to make that mistake.

Hopefully I can live with myself and hope the better. That last part was a tad corny. Maybe I am a corny person, a tad bit sentimental.

Sunday, August 30, 2015

Happy Merdeka 2015

Today is apparently Malaysia's 58th year in indepence. I would like to extend my well wishes to those who might just be reading this entry. I don't expect anybody to read this anytime time soon, since I barely have any audience at all anyway. Oh well well well.

Today, would also be the day I will remember back in those days over twenty or more so years ago, how my mum never relented and always insisted that we, we at this moment of writing feels more like just me and it sure feels like it has been a quarter of a decade ago or so. Anyway, she would always tell us to be proud to be who we are, a Sarawakian and foremost, a Malaysia.

I'll always remember August 31st as how I remember my mum tried to teach us a thing or two about being a Malaysian. I have always thought myself as Sarawakian first, and being Malaysian always comes second. I won't start as to why I had this particular notion over those years. We have never been more divided lately, and perhaps it is time to stand united over a common ground.

I would like to end this note, by wishing all Selamat Menyambut Hari Kemerdekaan.

Saturday, January 10, 2015

Linux - generating gpg key for rpm signing

Here how it goes. I wanted to create my own rpm for rootsh-1.5.3 after wanting to disable logging to syslog.

First things first, here's how you generate a gpg key.

[user@host~]$ gpg --gen-key
gpg (GnuPG) 1.4.5; Copyright (C) 2006 Free Software Foundation, Inc.
This program comes with ABSOLUTELY NO WARRANTY.
This is free software, and you are welcome to redistribute it
under certain conditions. See the file COPYING for details.

gpg: keyring `/home/xxxxxx/.gnupg/secring.gpg' created
gpg: keyring `/home/xxxxxx/.gnupg/pubring.gpg' created
Please select what kind of key you want:
   (1) DSA and Elgamal (default)
   (2) DSA (sign only)
   (5) RSA (sign only)
Your selection? X
RSA keys may be between 1024 and 4096 bits long.
What keysize do you want? (2048)
Requested keysize is XXXX bits
Please specify how long the key should be valid.
         0 = key does not expire
       = key expires in n days
      w = key expires in n weeks
      m = key expires in n months
      y = key expires in n years
Key is valid for? (0) XX
Key expires at Day DD Mmm YYYY HH:MM:SS AM MYT
Is this correct? (y/N) y

You need a user ID to identify your key; the software constructs the user ID
from the Real Name, Comment and Email Address in this form:
    "Heinrich Heine (Der Dichter) "

Real name: Xxxxxx Xxxxxx
Email address: xxxxxx@xxxxx.xxx
Comment:
You selected this USER-ID:
    "Xxxxxx Xxxxxx "

Change (N)ame, (C)omment, (E)mail or (O)kay/(Q)uit? O
You need a Passphrase to protect your secret key.

We need to generate a lot of random bytes. It is a good idea to perform
some other action (type on the keyboard, move the mouse, utilize the
disks) during the prime generation; this gives the random number
generator a better chance to gain enough entropy.


gpg: /home/xxxxxx/.gnupg/trustdb.gpg: trustdb created
gpg: key 2F74F867 marked as ultimately trusted
public and secret key created and signed.

gpg: checking the trustdb
gpg: 3 marginal(s) needed, 1 complete(s) needed, PGP trust model
gpg: depth: 0  valid:   1  signed:   0  trust: 0-, 0q, 0n, 0m, 0f, 1u
gpg: next trustdb check due at YYYY-MM-DD
pub   2048R/2F74F867 2015-01-10 [expires: 2016-01-10]
      Key fingerprint = XXXX XXXX XXXX XXXX XXXX  XXXX XXXX XXXX XXXX XXXX 
uid                  Xxxxxx Xxxxxx

Note that this key cannot be used for encryption.  You may want to use
the command "--edit-key" to generate a subkey for this purpose.

And pick a long passphrase and remember them. The next time I will be doing would be to add a jpeg photo. It's actually unnecessary but I'll just doing for completeness sake. It's usually done when uploading gpg keys onto public key servers.

Downloaded a photo of myself and had it called me.jpg on my home directory.

[user@host ~]$ gpg --edit-key xxxxxx@xxxxx.xxx
gpg (GnuPG) 1.4.5; Copyright (C) 2006 Free Software Foundation, Inc.
This program comes with ABSOLUTELY NO WARRANTY.
This is free software, and you are welcome to redistribute it
under certain conditions. See the file COPYING for details.

Secret key is available.

pub  2048R/2F74F867  created: 2015-01-10  expires: 2016-01-10  usage: SC
                     trust: ultimate      validity: ultimate
[ultimate] (1). Xxxxxx Xxxxxx

Command> addphoto

Pick an image to use for your photo ID.  The image must be a JPEG file.
Remember that the image is stored within your public key.  If you use a
very large picture, your key will become very large as well!
Keeping the image close to 240x288 is a good size to use.

Enter JPEG filename for photo ID: /home/xxxxxx/me.jpg
This JPEG is really large (9181 bytes) !
Are you sure you want to use it? (y/N) n

Enter JPEG filename for photo ID: /home/xxxxxx/me.jpg
cannot open display:
Run 'eog --help' to see a full list of available command line options.
gpg: unable to display photo ID!
Is this photo correct (y/N/q)? y

You need a passphrase to unlock the secret key for
user: "Xxxxxx Xxxxxx "
XXXX-bit RSA key, ID XXXXXX, created YYYY-MM-DD


pub  XXXXX/XXXXXXXX  created: YYYY-MM-DD  expires: YYYY-MM-DD  usage: SC
                     trust: ultimate      validity: ultimate
[ultimate] (1). Xxxxxx Xxxxxx
[ unknown] (2)  [jpeg image of size 4936]

Command> save

To list of keys all available keys.

[user@host ~]$ gpg  --list-keys --fingerprint
/home/user/.gnupg/pubring.gpg
---------------------------------
pub   XXXXX/XXXXXXXX YYYY-MM-DD[expires: YYYY-MM-DD]
      Key fingerprint = XXXX XXXX XXXX XXXX XXXX  XXXX XXXX XXXX XXXX XXXX
uid                  Xxxxxx Xxxxxx 
uid                  [jpeg image of size 4936]

To list out the keys that has been installed:

[user@host ~]# rpm -q gpg-pubkey --qf '%{name}-%{version}-%{release} --> %{summary}\n' 

Thursday, December 18, 2014

Linux/Unix - ssh skip host key checkin and Known Hosts file

If you need to automate you remote scripting, like I do and without having being bothered to enter 'Yes' when being prompted to accept the key or with any other prompts you might see.

ssh -o UserKnownHostsFile=/dev/null -o StrictHostKeyChecking=no ${user}@${host}

Saturday, November 29, 2014

Linux - Extracting a single file from an RPM package

Create a temporary directory

# mkdir /tmp/1
# cd /tmp/1
# rpm2cpio /media/Packages/setup-2.8.14-20.el6_4.1.noarch.rpm | cpio -ivd

Copy the needed file out