Home > linux, mysql, open source > Howto setup mysql-cluster (beginners tutorial)

Howto setup mysql-cluster (beginners tutorial)

Introduction

This HOWTO is designed for a classic setup of two servers behind a load-balancer. The aim is to have true redundancy – either server can be unplugged and yet the site will remain up.

Notes:

You MUST have a third server as a management node but this can be shut down after the cluster starts. Also note that I do not recommend shutting down the management server (see the extra notes at the bottom of this document for more information). You can not run a MySQL Cluster with just two servers And have true redundancy.

Although it is possible to set the cluster up on two physical servers you WILL NOT GET the ability to “kill” one server and for the cluster to continue as normal. For this you need a third server running the management node.

we are going to talk about three servers:

node01.example.com 192.168.0.10

node02.example.com 192.168.0.20

node03.example.com 192.168.0.30

Servers node01 and node02 will be the two that end up “clustered”. This would be perfect for two servers behind a loadbalancer or using round robin DNS and is a good replacement for replication. Server node03 needs to have only minor changes made to it and does NOT require a MySQL install. It can be a low-end machine and can be carrying out other tasks.

Get the software:

For Generally Available (GA), supported versions of the software, download from

http://www.mysql.com/downloads/cluster/

Make sure that you select the correct platform – in this case, “Linux – Generic” and then the correct architecture (for LINUX this means x86 32 or 64 bit).

Note: Only use MySQL Server executables (mysqlds) that come with the MySQL Cluster installation.

STAGE1: Installation of Data and SQL nodes on node01 and node02

On each of the machines designated to host data or SQL nodes(in our case node01 and node02), perform the following steps as the system root user:

  1. create a new mysql user group, and then add a mysql user to this group:
    shell> groupadd mysql

    shell> useradd -g mysql mysql


  2. Change location to the directory containing the downloaded file, unpack the archive, and create a symlink to the mysql directory named mysql. Note that the actual file and directory names vary according to the MySQL Cluster version number.
    shell> cd /var/tmp

    shell> tar -C /usr/local -xzvf mysql-cluster-gpl-7.1.5-linux-x86_64-glibc23.tar.gz

    shell> ln -s /usr/local/mysql-cluster-gpl-7.1.5-linux-i686-glibc23 /usr/local/mysql

    shell> export PATH=$PATH:/usr/local/mysql/bin

    shell> echo "export PATH=\$PATH:/usr/local/mysql/bin" >> /etc/bash.bashrc

  3. Change location to the mysql directory and run the supplied script for creating the system databases:
    shell> cd mysql

    shell> ./scripts/mysql_install_db --user=mysql

  4. Set the necessary permissions for the MySQL server and data directories:
    shell> chown -R root .

    shell> chown -R mysql data

    shell> chgrp -R mysql .

  5. Copy the MySQL startup script to the appropriate directory, make it executable, and set it to start when the operating system is booted up:
    shell> cp support-files/mysql.server /etc/init.d/mysql

    shell> chmod +x /etc/init.d/mysql

    shell> update-rc.d mysql defaults

STAGE2: Installation of Management node on node03

Installation of the management node does not require the mysqld binary. Only the MySQL Cluster management server (ndb_mgmd) is required; I assume that you have placed mysql-cluster-gpl-7.1.5-linux-i686-glibc23.tar.gz in /var/tmp.

As system root perform the following steps to install ndb_mgmd and ndb_mgm on the Cluster management node host (node02):

  1. Change location to the /var/tmp directory, and extract the ndb_mgm and ndb_mgmd from the archive into a suitable directory such as /usr/local/bin:
    shell> cd /var/tmp

    shell> tar -zxvf mysql-cluster-gpl-7.1.5-linux-i686-glibc23.tar.gz

    shell> cd /usr/local/mysql-cluster-gpl-7.1.5-linux-i686-glibc23

    shell> cp bin/ndb_mgm* /usr/local/bin

  2. Change location to the directory into which you copied the files, and then make both of them executable:
    shell> cd /usr/local/bin

    shell> chmod +x ndb_mgm*

STAGE3: Configuration of Management node

The first step in configuring the management node is to create the directory in which the configuration file can be found and then to create the file itself. For example (running as root):

shell> mkdir /var/lib/mysql-cluster

shell> cd /var/lib/mysql-cluster

shell> vi config.ini

For our setup, the config.ini file should read as follows:

[ndbd default]

NoOfReplicas=2

DataMemory=80M

IndexMemory=18M

[tcp default]

[ndb_mgmd]

hostname=192.168.0.30 # Hostname or IP address of MGM node

datadir=/var/lib/mysql-cluster # Directory for MGM node log files

[ndbd]

hostname=192.168.0.10 # Hostname or IP address

datadir=/usr/local/mysql/data # Directory for this data node's data files

[ndbd]

hostname=192.168.0.20 # Hostname or IP address

datadir=/usr/local/mysql/data # Directory for this data node's data files

[mysqld]

hostname=192.168.0.10 # Hostname or IP address

[mysqld]

hostname=192.168.0.20 # Hostname or IP address

STAGE4: Configuration of Data and SQL nodes

The first step in configuring the management node is to create the directory in which the configuration file can be found and then to create the file itself. For example (running as root):

shell> vi /etc/my.cnf

Note :
We show vi being used here to create the file, but any text editor should work just as well.

For each data node and SQL node in our setup, my.cnf should look like this:

[client]

port = 3306

socket = /tmp/mysql.sock

[mysqld]

port = 3306

socket = /tmp/mysql.sock

skip-locking

ndbcluster # run NDB storage engine

ndb-connectstring=192.168.0.30 # location of management server

[mysql_cluster]

ndb-connectstring=192.168.0.30 # location of management server

Important :
Once you have started a mysqld process with the NDBCLUSTER and ndb-connectstring parameters in the [mysqld] in the my.cnf file as shown previously, you cannot execute any CREATE TABLE or ALTER TABLE statements without having actually started the cluster. Otherwise, these statements will fail with an error.

STAGE4: Starting the MySQL Cluster

Starting the cluster is not very difficult after it has been configured. Each cluster node process must be started separately, and on the host where it resides. The management node should be started first, followed by the data nodes, and then finally by any SQL nodes:

  1. On the management host(node03), issue the following command from the system shell to start the management node process:
    shell> ndb_mgmd -f /var/lib/mysql-cluster/config.ini --configdir=/var/lib/mysql-clusetr
  2. On each of the Data/SQL node hosts, run these commands to start the ndbd and mysql server process:
    shell> /usr/local/mysql/bin/ndbd

    shell> /etc/init.d/mysql start

If all has gone well, and the cluster has been set up correctly, the cluster should now be operational. You can test this by invoking the ndb_mgm management node client. The output should look like that shown here:

node03:~# 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 @192.168.0.10 (mysql-5.1.44 ndb-7.1.5, Nodegroup: 0, Master)

id=3 @192.168.0.20 (mysql-5.1.44 ndb-7.1.5, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)

id=1 @192.168.0.30 (mysql-5.1.44 ndb-7.1.5)

[mysqld(API)] 2 node(s)

id=4 @192.168.0.10 (mysql-5.1.44 ndb-7.1.5)

id=5 @192.168.0.20 (mysql-5.1.44 ndb-7.1.5)

STAGE5: Testing the Setup

If you are OK to here it is time to test mysql. On either server node01 or node02 enter the following commands: Note that we have no root password yet:

shell> mysql

create database testdb;

use test;

CREATE TABLE cluster_test (i INT) ENGINE=NDBCLUSTER;

INSERT INTO cluster_test (i) VALUES (1);

SELECT * FROM cluster_test;

You should see 1 row returned (with the value 1).

If this works, now go to the other server and run the same SELECT and see what you get. Insert from that host and go back to previous host and see if it works. If it works then congratulations!

About these ads
  1. September 7, 2010 at 12:45 pm

    Good article, thankyou. I have book marked it and will use it soon

  2. October 27, 2010 at 12:55 pm

    Thanks very much.
    It was very useful.

    you made my day ;)

  3. ida
    November 6, 2010 at 11:32 am

    thank you very much admin!! =)

  4. Luca Cisetti
    November 29, 2010 at 9:42 pm

    Hi.
    mysql (predefined db) is replicated in MySql Cluster ?
    Thanks.
    Luca

  5. m1k3y
    February 11, 2011 at 2:52 pm

    there is a spelling mistake you may want to correct

    shell> ndb_mgmd -f /var/lib/mysql-cluster/config.ini –configdir=/var/lib/mysql-clusetr

    with
    shell> ndb_mgmd -f /var/lib/mysql-cluster/config.ini –configdir=/var/lib/mysql-cluster

    and in section STAGE 2

    As system root perform the following steps to install ndb_mgmd and ndb_mgm on the Cluster management node host (node02):

    node02 is misleading as it should be node03

    otherwise good article.

    maybe load balancing would be a good continuation ?

    cheers.
    m.

  6. nozyneo
    June 29, 2011 at 3:46 am

    hello, good morning, im a newbie with Mysql Clustering,

    i followed every instruction, it was working, and then we will fall

    in STAGE5: Testing the Setup

    i followed every instruction

    and then when i execute this command ‘CREATE TABLE cluster_test (i INT) ENGINE=NDBCLUSTER;’

    an error occured and prompt me this ‘ERROR 157 (HY000): Could not connect to storage engine’

    please do help me

  7. tanya
    February 23, 2012 at 2:47 am

    i am not able to get mysql to start, this is what i get. what is the fix for this?

    /etc/init.d/mysql start
    Starting MySQL………………………………………… ERROR! The server quit without updating PID file

  8. Willem
    March 9, 2012 at 5:16 pm

    tanya :
    i am not able to get mysql to start, this is what i get. what is the fix for this?
    /etc/init.d/mysql start
    Starting MySQL………………………………………… ERROR! The server quit without updating PID file

    I was able to correct this problem by removing skip-locking from my /etc/my.cnf

  9. Willem
    March 9, 2012 at 5:43 pm

    Some additional notes to those that come after me:

    1. In stage 2 the author says (node02) when I think he means (node03)
    2. In the final test stage the author says “use test” should be “use testdb”
    3. On CentOS I had uninstalled mysql-clients, if you get an error that mysql is an unrecognized command download the latest MySQL-client rpm and install it

    This was a fantastic springboard, thank you for writing it. Helped me out a lot.

  10. Willem
    March 9, 2012 at 8:30 pm

    One last thing, sorry for the comment spam. Seeing as how the table locking might be important I looked into the issue. MySQL removed support for skip-locking in 5.5 according to:

    http://dev.mysql.com/doc/refman/5.5/en/mysql-nutshell.html

    [quote]
    The following constructs are obsolete and have been removed in MySQL 5.5. Where alternatives are shown, applications should be updated to use them.
    ….
    The –enable-locking and –skip-locking server options (use –external-locking and –skip-external-locking).
    [/quote]

    The solution is to use the new option: skip-external-locking

    So just update your my.cnf to use skip-external-locking instead of skip-locking

  11. sonif
    March 22, 2012 at 4:33 am

    when i try run ndb_mgmd -f /var/lib/mysql-cluster/config.ini –configdir=/var/lib/mysql-cluster

    i get error :

    MySQL Cluster Management Server mysql-5.5.19 ndb-7.2.4
    2012-03-21 21:31:52 [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
    2012-03-21 21:31:52 [MgmtSrvr] ERROR — Could not create directory ‘/usr/local/mysql/mysql-cluster’. Either create it manually or specify a different directory with –configdir=

    can somebody give me solution for my problem
    thanks

  12. sonif
    March 26, 2012 at 2:33 am

    my previous problem have been solved
    now i have another problem

    in my sever doesn’t recognizer mysql on client… but nbdb client recognizer by server

    Cluster Configuration
    ———————
    [ndbd(NDB)] 2 node(s)
    id=2 @192.168.0.10 (mysql-5.5.19 ndb-7.2.4, Nodegroup: 0, Master)
    id=3 (not connected, accepting connect from 192.168.0.20)

    [ndb_mgmd(MGM)] 1 node(s)
    id=1 @192.168.0.30 (mysql-5.5.19 ndb-7.2.4)

    [mysqld(API)] 2 node(s)
    id=4 (not connected, accepting connect from 192.168.0.10)
    id=5 (not connected, accepting connect from 192.168.0.20)

    why it can be like that?
    thx

  13. enyrix
    March 26, 2012 at 4:01 pm

    sonif :
    my previous problem have been solved
    now i have another problem
    in my sever doesn’t recognizer mysql on client… but nbdb client recognizer by server
    Cluster Configuration
    ———————
    [ndbd(NDB)]2 node(s)
    id=2@192.168.0.10 (mysql-5.5.19 ndb-7.2.4, Nodegroup: 0, Master)
    id=3 (not connected, accepting connect from 192.168.0.20)
    [ndb_mgmd(MGM)]1 node(s)
    id=1@192.168.0.30 (mysql-5.5.19 ndb-7.2.4)
    [mysqld(API)]2 node(s)
    id=4 (not connected, accepting connect from 192.168.0.10)
    id=5 (not connected, accepting connect from 192.168.0.20)
    why it can be like that?
    thx

    I have installed mysqld and data node on ubuntu 11.10
    I have the same problem, mysqld(api) not connected. If you find a solution please tell us. thx

  14. enyrix
    March 27, 2012 at 9:24 pm

    My problem is solved, on Ubuntu 10.11 (correction for my last post), we must uninstall pre-installed mysql before. See http://askubuntu.com/questions/86120/how-do-i-set-up-mysql-cluster for more details. (i used mysql-cluster-gpl-7.1.19-linux-x86_64-glibc23.tar.gz) for my test and worked successfully)

    apt-get remove mysql-client mysql-client-5.1 mysql-client-core-5.1 mysql-common mysql-server mysql-server-5.1 mysql-server-core-5.1
    apt-get –purge remove
    dpkg –get-selections | grep mysql
    aptitude purge $(dpkg –get-selections | grep deinstall | sed s/deinstall//) #To completely remove all deinstall package
    pkill -9 mysqld

    In response for sonif: Try to start a second node for the first time and verify also your port (by default 1186), verify also if you don’t have another mysqld in conflict on your SQL node.

    I hope that it’s can help someone !

  15. Alonso
    March 28, 2012 at 4:17 pm

    I am very grateful for your article.
    I have windows xp. Until now I’m going to try MySQL Cluster, so your expertice/experince is very wellcome. Right now, MySQL Cluster is in version 7.2. Could you be so kind to give me hints/tips about:
    * This version.
    * How to try/configure the environment in windows xp (only) machines.

    I just saw your section on Asterisk. Can you write a similar article (like this), of voiceone?

    THAAANKS A LOOOOOOOOT !!!!

  16. Rafael
    April 2, 2012 at 1:16 am

    On the log: [ERROR] Fatal error can’t open and lock privilege tables table ‘mysql.host’ doesn’t exist
    How i fix this?

  17. vjanicek
    April 16, 2012 at 11:02 pm

    Hey there… what a great guide… I have a problem that I can’t somehow find a solution!!! any help would be greatly appreciated!

    I am able to connect to node03 easily and initiate mysql on the data nodes

    this is my mgmnt node’s output showing just the fist node (perfectly working)

    ——————
    ndb_mgm> show
    Cluster Configuration
    ———————
    [ndbd(NDB)] 2 node(s)
    id=2 @192.168.56.101 (mysql-5.5.20 ndb-7.2.5, starting, Nodegroup: 0)
    id=3 (not connected, accepting connect from node02)
    ——————

    but if I go to node01 and I try to create a cluster table, I get this error:

    ——————
    mysql> use test;
    Database changed
    mysql> CREATE TABLE cluster_test (i INT) ENGINE=NDBCLUSTER;
    ERROR 157 (HY000): Could not connect to storage engine
    mysql>
    ——————

    This is my error log:

    ——————
    120416 22:54:33 [Note] Starting Cluster Binlog Thread
    120416 22:54:33 InnoDB: The InnoDB memory heap is disabled
    120416 22:54:33 InnoDB: Mutexes and rw_locks use InnoDB’s own implementation
    120416 22:54:33 InnoDB: Compressed tables use zlib 1.2.3
    120416 22:54:33 InnoDB: Using Linux native AIO
    120416 22:54:33 InnoDB: Initializing buffer pool, size = 128.0M
    120416 22:54:34 InnoDB: Completed initialization of buffer pool
    120416 22:54:34 InnoDB: highest supported file format is Barracuda.
    120416 22:54:34 InnoDB: Waiting for the background threads to start
    120416 22:54:35 InnoDB: 1.1.8 started; log sequence number 1595675
    120416 22:54:36 [Note] Event Scheduler: Loaded 0 events
    120416 22:54:36 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
    Version: ’5.5.20-ndb-7.2.5-gpl’ socket: ‘/tmp/mysql.sock’ port: 3306 MySQL Cluster Community Server (GPL)
    120416 22:55:06 [Warning] NDB : Tables not available after 30 seconds. Consider increasing –ndb-wait-setup value
    120416 22:55:33 [Warning] NDB: Could not acquire global schema lock (4009)Cluster Failure
    120416 22:55:35 [Warning] NDB: Could not acquire global schema lock (4009)Cluster Failure
    ——————

    and what is worse, is that there are no firewalls and I can access the mgmnt node perfectly from node01…

    look:

    ——————
    node01 ~: telnet 192.168.56.103 1186
    Trying 192.168.56.103…
    Connected to 192.168.56.103.
    Escape character is ‘^]’.
    ^]
    telnet> Connection closed.
    ——————

    This is my node01′s show warnings
    mysql> show warnings;
    +———+——+———————————————————————————+
    | Level | Code | Message |
    +———+——+———————————————————————————+
    | Warning | 1296 | Got error 4009 ‘Cluster Failure’ from NDB. Could not acquire global schema lock |
    | Warning | 1296 | Got error 4009 ‘Cluster Failure’ from NDB |
    | Error | 157 | Could not connect to storage engine |
    | Error | 1499 | Too many partitions (including subpartitions) were defined |
    +———+——+———————————————————————————+
    4 rows in set (0.00 sec)

    Any ideas? I’m about to go crazy with this!!

    Thanks!

  18. November 20, 2012 at 12:12 pm

    It worked for me, just pay attention to some dashes here, they are actually double dash
    eg.:
    shell> ./scripts/mysql_install_db –user=mysql
    this is
    shell> ./scripts/mysql_install_db –user=mysql

    BTW, what’s next once you get this set?
    How do we figure out the best node, the latest fresh data etc?
    Balancing should be done in application or we have a solution for this too?

  19. April 24, 2013 at 11:48 am

    Sweet blog! I found it while searching on Yahoo
    News. Do you have any tips on how to get listed in Yahoo News?

    I’ve been trying for a while but I never seem to get there! Cheers

  20. Ripoff Artist
    July 18, 2013 at 4:01 pm

    This is a straight-up ripoff from the MySQL documentation with only minor changes.

  21. ravi
    July 20, 2013 at 11:43 am

    very useful…… and clear thanks lot.,………….

  22. September 12, 2013 at 7:14 am

    This is one of the best guide to start making your own Cluster config. thanks again

  23. Rajesh
    September 25, 2013 at 9:27 am

    Thanks a lot.. very easy to setup using this guide. you saved our time….

  24. Ashok
    October 15, 2013 at 8:44 am

    This is good notes i have configured. I just want to ask one question now what ip will use for our application. I should use master node ip or any other.

    Please let me know

  25. Rommel
    November 23, 2013 at 9:19 am

    How did you fix this issue? I am having the same problem on the Management node

    sonif :
    when i try run ndb_mgmd -f /var/lib/mysql-cluster/config.ini –configdir=/var/lib/mysql-cluster
    i get error :
    MySQL Cluster Management Server mysql-5.5.19 ndb-7.2.4
    2012-03-21 21:31:52 [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
    2012-03-21 21:31:52 [MgmtSrvr] ERROR — Could not create directory ‘/usr/local/mysql/mysql-cluster’. Either create it manually or specify a different directory with –configdir=
    can somebody give me solution for my problem
    thanks

  26. Rommel
    November 23, 2013 at 9:20 am

    Guys – how do I fix this?

    [root@perconadb1 mysql-cluster]# ndb_mgmd -f /var/lib/mysql-cluster/config.ini –configdir=/var/lib/mysql-cluster/config.ini
    MySQL Cluster Management Server mysql-5.6.14 ndb-7.3.3
    2013-11-23 11:15:25 [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
    2013-11-23 11:15:25 [MgmtSrvr] ERROR — Could not create directory ‘/usr/local/mysql/mysql-cluster’. Either create it manually or specify a different directory with –configdir=

  27. Rommel
    November 23, 2013 at 10:01 am

    OK I fixed that issue by just renaming the directory.

    Then I went in to the Node1 and Node to and I ran:

    shell> /usr/local/mysql/bin/ndbd — > this works and I am able to connect to the management node but when I run this:

    shell> /etc/init.d/mysql start

    I receive the error:

    [root@mysqlcentos2 mysql]# /etc/init.d/mysql start
    Starting MySQL………………………………The server quit without updating PID file (/var/lib/mysql/mysqlcentos2.pid).

    from both nodes

    — How do I resolve that?

  28. Abhi
    January 29, 2014 at 3:27 am

    While running the command /usr/local/mysql/bin/ndbd I got error in /usr/local/mysql/data localhost err that Table mysql.plugin not found. and I m not able to connect with the management node. can anyone solve this problem.Setting r as given in tutorial.
    Thanks in advance

  29. July 15, 2014 at 7:24 am

    root@mysqlnode02:~# /usr/local/mysql/bin/ndbd
    2014-07-15 12:52:42 [ndbd] INFO — Angel connected to ’192.168.1.165:1186′
    2014-07-15 12:52:42 [ndbd] ERROR — Failed to allocate nodeid, error: ‘Error: Could not alloc node id at 192.168.1.165 port 1186: Connection done from wrong host ip 192.168.1.164.’

  30. July 15, 2014 at 7:26 am

    Hi guys when i tried to log in from the 2nd data node i got this error i setup the 2 data nodes with static ip:192.168.1.163 and 192.168.1.164 and for management ip is 192.168.1.165 could any body help over this error thanks in advance

  1. December 8, 2011 at 4:39 pm
  2. July 11, 2012 at 12:58 am
  3. January 22, 2013 at 2:14 pm
  4. June 22, 2014 at 8:01 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: