-
Deploying MySQL Cluster over multiple hosts
Posted on July 10th, 2009 77 commentsThis 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.
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-binmy.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-binThose 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> quitUsing 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 ByeIf 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 ByeYour 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.
73 responses to “Deploying MySQL Cluster over multiple hosts”

-
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
-
fairsimple November 24th, 2012 at 09:24
[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 ?? -
fairsimple November 24th, 2012 at 09:31
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 November 24th, 2012 at 11:54
Inwhich dir do u think in following:
wwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwConnected 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 -
fairsimple November 24th, 2012 at 12:03
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 ? -
fairsimple November 24th, 2012 at 12:52
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
wwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwwww2012-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: ] -
fairsimple November 24th, 2012 at 12:57
Still Same Output
wwwwwwwwwwwwwwwwwwwwwwwConnected 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) -
fairsimple November 24th, 2012 at 13:10
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 November 25th, 2012 at 12:37
do u refer any step by step cluster administration tutorials?
Any Db conversion tutorials?
-
fairsimple November 25th, 2012 at 12:39
Specially:
If one have mgm server on third node should he use that mgm’s Ip to auto failover and HA?
-
fairsimple November 27th, 2012 at 00:02
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
-
fairsimple November 27th, 2012 at 17:46
do u have any idea?
-
fairsimple December 1st, 2012 at 15:00
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 -
p0is0n December 5th, 2012 at 23:11
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 December 6th, 2012 at 11:07
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 -
p0is0n December 6th, 2012 at 17:23
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 :/
-
Nick Shaw February 4th, 2013 at 11:03
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
4 Trackbacks / Pingbacks
-
Creating a simple Cluster on a single LINUX host @ Andrew Morgan’s MySQL Cluster Database Blog July 10th, 2009 at 19:37
[...] physical hosts in the interests of performance and redundancy (take a look at the new article (Deploying MySQL Cluster over multiple host) to see how to do that). You’d also set several more variables in the configuration files in [...]
-
Setting up MySQL Asynchronous Replication for High Availability @ Andrew Morgan’s MySQL Cluster Database Blog August 4th, 2009 at 13:49
[...] will focus on setting up the replication, you can refer to Deploying MySQL Cluster over multiple hosts for the steps to configure and run each MySQL Cluster [...]
-
Download, install, configure, run and test MySQL Cluster in under 15 minutes @ Andrew Morgan’s MySQL Cluster Database Blog June 25th, 2010 at 08:18
[...] A series of quick-start guides are now available to get you up and running with MySQL Cluster in as little time as possible; they are available for LINUX/Mac OS X, Windows and Solaris. The configuration is intentionally a simple one – 2 data nodes, 1 management node and 1 MySQL Server. Once you have this up and running, your next experiment may be to extend this over multiple hosts. [...]
-
openSUSE 11.3 announces introduction of MySQL Cluster @ Andrew Morgan’s MySQL Cluster Database Blog July 22nd, 2010 at 15:57
[...] I prefer installing MySQL Cluster from the tar ball as it gives me more control (for example, openSUSE is using MySQL Cluster 7.0 but I prefer to use [...]
Leave a reply
-



msq August 30th, 2012 at 09:49