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

Architecture

  • 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
     

Requirements

  • 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

Installing the MCM Agent with MySQL Cluster

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).

Firing the First Cluster

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
    

Other tasks

  • 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];

Deleting the cluster

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

other useful commands

list clusters mysite;
list packages mysite;
list sites;


Credits to Andrew Morgan for the write-up and images.