MySQL Cluster database & MySQL Replication
RSS icon Email icon Home icon

  • Deploying MySQL Cluster over multiple hosts

    Posted on July 10th, 2009 andrew 77 comments

    This post builds upon the earlier article (Creating a simple Cluster on a single LINUX host) which explained how to install and run a Cluster where all of the nodes run on the same physical host.

    The single host solution is not great for a real deployment – MySQL Cluster is designed to provide a High Availability (HA) solution by synchronously replicating data between data nodes – if all of the data nodes run on a single host that that machine is a single point of failure.

    MySQL Cluster running accross 2 hosts

    MySQL Cluster running accross 2 hosts

    This article demonstrates how to split the nodes between hosts; the configuration will still be fairly simple, using just 2 machines but it should be obvious how to extend it to more.

    This new Cluster will be split across 2 machines, with all functions duplicated on each machine as shown in the diagram.

    Downloading and installing

    In the following example, host “ws1″ has the IP Address 192.168.0.3 and “ws2″ has 192.168.0.4.

    Browse to the MySQL Cluster LINUX download page at mysql.com and download the correct version (32 or 64 bit) and store it in the desired directory (in my case, /home/billy/mysql) on both hosts and then extract and rename the new folder to something easier to work with…

    [billy@ws1 mysql]$ tar xvf mysql-cluster-gpl-7.0.6-linux-x86_64-glibc23.tar.gz
[billy@ws1 mysql]$ mv mysql-cluster-gpl-7.0.6-linux-x86_64-glibc23 7_0_6

[billy@ws2 mysql]$ tar xvf mysql-cluster-gpl-7.0.6-linux-x86_64-glibc23.tar.gz
[billy@ws2 mysql]$ mv mysql-cluster-gpl-7.0.6-linux-x86_64-glibc23 7_0_6

    Setup the files that will be needed for the MySQL Server processes (mysqld) to run correctly on each host…

    [billy@ws1 mysql]$ cd 7_0_6
[billy@ws1 7_0_6]$ scripts/mysql_install_db --basedir=/home/billy/mysql/7_0_6 --datadir=/home/billy/mysql/7_0_6/data

[billy@ws2 mysql]$ cd 7_0_6
[billy@ws2 7_0_6]$ scripts/mysql_install_db --basedir=/home/billy/mysql/7_0_6 --datadir=/home/billy/mysql/7_0_6/data

    Configure and run the Cluster

    Create a sub-directory called “conf” on each host and create the following 3 files:

    config.ini – note that this file is used by the management nodes and as we’re going to run an ndb_mgmd process on each host, this is created on both ws1 and ws2 

    [ndbd default]
noofreplicas=2

[ndbd]
hostname=192.168.0.3
id=1

[ndbd]
hostname=192.168.0.4
id=2

[ndb_mgmd]
id = 101
hostname=192.168.0.3

[ndb_mgmd]
id = 102
hostname=192.168.0.4

[mysqld]
id=51
hostname=192.168.0.3

[mysqld]
id=52
hostname=192.168.0.4

    my.1.conf – to be used by the MySQL Server on ws1 and so store on ws1

    [mysqld]
ndb-nodeid=51
ndbcluster
datadir=/home/billy/mysql/7_0_6/data
basedir=/home/billy/mysql/7_0_6
port=3306
server-id=51
log-bin

    my.2.conf - to be used by the MySQL Server on ws2 and so store on ws2

    [mysqld]
ndb-nodeid=52
ndbcluster
datadir=/home/billy/mysql/7_0_6/data
basedir=/home/billy/mysql/7_0_6
port=3306
server-id=52
log-bin

    Those files configure the nodes that make up the Cluster. From a command prompt window, launch the management nodes:

    [billy@ws1 7_0_6]$ bin/ndb_mgmd --initial -f conf/config.ini --configdir=/home/billy/mysql/7_0_6/conf

[billy@ws2 7_0_6]$ bin/ndb_mgmd --initial -f conf/config.ini --configdir=/home/billy/mysql/7_0_6/conf

    Check that the management nodes are up and running:

    [billy@ws1 7_0_6]$ bin/ndb_mgm
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]	2 node(s)
id=1 (not connected, accepting connect from 192.168.0.3)
id=2 (not connected, accepting connect from 192.168.0.4)

[ndb_mgmd(MGM)]	2 node(s)
id=101	@192.168.0.3  (mysql-5.1.34 ndb-7.0.6)
id=102 (not connected, accepting connect from 192.168.0.4)

[mysqld(API)]	2 node(s)
id=51 (not connected, accepting connect from 192.168.0.3)
id=52 (not connected, accepting connect from 192.168.0.4)

ndb_mgm> quit

[billy@ws1 7_0_6]$ bin/ndb_mgm -c 192.168.0.4:1186
ndb_mgm> show
Connected to Management Server at: 192.168.0.4:1186
Cluster Configuration
---------------------
[ndbd(NDB)]	2 node(s)
id=1 (not connected, accepting connect from 192.168.0.3)
id=2 (not connected, accepting connect from 192.168.0.4)

[ndb_mgmd(MGM)]	2 node(s)
id=101 (not connected, accepting connect from 192.168.0.3)
id=102	@192.168.0.4  (mysql-5.1.34 ndb-7.0.6)

[mysqld(API)]	2 node(s)
id=51 (not connected, accepting connect from 192.168.0.3)
id=52 (not connected, accepting connect from 192.168.0.4)
ndb_mgm> quit

    and then start the 2 data nodes (ndbd) and 2 MySQL API/Server nodes (mysqld) and then check that they’re all up and running:

    [billy@ws1 7_0_6]$ bin/ndbd --initial -c localhost:1186
2009-06-17 13:05:47 [ndbd] INFO     -- Configuration fetched from 'localhost:1186', generation: 1

[billy@ws2 7_0_6]$ bin/ndbd --initial -c localhost:1186
2009-06-17 13:05:51 [ndbd] INFO     -- Configuration fetched from 'localhost:1186', generation: 1

[billy@ws1 7_0_6]$ bin/mysqld --defaults-file=conf/my.1.conf&

[billy@ws2 7_0_6]$ bin/mysqld --defaults-file=conf/my.2.conf&

[billy@ws1 7_0_6]$ bin/ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]	2 node(s)
id=1	@127.0.0.1  (mysql-5.1.34 ndb-7.0.6, Nodegroup: 0, Master)
id=2	@192.168.0.4  (mysql-5.1.34 ndb-7.0.6, Nodegroup: 0)

[ndb_mgmd(MGM)]	2 node(s)
id=101	@127.0.0.1  (mysql-5.1.34 ndb-7.0.6)
id=102	@127.0.0.1  (mysql-5.1.34 ndb-7.0.6)

[mysqld(API)]	2 node(s)
id=51	@192.168.0.3  (mysql-5.1.34 ndb-7.0.6)
id=52	@192.168.0.4  (mysql-5.1.34 ndb-7.0.6)
ndb_mgm> quit

    Using the Cluster

    There are now 2 API nodes/MySQL Servers/mysqlds running on the 2 different hosts; both accessing the same data. Each of those nodes can be accessed by the mysql client using the hostname and ports that were configured in the my.X.cnf files. For example, we can access the first of those nodes (node 51) in the following way (each API node is accessed using the host and port number in its associate my.X.cnf file:

    [billy@ws1 7_0_6]$ bin/mysql -h localhost -P 3306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.34-ndb-7.0.6-cluster-gpl-log MySQL Cluster Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test;
Database changed
mysql> create table assets (name varchar(30) not null primary key,
-> value int) engine=ndb;
Query OK, 0 rows affected (0.99 sec)
mysql> insert into assets values ('Car','1900');
Query OK, 1 row affected (0.03 sec)
mysql> select * from assets;
+------+-------+
| name | value |
+------+-------+
| Car  |  1900 |
+------+-------+
1 row in set (0.00 sec)
mysql> quit
Bye

    If you see “ERROR 1130 (HY000): Host ’192.168.0.3′ is not allowed to connect to this MySQL server” then you need to set up the privileges (repeat on each host) – in a real system, you’d likely want tighter security that this:

    [billy@ws1 7_0_6]$ bin/mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.34-ndb-7.0.6-cluster-gpl-log MySQL Cluster Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> GRANT ALL ON *.* TO ''@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL ON *.* TO ''@'192.168.0.4';
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye

[billy@ws2 7_0_6]$ bin/mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.34-ndb-7.0.6-cluster-gpl-log MySQL Cluster Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> GRANT ALL ON *.* TO ''@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT ALL ON *.* TO ''@'192.168.0.3';
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye

    Note that as this table is using the ndb (MySQL Cluster) storage engine, the data is actually held in the data nodes rather than in the SQL node and so we can access the exact same data from the the other SQL nodes:

    [billy@ws1 7_0_6]$ bin/mysql -h 192.168.0.4 -P 3306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.1.34-ndb-7.0.6-cluster-gpl-log MySQL Cluster Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test;
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> select * from assets;
+------+-------+
| name | value |
+------+-------+
| Car  |  1900 |
+------+-------+
1 row in set (0.01 sec)
mysql> quit
Bye

    Your next steps

    This is still a fairly simple, contrived set up. Hopefully it’s clear how additional data or SQL nodes could be added and in a larger deployment you may well decide to run the management and SQL nodes on different hosts to the data nodes (in fact, when starting up the management nodes there is a warning message suggesting you deploy them elsewhere!).

    To move the management node to a 3rd, independent physical host (and a 4th one if you want 2 management nodes for redundancy -a future article will explain when you might want to do that),  just change the IP address in the  [ndb_mgmd] section of config.ini and then run the ndb_mgmd executable on that new host. Note that the management node consumes very few resources and so can share that host with other functions/applications (e.g. SQL Server nodes but not data nodes).

    You’d also set several more variables in the configuration files in order to size and tune your Cluster.

    In this article, I used 2 LINUX hosts but you could extend the Windows example introduced in My first Cluster running on Windows in exactly the same way.

    MySQL Cluster , , ,
     

    73 responses to “Deploying MySQL Cluster over multiple hosts” RSS icon

    • Thank you very much SUGUN AND ANDREW i have successfully rstored the databases on one and get on the second node.

      There is one issue, i have setup the cluster accord to this article but when one of my mgm node got down (which show master in normal ruuning) the other one could not get online untill i manual online the master.. IS it possible that one will run in case of down of other one automatically

    • Is this possible to have one node available in case of down of other node automatically

    • [root@Store01p 727i686]# bin/ndb_mgm -e show
      Connected to Management Server at: localhost:1186
      Cluster Configuration
      ———————
      [ndbd(NDB)] 2 node(s)
      id=1 @127.0.0.1 (mysql-5.5.25 ndb-7.2.7, starting, Nodegroup: 0)
      id=2 @192.168.1.42 (mysql-5.5.25 ndb-7.2.7, starting, Nodegroup: 0)

      [ndb_mgmd(MGM)] 2 node(s)
      id=101 @192.168.1.41 (mysql-5.5.25 ndb-7.2.7)
      id=102 @192.168.1.42 (mysql-5.5.25 ndb-7.2.7)

      [mysqld(API)] 2 node(s)
      id=51 (not connected, accepting connect from 192.168.1.41)
      id=52 (not connected, accepting connect from 192.168.1.42)

      wwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwww
      1:
      Please tell y this is hang on starting…
      I have checked it by other user aswell but its still ??

    • Please help
      When ever I issue command bin/mysqld –defaults-file=conf/my.2.conf&

      I Got This output—
      Neither mdbd is going out of “starting” nor this response understandable
      ==========================

      121124 4:27:07 [Warning] No argument was provided to –log-bin, and –log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use ‘–log-b in=Store02p-bin’ to avoid this problem.
      121124 4:27:07 [Note] Plugin ‘FEDERATED’ is disabled.
      121124 4:27:07 InnoDB: The InnoDB memory heap is disabled
      121124 4:27:07 InnoDB: Mutexes and rw_locks use InnoDB’s own implementation
      121124 4:27:07 InnoDB: Compressed tables use zlib 1.2.3
      121124 4:27:07 InnoDB: Using Linux native AIO
      121124 4:27:07 InnoDB: Initializing buffer pool, size = 128.0M
      121124 4:27:07 InnoDB: Completed initialization of buffer pool
      121124 4:27:07 InnoDB: highest supported file format is Barracuda.
      121124 4:27:07 InnoDB: Waiting for the background threads to start
      121124 4:27:08 InnoDB: 1.1.8 started; log sequence number 1595675
      121124 4:27:43 [Note] Starting Cluster Binlog Thread
      121124 4:27:43 [Note] Recovering after a crash using Store02p-bin
      121124 4:27:43 [Note] Starting crash recovery…
      121124 4:27:43 [Note] Crash recovery finished.
      121124 4:27:43 [Note] Server hostname (bind-address): ’0.0.0.0′; port: 5000
      121124 4:27:43 [Note] – ’0.0.0.0′ resolves to ’0.0.0.0′;
      121124 4:27:43 [Note] Server socket created on IP: ’0.0.0.0′.
      121124 4:27:43 [Note] Event Scheduler: Loaded 0 events
      121124 4:27:43 [Note] bin/mysqld: ready for connections.
      Version: ’5.5.25a-ndb-7.2.7-gpl-log’ socket: ‘/tmp/mysql.sock’ port: 5000 MyS QL Cluster Community Server (GPL)
      [admin@Store02p 727i686]$ 121124 4:28:13 [Warning] NDB : Tables not available after 30 seconds. Consider increasing –ndb-wait-setup value

    • fairsimple – you need to figure out why the data nodes aren’t starting correctly. Check the cluster logs – should be in the ndb_mgmd’s datadir and the ndbds’s logs – should be in their datadirs.

    • Inwhich dir do u think in following:
      wwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwww

      Connected to Management Server at: localhost:1186
      Cluster Configuration
      ———————
      [ndbd(NDB)] 2 node(s)
      id=1 @127.0.0.1 (mysql-5.5.25 ndb-7.2.7, starting, Nodegroup: 0)
      id=2 @192.168.1.42 (mysql-5.5.25 ndb-7.2.7, starting, Nodegroup: 0)

      [ndb_mgmd(MGM)] 2 node(s)
      id=101 @192.168.1.41 (mysql-5.5.25 ndb-7.2.7)
      id=102 @192.168.1.42 (mysql-5.5.25 ndb-7.2.7)

      [mysqld(API)] 2 node(s)
      id=51 (not connected, accepting connect from 192.168.1.41)
      id=52 (not connected, accepting connect from 192.168.1.42)

      [root@Store01p 727i686]# ls -la
      total 544
      drwxrwxrwx 15 admin root 4096 Nov 24 06:33 .
      drwxrwxrwx 3 admin root 4096 Nov 24 02:22 ..
      drwxr-xr-x 2 admin root 4096 Nov 24 04:55 bin
      drwxr-xr-x 2 admin root 4096 Nov 24 06:34 conf
      -rw-r–r– 1 admin root 17987 Jul 13 05:27 COPYING
      drwxr-xr-x 6 admin root 4096 Nov 24 06:30 data
      drwxr-xr-x 2 admin root 4096 Nov 24 02:12 docs
      drwxr-xr-x 4 admin root 4096 Nov 24 02:12 include
      -rw-r–r– 1 admin root 7604 Jul 13 05:27 INSTALL-BINARY
      drwxr-xr-x 3 admin root 4096 Nov 24 02:11 lib
      drwxr-xr-x 4 admin root 4096 Nov 24 02:12 man
      drwxr-xr-x 10 admin root 4096 Nov 24 02:12 mysql-test
      -rw-r–r– 1 admin root 416548 Nov 24 06:51 ndb_101_cluster.log
      -rw-r–r– 1 admin root 536 Nov 24 06:34 ndb_101_out.log
      -rw-r–r– 1 root root 4 Nov 24 06:33 ndb_101.pid
      drwxr-x— 2 admin root 4096 Nov 24 02:38 ndb_1_fs
      -rw-r–r– 1 admin root 26065 Nov 24 06:35 ndb_1_out.log
      -rw-r–r– 1 admin root 4 Nov 24 06:35 ndb_1.pid
      -rw-r–r– 1 admin root 2552 Jul 13 05:27 README
      drwxr-xr-x 2 admin root 4096 Nov 24 02:12 scripts
      drwxr-xr-x 29 admin root 4096 Nov 24 02:12 share
      drwxr-xr-x 4 admin root 4096 Nov 24 02:11 sql-bench
      drwxr-xr-x 2 admin root 4096 Nov 24 02:12 support-files

    • Secondly,
      1:
      Why its showning me “127.0.0.1″ ?

      id=1 @127.0.0.1 (mysql-5.5.25 ndb-7.2.7, starting, Nodegroup: 0)

      2:
      Do I have to setup IP in etc/hosts ?

    • Please help Its urgent—- I have been working with it for 4 days—-or if u could share skype id for some time so we can talk

      Here are the logs on cluster on Node A same are on Node B
      wwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwww

      2012-11-24 07:49:37 [MgmtSrvr] INFO — Node 1: Initial start, waiting for 2 to connect, nodes [ all: 1 and 2 connected: 1 no-wait: ]
      2012-11-24 07:49:39 [MgmtSrvr] INFO — Node 2: Initial start, waiting for 1 to connect, nodes [ all: 1 and 2 connected: 2 no-wait: ]
      2012-11-24 07:49:40 [MgmtSrvr] INFO — Node 1: Initial start, waiting for 2 to connect, nodes [ all: 1 and 2 connected: 1 no-wait: ]
      2012-11-24 07:49:42 [MgmtSrvr] INFO — Node 2: Initial start, waiting for 1 to connect, nodes [ all: 1 and 2 connected: 2 no-wait: ]
      2012-11-24 07:49:43 [MgmtSrvr] INFO — Node 1: Initial start, waiting for 2 to connect, nodes [ all: 1 and 2 connected: 1 no-wait: ]
      2012-11-24 07:49:45 [MgmtSrvr] INFO — Node 2: Initial start, waiting for 1 to connect, nodes [ all: 1 and 2 connected: 2 no-wait: ]
      2012-11-24 07:49:46 [MgmtSrvr] INFO — Node 1: Initial start, waiting for 2 to connect, nodes [ all: 1 and 2 connected: 1 no-wait: ]

    • Still Same Output
      wwwwwwwwwwwwwwwwwwwwwww

      Connected to Management Server at: localhost:1186
      Cluster Configuration
      ———————
      [ndbd(NDB)] 2 node(s)
      id=1 @192.168.1.41 (mysql-5.5.25 ndb-7.2.7, starting, Nodegroup: 0)
      id=2 @192.168.1.42 (mysql-5.5.25 ndb-7.2.7, starting, Nodegroup: 0)

      [ndb_mgmd(MGM)] 2 node(s)
      id=101 @192.168.1.41 (mysql-5.5.25 ndb-7.2.7)
      id=102 @192.168.1.42 (mysql-5.5.25 ndb-7.2.7)

      [mysqld(API)] 2 node(s)
      id=51 (not connected, accepting connect from 192.168.1.41)
      id=52 (not connected, accepting connect from 192.168.1.42)

    • No need of getting tension Andrew:;;;

      I have solved it my self and finally got through it Now the things are working fine…

      Its some where firewall issue though I have allowed 1186 and 5000 3306 but it wasnt working..
      Just stoped the fw and its connected…Is there any other port hidden which should be permitted?

    • fairsimple – ports also need to be open for the data nodes to connect to each other and for the mysqlds to connect to the data nodes. Take a look at http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-ndbd-definition.html#ndbparam-ndbd-serverport to configure the ports used by the mysqlds to connect to the data nodes. Of course, you also have the ports that the mysqld allows clients to connect on (3306 by default).

      Andrew.

    • do u refer any step by step cluster administration tutorials?

      Any Db conversion tutorials?

    • Specially:

      If one have mgm server on third node should he use that mgm’s Ip to auto failover and HA?

    • Hi Andrew

      What about HA in your this article or if someone wants to have auto failover and have mgmd on third node which IP or setts he should do for web apps to use HA of mysql cluster

    • do u have any idea?

    • Hi
      What about HA in your this article or if someone wants to have auto failover and have mgmd on third node which IP or setts he should do for web apps to use HA of mysql cluster

    • Hi,
      having read this tutorial, I wonder why putting three nodes (mgmt,db,sql) on a single host in two host cluster is so problematic in terms of HA? In fact, we have all nodes doubled.Is it any technical reason that mgmt node cannot coexist with db node on the same host “Note that the management node consumes very few resources and so can share that host with other functions/applications (e.g. SQL Server nodes but not data nodes).” so that it affects HA? I have powerful-enough machine to host all of them.All FAQs simply are simply reapeating that one needs at least 4 hosts for full redundancy but don’t explain why..Will be grateful if someone could shed some light on it.
      Thanks in advance.

    • p0is0n,

      Take a look at http://www.clusterdb.com/mysql-cluster/mysql-cluster-fault-tolerance-impact-of-deployment-decisions/ for an explanation.

      Andrew.

    • Andrew,
      thanks for help.
      From what I understood from this example, colocation of mgmt and data nodes implies that in case of a failure of the entire host with active arbitrator, service will be lost because it will end up with inactive arbitrator on the remaining node. Does that mean that failure of the arbitrator (that is:mgmd) does not effect in electing a new one?Not sure what is the difference between this scenario and another -when we have 2 hosts with mgmt nodes and the active arbitrator host fails entirely. I guess in both cases the new arbitrator election should happen. Pls advice. Thanks

    • And one more thing – is it possible to have mysql+db nodes on linux hosts and management nodes on windows hosts? Will it work flawlessly and is supported configuration?Unfortunately, if I had to spare some resources for managements on external hosts, these would have to be windows boxes :/

    • p0i0n,

      if you lost the host running the arbitrator and half of the data nodes then that doesn’t leave you with a quorum to elect a new arbitrator and so the only safe thing to do is shut down.

      There should be no problem running the ndb_mgmd on Windows and everything else on Linux.

      Andrew.

    • Hi Andrew,

      I’ve run into an issue in our environment, we are using mysql cluster manager to manage our data nodes.

      It seems that the data nodes (running ndbmtd) assign random local ports, this is causing issues as our database tier is firewalled. Is there a way to force the multi threaded engine to specific ports?

      Nick

    • Hi Nick,

      take a look at http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-ndbd-definition.html#ndbparam-ndbd-serverport

      Andrew.

    « Older Comments

    4 Trackbacks / Pingbacks

    Leave a reply

Search

Maidenhead self catering accommodation
This site is independent of Oracle/MySQL