MySQL Cluster installation

Cluster This blog entry describes how to install MySQL clusters on Solaris.

MySQL cluster consist of 3 seperate types of nodes:
  • SQL nodes
  • Storage nodes
  • Management nodes

The SQL nodes are the nodes that applications can connect to. Internally SQL nodes connect to storage nodes to  process the queries and return the result set to the end client.

The storage nodes are controlled by management nodes. They do most of the work in processing the queries.

Managment nodes manages the entire cluster. They start and stop the data and SQL nodes and manage backups.

Lets start with the simplistic installation where all the nodes of the cluster are on the same box. Of course this is not how you would do a typical MySQL cluster installation...but this is just to get a feel of what is involved in MySQL cluster installation.

The following steps are required to create a MySQL cluster on a single machine:


1)  Create config.ini file in /var/lib/mysql-cluster directory. The contents of config.ini are as under

[NDBD DEFAULT]
NoOfReplicas= 1

[MYSQLD DEFAULT]
[NDB_MGMD DEFAULT]
[TCP DEFAULT]

[NDB_MGMD]
HostName= hostname

[NDBD]
HostName= hostname
DataDir= /var/lib/mysql-cluster

[MYSQLD]
HostName=hostname

Replace <hostname> with the your hostname. The ndb , mysqld and ndb_mgmd sections donate settings for Storage , SQL and mangement nodes respectively. As indicated in the configuration file above , all the nodes are on the same hostname.

2)  Start the managment server by invoking ndb_mgmd command. This binary reads config.ini to get configuration information

3) Start the storage or data node by running ndbd.    

4)Add the cluster options to my.cnf

Essentially add ndbcluster option to mysqld section of my.cnf file.
[mysqld]
ndbcluster
datadir=/var/lib/mysql-cluster

5) Start mysql as usual.

6) Ensure MySQL is using ndb engine by issusing " show engines " command

7) To ensure everything is working fine ,log in to mangement client and issue show command:
ndb_mgm> show

Detailed documentation can be found at http://dev.mysql.com/doc/refman/5.0/en/mysql-cluster-quick.html

Now, lets do an installation of MySQL cluster on three seperate machines:
Lets assume that <mgmt_host> is the hostname for management node, <sql_host> is the hostname for SQL node and <storage_node> is the hostname for storage nodes.

1) Create the config.ini file under /var/lib/mysql-cluster directory on management node <mgmt_host>
[NDBD DEFAULT]
NoOfReplicas=1
DataDir= /var/lib/mysqlcluster
 # Management Node
[NDB_MGMD]
HostName=<mgmt_host>
DataDir= /var/lib/mysql/cluster
# Storage Nodes
[NDBD]
HostName=<storage_host>
# SQL Nodes
[MYSQLD]
HostName=sql_host

2) Start the management server using ndb_mgmd from /var/lib/mysql-cluster directory

3) Log to storage node <storage_host> and start the storage node by invoking ndbd as under
ndbd --connect-string={mangement server clear21} --initial
or put the following in /etc/my.cnf file on storage node [mysql_cluster]
ndb-connectstring=<mgmt_host>

4)
Add the following lines in /etc/my.cnf of SQL node <sql_host>

[mysqld]
ndbcluster
ndb-connectstring=<mgmt_host>

5) Start mysql as usual.

6) Ensure MySQL is using ndb engine by issusing " show engines " command

7) To ensure everything is working fine ,log in to mangement client and issue show command:
ndb_mgm> show



Comments:

Hi ,
We have set up the Mysql Cluster and details of the same are as follows

Management Node – 10.10.1.1[MySQL-clustermanagement-community-5.1.23-0.rhel5.i386.rpm, MySQL-clustertools-community-5.1.23-0.rhel5.i386.rpm]
Data node ‘A’ - 10.10.1.2[MySQL-clusterstorage-community-5.1.23-0.rhel5.i386.rpm]
Data node ‘B’ - 10.10.1.3[MySQL-clusterstorage-community-5.1.23-0.rhel5.i386.rpm]
SQL node - 10.10.1.4[mysql-5.1.23-ndb-6.2.15.tar.gz]

The my.cnf files in Data node ‘A’, Data node ‘B’,SQL node were updated with following

# Options for mysqld process:
[mysqld]
ndbcluster # run NDB storage engine
ndb-connectstring=10.10.1.1 # location of management server

# Options for ndbd process:
[mysql_cluster]
ndb-connectstring=10.10.1.1 # location of management server

and the config.ini is as below
# Options affecting ndbd processes on all data nodes:
[ndbd default]
NoOfReplicas=2 # Number of replicas
DataMemory=80M # How much memory to allocate for data storage
IndexMemory=18M # 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.

# TCP/IP options:
[tcp default]
portnumber=2202 # This the default; however, you can use any
# port that is free for all the hosts in the cluster
# Note: It is recommended that you do not specify the
# portnumber at all and allow the default value to be
# used instead
# Management process options:
[ndb_mgmd]
hostname=10.10.1.1 # Hostname or IP address of MGM node
datadir=/var/lib/mysql-cluster # Directory for MGM node log files

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

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

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

The cluster was started in the following order
MGM node : ndb_mgmd -f /var/lib/mysql-cluster/config.ini
Data nodes A and B : ndbd
SQL node: mysqld_safe --user=mysql --ndbcluster --ndb-connectstring=10.10.1.1:1186 &
Running show from ndb_mgm client on MGM node we get the following

Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 @10.10.1.2 (Version: 5.1.23, Nodegroup: 0, Master)
id=3 @10.10.1.3 (Version: 5.1.23, Nodegroup: 0)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @10.10.1.1 (Version: 5.1.23)
[mysqld(API)] 1 node(s)
id=4 @10.10.1.4 (Version: 6.2.15)

Following checks were done on the SQL node
1. Show Engines;

'ndbcluster' 'YES' 'Clustered, fault-tolerant tables' 'YES' 'NO' 'NO'
'MRG_MYISAM' 'YES' 'Collection of identical
MyISAM tables' 'NO' 'NO' 'NO'
'CSV' 'YES' 'CSV storage engine' 'NO' 'NO' 'NO'
'MyISAM' 'DEFAULT' 'Default engine as of MySQL 3.23
with great performance' 'NO' 'NO' 'NO'
'MEMORY' 'YES' 'Hash based stored in memory
useful for temporary tables' 'NO' 'NO' 'NO'

2. SHOW PROCESSLIST \\G
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*
Id: 1
User: system user
Host:
db: NULL
Command: Daemon
Time: 1732
State: Waiting for ndbcluster to start
Info: NULL
1 rows in set (0.00 sec)

When we try to create a NDB table we the following error

· CREATE TABLE cluster_tab (rollno INT NOT NULL , name varchar(255) ) ENGINE=NDBCLUSTER;

ERROR 1005 (HY000): Can't create table 'test.cluster_tab' (errno: 157)

· show warnings;
+-------+------+------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------------------------+
| Error | 1296 | Got error 4009 'Cluster Failure' from NDB |
| Error | 1005 | can’t create table 'test.cluster_tab' (errno: 157) |
+-------+------+------------------------------------------------------------------+

and the mgm log is as follows
2008-06-02 23:09:09 [MgmSrvr] INFO -- Node 2: Communication to Node 4 closed
2008-06-02 23:09:09 [MgmSrvr] INFO -- Node 3: Communication to Node 4 closed
2008-06-02 23:09:09 [MgmSrvr] ALERT -- Node 2: Node 4 Disconnected
2008-06-02 23:09:09 [MgmSrvr] ALERT -- Node 3: Node 4 Disconnected
2008-06-02 23:09:13 [MgmSrvr] INFO -- Node 2: Communication to Node 4 opened
2008-06-02 23:09:13 [MgmSrvr] INFO -- Node 3: Communication to Node 4 opened
2008-06-02 23:09:13 [MgmSrvr] INFO -- Node 2: Node 4 Connected
2008-06-02 23:09:13 [MgmSrvr] INFO -- Node 2: Node 4: API version 6.2.15
2008-06-02 23:09:13 [MgmSrvr] INFO -- Node 3: Node 4 Connected
2008-06-02 23:09:13 [MgmSrvr] INFO -- Node 3: Node 4: API version 6.2.15
2008-06-02 23:09:16 [MgmSrvr] WARNING -- Node 2: Node 4 missed heartbeat 2
2008-06-02 23:09:17 [MgmSrvr] WARNING -- Node 3: Node 4 missed heartbeat 2
2008-06-02 23:09:18 [MgmSrvr] WARNING -- Node 2: Node 4 missed heartbeat 3
2008-06-02 23:09:18 [MgmSrvr] WARNING -- Node 3: Node 4 missed heartbeat 3
2008-06-02 23:09:19 [MgmSrvr] WARNING -- Node 2: Node 4 missed heartbeat 4
2008-06-02 23:09:19 [MgmSrvr] ALERT -- Node 2: Node 4 declared dead due to missed heartbeat
2008-06-02 23:09:19 [MgmSrvr] INFO -- Node 2: Communication to Node 4 closed
2008-06-02 23:09:19 [MgmSrvr] INFO -- Node 3: Communication to Node 4 closed
2008-06-02 23:09:20 [MgmSrvr] ALERT -- Node 2: Node 4 Disconnected
2008-06-02 23:09:20 [MgmSrvr] ALERT -- Node 3: Node 4 Disconnected
Please let us know how to overcome this and create the NDB tables

Thanks in advance

regards,
Sunitha

Posted by Sunitha on June 01, 2008 at 11:14 PM PDT #

hello

Posted by hey on June 08, 2008 at 11:02 PM PDT #

Ritu,
Either you dont know what you are doing or simply your thoughts are scattered. What you are saying is 'How to configure things with MySQL Cluster' and not installation. There is no mention of installation procedure...Dont waste people's time by writting these stupid things.

Posted by Deepak on June 24, 2008 at 03:08 AM PDT #

Yeaaah, flame on!!

Posted by Rob on August 28, 2008 at 02:19 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Ritu Kamboj

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today