Creating a simple Cluster on a single LINUX host

It isn’t necessarily immediately obvious how to set up a Cluster on LINUX; this post attempts to show how to get a simple Cluster up and running. For simplicity, all of the nodes will run on a single host – a subsequent post will take the subsequent steps of moving some of them to a second host. As with my Windows post the Cluster will contain the following nodes:

  • 1 Management node (ndb_mgmd)
  • 2 Data nodes (ndbd)
  • 3 MySQL Server (API) nodes (mysqld)

Downloading and installing

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) 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

Create 3 data folders (one for each of the MySQL API – mysqld – processes) and setup the files that will be needed for them to run correctly…

[billy@ws1 mysql]$ cd 7_0_6/data
[billy@ws1 data]$ mkdir data1 data2 data3
[billy@ws1 data]$ mkdir data1/mysql data1/test data2/mysql data2/test data3/mysql data3/test
[billy@ws1 7_0_6]$ cd ..
[billy@ws1 7_0_6]$ scripts/mysql_install_db --basedir=/home/billy/mysql/7_0_6 --datadir=/home/billy/mysql/7_0_6/data/data1
[billy@ws1 7_0_6]$ scripts/mysql_install_db --basedir=/home/billy/mysql/7_0_6 --datadir=/home/billy/mysql/7_0_6/data/data2
[billy@ws1 7_0_6]$ scripts/mysql_install_db --basedir=/home/billy/mysql/7_0_6 --datadir=/home/billy/mysql/7_0_6/data/data3

Configure and run the Cluster

Create a sub-directory called “conf” and create the following 4 files there:

config.ini

[ndbd default]
noofreplicas=2

[ndbd]
hostname=localhost
id=2

[ndbd]
hostname=localhost
id=3

[ndb_mgmd]
id = 1
hostname=localhost

[mysqld]
id=4
hostname=localhost

[mysqld]
id=5
hostname=localhost

[mysqld]
id=6
hostname=localhost

my.1.conf

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

my.2.conf

[mysqld]
ndb-nodeid=5
ndbcluster
datadir=/home/billy/mysql/7_0_6/data/data2
basedir=/home/billy/mysql/7_0_6
port=3307
server-id=2
log-bin

my.3.conf

[mysqld]
ndb-nodeid=6
ndbcluster
datadir=/home/billy/mysql/7_0_6/data/data3
basedir=/home/billy/mysql/7_0_6
port=3308
server-id=3
log-bin

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

[billy@ws1 7_0_6]$ bin/ndb_mgmd --initial -f conf/config.ini --configdir=/home/billy/mysql/7_0_6/conf
2009-06-17 13:00:08 [MgmSrvr] INFO     -- NDB Cluster Management Server. mysql-5.1.34 ndb-7.0.6
2009-06-17 13:00:08 [MgmSrvr] INFO     -- Reading cluster configuration from 'conf/config.ini'

Check that the management node is 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=2 (not connected, accepting connect from localhost)
id=3 (not connected, accepting connect from localhost)

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

[mysqld(API)]    3 node(s)
id=4 (not connected, accepting connect from localhost)
id=5 (not connected, accepting connect from localhost)
id=6 (not connected, accepting connect from localhost)
ndb_mgm> quit

and then start the 2 data nodes (ndbd) and 3 MySQL API/Server nodes (ndbd) 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@ws1 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@ws1 7_0_6]$ bin/mysqld --defaults-file=conf/my.2.conf&
[billy@ws1 7_0_6]$ bin/mysqld --defaults-file=conf/my.3.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=2    @127.0.0.1  (mysql-5.1.34 ndb-7.0.6, Nodegroup: 0, Master)
id=3    @127.0.0.1  (mysql-5.1.34 ndb-7.0.6, Nodegroup: 0)

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

[mysqld(API)]    3 node(s)
id=4    @127.0.0.1  (mysql-5.1.34 ndb-7.0.6)
id=5    @127.0.0.1  (mysql-5.1.34 ndb-7.0.6)
id=6    @127.0.0.1  (mysql-5.1.34 ndb-7.0.6)
ndb_mgm> quit

Using the Cluster

There are now 3 API nodes/MySQL Servers/mysqlds running; all accessing the same data. Each of those nodes can be accessed by the mysql client using the ports that were configured in the my.X.cnf files. For example, we can access the first of those nodes (node 4) in the following way (each API node is accessed using the 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;
090617 13:21:36 [Note] NDB Binlog: CREATE TABLE Event: REPL$test/assets
090617 13:21:36 [Note] NDB Binlog: logging ./test/assets (UPDATED,USE_WRITE)
090617 13:21:37 [Note] NDB Binlog: DISCOVER TABLE Event: REPL$test/assets
090617 13:21:37 [Note] NDB Binlog: DISCOVER TABLE Event: REPL$test/assets
090617 13:21:37 [Note] NDB Binlog: logging ./test/assets (UPDATED,USE_WRITE)
090617 13:21:37 [Note] NDB Binlog: logging ./test/assets (UPDATED,USE_WRITE)
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

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 either of the other 2 SQL nodes:

[billy@ws1 7_0_6]$ bin/mysql -h localhost -P 3307
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 5
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.00 sec)
mysql> quit
Bye

Your next steps

This is a very simple, contrived set up – in any sensible deployment, the nodes would be spread across multiple 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 order to size and tune your Cluster.





12 comments

  1. Gareth Bult says:

    Be interested to hear from other people running this sort of setup .. I run something almost identical (but with two management nodes) and have done for about 18 months. Reliability and performance are terrible and to date I’ve lost all my data twice. [in particular, it seems not to want to deal with power outages gracefully]

    Maybe it’s because I’m on a slightly older version, but given the problems I’ve had and the ones I’ve seen some people having upgrading, I’m pretty much afraid to touch it.

    I’ve also found the default action of using RAM rather than disk rather limiting, and although you can now use disk for some tables, it seems “fiddley”.

    Also (!) some things that work on other tables fail to work on NDB based tables .. although documented this isn’t necessarily all that obvious. For example I was caught out thinking I could just move current stuff to the cluster .. this fell down when I discovered full text indexing isn’t supported (!)

  2. admin says:

    Gareth,

    have you raised the data loss issue with the Cluster forum (http://forums.mysql.com/list.php?25) or the Cluster mailing list (cluster@lists.mysql.com)? While the advice would be to try to set things up such that a single power failure didn’t impact all of the data nodes making up a node group, that isn’t always possible and I wouldn’t expect data to be lost – if you haven’t done so already, raise the issue so that it can be identified whether there’s a bug or if there is something in your configuration that could be changed.

    You make a good point that it’s worth checking the known limitations of MySQL Cluster in the documentation before making the switch. Over time, the list will get shorter but there are things that MySQL currently can’t do that other storage engines can (e.g. foreign keys aren’t supported but you can simulate them using stored procedures).

  3. Phil says:

    Hello Andrew,

    Great article – thanks a lot! I am really looking forward to the article you mentioned redarding the distribution amongst multiple servers.

    Cheers,
    Phil

  4. [...] 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 [...]

  5. [...] Following this you can configure and use the software as normal (refer to Creating a simple Cluster on a single LINUX host). [...]

  6. [...] installed MySQL Cluster  – if that isn’t the case then please first refer to “Creating a simple Cluster on a single LINUX host” or “Deploying MySQL Cluster over multiple [...]

  7. [...] installed MySQL Cluster  – if that isn’t the case then please first refer to “Creating a simple Cluster on a single LINUX host” or “Deploying MySQL Cluster over multiple [...]

  8. [...] on ClusterJ rather than on running MySQL Cluster; if you are new to MySQL Cluster then refer to running a simple Cluster before trying this [...]

  9. [...] on ClusterJPA rather than on running MySQL Cluster; if you are new to MySQL Cluster then refer to Running a simple Cluster before trying these [...]

  10. Steve says:

    Hi.. How to know where i can find the configuration for localhost:1186 ? And what for we used this port ? Can you help me to show me, where i can found the configuration ? Because i try to connect to “ndb_mgm”, when i use “show” it shown “Unable to connect with connect string: nodeid=0, localhost:1186″. I try to look for that port with “netstat”, but i don’t find that port is listen.

    • admin says:

      Hi Steve,

      1186 is the default port that the MySQL Cluster management node uses (can be override it in config.ini). When you run ndb_mgmd, it will listen to that port.

      Regards, Andrew.

  11. Daniel Cabrera says:

    Great work and thanks a lot for this guide.

    I´ve followed the instructions, and found some problems on ubuntu 10.04 and MySqlCluster 7.0.13 .

    I´d like to share this to people who migth find the same problems on ubuntu.

    First, the base_dir must be owned by mysql user( “mysql” in my system), else scripts/mysql_install_db fails to install the databases.
    It took me a while to discover this, but scripts/mysql_install_db runs flawlessly after running

    shell>chown -R mysql “extracted folder” ( in this guide /home/billy/mysql/7_0_6 )

    after creating the data folders

    Then, in the my.*.conf files, you must alter three lines :

    1.-> “log-bin” should read “log-bin=auth-bin” (or whatever log type you want ) to get rid of this warning when running mysqld

    ” 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=auth-bin’ to avoid this problem. ”

    2.-> You must add this line “user=mysql” to run bin/mysql without errors and security warnings

    3.-> Also,you should include “socket=/tmp/my.*.sock”
    were * is the nomber of the config file name(you could use whatever file name you want, they just have to be different for each config file) to connect to servers using bin/mysql.

    The command should be :

    shell>bin/mysql -h localhost -P 3306 –socket=/tmp/my.1.sock

    to avoid this error :

    ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)

    So, finally the conf/my.1.conf file should look like this :

    [mysqld]
    user=mysql
    ndb-nodeid=4
    ndbcluster
    datadir=”basedir”/data/data1
    basedir=”basedir”
    port=3306
    server-id=1
    log-bin=auth-bin
    socket=/tmp/my.1.sock

    Hope this helps to get this demo install running on Lucid Lynx

    Thanks again for your guide.

Leave a Reply

Your email address will not be published. Required fields are marked *