How to setup Mysql Cluster in Amazon EC2

by jagbir on July 31, 2008

This is a quick Howto for setting up MySQL cluster using Amazon EC2 instances. I have used 3 small instances (1 mgmt + 2 data nodes) from RightScale’s CentOS 5 public image to test things. I’ve a working cluster using these steps but there’s no guarantee that these steps works for you as well.

Management server for cluster:
Although we can set up management server for cluster in one of the data node but we should have a third node as if one of the two MySQL cluster nodes fails, and the management server is not running, then the data on the two cluster nodes will become inconsistent and if the same node fails which have management server, then we will not be able to create another node or configure cluster.

Beginning with MySQL 5.1.6, it is possible to store the non-indexed columns of NDB tables on disk, rather than in RAM as with previous versions of MySQL Cluster. Here I’m using MySQL 5.1.26-rc as its the latest version while writing this article.

Let’s dive in practical:
1. Fire up 3 instances and note down their public and private IPs, dedicate roles(mgmt, node) for them and login in management node. Install ncurses library for MySQL compilation requirement:

$ yum install ncurses ncurses-devel

2. Download mysql source from its source download page:

$ cd /usr/src
$ groupadd mysql
$ useradd -g mysql mysql
$ wget http://dev.mysql.com/get/Downloads/MySQL-5.1/mysql-5.1.26-rc.tar.gz/from/ftp://mirror.services.wisc.edu/mirrors/mysql/
$ tar xzf mysql-5.1.26-rc.tar.gz
$ cd mysql-5.1.26-rc
<pre>
 
<strong> 3. Compile and install: </strong>
<pre lang="bash">
$ ./configure --enable-community-features --with-mysqld-user=mysql --with-mysqlmanager --with-plugins=ndbcluster 
$ make && make install

4. Create required directories:

$ mkdir -p /var/lib/mysql/mysql-cluster/backup
$ chown -R mysql:mysql /var/lib/mysql
$ cd /var/lib/mysql/mysql-cluster
$ vim config.ini
[NDBD DEFAULT]
NoOfReplicas=2
DataMemory=80M    # How much memory to allocate for data storage
IndexMemory=18M   # How much memory to allocate for index storage
# For DataMemory and IndexMemory, we have used the
# default values. Since the "world" database takes up
# only about 500KB, this should be more than enough for
# this example Cluster setup.
[MYSQLD DEFAULT]
[NDB_MGMD DEFAULT]
[TCP DEFAULT]
# Section for the cluster management node
[NDB_MGMD]
# IP address of the management node (this system)
HostName=10.252.169.x
# Section for the storage nodes
[NDBD]
# IP address of the first storage node
HostName=10.254.237.x
DataDir=/var/lib/mysql/mysql-cluster
BackupDataDir=/var/lib/mysql/mysql-cluster/backup
DataMemory=2048M
[NDBD]
# IP address of the second storage node
HostName=10.251.74.x
DataDir=/var/lib/mysql/mysql-cluster
BackupDataDir=/var/lib/mysql/mysql-cluster/backup
DataMemory=2048M
 
# one [MYSQLD] per storage node
[MYSQLD]
[MYSQLD]

Save and close the file.

5. Start management server. You can also copy ndb_mgmd in system path for easy access.

$ /usr/src/mysql-5.1.26-rc/storage/ndb/src/mgmsrv/ndb_mgmd -f /var/lib/mysql/mysql-cluster/config.ini

***

Setup of Node1 and Node2:

1. These settings are identical for Node 1 and Node 2. Let’s start by adding mysql user and group:

$ groupadd mysql 
$ useradd -g mysql mysql

2. Install required ncurses library for MySQL compilation:

$ yum install ncurses ncurses-devel

3. Download, compile and install MySQL:

$ cd /usr/src
$ wget http://dev.mysql.com/get/Downloads/MySQL-5.1/mysql-5.1.26-rc.tar.gz/from/ftp://mirror.services.wisc.edu/mirrors/mysql/
$ tar xzf mysql-5.1.26-rc.tar.gz
$ cd mysql-5.1.26-rc
$ ./configure --enable-community-features --with-mysqld-user=mysql --with-mysqlmanager --with-plugins=ndbcluster 
$ make && make install

4. Install initial mysql db:

$ /usr/src/mysql-5.1.26-rc/scripts/mysql_install_db --user=mysql

5. Create directories:

$ mkdir -p /var/lib/mysql/mysql-cluster/backup
$ chown -R mysql:mysql /var/lib/mysql

6. Copy init script from mysql source to preferred location:

$ cp /usr/src/mysql-5.1.26-rc/support-files/mysql.server /etc/init.d/
$ chmod 755 /etc/init.d/mysql.server

7. Create config file for MySQL:

$ vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
ndbcluster
# IP address of the cluster management node
ndb-connectstring=10.253.191.210
default-storage-engine=NDBCLUSTER
#Those are for future tuning
#max_connections=341
#query_cache_size=16M
#thread_concurrency = 4
[client]
port=3306
socket=/var/lib/mysql/mysql.sock
[mysql.server]
user=mysql
basedir=/usr/local
[mysql_cluster]
# IP address of the cluster management node
ndb-connectstring=10.253.191.x
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Save and close the file.

8. You can also include MySQL directories in your path (Optional).

$ PATH=$PATH:/usr/local/mysql/bin:/usr/local/mysql/libexec
$ export PATH

9. Start MySQL Server now:

$ /etc/init.d/mysql.server start

10. Repeat these steps in node 2 and start MySQL on both nodes. MySQL should start without any complain. You should also update the password for root user.

Test the cluster:

1. Focus again on Management node to have a look of Cluster connectivity. Open the ndb cluster management console. You should add this directory in PATH or can copy files to your system bin. (I’m a bit lazy ;)

$ /usr/src/mysql-5.1.26-rc/storage/ndb/src/mgmclient/ndb_mgm
-- NDB Cluster -- Management Client --

2. Issue command to view status of cluster:

ndb_mgm> show;
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=2    @10.254.237.x  (Version: 5.1.26, Nodegroup: 0, Master)
id=3    @10.251.74.x  (Version: 5.1.26, Nodegroup: 0)
 
[ndb_mgmd(MGM)] 1 node(s)
id=1    @10.252.169.x  (Version: 5.1.26)
 
[mysqld(API)]   2 node(s)
id=4    @10.254.237.x  (Version: 5.1.26)
id=5    @10.251.74.x  (Version: 5.1.26)</code>
ndb_mgm> quit

Everything should run fine.

3. Jump on the node 1 (or 2, doesnt matter in a cluster :) and try to create db, table and issue commands. BE SURE to use ENGINE=NDBCLUSTER while table creation else cluster will not work, means data will not be replicated throughtout cluster. Insert some records and check that on other node.

Conclusion:

It’s a basic test cluster and I would not recommend it for any production use. In fact, as per one of MySQL expert, “I would advise you to consider if MySQL Cluster is really a right choice for your project, it’s often not, probably in 99.9% of the cases I encountered, and does not give expected results” and when asking the alternate, “usually some MySQL/InnoDB in Master/Master, Master/Slave setup can work well enough to handle very high rate of transactions.”

Of course, ther are several considerations you have to take care, like network connectivity between instanes, backup to S3 and automatic promotion of second master in case first one failed. I’m looking on these and as well as on to setup Master/Master replication with S3 backup. I’ll post that here, once done.

You may also like to read:
* Install and configure FTP server in Amazon Instance
* Quick and simple php based script to check replication status of multiple MySQL Servers
* Setup multiple MySQL database servers in a single Linux host
* Optimize MySQL on a large Database Server
* Recover or reset root password of MySQL and PostgreSQL Servers
* How to setup MySQL Cluster 7.0 in Redhat based Linux
* Optimize and fix MySQL Server running slow without any load
* How to find out the clients connecting to your MySQL server
* Quickly repair huge corrupted or crashed table in MySQL
* Install and configure PhpMyAdmin to manage multiple MySQL Servers

  • Iñaki

    You forget run:
    # ndbd

    in each node

    BR.

  • Chris Brooks

    I’m curious about the speed of your cluster, I have configured a cluster with 2 node groups and 2 nodes in each, 1 management node and 3 API nodes, with no other traffic, and using bencher from severalnines I get around 600 tps, which is shockingly bad!

    Have you done any performance tests?

    • http://www.jagbir.info jagbir

      Hi Chris, unfortunately I didn’t got opportunity to conduct performance tests as our DB consultants recommended implementation of master-master replication instead of clustering after testing/detection of bandwidth fluctuations inside ec2 network. I’ve implemented master-master there with automatic fail-over scripts from rightscale and quite happy with this.

  • Chris Brooks

    ok thanks, I think I am getting the same issues, you *can* setup a cluster in EC2, but if you want performance then you shouldn’t :)

    Did you blog the master/master set-up ?

    Chris.

  • Ravi

    Where is the load balancing ??

    What is the use of this kind of set up ??

  • Pingback: RSS Week #71: letture per il weekend - Matteo Moro

  • Pingback: Destillat #11 | duetsch.info - Open Source, Wet-, Web-, Software

  • Pingback: How to set up a Mysql Cluster in Amazon EC2

  • Tonny

    Hello,

    i need some Help setting up MySQL Cluster on Amazon EC2. i went through all the Steps but i cant start make && make install. Im getting the error: sudo: make: command not found
    What can i do??

    i launched 1 basic EC2 Instance: Basic 32-bit Amazon Linux AMI 2011.02.1 Beta

    Thx in Advance for ur Help…

    vinzi

  • Tonny

    Okay, i just installed the missing packages on EC2..
    But stillt getting the following message:

    make: *** No targets specified and no makefile found. Stop.

    when i try to run:

    make && make install

    Any Idea?

  • santhoshsd

    what about the security groups ?

    which ports need to be open on the management node and which ones on the data nodes/ mysqld nodes ?

    thanks

  • sai kumar Ganji

    Urgent Help!!!

    I followed all the instructions till ‘test cluster’ and my management server is fine but my data nodes are not connected. And when I try to do “ndbd -c” in data nodes. it says

    # ndbd -c
    -bash: ndbd: command not found

    Please help me.

    • jagbirs

      Hi Sai Kumar,

      ndbd command not find usually means the cluster is not installed in server yet. Are you sure you have performed step 2 in both data nodes?

      • sai kumar Ganji

        Hi jagbris, thanks for reply.

        I resolved it and my cluster is up and running.

        Then the status of the cluster on management node is:

        [ndbd(NDB)] 2 node(s)
        id=2 @10.252.151.xx (Version: 5.1.68, Nodegroup: 0, Master)
        id=3 @10.253.4.xx (Version: 5.1.68, Nodegroup: 0)

        [ndb_mgmd(MGM)] 1 node(s)
        id=1 @10.253.19.xx (Version: 5.1.68)

        [mysqld(API)] 2 node(s)
        id=4 @10.253.4.xx (Version: 5.1.68)
        id=5 @10.252.151.xx (Version: 5.1.68)

        Then I tried to create the following ‘user_table’ on ‘user_database’:

        create table user_table(

        ycsb_key varchar(32) primary key,

        field1 varchar(100), field2 varchar(100), field3 varchar(100), field4 varchar(100),

        field5 varchar(100), field6 varchar(100), field7 varchar(100), field8 varchar(100),

        field9 varchar(100), field10 varchar(100))

        max_rows=1000 engine=ndbcluster partition by key(ycsb_key);

        I get this error:

        ERROR 1005 (HY000): Can’t create table ‘user_database.user_table’ (errno: 157)

        Can you please help me in this .

        • jagbirs

          Hi Sai Kumar,

          can you check ‘show engines;’ and also try to check the logs from where you can get clue why its unable to create table. in general such errors occurs due to communication issues between nodes (firewall, ports issues) and if ndbcluster engine not available somehow.

Previous post:

Next post: