Install MySQL Cluster in Debian

MySQL Cluster is different compare to normal MySQL server. It has 3 roles:

  • management
  • data
  • SQL or API

Data node will required a lot of memory utilization. It is recommended for these nodes to not share any workload with SQL or management nodes as it would end up with resources exhaustion. So we will setup SQL node together with management node to reduce number of servers used to 4 (instead of 6 – 3 roles x 2 servers).

To have the best minimum setup, we will setup 2 servers as 2 data nodes, and another 2 servers will be SQL nodes co-located with management nodes:

  • sql1 (192.168.10.101) – sql node #1 + management node #1
  • sql2 (192.168.10.102) – sql node #2 + management node #2
  • data1 (192.168.10.103) – data node #1
  • data2 (192.168.10.104) – data node #2

I am using Debian 6.0.7 Squeeze 64bit.

 

All Nodes

1. Install libaio-dev which required by MySQL Cluster:

$ apt-get update && apt-get install libaio-dev -y

2. Download the package from MySQL Downloads page here and extract it under /usr/local directory:

$ cd /usr/localwget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.2/mysql-cluster-gpl-7.2.12-linux2.6-x86_64.tar.gz/from/http://cdn.mysql.com/
$ tar -xzf mysql-cluster-gpl-7.2.12-linux2.6-x86_64.tar.gz

3. Rename the extracted directory to a shorter name which is mysql:

$ mv mysql-cluster-gpl-7.2.12-linux2.6-x86_64 mysql

4. Create MySQL configuration directory:

$ mkdir /etc/mysql

5. Export MySQL bin path into user environment:

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

6. Create mysql user and group and assign the correct permission to MySQL base directory:

$ useradd mysql
$ chown -R mysql:mysql /usr/local/mysql

Once completed, make sure that MySQL path is /usr/local/mysql with correct ownership of user mysql.

Data Nodes

1. Login to data1 and data2 create MySQL configuration file at /etc/mysql/my.cnf and add following lines:

[mysqld]
ndbcluster
ndb-connectstring=sql1,sql2
 
[mysql_cluster]
ndb-connectstring=sql1,sql2

2. Create the MySQL cluster data directory:

$ mkdir /usr/local/mysql/mysql-cluster

SQL Nodes + Management Nodes

1. Login to sql1 and sql2 and configure SQL nodes by adding following lines into /etc/mysql/my.cnf:

[mysqld]
ndbcluster
ndb-connectstring=sql1,sql2
port=3306
default_storage_engine=ndbcluster
 
[mysql_cluster]
ndb-connectstring=sql1,sql2

2. Create data directory for MySQL cluster:

$ mkdir -p /usr/local/mysql/mysql-cluster

3. Create MySQL Cluster configuration file at /etc/mysql/config.ini:

$ vim /etc/mysql/config.ini

And add following lines:

[ndb_mgmd default]
DataDir=/usr/local/mysql/mysql-cluster
 
[ndb_mgmd]
NodeId=1
HostName=sql1
 
[ndb_mgmd]
NodeId=2
HostName=sql2
 
[ndbd default]
NoOfReplicas=2
DataMemory=256M
IndexMemory=128M
DataDir=/usr/local/mysql/mysql-cluster
 
[ndbd]
NodeId=3
HostName=data1
 
[ndbd]
NodeId=4
HostName=data2
 
[mysqld]
[mysqld]

4. Copy the mysql.server init script from support-files directory into /etc/init.d directory and setup auto-start on boot:

$ cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
$ update-rc.d mysql defaults

5. Install MySQL system tables:

$ cd /usr/local/mysql
$ scripts/mysql_install_db --user=mysql

Starting the Cluster

1. Start MySQL cluster management service in sql1 and sql2:

For sql1:

$ ndb_mgmd -f /etc/mysql/config.ini --ndb-nodeid=1

For sql2:

$ ndb_mgmd -f /etc/mysql/config.ini --ndb-nodeid=2

2. Start MySQL cluster storage service:

For data1:

$ ndbd --ndb-nodeid=3

For data2:

$ ndbd --ndb-nodeid=4

3. Start the MySQL API service at sql1 and sql2:

$ service mysql start

4. Check the MySQL cluster status in sql1 or sql2:

$ ndb_mgm -e show
Connected to Management Server at: ndb1:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=3 @192.168.197.10.103(mysql-5.5.30 ndb-7.2.12, Nodegroup: 0, Master)
id=4 @192.168.197.10.104 (mysql-5.5.30 ndb-7.2.12, Nodegroup: 0)
 
[ndb_mgmd(MGM)] 2 node(s)
id=1 @192.168.197.10.101 (mysql-5.5.30 ndb-7.2.12)
id=2 @192.168.197.10.102 (mysql-5.5.30 ndb-7.2.12)
 
[mysqld(API)] 2 node(s)
id=5 @192.168.197.10.101 (mysql-5.5.30 ndb-7.2.12)
id=6 @192.168.197.10.102 (mysql-5.5.30 ndb-7.2.12)

5. Since MySQL user table do not run on ndb storage enginer, we need to create MySQL root password in both nodes (sql1 and sql2):

$ mysqladmin -u root password 'r00tP4ssword'

Cluster ready! Now you can start to query the MySQL cluster by connecting to sql1 or sql2. For best performance in load balancing and failover, you can setup HAproxy in front of sql1 or sql2.

3 thoughts on “Install MySQL Cluster in Debian

  1. When i execute scripts/mysql_install_db –user=mysql i get :
    FATAL ERROR
    Could not find ./bin/my_print_defaults
    How can i fix this error ?
    ThANKS TO ALL

    Reply

  2. I used this to set up two clusters, the first one started without any issues, the second one didn’t start. I had the run the following two lines on the sql nodes to get it to start.

    ndb_mgmd -f /etc/mysql/config.ini
    ndb_mgmd –skip-config-cache -f /etc/mysql/config.ini

    Reply

Leave a Reply

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