Mysql cluster setup on amazon cloud

Setting up 6 node Mysql Cluster

Read thro http://dev.mysql.com/doc/mysql-cluster-excerpt/5.1/en/mysql-cluster-multi-install.html , there is not much after that. This is just a dump referring to auto scaling mysql cluster

This setup has 2 mgmt nodes on 2 different boxes, 2 data+api nodes on another 2 boxes.

In brief , you will need to

  • Get mysql mgmt installed on two boxes
  • Get mysql data+api node installed on another two boxes .
  • Open up firewall between all cluster members (open up all ports on udp and tcp)
  • If you are using ec2 , you can install on one instance and create AMI for another one , this will aid in auto scale too . choose a EBS store when choosing the image
  • Update cluster related configuration files on all cluster member nodes.
  • Start all members
  • Test the configuration

Mysql mgmt installation.

Mysql api/data node installation .

  • Get another amazon instance with EBS store .
  • wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.1/mysql-cluster-gpl-7.1.3-linux-x86_64-glibc23.tar.gz/from/http://ftp.heanet.ie/mirrors/www.mysql.com/
  • mv mysql-cluster-gpl-7.1.3-linux-x86_64-glibc23.tar.gz /var/tmp/
  • groupadd mysql
  • useradd -g mysql mysql
  • cd /var/tmp
  • tar -C /usr/local -xzvf mysql-cluster-gpl-7.1.3-linux-x86_64-glibc23.tar.gz
  • ln -s /usr/local/mysql-cluster-gpl-7.1.3-linux-x86_64-glibc23/ /usr/local/mysql
  • cd /usr/local/mysql
  • ./scripts/mysql_install_db –user=mysql
  • ./bin/mysqld_safe &
  • ./bin/mysqladmin -u root password xxxx
  • ps -A | grep mysql
  • chown -R root .
  • chown -R mysql data
  • chgrp -R mysql .
  • cp support-files/mysql.server /etc/init.d/
  • chmod +x /etc/init.d/mysql.server
  • /etc/init.d/mysql.server stop/start and check if all is well.
  • mkdir -p /usr/local/mysql/backup
  • chmod -R 700 /usr/local/mysql
  • cp /usr/local/mysql/bin/mysqld_safe /usr/local/bin/
  • cp /usr/local/mysql/bin/mysqlmanager /usr/local/bin/
  • cp /usr/local/mysql/bin/mysql /usr/local/bin/

Setup Amazon Firewall.

Open up all ports between cluster members.


Configure cluster related files.

Login to mgmt server 1 and 2.

vi /var/lib/mysql-cluster/config.ini

[NDBD DEFAULT]

NoOfReplicas=2

DataMemory=80M

IndexMemory=18M

[MYSQLD DEFAULT]

[NDB_MGMD DEFAULT]

[TCP DEFAULT]

[NDB_MGMD]

# IP address of the management node 1 (this system)

NodeId=1

HostName=mgmt1

DataDir=/var/lib/mysql-cluster

[NDB_MGMD]

# IP address of the management node 2 (this system)

NodeId=2

HostName=mgmt2

DataDir=/var/lib/mysql-cluster

# Section for the storage nodes

[NDBD]

# IP address of the first storage node

NodeId=3

HostName=node1

DataDir=/usr/local/mysql/data

BackupDataDir=/usr/local/mysql/backup

DataMemory=2048M

[NDBD]

# IP address of the second storage node

NodeId=4

HostName=node2

DataDir=/usr/local/mysql/data

BackupDataDir=/usr/local/mysql/backup

DataMemory=2048M

# one [MYSQLD] per storage node

[MYSQLD]

[MYSQLD]

Login to data/sql node server 1 and 2 .

Do foll on both servers .

Vi /etc/my.cnf

[mysqld]

datadir=/usr/local/mysql/data

socket=/var/lib/mysql/mysql.sock

ndbcluster

ndb-connectstring=mgmt1,mgmt2

default-storage-engine=NDBCLUSTER

[client]

port=3306

socket=/var/lib/mysql/mysql.sock

[mysql.server]

user=mysql

basedir=/usr/local

[mysql_cluster]

ndb-connectstring=mgmt1,mgmt2

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

Start up Mysql Cluster

Login to mgmt server 1 and 2 .

ndb_mgmd -f /var/lib/mysql-cluster/config.ini –initial (use intial only for the first tie and when config.ini chnages)

root@domU-X.X.X1-F2:/var/lib/mysql-cluster# ndb_mgm

— NDB Cluster — Management Client —

ndb_mgm> show

Connected to Management Server at: localhost:1186

Cluster Configuration

———————

[ndbd(NDB)] 2 node(s)

id=3 (not connected, accepting connect from domU-x.x.x3.compute-1.internal)

id=4 (not connected, accepting connect from domU- x.x.x4.compute-1.internal)

[ndb_mgmd(MGM)] 2 node(s)

id=1 (not connected, accepting connect from domU- x.x.x1.compute-1.internal)

id=2 @domU-x.x.x2 (mysql-5.1.44 ndb-7.1.3)

[mysqld(API)] 2 node(s)

id=5 (not connected, accepting connect from any host)

id=6 (not connected, accepting connect from any host)

login to both data/sql nodes

/usr/local/mysql/bin/ndbd

For debug , use /usr/local/mysql/bin/ndbd -v –foreground

You can also see debugging logs of ndbd in the data folder of your mysql installation

tail –f /usr/local/mysql/ndbd_x.log

/etc/init.d/mysql.server restart

Now go and check ndb_mgm on mgmt node, you should see all the members connected.

ndb_mgm> show

Cluster Configuration

———————

[ndbd(NDB)] 2 node(s)

id=3 @ip (mysql-5.1.44 ndb-7.1.3, Nodegroup: 0, Master)

id=4 @ip (mysql-5.1.44 ndb-7.1.3, Nodegroup: 0)

[ndb_mgmd(MGM)] 2 node(s)

id=1 @ip (mysql-5.1.44 ndb-7.1.3)

id=2 @ip (mysql-5.1.44 ndb-7.1.3)

[mysqld(API)] 2 node(s)

id=5 @ip (mysql-5.1.44 ndb-7.1.3)

id=6 @ip (mysql-5.1.44 ndb-7.1.3)

ndb_mgm>

check by creating tables in test database in node 1.

In node 2 , check if that table reflects in test schema.

Insert a row into this table in node 2 .

Test if the data is reflected in node 1.

NOTE : create users,permissions on both nodes. Creation of Database, Tables and data will be duplicated on both nodes when created on one.


Advertisements

4 Comments

  1. Tonny
    Posted June 20, 2011 at 4:38 pm | Permalink | Reply

    Hello,

    I need some Help
    Just trying to install the MySQL Cluster 7.1
    I followed the steps and im setting the 1st data Node.
    Somehow i can not install the mysql database… after this command:
    ./bin/mysqladmin -u root password xxxx

    i get the following error:

    ./bin/mysqladmin: connect to server at ‘localhost’ failed
    error: ‘Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)’
    Check that mysqld is running and that the socket: ‘/tmp/mysql.sock’ exists!

    What can i do???

  2. Tonnywampus
    Posted June 20, 2011 at 4:39 pm | Permalink | Reply

    Hello,

    I need some Help
    Just trying to install the MySQL Cluster 7.1
    I followed the steps and im setting the 1st data Node.
    Somehow i can not install the mysql database… after this command:
    ./bin/mysqladmin -u root password xxxx

    i get the following error:

    ./bin/mysqladmin: connect to server at ‘localhost’ failed
    error: ‘Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)’
    Check that mysqld is running and that the socket: ‘/tmp/mysql.sock’ exists!

    What can i do???

    Thx for your Help

  3. Tonnywampus
    Posted June 20, 2011 at 9:31 pm | Permalink | Reply

    Hello,

    i was able to install everthing but now when i want to start the management cluster,

    i get tht error:
    Unable to connect with connect string: nodeid=0,[10.203.23.171]:1186
    Retrying every 5 seconds. Attempts left: 2 1, failed.

    any Idea what is wrong?

  4. Posted October 3, 2012 at 5:08 am | Permalink | Reply

    Has anyone bench-marked a system on Amazon EC2 to see what kind of I/O performance the cluster can support and how large it needs to grow to support x reads and y writes?

Post a Comment

Required fields are marked *

*
*

%d bloggers like this: