Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Thanks for your reply.

I need your help to configure MySQL-Clustering server in following manner.

I have two hosts as,
1] 192.168.1.107
2] 192.168.1.125

Each hosts have node entries as,

On host: 192.168.1.107:
=======================
1] MGMT Node
2] DATA Node
3] MYSQL[API] Node

On host: 192.168.1.125:
=======================
1] MGMT Node
2] DATA Node
3] MYSQL[API] Node

Both the servers are placed behind "load balancer" to manage incoming connections. So the incoming connections or requests to the server will be managed (distributed) by the load balancer to control traffic on the server.

The load balancer is already configured in such a way, like if one server goes down or dead (due to the system crash) then load balancer will responsible to forward all incoming requests or all request or connections should be respond by live one (Server).

My current configuration files are as follows:
----------------------------------------------

"my.cnf" On host: 192.168.1.107:
================================
# The MySQL server
[mysql_cluster]
ndb-connectstring=192.168.1.107:1186

[ndbd]
connect-string=192.168.1.107:1186,192.168.1.125:1186

[ndb_mgm]
connect-string=192.168.1.107:1186

[ndb_mgmd]
config-file=/var/lib/mysql-cluster/config.ini

[mysqld]
ndbcluster
ndb-connectstring=192.168.1.107


"my.cnf" On host: 192.168.1.125:
================================
# The MySQL server
[mysql_cluster]
ndb-connectstring=192.168.1.125:1186

[ndbd]
connect-string=192.168.1.125:1186,192.168.1.107:1186

[ndb_mgm]
connect-string=192.168.1.125:1186

[ndb_mgmd]
config-file=/var/lib/mysql-cluster/config.ini

[mysqld]
ndbcluster
ndb-connectstring=192.168.1.125

AND

"config.ini" On host: 192.168.1.107:
====================================

[NDBD DEFAULT]
NoOfReplicas=2    # Number of replicas
DataMemory=3500M    # How much memory to allocate for data storage
IndexMemory=1000M   # How much memory to allocate for index storage
datadir=/usr/local/mysql/data   # Directory for this data node's datafiles
#MaxNoOfConcurrentOperations=1048576
#MaxNoOfConcurrentTransactions= 1048576
#MaxNoOfLocalOperations=1048576
#MaxNoOfConcurrentIndexOperations=16384
#MaxNoOfConcurrentScans=500
#LockPagesInMainMemory=Y
# TCP/IP options:
[TCP DEFAULT]
portnumber=2202   # This the default; however, you can use any

# Management process options:
[NDB_MGMD DEFAULT]
datadir=/var/lib/mysql-cluster  # Directory for MGM node logfiles

[NDB_MGMD]
hostname=192.168.1.107           # Hostname or IP address of MGM node

# Options for data node "A":
[NDBD]
id=3                             # (one [NDBD] section per data node)
hostname=192.168.1.107           # Hostname or IP address

# Options for data node "B":
[NDBD]
id=4
hostname=192.168.1.125          # Hostname or IP address

# SQL node options:
[MYSQLD]
id=5
hostname=192.168.1.107



"config.ini" On host: 192.168.1.125:
====================================
[NDBD DEFAULT]
NoOfReplicas=2    # Number of replicas
DataMemory=3500M    # How much memory to allocate for data storage
IndexMemory=1000M   # How much memory to allocate for index storage
datadir=/usr/local/mysql/data   # Directory for this data node's datafiles
#MaxNoOfConcurrentOperations=1048576
#MaxNoOfConcurrentTransactions= 1048576
#MaxNoOfLocalOperations=1048576
#MaxNoOfConcurrentIndexOperations=16384
#MaxNoOfConcurrentScans=500
#LockPagesInMainMemory=Y
# TCP/IP options:
[TCP DEFAULT]
portnumber=2202   # This the default; however, you can use any

# Management process options:
[NDB_MGMD DEFAULT]
datadir=/var/lib/mysql-cluster  # Directory for MGM node logfiles

[NDB_MGMD]
hostname=192.168.1.125           # Hostname or IP address of MGM node

# Options for data node "A":
[NDBD]
id=3                             # (one [NDBD] section per data node)
hostname=192.168.1.107           # Hostname or IP address

# Options for data node "B":
[NDBD]
id=4
hostname=192.168.1.125          # Hostname or IP address

# SQL node options:
[MYSQLD]
id=5
hostname=192.168.1.125

With respect to the above configuration I am trying to configure the servers as,

To create the replication between two data nodes. Means if one server goes down or crashes then automatically the same data should be served by another one live server.

In short, the nodes 'A' and 'B' should have data replication in between them and same data should be served if one will go down. All incoming request or connection are managed by the load balancer.

I have tried to implement the same with above configuration using following steps on both server but there are some problems as,

On host 192.168.1.107
=====================
1] [root@ns mysql-cluster]# ndb_mgmd -f /var/lib/mysql-cluster/config.ini
2009-09-07 17:48:24 [MgmSrvr] INFO     -- NDB Cluster Management Server.
mysql-5.1.34 ndb-7.0.6
2009-09-07 17:48:24 [MgmSrvr] INFO     -- Loaded config from
'//mysql-cluster/ndb_1_config.bin.1'

2] [root@ns mysql-cluster]# /usr/local/mysql/bin/ndbd
2009-09-07 17:49:36 [ndbd] INFO     -- Configuration fetched from
'192.168.1.107:1186', generation: 1

3] [root@ns mysql-cluster]# mysqld_safe --ndbcluster
--ndb-connectstring=192.168.1.107 &
[1] 12189
[root@ns mysql-cluster]# 090907 17:50:17 mysqld_safe Logging to
'/var/lib/mysql/ns.err'.
090907 17:50:17 mysqld_safe Starting mysqld daemon with databases from
/var/lib/mysql

OUT PUT OF (ndb_mgm -e show):
=============================
[root@ns mysql-cluster]# ndb_mgm -e show
Connected to Management Server at: 192.168.1.107:1186
Cluster Configuration
---------------------
[ndbd(NDB)]        2 node(s)
id=3        @192.168.1.107  (mysql-5.1.34 ndb-7.0.6, Nodegroup: 0, Master)
id=4 (not connected, accepting connect from 192.168.1.125)

[ndb_mgmd(MGM)]        1 node(s)
id=1        @192.168.1.107  (mysql-5.1.34 ndb-7.0.6)

[mysqld(API)]        1 node(s)
id=5        @192.168.1.107  (mysql-5.1.34 ndb-7.0.6)



After that the same steps carried out,
On on host 192.168.1.125:
=========================

1] [root@test mysql-cluster]# ndb_mgmd -f /var/lib/mysql-cluster/config.ini
2009-09-07 17:55:24 [MgmSrvr] INFO     -- NDB Cluster Management Server.
mysql-5.1.34 ndb-7.0.6
2009-09-07 17:55:24 [MgmSrvr] INFO     -- Loaded config from
'//mysql-cluster/ndb_1_config.bin.1'

2] [root@test mysql-cluster]# /usr/local/mysql/bin/ndbd
2009-09-07 17:55:59 [ndbd] INFO     -- Configuration fetched from
'192.168.1.125:1186', generation: 1

3] [root@test mysql-cluster]# mysqld_safe --ndbcluster
--ndb-connectstring=192.168.1.125 &
[2] 5276
[root@test mysql-cluster]# 090907 17:56:30 mysqld_safe Logging to
'/var/lib/mysql/test.testmachine.com.err'.
090907 17:56:30 mysqld_safe Starting mysqld daemon with databases from
/var/lib/mysql


OUT PUT OF (ndb_mgm -e show):
=============================

[root@test mysql-cluster]# ndb_mgm -e show
Connected to Management Server at: 192.168.1.125:1186
Cluster Configuration
---------------------
[ndbd(NDB)]        2 node(s)
id=3 (not connected, accepting connect from 192.168.1.107)
id=4        @192.168.1.125  (mysql-5.1.34 ndb-7.0.6, Nodegroup: 0, Master)

[ndb_mgmd(MGM)]        1 node(s)
id=1        @192.168.1.125  (mysql-5.1.34 ndb-7.0.6)

[mysqld(API)]        1 node(s)
id=5        @192.168.1.107  (mysql-5.1.34 ndb-7.0.6)

The above resulted out put shows that the both data nodes from each server are not able to communicate with each other. So please tell me the proper configuration or the proper steps to implement my scenario perfectly.

Awaiting for your reply.

share|improve this question

2 Answers 2

you have a fundamental problem in your cluster design. Mysql clustering needs 3 servers. Suppose the wire connecting the 2 servers is cut but both machines are still running. If they both continue running they will be getting different data and be out of sync.

So with 2 machines they both must shutdown to prevent a split brain scenario where each node in the cluster has different data.

You can add a third machine that runs only the management node (which takes limited resources) that works with the data nodes to decide how to handle a failure. If the management node goes away but both data nodes are still running they will continue to run.

Here are instructions for a 2 data node + 1 management node configuration: http://dev.mysql.com/tech-resources/articles/mysql-cluster-for-two-servers.html

share|improve this answer

Try this tutorial on howtoforge.

share|improve this answer
    
Hello Dear, My scenario is some different. Please read it carefully. I don't want to to set up A Load-Balanced MySQL Cluster With MySQL 5.1. I have already configured my own load balancer to manage incoming connections and my servers are placed behind it. So please I request you to go through above scenario and tell me the proper steps to configure mysql-cluster as per my scenario. –  Manoj Sep 8 '09 at 9:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.