This guide describes the step-by-step procedure on setting up a test MySQL Cluster using the MySQL Cluster Management Console (MCM).

• A total of four physical or virtual servers (known as Cluster Nodes in MySQL Cluster term)
  • Two cluster nodes will serve as Data Node (ndb, this is where our data reside)
  • Two other servers will serve as both SQL Nodes (mysqld) and Management Nodes (ndb_mgmd)
• All four servers will only need to have the MySQL Cluster Management Agent installed
• Each Data Node will have a pair of ndbd processs to maintain the replica assigned to it
• Clients (PHP web application) will connect to SQL Nodes (mysqld)
• This setup is supposed to survive a single Cluster Node failure
  

   

• VMware workstation, Hyper-V, Xen or even Amazon AWS
• Four server instances
• Each instance should have at least 1GB RAM (although Management Nodes/API Nodes can have lesser RAM. more about it during the steps)
• CentOS 6.2
• SELinux disabled
• iptables disabled
• MySQL Cluster Manager 1.1.4+Cluster for Red Hat and Oracle Linux 5 x86 (64-bit) - from oracle edelivery site. This package includes the MCM Agent and MySQL Cluster software

All four server instances should have this management agent. This is the only manual process that needs to be done on individual nodes. All other activities can be done through the MCM commandline console

• for configuration simplicity, register all nodes in the hosts file
  

  cat <<EOF>> /etc/hosts
  192.168.0.10 site1
  192.168.0.11 site2
  192.168.0.12 site3
  192.168.0.13 site4
  EOF

• copy the MCM Agent to /tmp
• prepare the MCM agent files
  

  cd /tmp
  unzip V31807-01-MCM-Cluster.zip
  mkdir /opt/mcm
  tar xvz --directory=/opt/mcm/ --strip-components=1 -f mcm-1.1.5_64-cluster-7.2.5_64-linux-rhel5-x86.tar.gz

• add users and fix directory permissions
  

  groupadd clustermanager && useradd -M -d /opt/mcm/ -g clustermanager clustermanager
  chown -R clustermanager.clustermanager /opt/mcm/

• start the MCM daemon
  

  sudo -u clustermanager /opt/mcm/bin/mcmd &

At this point, we are done with the rest of the Cluster Node instances, all of the steps from this point forward can be done at the first or second server instances (the management servers).

We are now ready to create our first cluster. The main steps are: create a site, add a package, create a cluster and finally start the cluster.

• connect to MCM command-line console. The default password is super
  

  /opt/mcm/cluster/bin/mysql -h127.0.0.1 -P1862 -uadmin -psuper --prompt='mcm> '

• create a site
  

  mcm> create site --hosts=site1,site2,site3,site4 mysite;

• create a package. A package is like a MySQL instance composed of MySQL binaries, libraries and configuration files. The name of the package we are going to create is 7.2
  

  mcm> add package --basedir=/opt/mcm/cluster 7.2;

• create a cluster
  ndb_mgmd - Cluster management node on site1 & site2
  ndbd - Single threaded Data node on site3 & site4 (twice. Each machine hold couple of data nodes for our demo)
  mysqld - MySQL interface node on site1 & site2
  ndbapi - for API interface
  ndbmtd - for the multi-threaded NDB engine
  

  mcm> create cluster --package=7.2 [email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected],[email protected] mycluster;

• if you do not have 1GB RAM for Data Node instances, you may need to modify innodb_buffer_pool_size here so that MySQL will start. This is also the right time to make other MySQL tuning
  

  get -d innodb_buffer_pool_size:mysqld mycluster;
  
  # This enables me to run cluster nodes with only 2GB RAM for testing purposes
  set innodb_buffer_pool_size:mysqld:51=16777216 mycluster;
  set innodb_buffer_pool_size:mysqld:52=16777216 mycluster;
  
  # Do this of you plan on storing large datasets
  set DataMemory:ndbd=3145728000 mycluster;
  set IndexMemory:ndbd=536870912 mycluster;
  
  

• Start the cluster
  

  mcm> start cluster -B mycluster;

• See the status of the cluster
  

  mcm> show status -r mycluster;

• Connecting through MySQL Client
  

  mkdir  /var/lib/mysql/
  ln -s /tmp/mysql.mycluster.51.sock /var/lib/mysql/mysql.sock
  mysql -uroot
  

• Changing from a single-threaded cluster node to multi-threaded
  

  mcm> change process ndbd:3=ndbmtd mycluster;

• You don't normally need to manually do a rolling restart since MySQL cluster will take care of it if you make changes that requires a restart. But if you need it, here's how it's done
  

  mcm> restart cluster -B mycluster;

• Here's how to do an online upgrade of cluster software. We call the new package as 7.3
  

  mcm> add package --basedir=/usr/local/mysql_7_3 7.3;
  mcm> upgrade cluster --package=7.3 mycluster;

• Adding new hosts
  
  

  

  
  
  

  # Initialize the new hosts. Also take note that you need to add necessary entries in /etc/hosts for the new hosts
   
  mcm> add hosts --hosts=site5,site6 mysite;
  mcm> add package --basedir=/opt/mcm/cluster --hosts=site5,site6 7.2;
   
  # Finally, add it to the cluster.
  # Note that the we are also adding API instances on site1 and site2. Also, as pointed out by Andrew Morgan, we have to guess the node-id's of the the new mysqld's. In our case, the will be node-id's are 53 and 54 following the output in show statur -r mycluster
  mcm> add process [email protected],[email protected],[email protected],[email protected],[email protected],[email protected] -s port:mysqld:53=3307,port:mysqld:54=3307 mycluster;
  mcm> start process --added mycluster; 
   
  # On any of the API servers, do the following commands to repartition the 
  # existing cluster and use the new data nodes
   
  mysql> ALTER ONLINE TABLE [table-name] REORGANIZE PARTITION;
  mysql> OPTIMIZE TABLE [table-name];

stop cluster -B mycluster;
 
delete cluster mycluster;
delete package 7.2;
delete site mysite;

list clusters mysite;
list packages mysite;
list sites;