Arquivos da categoria: MySQL HA

Starting with MySQL Cluster

MySQL Cluster originates from a product called Network DataBase from Ericson Telco, located on Swedish.  Ericson’s intention was to have a database running on network as a service. Today’s MySQL Cluster have its name NDB due to the original technology name. For example, the name of the Storage Engine used connected to MySQL Server is NDB (instead of InnoDB or MyISAM). IN general, whether you see NDBCLUSTER, you can think of it as “MySQL Cluster”.

Usually we have many parts involved in a cluster technology and in MySQL Cluster it isn’t different. MySQL Cluster implements three node’s kind to achieve the objective to have no SPOF (Single Point Of Failure) and eliminate downtime possibilities in order to have data available for more time. Besides, the automatic fail over processes happens on a regular basis when some of nodes crash in the middle of operations. Well, there are:

  • Management Node: this node have its function to serve environment as a management. The client program called ndb_mgm connects with its daemon called ndb_mgmd and can be used to retrieve information about other connected cluster nodes and execute some services as cluster backup, for example;
  • Storage Data (or Data Node): connected with management, these nodes are the cluster’s storage that will be used to retrieve data from databases. I strongly recommend start a new MySQL Cluster with at least two data nodes (four is better);
  • API Node (or SQL Node): this node is responsible for receive all external interaction (SQL commands) and manipulates data on storage nodes.

After to understand better what is each presented cluster node, we need to know how to start a configuration of a simple MySQL Cluster, what is the hottest topic of this article. Due to MySQL Cluster has its architecture shared-nothing based, e.g., each component has its own hardware and structure, we must initiate this “simple” project using some virtual machine software (I am using Oracle VirtualBox) to create at least five machines which will have name’s node at hostname, firewalls disabled, SELinux disabled and a static network IP. I am using CentOS 5.5 as operating system and MySQL Cluster 7.1.9.

The first step is configure out the node1 what I set up as Management Node, the node what will serve to retrieve all information about all other cluster nodes and execute services as a backup, start and restart nodes. During its machine operating system installment, you must ensure that all firewalls and SELinux were disabled (MySQL Cluster have problems with firewalls because they need use a couple of it). Configure OS static IP and download MySQL-cluster-gpl-management-xxxxx-rhel-x86-64.rpm and MySQL-cluster-gpl-tools-xxxxx-rhel-x86-64.rpm. After it, we’ll log on linux terminal as a root, create new directory named mysql-cluster under /usr/local and move files from Download directory (I am using firefox with default configuration) to /usr/local/mysql-cluster. We need to create the DataDir where will be found ndb_mgmd log files.

[ root@node1 ~ ] mkdir -p /usr/local/mysql-cluster
[ root@node1 ~ ] mkdir -p /var/lib/mysql-cluster
[ root@node1 ~ ] mv /home/bianchi/Desktop/Downloads/MySQL-* /usr/local/mysql-cluster
[ root@node1 ~ ] cd /usr/local/mysql-cluster

After to download right MySQL Cluster files to apply on Management Node, we need to create the MySQL Cluster configuration file (using your preferred text editor). This file will be located at /usr/local/mysql-cluster/ and its name will be config.ini.

[ root@node1 mysql-cluster ] pwd
/usr/local/mysql-cluster
[ root@node1 mysql-cluster ] vim config.ini

# Into this file we’ll put all configurations required to all nodes. Look for a comments that I’ll use # into this file (commands are used after # signal).

[ndb_mgmd]
#
# Configurations used to control ndb_mgmd behavior
#
NodeId=1
HostName=192.168.0.101
DataDir=/var/lib/mysql-cluster

[ndb_default]
#
# Configurations that will be inherited for all storage/data node
#
DataDir=/var/lib/mysql-cluster
NoOfReplicas=2

[ndb]
# registering new storage node
NodeId=3
HostName=192.168.0.102

[ndb]
# registering new storage node
NodeId=4
HostName=192.168.0.103

[mysqld]
# registering new API/SQL node
NodeId=11
HostName=192.168.0.104

 

[mysqld]
# registering new API/SQL node
NodeId=12
HostName=192.168.0.105

Save and close config.ini file. This file contain configurations to start up a MySQL Cluster with 1 management node, 2 storage nodes and 2 SQL nodes. Now, we will proceed with the management node software installation. As we are working with rpm package, we need only a command to install all MySQL packages located at /usr/local/mysql-cluster.

[ root@node1 mysql-cluster ] rpm -ivh MySQL-*
Preparing... #################################### [100%]
1:MySQL-Cluster-gpl-management ################## [100%]
2:MySQL-Cluster-gpl-tools ####################### [100%]
[ root@node1 mysql-cluster ]

After install, start ndb_mgmd indicating previous crated config.ini file:

[ root@node1 mysql-cluster ] pwd
/usr/local/mysql-cluster
[ root@node1 mysql-cluster ] ndb_mgmd --config-file=config.ini
MySQL Cluster Management Server mysql-5.1.51 ndb-7.1.9
[ root@node1 mysql-cluster ]

We can use $? shell variable to check if some error was rose when we started ndb_mgmd:

[ root@node1 mysql-cluster ] echo $?
0
[ root@node1 mysql-cluster ]

Shell $? variable can return the possible errors below:

0 -> no errors during last script execution, e.g., execution success
1 -> an unknown error occurred
2 -> an command into script error was detected
127 -> some nonexistent command was entered and rose an error

As you can see, our Management Node is up & running at this time and we can issue some commands to list all cluster’s node members. Type just ndb_mgm to use this client to connect with ndb_mgmd and retrieve information, as you see below:

[ root@node1 ~ ] # ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> SHOW
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=3 (not connected, accepting connect from 192.168.0.101)
id=4 (not connected, accepting connect from 192.168.0.102)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.0.100 (mysql-5.1.51 ndb-7.1.9)

 

[mysqld(API)] 4 node(s)
id=11 (not connected, accepting connect from 192.168.0.103)
id=12 (not connected, accepting connect from 192.168.0.104)

Naturally, as you can see above, we don’t have any nodes connected but management node. Now we have completed the first part of five job’s parts. Next I’ll register here how to configure Storage/Data Nodes to start it connecting the Management Node.

See you.

MySQL Cluster Storage Nodes

Going on with our MySQL Cluster studies, now I am starting this new post to talk or write about the Storage or Data Nodes, that one is responsible to storage all cluster data. When we start a cluster (as you have read at MySQL Cluster First Topics), the first step is to start the Management Node and the second part is to start the storage nodes, responsible to storage data using NDBCLUSTER Storage Engine. Normally, the correct, stated and desired form to have nodes to support storage node is to concept a separate machine in order to have only ndbd process using that machine resources. It is important cause, if data node or its daemon named ndbd do not have enough memory to maintain at least indexes on memory, it will crash and will not function properly. But, we will treat about this little things most ahead in order to introduce first steps to concept a data node, start and be happy with you cluster!

After to chose what machines or boxes will be the cluster storage nodes, paying attention about the configurations as the same model of CPU, same amount of memory and the most important part, concept a full 64-bit machine, including OS, hardware and softwares, we can start to download MySQL Cluster Storage Node software component in order to install correct packages and configure it to connect with Management node. Remember, all hardware involved must be the same configuration in order to avoid performance problems and keep the cluster simple as much as you can (normally, I have been implementing MySQL Cluster using Virtual Machines in order to have the max proximity of hardware configuration – the problem is, we must have a look on SPOFS, or, single point of failure). To build Storage Nodes, it will be required to download two packages:

  • MySQL-Cluster-gpl-tools-7.x.x-x
  • MySQL-Cluster-gpl-storage-7.x.x-x

As I am using CentOS 5.5 to write this post, I have downloaded “.rpm” packages that will be installed using rpm package manager at terminal linux level. You can apply this post on MS Windows, for example and install execs packages as you want. Below, I will demonstrate the install process:

[ root@node3 ]# rpm -ivh MySQL-*
Preparing… ############################################ [100%]
1:MySQL-Cluster-gpl-tools ################################## [100%]
2:MySQL-Cluster-gpl-stora ################################## [100%]
[ root@node1 ]#

After this, we can use the files cluster concepts, what is the local files and global files! Local files is that files that is created locally on the node's machine and will serve to configure the cluster Storage Nodes connectstring (ndb_connectstring variable or its shortcut "-c" can be used on local files or by passing through command line). A good touch is, when you are using local files, you will able to inform just little things that will be applied on Storage Nodes connection with Management Nodes. As we have necessary components installed at this moment, we must create a configuration file that one will be read when ndbd starts (you can query where is the default location that ndbd will read local files using ndbd --help and on the command line and reading the firsts lines). The local file will be created below on the example:

[ root@node3 ]# vim /etc/my.cnf

 

# my.cnf local file
# storage node's configuration
#
[ndbd]
ndb-connectstring=192.168.1.100:1186
# finish

Before I forget, the cluster configuration global file is that just one we have created on the first post, that one the majority of the configurations were mentioned and resides on the Management Node. There, we can mention that configurations that will be applied on all Storage Nodes using the section [ndbd default].

Now, you can simply call ndbd on the command line and it will read cluster configuration local file in order to know the exact location of the Management Node (ndb_mgmd) and initiate your tests though the normal levels before appear as started on ndb_mgm Management client. Remember that the location where you can check about all nodes are running is the ndb_mgm client on Management Node (if you are using two Management Nodes - this is good, what a wonderful thing! - you can use both to retrieve all cluster status information).

Calling ndbd on the command line:

[ root@node3 ]# ndbd
2011-03-25 13:21:13 [ndbd] INFO -- Angel connected to '192.168.1.100:1186'
2011-03-25 13:21:13 [ndbd] INFO -- Angel allocated nodeid: 3

As you can see, after start the ndbd process, two processes were started together, one is the ndbd Storage Node and another is the ndbd Angel, the process tha will rise up main Storage Node process in case it going down. The started Storage Node received its NodeID as previously configured and now is waiting the other nodes to finish its complete start! All Storage Nodes envolved on the MySQL Cluster must pass through the same process - installation components, creation of configuration file mentioning the ndb_connectstring under [ndbd] section and start of ndbd. After to complete these jobs on all cluster storage nodes, go to the Management Node and query for the nodes status using ndb_mgm client as mentioned below:

[ root@node1 ~ ] # ndb_mgm -e "SHOW"
-- NDB Cluster -- Management Client --
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=3 @192.168.1.103 (mysql-5.1.51 ndb-7.1.10, NodeGroup: 0, Master)
id=4 @192.168.1.104 (mysql-5.1.51 ndb-7.1.10, NodeGroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.0.100 (mysql-5.1.51 ndb-7.1.9)

[mysqld(API)] 4 node(s)
id=11 (not connected, accepting connect from 192.168.0.103)
id=12 (not connected, accepting connect from 192.168.0.104)

See ya.