Setting up a MySQL cluster 7.0 in Redhat based linux

by jagbir on March 1, 2010

MySQL Cluster is used to provide high-availability, high-redundancy for the distributed computing environment. You might know that beginning with MySQL 5.1.24, support for the NDBCLUSTER storage engine was removed from the standard MySQL server binaries built by MySQL. Therefore, here I’m using MySQL Cluster edition instead of MySQL Community edition. I’m using 3 servers (1 Management and 2 data) to setup cluster in CentOS 5.4 Linux box. The steps mentioned here can be used for quickly deploying a cluster and worked out for me but no guarantee that this will work for you, So its always advisable to go through official MySQL guide as well for production environment. In case you are also riding on cloud computing wave, I’ve already blogged way to setup MySQL cluster in Amazon EC2 environment.

1 server will play role of Management server (Example IP: 10.17.250.130) having AMD Opetron 2Ghz with 2 GB of RAM. The load on this server will not be any significant, so a server with low configuration will do the job decently. You can install some monitoring software here to monitor you cluster.

2 data/sql servers (Example IPs: 10.12.248.66, 10.8.75.70) having Intel Xeon 5335 4×2.00GHz with 16 GB of RAM each. These servers will store/replicate data.

I want to say here that we can also set both cluster management service and MySQL service in a single server, even management service with 2 mysql instance in a single box (for more info refer: how to run multiple instances of MySQL in single server) but that’s definitely NOT recommended and apparently have no use here. Please note that I’m using only IP addresses while referencing the hosts, you can use the host name as well if you want.

Step 1. Configuration of Management Server

a) MySQL Cluster rpms are available on download site for RedHat linux (on which CentOS is based), so download the rpms for our servers. I’ve downloaded all rpms except which provides debugging etc.

# cd /usr/src
# wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.0/MySQL-Cluster-gpl-client-7.0.9-0.rhel5.x86_64.rpm/from/ftp://mysql.mirror.rafal.ca/pub/mysql/

.. download all others in same way in /usr/src directory. I’ve downloaded the following rpms:
MySQL-Cluster-gpl-management-7.0.9-0.rhel5.x86_64.rpm
MySQL-Cluster-gpl-storage-7.0.9-0.rhel5.x86_64.rpm
MySQL-Cluster-gpl-client-7.0.9-0.rhel5.x86_64.rpm
MySQL-Cluster-gpl-server-7.0.9-0.rhel5.x86_64.rpm
MySQL-Cluster-gpl-tools-7.0.9-0.rhel5.x86_64.rpm
MySQL-Cluster-gpl-extra-7.0.9-0.rhel5.x86_64.rpm
MySQL-Cluster-gpl-shared-7.0.9-0.rhel5.x86_64.rpm

Now before installing, make sure you should remove any prior installation of MySQL in the box, if any. Then install the rpms:

# rpm -ivh MySQL-Cluster-gpl-*

Installation should go smooth. We need to create config.ini in directory /var/lib/mysql-cluster which will acts are configuration file for cluster. Add following configuration directives in the file. Make sure to write IP addresses etc as per your own environment:

# cd /var/lib/mysql-cluster
# vi config.ini
[NDBD DEFAULT]
NoOfReplicas=2
LockPagesInMainMemory=1
 
DataMemory=12288M
IndexMemory=1536M
 
ODirect=1
 
NoOfFragmentLogFiles=300
MaxNoOfConcurrentOperations=100000
TimeBetweenGlobalCheckpoints=1000
TimeBetweenEpochs=200
DiskCheckpointSpeed=10M
DiskCheckpointSpeedInRestart=100M
RedoBuffer=32M
# MaxNoOfLocalScans=64
MaxNoOfTables=1024
MaxNofOfOrderedIndexes=256
 
[MYSQLD DEFAULT]
 
[NDB_MGMD DEFAULT]
 
[TCP DEFAULT]
# Managment Server
SendBufferMemory=8M
ReceiveBufferMemory=8M
 
[NDB_MGMD]
# the IP of THIS SERVER
HostName=10.17.250.130
 
[NDBD]
# the IP of the FIRST SERVER (Data Node)
HostName=10.12.248.66
DataDir= /var/lib/mysql-cluster
 
[NDBD]
# the IP of the SECOND SERVER (Data Node)
HostName=10.8.75.70
DataDir=/var/lib/mysql-cluster
 
[MYSQLD]
[MYSQLD]

Please note that values in DataMemory, IndexMemory variables are based in available physical memory in data nodes, in this case 16 GB. To understand the purpose and ideal value of various variable, please check MySQL cluster documentation.

b) Start the management service mentioning the config file we just created above:

# ndb_mgmd -f /var/lib/mysql-cluster/config.ini 
2010-02-27 02:08:49 [MgmtSrvr] INFO     -- NDB Cluster Management Server. mysql-5.1.39 ndb-7.0.9b
2010-02-27 02:08:49 [MgmtSrvr] INFO     -- The default config directory '//mysql-cluster' does not exist. Trying to create it...
2010-02-27 02:08:49 [MgmtSrvr] INFO     -- Sucessfully created config directory
2010-02-27 02:08:49 [MgmtSrvr] INFO     -- Reading cluster configuration from '/var/lib/mysql-cluster/config.ini'

Management server configuration is done. We will revisit here after setting up data nodes.

Step 2. Configure data/storage nodes of cluster. You should implement below steps in both of data nodes while taking care of IP address etc.

a) Check that there should be no prior installation of mysql in the server, if there’s any, remove it.

# rpm -qa | grep mysql 
mysql-5.0.77-4.el5_4.1
 
# yum remove mysql

b) Install cluster (copy rpms you downloaded in management node to here). Again, I’ve installed everything just for the sake of ease.

# cd /usr/src
# rpm -ivh MySQL-Cluster-gpl-client-7.0.9-0.rhel5.x86_64.rpm MySQL-Cluster-gpl-client-7.0.9-0.rhel5.x86_64.rpm MySQL-Cluster-gpl-extra-7.0.9-0.rhel5.x86_64.rpm MySQL-Cluster-gpl-shared-7.0.9-0.rhel5.x86_64.rpm MySQL-Cluster-gpl-server-7.0.9-0.rhel5.x86_64.rpm MySQL-Cluster-gpl-storage-7.0.9-0.rhel5.x86_64.rpm MySQL-Cluster-gpl-tools-7.0.9-0.rhel5.x86_64.rpm

c) Time to create my.cnf for MySQL service, this is very basic config file having required entries for Cluster. We have to optimize this as we go forward.

# vi /etc/my.cnf
[client]
port=3306
socket=/var/lib/mysql/mysql.sock
 
[mysqld]
 
ndbcluster
# IP address of the cluster management node
ndb-connectstring=10.17.250.130
default-storage-engine=NDBCLUSTER
 
#max_connections=341
#query_cache_size=16M
#thread_concurrency = 4
 
[mysql_cluster]
# IP address of the cluster management node
ndb-connectstring=10.17.250.130

d) Start ndbd service with initial setup. The initial parameters needed only first time while you are starting the service to create cluster file system and other things, subsequently we just need to enter ‘ndbd’ to start this.

# ndbd --initial 
2010-02-27 02:40:51 [ndbd] INFO     -- Configuration fetched from '10.17.250.130:1186', generation: 1

e) Start MySQL server now:

# start mysql server
# /etc/init.d/mysql start

f) Dont forget to set password for root user.

# mysqladmin -u root password 'yourpassword'

If there’s any unfortunate event when you forget root password of MySQL, here’s a quick way to reset that.

Repeat Step 2 in second data node as well. After completion of this we have done setup of basic MySQL cluster and now its time to check this out.

Step 3. Testing the cluster and other important things to remember

a) Login to management node again and issue ndb_mgm command to open command prompt where we can issue management commands and check status of cluster by issuing ‘show’ command:

# ndb_mgm
-- NDB Cluster -- Management Client --
 
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]	2 node(s)
id=2	@10.12.248.66  (mysql-5.1.39 ndb-7.0.9, Nodegroup: 0, Master)
id=3	@10.8.75.70  (mysql-5.1.39 ndb-7.0.9, Nodegroup: 0)
 
[ndb_mgmd(MGM)]	1 node(s)
id=1	@10.17.250.130  (mysql-5.1.39 ndb-7.0.9)
 
[mysqld(API)]	2 node(s)
id=4	@10.12.248.66  (mysql-5.1.39 ndb-7.0.9)
id=5	@10.8.75.70  (mysql-5.1.39 ndb-7.0.9)
 
ndb_mgm>

You can see that both ndb nodes are connected, 1 management node is there and then 2 mysqld/api nodes there. You can notice that both of data nodes are referred as ndb and mysqld/api nodes because we are running ndb service there as well as mysql service. This will facilitate that our data node also runs mysql service and we can connect to any of these nodes to manage our data. If you refer MySQL cluster documentation, you can see that they are stressing to have 1 mysqld/api node and then 2 data nodes, I dont see any advantage in that. Due to availability of faster hardware and lots of storage, I prefer to keep both data/api in same machine. This will also help in setting up load balancer later to distribute load among available data/mysqld nodes.

Coming back to ndb management prompt, there are several command we can issue here to check/view most stats. like “ALL REPORT MEMORY” shows memory usage from all machines.

ndb_mgm> all report memory
 
ndb_mgm> Node 2: Data usage is 0%(22 32K pages of total 2560)
Node 2: Index usage is 0%(16 8K pages of total 2336)
Node 3: Data usage is 0%(22 32K pages of total 2560)
Node 3: Index usage is 0%(16 8K pages of total 2336)
 
ndb_mgm>

there are many commands supported here, which can be found on MySQL cluster official documentation.

b) Testing data nodes and create/importing data

As you already know, cluster uses its own stroage engine, therefore you MUST create tables or alter them if you import from backup etc. to NDBCLUSTER storage engine otherwise tables will not replicate in cluster. (here’s solution of one issue where we want to ignore errors coming while backup restore.
)

Login to data node 1 and connect to mysql there:

# mysql -u root -p
 
mysql> create database testdb; 
Query OK, 1 row affected (0.21 sec)
 
mysql> use testdb; 
Database changed
 
mysql> create table t (seq int, name varchar(10)) engine = ndbcluster; 
Query OK, 0 rows affected (0.28 sec)
 
mysql> insert into t values (1,'Mohan'); 
Query OK, 1 row affected (0.00 sec)

Now login to node 2, access mysql and check data:

# mysql -u root -p
 
mysql> show databases; 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
| testdb             |
+--------------------+
4 rows in set (0.00 sec)
 
mysql> use testdb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> show tables; 
+------------------+
| Tables_in_testdb |
+------------------+
| t                |
+------------------+
1 row in set (0.03 sec)
 
mysql> select * from t; 
+------+-------+
| seq  | name  |
+------+-------+
|    1 | Mohan |
+------+-------+
1 row in set (0.00 sec)
 
mysql>

Cluster is working as expected. You can start making use of it.

One thing to note down that if you want to change/add new configs in management server, be sure to stop/start the ndb_mgm daemon and also restart ndbd and mysql services in all data nodes.

This setup is very basic and needs lots of optimization before deploying in production. One issue here is that the cluster doesn’t provide any fault tolerance as your application needs to be aware that two mysql nodes are available so in case of one node fails, you must connect to other node in meantime the first node get repaired.

To overcome such issues, and eliminate single point of failure, I’ve to setup 2 load balancer servers with single virtual IP among them to present single point of contact for application. Currently I’m testing that thing and will blog the stuff here very soon, till then stay tuned. Please let me know your valuable suggestions/queries regarding this article.

  • Pingback: Links 7/3/2010: Deutsche Börse and Red Hat | Boycott Novell

  • Pingback: Destillat #11 | duetsch.info - Open Source, Wet-, Web-, Software

  • Phrontiste

    Hi Jagpir,

    Thank you for the informative article ; I am new to Mysql, I can install mysql, setup a database (schema) assign privilieges etc via the Mysql admin gui but not very fluent with the command line.

    Now, I am trying to setup a cluster in a virtual environment (test) on redhat enterprise linux 5.

    My understanding so far is, that first we download the Mysql cluster rpms for linux (RHEL) and then we download the prefered Mysql server version we want to run inside that cluster ? or does that cluster already have the database server ?

    If I am wrong please excuase and correct me :-)

    Will be grateful for your reply
    Thanks

  • http://www.jagbir.info jagbir

    @Phrontiste
    Hi, thanks for comment.

    MySQL Cluster suite is actually MySQL Database + Clustering capabilities combined. If you notice in rpms list, you can see multiple tools, these tools are actually components of cluster which includes database as well. So, when you install MySQL Cluster you are installing database and essential tools to build cluster.

    Hope that help.

    - Jagbir

  • Phrontiste

    Hi Jagbir

    Thanks for your mega quick reply ; if the web app is desgined /coded with an earlier version of Mysql, (4.1.18), would that be a problem ? as i can’t seem to find the download for Mysql cluster for that version and its version 7 of the cluster.

    I will be grateful for your assistance / guidance on this ;
    Thanks so much

  • http://www.jagbir.info jagbir

    @Phrontiste

    well, MySQL 4.1.18 is quite old but there should be no issue. I would suggest you to dump your data from your existing setup before terminating/stopping your existing app and then import it in new test env with this cluster setup. Testing is what I recommend here in this case.

    - Jagbir

  • Phrontiste

    Hi Jagbir,

    Thanks for your prompt reply, so basically :
    I will download the MySQL cluster package (rpms) for Redhat linux
    Install them

    As this is a test environment being setup, what my plan is :

    a) to get the mysql dump from existing database
    b) setup a single vm with cluster management (rhel)
    c) setup two separate virtual machines (rhel) with mysql server?

    so, I would need 3 Servers to run this cluster succesfully if tested fine ? I will use 3 virtual machines for this for the time, but what would you recommend as to the number of servers /roles required ?

    Also, do we install the Mysql Server on the two servers or we install the cluster rpms and then import the data dump ?

    How is the data dump imported ?

    I am sorry to have asked you so many questions.
    Thank you so much for your support
    Phrontiste

  • http://www.jagbir.info jagbir

    @Phrontiste
    Hi,

    Your plan seems ok. Here’s few tips:

    a) Create 3 VMs.
    b) Install cluster management in first VM and other rpms (server,client etc.) in other 2 VMs just as described in this article.
    c) Test your cluster first by test data.
    d) Dump database from existing setup using Mysqldump utility. example:
    i) to dump a single ‘mydb’ database:
    $ mysqldump -u user -p mydb > mydb.sql
    ii) to dump all databases:
    $ mysqldump -u user -p –all-databases > alldb.sql
    e) import the dump in any of two sql (data) nodes of your cluster. (remember that management server is just to manage cluster, nothing else)
    login to any of 2 data node and execute:
    $ mysql -u user -p < mydb.sql
    Or for all databases:
    $ mysql -u user -p < alldb.sql
    you can create user beforehand or can use root for these activities.

  • Phrontiste

    Hi Jagpir,

    Thank you so MUCH !!!!!!

    I read a sun.com article / pdf, in which it states that data nodes are separate as compared to the MySQL Server ?

    Do you think one would be good in a high traffic environment with One Management and 2 data nodes ? or do we need the MySQL Server installed on two servers and then they talk to the data nodes ?
    Sorry for being dump, I will post my findings / results to you.

    my email is phrontiste @ gmail dot com

    Thanks again

  • http://www.jagbir.info jagbir

    @Phrontiste
    Hi phrontiste,

    I preferred combining data nodes+sql nodes in both servers, so you can access mysql from any of them. I tested my cluster and it gave good results.

    on other side for your info, for my environment, though I tested MySQl cluster but at the end, decided on implementing Master-Master replication setup because MySQL cluster have some distinct characteristics which your app might not like. more details can be found on MySQL site docs.

    - Jagbir

  • http://www.pyrogroup.com Mukund

    Hi after following up the steps i can see my ndbd node is not connected. here is teh output:
    ndb_mgm> show
    Connected to Management Server at: localhost:1186
    Cluster Configuration
    ———————
    [ndbd(NDB)] 2 node(s)
    id=2 (not connected, accepting connect from x.x.x.x)
    id=3 (not connected, accepting connect from x.x.x.y)

    [ndb_mgmd(MGM)] 1 node(s)
    id=1 @x.x.x.z (mysql-5.1.41 ndb-7.0.13)

    [mysqld(API)] 2 node(s)
    id=4 (not connected, accepting connect from any host)
    id=5 (not connected, accepting connect from any host)

    i tried restarting data nodes.Please suggest.

  • Mathiau

    Great guide, i am currently looking into configuring a cluster under linux (since windows i only in beta) and this guide is so clear, i am sure i will be visiting it often!

    “Currently I’m testing that thing and will blog the stuff here very soon, till then stay tuned.”

    Cant wait, will be doing this in 3 vm’s first for testing.

  • Pingback: Mereset username dan password untuk MySQL dan PostgreSQL « eX-GeodetaNews

  • Pingback: Fleece vests toddlers

  • Jacques De Molay

    Hi,

    I have a doubt. Where is the directory for “start mysql server”?

    e) Start MySQL server now:

    # start mysql server
    # /etc/init.d/mysql start

    Thanks you

    • http://www.jagbir.info jagbir

      Hi Jacques,

      didn’t understood your question? in “/etc/init.d/mysql start” the ‘mysql’ here is a script which we use to start/stop mysql service. It should be automatically placed there in /etc/init.d while installation. let me know if its still not clear with you.

      - jagbir

  • Bob

    Hello,

    Very nice tutorial ! I have a question, what will happen if I import an InnoDB database ? I need a transaction-safe storage engine.

    Thanks in advance,

    - Bob -

  • http://www.wains.be Seb

    Thanks. My MySQL HA book is confusing as hell. This is clear and to the point.

    I only allocated 1 GB on my mysql test nodes in the first place. It resulted in ndbd being killed :

    Jan 5 13:20:51 node2 kernel: Out of memory: kill process 2186 (ndbd) score 578532 or a child
    Jan 5 13:20:51 node2 kernel: Killed process 2187 (ndbd) vsz:2237720kB, anon-rss:884412kB, file-rss:4280kB

    On the mgmt I was getting “ndb_mgm> Node 2: Forced node shutdown completed. Occured during startphase 0. Initiated by signal 9.”

    With 2 GB, it works fine (Red Hat 6.0 x86_64, MySQL Cluster 7.1)

    Cheers

  • http://www.potenvdzmggeesd.com Berniece Valdo

    Like a Newbie, I’m usually looking on-line for content articles that will support me. Thank you

  • Xonner

    Hello,

    I’ve done all this steps, but then i get this problem, when i start the daemon on the data nodes, with ndbd –initial, in the management node i get a looping message saying this:

    Node 3: Node shutdown completed, restarting, no start.
    Node 3: Node shutdown completed, restarting, no start.
    Node 3: Node shutdown completed, restarting, no start.

    What cen be the problem? I guess i did everything right. Please help.

    Thank you.

    • http://www.jagbir.info jagbir

      @Xonner. Not very sure here but looks like you missed something. you can check settings along with view on logs and try.

  • http://www.moneyca.com jelly140

    This worked like magic…
    best so far.
    Thanks.

  • wingman

    This article is really nice and clear and easily to follow.

    At the end, you have mentioned about fault tolerance. How is your test? Would you mind sharing your finding here?

    • http://www.jagbir.info jagbir

      @wingman, Thanks for your comment and nice words. Unfortunately, I moved to some different tech and didn’t get completed that test. So no views on that aspect.

  • Weber

    Hey,

    I having the same issue as Xonner. My keeps saying it with node 2. I have tried allot of things, but i just cant get it to work.

    This is my config.ini:
    [NDBD DEFAULT]
    NoOfReplicas=2
    LockPagesInMainMemory=1

    DataMemory=12288M
    IndexMemory=1536M

    ODirect=1

    NoOfFragmentLogFiles=300
    MaxNoOfConcurrentOperations=100000
    TimeBetweenGlobalCheckpoints=1000
    TimeBetweenEpochs=200
    DiskCheckpointSpeed=10M
    DiskCheckpointSpeedInRestart=100M
    RedoBuffer=32M
    # MaxNoOfLocalScans=64
    MaxNoOfTables=1024
    MaxNoOfOrderedIndexes=256

    [MYSQLD DEFAULT]

    [NDB_MGMD DEFAULT]

    [TCP DEFAULT]
    # Managment Server
    SendBufferMemory=8M
    ReceiveBufferMemory=8M

    [NDB_MGMD]
    # the IP of THIS SERVER
    NodeId=1
    HostName=192.168.1.2

    [NDBD]
    # the IP of the FIRST SERVER (Data Node)
    NodeId=2
    HostName=192.168.1.4
    DataDir= /var/lib/mysql-cluster

    [NDBD]
    # the IP of the SECOND SERVER (Data Node)
    NodeId=3
    HostName=192.168.1.5
    DataDir=/var/lib/mysql-cluster

    [MYSQLD]
    [MYSQLD]

    And here is my my.cnf:

    [client]
    port=3306
    socket=/var/lib/mysql/mysql.sock

    [mysqld]

    ndbcluster
    # IP address of the cluster management node
    ndb-connectstring=192.168.1.2
    default-storage-engine=NDBCLUSTER

    #max_connections=341
    #query_cache_size=16M
    #thread_concurrency = 4

    [mysql_cluster]
    # IP address of the cluster management node

    ndb-connectstring=192.168.1.2

    Any idea???

  • Rajnish

    Hi ,
    I have gone through the mysql custering step mentioned by you . its working fine .
    but when i restart machine all ndb engine tables vanished from mysql DB rest tables like myisam /innodb still there .
    is their is any specific setting for store all ndb tables in disk not in memory .

  • Pingback: Mysql Cluster Community Edition 7.1 - Technology Blog

  • Sri

    Hi Rajnish,

    I am having the same problem. The NDB cluster is working fine without any problem for couple of days. But then the tables gone disappear. Did you manage to solve the problem?

  • kanuj

    We have set up the Mysql Cluster and we follow the following steps

    one management node and 2 data nodes and one sql node

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

    [mysqld]
    ndbcluster
    ndb-connectstring=10.10.1.1

    [mysql_cluster]
    ndb-connectstring=10.10.1.1

    Management Node config.ini:-

    [ndbd default]
    NoOfReplicas=2
    DataMemory=80M
    IndexMemory=18M

    [ndb_mgmd]
    hostname=10.10.1.1
    datadir=/var/lib/mysql-cluster
    [ndbd]
    hostname=10.10.1.2
    datadir=/usr/local/mysql/data

    [ndbd]
    hostname=10.10.1.3
    datadir=/usr/local/mysql/data

    [mysqld]
    hostname=10.10.1.4

    copy the config.ini file in 2 data nodes:-

    scp config.ini 10.10.1.2:/var/lib/mysql-cluster/

    scp config.ini 10.10.1.3:/var/lib/mysql-cluster/

    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 –initial

    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 (mysql-5.1.51 ndb-7.1.10, Nodegroup: 0, Master)
    id=3 @10.10.1.3 (mysql-5.1.51 ndb-7.1.10, Nodegroup: 0)

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

    [mysqld(API)] 1 node(s)
    id=4 @10.10.1.4 (mysql-5.1.51 ndb-7.1.10)

    ndb_mgm>

    Following checks were done on the SQL node

    1. Show Engines;

    mysql> Show Engines;
    +————+———+—————————————————————-+————–+——+————+
    | Engine | Support | Comment | Transactions | XA | Savepoints |
    +————+———+—————————————————————-+————–+——+————+
    | ndbcluster | YES | Clustered, fault-tolerant tables | YES | NO | NO |
    | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
    | ndbinfo | YES | MySQL Cluster system information storage engine | NO | NO | NO |
    | CSV | YES | CSV storage engine | NO | NO | NO |
    | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
    | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
    | ARCHIVE | YES | Archive storage engine | NO | NO | NO |
    | InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
    | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
    | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
    +————+———+—————————————————————-+————–+——+————+
    10 rows in set (0.00 sec)

    mysql> show processlist;
    +—-+————-+———–+——+———+——+———————————–+——————+
    | Id | User | Host | db | Command | Time | State | Info |
    +—-+————-+———–+——+———+——+———————————–+——————+
    | 1 | system user | | | Daemon | 0 | Waiting for event from ndbcluster | NULL |
    | 8 | root | localhost | NULL | Query | 0 | NULL | show processlist |
    +—-+————-+———–+——+———+——+———————————–+——————+
    2 rows in set (0.00 sec)

    2) create database and table ;

    mysql> create database hk;
    Query OK, 1 row affected (0.00 sec)

    mysql> \u hk
    Database changed
    mysql> create table hh(id int(1)) engine=ndbcluster;
    Query OK, 0 rows affected (0.05 sec)

    mysql>

    Following checks were done on the Data node 1:

    mysql> show engines;
    +————+———+—————————————————————-+————–+——+————+
    | Engine | Support | Comment | Transactions | XA | Savepoints |
    +————+———+—————————————————————-+————–+——+————+
    | ndbcluster | YES | Clustered, fault-tolerant tables | YES | NO | NO |
    | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
    | ndbinfo | YES | MySQL Cluster system information storage engine | NO | NO | NO |
    | CSV | YES | CSV storage engine | NO | NO | NO |
    | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
    | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
    | ARCHIVE | YES | Archive storage engine | NO | NO | NO |
    | InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
    | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
    | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
    +————+———+—————————————————————-+————–+——+————+
    10 rows in set (0.00 sec)

    mysql> create database hello1;
    Query OK, 1 row affected, 1 warning (0.00 sec)

    mysql> show warnings;
    +——-+——+———————————————————————————+
    | Level | Code | Message |
    +——-+——+———————————————————————————+
    | Error | 1296 | Got error 4009 ‘Cluster Failure’ from NDB. Could not acquire global schema lock |
    +——-+——+———————————————————————————+
    1 row in set (0.00 sec)

    mysql> \u hello1
    Database changed

    mysql> create table nn(id int(1)) engine=ndbcluster;
    ERROR 1005 (HY000): Can’t create table ‘hellol.nn’ (errno: 157)
    mysql>

    mysql> create table hh(id int(1));
    Query OK, 0 rows affected, 2 warnings (0.04 sec)

    mysql> show warnings;
    +——-+——+———————————————————————————+
    | Level | Code | Message |
    +——-+——+———————————————————————————+
    | Error | 1296 | Got error 4009 ‘Cluster Failure’ from NDB. Could not acquire global schema lock |
    | Error | 1296 | Got error 4009 ‘Cluster Failure’ from NDB |
    +——-+——+———————————————————————————+
    2 rows in set (0.00 sec)

    mysql> show processlist;
    +—-+————-+———–+——–+———+——–+———————————+——————+
    | Id | User | Host | db | Command | Time | State | Info |
    +—-+————-+———–+——–+———+——–+———————————+——————+
    | 1 | system user | | NULL | Daemon | 162545 | Waiting for ndbcluster to start | NULL |
    | 6 | root | localhost | hello1 | Query | 0 | NULL | show processlist |
    +—-+————-+———–+——–+———+——–+———————————+——————+
    2 rows in set (0.00 sec)

    mysql>

    Please let us know how to overcome this and create the NDB tables

  • http://love-loves.ru/ love

    To me is over what to think. Thanks big

  • http://na nozyneo

    hello, good morning, im a newbie with Mysql Clustering,
    i followed every instruction, it was working, and then we will fall

    testinf in DB set-up

    i followed every instruction
    and then when i execute this command

    ‘CREATE TABLE cluster_test (i INT) ENGINE=NDBCLUSTER;’

    an error occured and prompt me this

    ‘ERROR 157 (HY000): Could not connect to storage engine’

    ————————————————–
    Here is the actual sequence of commands made;

    mysql> show databases;
    +——————–+
    | Database |
    +——————–+
    | information_schema |
    | mysql |
    | ndbinfo |
    | test |
    +——————–+
    4 rows in set (0.00 sec)

    mysql> create database testdb;
    Query OK, 1 row affected, 1 warning (0.00 sec)

    mysql> use testdb;
    Database changed
    mysql> create table t (seq int, name varchar(10)) engine = ndbcluster;
    ERROR 157 (HY000): Could not connect to storage engine
    mysql>

    ——————————————————–

    Where did I go wrong?

    Any help will be highly appreciated.

    Thank you very much. :)

  • http://linuxadminzone.com jagbir

    Hello nozyneo, My wild guess suggest you to please verify the port/ip address etc from your config file. You should also check that any firewall/host security also not preventing any cluster communication via port/ipaddress etc.

  • http://love-vsem.ru/ знакомства

    I think it will turn out should

  • http://na nozyneo

    hello jagbir,

    thanks for the help, my firewall is on but i already permit port:1186, 3306 for this operation but still error occured.

    do i need to turn-off the firewall (iptables) ?

    thanks

  • http://linuxadminzone.com jagbir

    Hello nozyneo, I would suggest you to turn off firewall and then test to confirm that firewall etc are not causing any issue.

  • http://na nozyneo

    Hello Jagbir, i stop the firewall then the connection works, thanks

    how about i will start the firewall,

    what port numbers i will configure as open/excempted in firewall.

    thank you very much

  • http://linuxadminzone.com jagbir

    Glad to know that connection works. I guess you already opened port 1186, 3306 so it should work in general but as the case with you, you may need to verify your firewall config to make sure that these ports are accessible. You may also need to read mysql cluster document for any other requirements, though I didn’t faced them yet. The link to mysql cluster documentation is provided in the article.

  • http://na nozyneo

    hello Jagbir, thank you for all the help :) Very Good Article

    Im looking for some other article written by you.

    How about MySQL with Heartbeat?

  • http://na nozyneo

    Hello Jagbir, can i install PhpMyADmin Web UI on both clusters?

  • http://linuxadminzone.com jagbir

    Hello nozyneo, you should be able to do it easily, check my article on installing phpmyadmin for multiple mysql hosts: http://linuxadminzone.com/install-and-configure-phpmyadmin-to-manage-multiple-mysql-servers/

  • http://uroki-vsem.ru/ видеокурсы

    Interesting but what you are going to do further?

  • http://games-vsem.ru/ мини игры

    And it is possible more in detail, I in any way won’t understand)))}

  • niky

    hello Jagbir, thank you for all the help Very Good Article.

    But there have been some problems. When I start mysqld api will not start.hope to get your help, thank you.

    [mysqld(API)] 2 node(s)
    id=4 (not connected, accepting connect from any host)
    id=5 (not connected, accepting connect from any host)

  • Pingback: .play.open.minded. » Blog Archive » MySQL ERROR 157 (HY000)

  • Tim

    I seem to have an issue with my data connections, they just report as starting…. Any ideas?

    ndb_mgm> show
    Connected to Management Server at: 1XX.XX8.XX0.190:1186
    Cluster Configuration
    ———————
    [ndbd(NDB)] 2 node(s)
    id=2 @1XX.XX8.XX0.195 (mysql-5.1.56 ndb-7.1.15, starting, Nodegroup: 0)
    id=3 @1XX.XX8.XX0.196 (mysql-5.1.56 ndb-7.1.15, starting, Nodegroup: 0)

  • http://karatetraining.org/weblog Sensei Mitch

    I am having the same issue with my data connections.

    ndb_mgm> show
    Connected to Management Server at: localhost:1186
    Cluster Configuration
    ———————
    [ndbd(NDB)] 2 node(s)
    id=2 (not connected, accepting connect from 1**.**.**.**)
    id=3 (not connected, accepting connect from 1**.**.**.**)

    [ndb_mgmd(MGM)] 1 node(s)
    id=1 @1**.**.**.** (mysql-5.1.56 ndb-7.1.18)

    [mysqld(API)] 2 node(s)
    id=4 (not connected, accepting connect from any host)
    id=5 (not connected, accepting connect from any host)
    ——————–

    when I run tcpdump I see the data/sql node attempting to connect but the connection never gets made, just a bunch of syn packets. On thing I noticed, the data/sql nodes are trying to connect to id=0 NOT id=1.

  • Pingback: oracle tuning

  • Pingback: XMPlay Standalone

  • Pingback: знакомства

  • Pingback: Mysql Cluster 7.1.9 @ @ Secure Is EasySecure Is Easy

  • monica

    thank you very much for this artical
    sorry my english is but, i’m new in mysql-cluster
    i have this message when i try to start mysql in storage nodes :
    Starting MySQL……………….Manager of pid-file quit w[ÃCHOUÃ]pdating file.

    please your help, it ‘s very urgent

  • http://linuxadminzone.com jagbir

    Hi monica, I don’t think the error Manager of pid-file quit without updating file is specifically related to cluster step. could you try stopping/killing all mysql instances (ps aux | grep mysqld) and then remove mysql pid file from /var/run or wherever it is stored? you can also check the mysql log for any clue why its happening. Start mysql again and it should go fine.

    -Jagbir

  • virender

    hi
    you wrote a wonder full tutorial
    but i m loooking turorial for configure mysql cluster loadbalance in case of failover configured on centos

Previous post:

Next post: