MySQL Cluster 7.2 with MySQL Cluster Management (MCM)
3/26/2012 04:58:00 PM
Posted by johnhomer
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)
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 &
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.
This entry was posted on October 4, 2009 at 12:14 pm, and is filed under
mysql
. Follow any responses to this post through RSS. You can leave a response, or trackback from your own site.
Subscribe to:
Post Comments (Atom)
September 29, 2016 at 9:39 PM
My devs asked to say thank you! They used some of your tips while we were switching our job to secure virtual data room . Waiting for some your new posts!
July 13, 2017 at 1:36 PM
Nice blog thanq for such a wonderful post it is very informative blog I really liked your blog
CCNA Openings in Hyderabad .
January 1, 2019 at 7:40 PM
aws is growing cloud computing technology. Start aws training in hyderabad