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

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…

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:

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:

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:

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.

That implies that the mysqld is using a non-standard location for its socket. A couple of options:

connect using the IP Address/port number: mysql -h 192.168.0.4 -P 3306 (where the port number is defined in the defaults file that you specify when starting mysqld)
When you start the mysqld process, it should tell you the location it’s using for the socket; you can then specify that location using the “mysql -S” option
Specify where you want the socket in the defaults file you use when starting mysqld by including “socket=/tmp/mysql.sock” which is where the mysql is looking by default (obviously, each mysqld on the same host would need to use a different socket

Just a quick note to say “Thank you”. This post was extremely helpful in understanding the basics of how each piece of the MySQL cluster works together. Thank you for taking the time to share this with the community.

there should be just one instance of each my.*.cnf file and each my.*.cnf file should be used with just one mysqld process. It looks like you’re trying to run 2 mysqld processes who have the same id defined in their my.*.cnf file.

Will it be necessary for the data nodes(say 2), management nodes(2) and the mysqld(2) nodes to be deployed in different machines (total of 6 machines)? Or just 4(2 data+mysqld nodes + 2 management nodes)?

Would you think implementing MySQL cluster in a virtualized(Xen) environment good? My current setup would have two physical servers (8GB available each), no SAN, each machine hosting three VMs (VM for manager, VM for data+sqld #1, VM for data+sqld #2). Totaling 6 VMs overall.

MySQL Cluster is not tested on XEN and you may run into technical issues. The support position is that if you find an error that is specific to running on XEN (or other VMs) then you need to look to the VM provider to provide a fix.

Yes – you could have MySQL Servers on Windows with Data Nodes on LINUX. The restriction to watch out for is that the MySQL Servers (and any other NDB API client) needs to be running on an architecture with the same endienness as the data nodes.

Once you’ve edited your config.ini file, shut down the ndb_mgmd process and then start it again with the –initial option. Then restart each of your data nodes in turn (wait for the 1st data node to come back up before restarting the next one to avoid an outage) – do *not* use the –initial option when starting the data nodes unless you want to remove all of your data.

Great post got it working right away. I was hoping you could explain why the cluster warns that running the management server on the same host as the data node is not advised? I did some googling and couldn’t find a definitive answer.

Thomas, the first thing I would do is to replace 127.0.0.1 with the real IP address (192.168.180.??). 127.0.0.1 only makes sense on that machine whereas the configuration data needs to be shared and interpreted on all hosts in the Cluster.

to get more information, if you were using the start /B approach then try without it (you’ll need more windows to cope with the number of processes). After that check the log files that are created – there should be some clues there.

what makes you think that the data hasn’t been written to the data nodes? The normal way you’d check that it has would be reading the same data through another MySQL Server (mysqld) that is part of the same cluster. Judging by the output from ndb_mgm, you only have a single mysqld.

My bet is that the data is in fact being written to both data nodes – I see no reason to suspect otherwise.

I have 3 offices all connecting to a single database, on a server at my site.
Internet is very slow, so am looking to set up a server in each office, with replication. I assume the best way for everyone to have the same data is to use a cluster with master-master replication.
I will be using ubuntu, running on a VM.
What is the minimum number of machines you would recommend (3 or 4)?

Using asynchronous replication between the sites is the way to go if the connection between them isn’t fast and reliable. If you want to use MySQL Cluster then the minimum fault tolerant configuration for each site/cluster is 3 machines (the management node should run on a different machine than the data nodes or else it can result in the cluster shutting down in the event of network partitioning).

1. Shutdown data node 1
2. Added the new rows
3. Made the node 1 up and data is in sync
4. Now shutdown data node 2
5. Added new rows;
6. After 2 minutes, shutdown the entire cluster using shutdown command.
7. Now when I make all nodes up, there is no data found including our assets table.
Entire data created is lost. Is that a known issue?

Thanks a lot for your reply.
I have got through these. basically my mysqld was already running on Node2 and upon shuting it manually got able to start mysqld.

First: Please tell me in which way we should power off these two nodes (commands wise please).

secondly, incase of power outage, in whet manner we should start them. (commands please)

thirdly, I want all of them(ndb_mgm, ndb, mysqld) on both to be auto on. what should I do?

Fourthly, I have setup a production env based upon your this multi host artilce. that is succesfull and up running but i want to back up their (both) cluster configs and in quick restoreable format. Could u please help me or any step by step doc?

mysqlds can be stopped by issuing the command “mysqladmin -u root -h 127.0.0.1 -P3306 shutdown” and can be started with “mysqld –defaults-file=xxxxx”. The rest of the Cluster can be stopped with “ndb_mgm -e shutdown”. You can safely start ndb_mgmds with the –initial option every time but if you do that for data nodes then all of your data will be deleted.

You can use the infrastructure offered by your OS to automate starting and stopping the processes (for example by placing entries in the /etc/init.d directory on Linux.

To back up your configuration data, copy your config.ini and my.cnf files. You can use the backup command within ndb_mgm to backup the data held in NDB tables and run regular MySQL backup commands for any other (i.e. InnoDB or MyISAM) tables. You can restore NDB tables using the ndb_restore command. These are documented in the MySQL Cluster documentation.

I hv created a db “abc” on Node A using
create database abc; and on Node B db created automatically but when i restores backup on it using bin/mysql -u root -P 3306 -p abc < /home/myuser/abcDump.sql, it restores all tables to Node A including data inside it but no tables found on Node B.
should i use mdbcluster with restore command or what syntax?

I configured MGMT node and mysqld on single host. Data nodes are on two diff machines.
MGMT and data nodes are doing fine. I can get their status via ndb_mgm. But mysqld is not coming up. My steps are listed below:

on mgmt box, I just the scripts/mysql_install_db script and then configued a my.conf file.

2nd thing – make sure that you use the Cluster basedir for mysql_install_db and then the mysqld from that basedir (rather than a non-Cluster mysqld or one that came with a different version of Cluster).

I hv created a db “abc” on Node A using
create database abc; and on Node B db created automatically but when i restores backup on it using bin/mysql -u root -P 3306 -p abc < /home/myuser/abcDump.sql, it restores all tables to Node A including data inside it but no tables found on Node B.
should i use mdbcluster with restore command or what syntax?

msq – for MySQL Cluster, your best bet for backups is to use the online backup command within ndb_mgm and the retire using ndb_restore (note that you’ll need an unused [ndbpai] or [mysqld] section in your config.ini file for ndb_restore to connect to.

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

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.

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 :/

as you’d expect, there are many reasons for why a database operation may be slow – depending on what your application is doing, what the schema looks like and how the database is configured. A good place to start would be this performance white paper.

Restores are performed using the ndb_restore command standalone command and not part of ndb_mgm.

Thanks to this post, I successfully managed to have a working MySQL cluster, that’s great !
I now have a little question : I have two SQL nodes, so data can be accessed independently from mysql-1 or mysql-2.

Do you know if any MySQL connector (I’m thinking of PHP, for example) is able to handle two or more MySQL servers ?

I would like my application to be able to transparently load-balance its connections to MySQL, and also switch to the second MySQL node if the first fails.

Is it included in the MySQL connectors, so I would only have to specify something like : connect=mysql-1,mysql-2, or do I have to implement it myself ?

The ability to connect to multiple MySQL Servers is dependent on the connector (for example, I know that it’s supported by Connector/J). For PHP connectors, I’d suggest posting the question to http://forums.mysql.com/list.php?52

What ethernet links one would need to deploy 2 node ( 2 x dbd/2x sqld, that is: 1xdbd/1xmysqld on the same host) in master-master/master-slave config when used in CRM system (mostly textual data) in 25/50/100 concurrent users scenario?
Would 100Mbps for 25 users and 1000Mbps for 50/1000 users respectively be enough or should I forget the whole idea withouth 10Gbps link?
Could someone tell from their experience?
Thanks a lot.

of course it’s going to be dependent on the application load but on the face of it the number of users you specify seems pretty low and so I think you could start with the networking you propose and see how it performs.

Andrew,
ok, that is understandable and before deployment one cannot really be precise with the estimation, however I can say that data transfers would be small text chunks, so probably equal to or even less than typical “web usage” scenario /but no flash or other heavy objects-only graphics will probably be those of GUI/.
However, in terms of cluster consistency- is there any minimum interconnect speed/max latency that breaks it or makes it? Should 1Gbps suffice for 50-100 concurrent users?
While 1Gbps infrastructure has pretty modest costs, a need to upgrade to 10Gbps may be costly.

Sire,
The speed of the connection required is dependent on the volume of data that’s being read and (especially) written. As you say, your requirements seem modest and so 1G may well be enough. Regarding latency of the network, the most sensitive area is the connection between data nodes – we recommend that you aim to have this less than 20ms (should be easy for a LAN, the reccommendation is really aimed as multi-data-center Clusters) but even then you can adjust the heartbeat settings to make the Cluster more tolerant of higher latencies.

as u suggested in previous post, this time i am doing this with normal user but facing errors from the very starting,

[billy@cls1 mysql]$ scripts/mysql_install_db –basedir=/home/billy/mysql/ –datadir=/home/billy/mysql/data/
chown: changing ownership of `/home/billy/mysql/data/’: Operation not permitted
Cannot change ownership of the database directories to the ‘mysql’
user. Check that you have the necessary permissions and try again.

where it’s neccessary to grant permissions, you need to be logged in as a user able to grant those permissions. Where you’re trying to run chmod, chown or similar commands you need the required authority – for example run them as root. The only root restricion is that when you run mysqld, you must *not* be the root user.

Hello I have an environment with 2 NDB MGM and 2 SQL when I start mysql does not connect at MGM in the log that failed to allocate nodeid is api at 10.139.139.94. Returned error: ‘No free node id found for mysqld (API). the ndbd starts and connects to MGM without error. ‘So that mysqld does not connect.

2015-04-18 12:14:24 12618 [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-bin=ise6-bin’ to avoid this problem.
bin/mysqld: File ‘./ise6-bin.index’ not found (Errcode: 13 – Permission denied)
2015-04-18 12:14:24 12618 [ERROR] Aborting

Thank you sir, the error has been resolved . now i am facing new issue wherein when i run bin/mysql -h 10.2.0.173 -P 3306 i am getting following error :
ERROR 1130 (HY000): Host ‘10.2.0.173’ is not allowed to connect to this MySQL server. please help me with this.
Thanks in advance.

WARNING: The host ‘linux06
mongo1.simplify360.in’ could not be looked up with /home/linux/mysql7/bin/resolveip.
This probably means that your libc libraries are not 100 % compatible
with this binary MySQL version. The MySQL daemon, mysqld, should work
normally with the exception that host name resolving will not work.
This means that you should use IP addresses instead of hostnames
when specifying MySQL privileges !

Presumably, you’re defining the name of the binlog file as mongo1-bin in your my.1.cnf file – have you checked that the directory you give is valid and that this user has read/write permissions for it?

You can connect from a remote host like this: mysql -h :3306. If your told that you don’t have permissions then issue a GRANT statement on the target machine to allow remote connections from your remote machine… https://dev.mysql.com/doc/refman/5.6/en/grant.html