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.
Best Answer
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