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…

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…

Configure and run the Cluster

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

config.ini

my.1.conf

my.2.conf

my.3.conf

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

Check that the management node is up and running:

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:

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:

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:

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.

41 thoughts on “Creating a simple Cluster on a single LINUX host

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

    1. Hi Andrew,
      I am trying to implement the MySQL cluster … but I’m having a lot of difficulties and can barely find material on my doubts.

      I know the topic is not about that, but I would like to help me …

      Installed cluster religiously following:
      http://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-install-linux-binary.html

      At the end, I used the Auto-installer to configure and start the cluster so far ok … I performed tests and I could see my data replicated … but when testing the resistance to failure, simulating the desligamendo the physical server where is the date Nod1, that did not work … I can not start the Nod1 again … to try to get the following error:
      [Root @ Nod1 bin] # ./ndbd
      Unable to connect with connect string: nodeid = 0, localhost: 1186
      Retrying every 5 seconds. Attempts left: 12 11 10 9 8 7 6 5 4 3 2 1 failed.
      05/19/2015 18:05:16 [ndbd] ERROR – Could not connect to server management, error ”

      What should I do to fix? Replication still works but I’m just with NOD2 ..

      I appreciate your help.

      1. Is the ndb_mgmd process running on the same machine where you’re trying to start the data node? If not then you you need to explicitly provide its location… ndbmtd --connect-string="other-host:1186".

  3. 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. 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.

    1. 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.

  5. 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.

  6. Hi, I have followed your instruction to setup the mysql cluster on cloud VM with CentOS hosted on it. First time all went fine, i was able to see the whole cluster connected. after that I shutdown the cluster with ndb_mgmd shutdown command. Then after some time I tried to re-start it, but there is an error saying
    2015-03-23 13:05:57 [MgmtSrvr] INFO — The default config directory ‘/usr/local/mysql/mysql-cluster’ does not exist. Trying to create it…
    Failed to create directory ‘/usr/local/mysql/mysql-cluster’, error: 2
    2015-03-23 13:05:57 [MgmtSrvr] ERROR — Could not create directory ‘/usr/local/mysql/mysql-cluster’. Either create it manually or specify a different directory with –configdir=

    But when i create the directory it say, unable to identify which node need to start.

    What to do… i searched on the net, tried all the possible solution I could found. Can you please help me out. thanks.

    1. Did you forget to specify the location of the config file when you restarted the ndb_mgmd process (--config-file=...)?

  7. I am trying this:

    bin/mysqld –user=root –defaults-file=conf/my.1.conf&

    I am getting following error.

    2015-04-24 07:36:07 0 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
    2015-04-24 07:36:07 0 [Warning] Ignoring user change to ‘root’ because the user was set to ‘mysql’ earlier on the command line

    2015-04-24 07:36:07 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicit_defaults_for_timestamp server option (see documentation for more details).
    2015-04-24 07:36:07 0 [Note] bin/mysqld (mysqld 5.6.24-ndb-7.3.9-cluster-gpl-log) starting as process 10591 …
    2015-04-24 07:36:07 10591 [ERROR] Can’t read from messagefile ‘/usr/share/mysql/english/errmsg.sys’

    1. When you setup the system tables, did you provide your config file…
      ./scripts/mysql_install_db --defaults-file=my.cnf

      Andrew.

          1. Hi, I have did what you suggested, but not working. I can see the following error in error.log file:

            2015-04-24 08:57:21 15545 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
            2015-04-24 08:57:21 15545 [Note] Plugin ‘FEDERATED’ is disabled.
            2015-04-24 08:57:21 15545 [Note] Plugin ‘ndbcluster’ is disabled.
            2015-04-24 08:57:21 15545 [Note] InnoDB: Using atomics to ref count buffer pool pages
            2015-04-24 08:57:21 15545 [Note] InnoDB: The InnoDB memory heap is disabled
            2015-04-24 08:57:21 15545 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
            2015-04-24 08:57:21 15545 [Note] InnoDB: Memory barrier is not used
            2015-04-24 08:57:21 15545 [Note] InnoDB: Compressed tables use zlib 1.2.3
            2015-04-24 08:57:21 15545 [Note] InnoDB: Using Linux native AIO
            2015-04-24 08:57:21 15545 [Note] InnoDB: Using CPU crc32 instructions
            2015-04-24 08:57:21 15545 [Note] InnoDB: Initializing buffer pool, size = 128.0M
            2015-04-24 08:57:21 15545 [Note] InnoDB: Completed initialization of buffer pool
            2015-04-24 08:57:21 15545 [Note] InnoDB: Highest supported file format is Barracuda.
            2015-04-24 08:57:22 15545 [Note] InnoDB: 128 rollback segment(s) are active.
            2015-04-24 08:57:22 15545 [Note] InnoDB: Waiting for purge to start
            2015-04-24 08:57:22 15545 [Note] InnoDB: 5.6.24 started; log sequence number 298796228394
            2015-04-24 08:57:22 15545 [ERROR] bin/mysqld: unknown variable ‘defaults-file=conf/my.3.conf’
            2015-04-24 08:57:22 15545 [ERROR] Aborting

          2. Andrew, this is what I am trying.

            bin/mysqld –user=root –defaults-file=/home/linux/mysql73/conf/my.3.conf&

          3. Andrew, I am using the correct syntax, may be my copy paste in the comment is wrong, but this is what exactly I am trying.

            bin/mysqld –user=root –defaults-file=/home/linux/mysql73/conf/my.3.conf&

          4. Hey Andrew, I got it fixed, actually –defaults-file=/home/linux/mysql73/conf/my.3.conf should be my first option and –user=root should be my second option. I have made it reverse. Now the conrfiguraton shows in this way. Please tell me if it is correct.

            root@linux03:~/mysql73# 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.6.24 ndb-7.3.9, Nodegroup: 0, *)
            id=3 @127.0.0.1 (mysql-5.6.24 ndb-7.3.9, Nodegroup: 0)

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

            [mysqld(API)] 3 node(s)
            id=4 @127.0.0.1 (mysql-5.6.24 ndb-7.3.9)
            id=5 @127.0.0.1 (mysql-5.6.24 ndb-7.3.9)
            id=6 @127.0.0.1 (mysql-5.6.24 ndb-7.3.9)

          5. Andrew, sorry to post again, now I face a different kind of error:

            root@linux03:~/mysql73# bin/mysql -h localhost -P 3306
            ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (111)

            Can you please help on this.

          6. No reason, I just went to the downloaded page and it 7.3 was the latest one.

            You mean to say I need to change configuration in config.ini file? or in my.cnf?

          7. Hey Andrew, It worked great. Appreciate you patience in answering.

            You dont have to reply to all of us, but still you are doing. Totally appreciate your work.

            Looking forward for a tutorial on how to put one data node in a different machine by make necessary changes in the same setup.

  8. Hi, I have did what you suggested, but not working. I can see the following error in error.log file:

    2015-04-24 08:57:21 15545 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
    2015-04-24 08:57:21 15545 [Note] Plugin ‘FEDERATED’ is disabled.
    2015-04-24 08:57:21 15545 [Note] Plugin ‘ndbcluster’ is disabled.
    2015-04-24 08:57:21 15545 [Note] InnoDB: Using atomics to ref count buffer pool pages
    2015-04-24 08:57:21 15545 [Note] InnoDB: The InnoDB memory heap is disabled
    2015-04-24 08:57:21 15545 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
    2015-04-24 08:57:21 15545 [Note] InnoDB: Memory barrier is not used
    2015-04-24 08:57:21 15545 [Note] InnoDB: Compressed tables use zlib 1.2.3
    2015-04-24 08:57:21 15545 [Note] InnoDB: Using Linux native AIO
    2015-04-24 08:57:21 15545 [Note] InnoDB: Using CPU crc32 instructions
    2015-04-24 08:57:21 15545 [Note] InnoDB: Initializing buffer pool, size = 128.0M
    2015-04-24 08:57:21 15545 [Note] InnoDB: Completed initialization of buffer pool
    2015-04-24 08:57:21 15545 [Note] InnoDB: Highest supported file format is Barracuda.
    2015-04-24 08:57:22 15545 [Note] InnoDB: 128 rollback segment(s) are active.
    2015-04-24 08:57:22 15545 [Note] InnoDB: Waiting for purge to start
    2015-04-24 08:57:22 15545 [Note] InnoDB: 5.6.24 started; log sequence number 298796228394
    2015-04-24 08:57:22 15545 [ERROR] bin/mysqld: unknown variable ‘defaults-file=conf/my.3.conf’
    2015-04-24 08:57:22 15545 [ERROR] Aborting

  9. i did this
    bin/mysqld –defaults-file=conf/my.1.conf&
    did not work

    then i added –user=root and still it did not work

    root@ashishbatler:/home/ashu/mysql/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.67 ndb-7.0.37, Nodegroup: 0, Master)
    id=3 @127.0.0.1 (mysql-5.1.67 ndb-7.0.37, Nodegroup: 0)

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

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

    1. Were you given an error message when the mysqld failed to start? Note that the --user=root option is for when you run the mysql client. Your command shows that your running as your machine’s root – the mysqld is not meant to be run by root and so that might be why it’s failing.

      Andrew.

  10. Hi Andrew,

    I have created database in one mysqld and trying to see it in others, it worked fine.
    But when I created few tables in that database and tried to see in the others, it is not working, actually tables what I created are more, will take time to reflect on other mysqld’s ??

    1. Hi Andrew, I got the answer from my query, thanx.

      But there is another issue, I am not able to access sql nodes from outside, I have executed following command in mysql console, it worked fine.
      grant all on *.* to ‘%’@’%’ identified by ‘password’ with grant option

      But, when I went to remote machine and does this, it is not working. This is the output:

      manoj@manoj-Vostro-3546:~$ mysql -h 10.0.0.203 -P 3308
      ERROR 1045 (28000): Access denied for user ‘manoj’@’manoj-Vostro-3546.local’ (using password: NO)

      Can you please help on this.

  11. Hi Andrew, I am trying to create some tables, but after some time it is showing following error, any idea how to handle this.

    2015-06-02 19:12:18 3888 [ERROR] bin/mysqld: The table ‘assets’ is full
    ERROR 1114 (HY000): The table ‘assets’ is full

    1. Hi Andrew, this is the result of ALL REPORT MEMORY

      Node 2: Data usage is 0%(45 32K pages of total 98048)
      Node 2: Index usage is 0%(408 8K pages of total 392224)
      Node 3: Data usage is 0%(45 32K pages of total 98048)
      Node 3: Index usage is 0%(408 8K pages of total 392224)

      it shows 0%, but still I am getting table full error, thanks in advance.

Leave a Reply

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

Please enter. * Time limit is exhausted. Please reload CAPTCHA.