MySQL Cluster Replication with Circular

Posted on by

The term cluster applies to groups or conglomerates of computers built using common hardware and behaving as if they were a single computer. Nowadays they play an important role in the solution of problems of science, engineering and modern commerce.

Cluster technology allows organizations to increase their processing capacity using standard technology, both in hardware and software components that can be purchased at a relatively low cost.

In an application high availability cluster, if a hardware or application failure of one of the machines occurs, the high availability software is able to automatically start the services that have failed in any of the others. When the failed machine is recovered, the services are again migrated to the original. This capacity of automatic recovery of services guarantees us the integrity of the information, since there is no loss of data, and avoids inconveniences to the users, who do not have to notice that a problem has occurred.

Development

For the implementation and development of a MySQL database cluster, two professional Hewlett-Packard (HP) servers were used, with an Intel Xeon microprocessor and 16GB of RAM and the distribution of Linux openSUSE version 12.1. The tools used to carry out the work are briefly explained below.

MySQL is a relational, multi-threaded and multi-user database management system with more than six million installations. This is widely used in web applications such as MediaWiki, Drupal or phpBB, on platforms (Linux / Windows, Apache, MySQL, PHP / Perl / Python), and bug tracking tools such as Bugzilla. Its popularity as a web application is closely linked to PHP, which often appears in combination with this manager. MySQL is a very fast database when reading the non-transactional MyISAM engine, but it can cause integrity problems in highly concurrent environments in the modification. In web applications, there is low concurrency in the modification of data and instead, the environment is intensive in reading data, which makes MySQL ideal for this type of applications.

The Pacemaker is a cluster administrator. This allows to obtain the maximum performance of the services, making use of the messages in the infrastructure of the cluster, allows making any type of redundancy including Active / Active Asset / Passive, etc. The Pacemaker is composed of three fundamental parts, the cluster core that allows messages and cluster membership (OpenAIS), a set of script to start, stop and monitor resources and a brain that processes and reacts to cluster events.

To achieve a fully functional cluster with this type of features, it is necessary to install the Pacemaker and Corosync software on the two computers that are to be clustered as shown below:

#zypper in corosync pacemaker
Getting the data from the repository ...
Reading the installed packages ...
mysql was not found as a package name, testing on features.
Resolving dependencies ...
The following NEW packages are going to be installed:
  cluster-glue corosync crmsh fence-agents libcorosync4 libdlm libdlm3 libesmtp libestr0 libfreebl3 libglue2 libgthread-2_0-0 libnet1 libpacemaker3 libqb0 libsensors4
  libsnmp30 libsoftokn3 libXss1 mariadb mozilla-nss mozilla-nss-certs openhpi OpenIPMI pacemaker pacemaker-cli perl-TimeDate psmisc pssh python-dateutil python-pexpect
  python-pssh python-pycurl python-tk resource-agents rsyslog snmp-mibs syslog-service tcl tk xhost


The following recommended packages were selected automatically:
  crmsh fence-agents libdlm


The following package is suggested, but it will not be installed:
  graphviz
41 new packages to install.


Total size to download: 18.2 MiB. After the operation, an additional 105.6 MiB will be used.
do you wish to continue? [s / n /? show all options] (s):s

If the installation completes successfully, the next step is to verify the connection between the two machines, using the ping command. To configure OpenAIS it is necessary to establish a port, a multicast address and the domain address of the cluster. In this work, 226.94.1.1 was used as a multicast address and 4000 as the service port. Once these parameters are set, the Corosync service can be started in the following way:

# rcopenais start
Starting OpenAIS / Corosync daemon (corosync): starting...OK

The Pacemaker has a tool that provides a command line interface called crm, which allows management and configuration of high availability clusters from scratch.

node hera \
        attributes standby = "off"
node zeus \
        attributes standby = "off"
primitive ClusterIP ocf: heartbeat: IPaddr2 \
        params ip = "172.16.0.200" cidr_netmask = "32" \
        op monitor interval = "30s"
primitive MySQL ocf: heartbeat: mysql \
        params binary = "/ usr / bin / mysqld_safe" user = "mysql" pid = "/ var / run / mysql / mysqld.pid" socket = "/ var / run / mysql / mysql.sock" \
        op start interval = "0" timeout = "120" \
        op stop interval = "0" timeout = "120" \
        op promote interval = "0" timeout = "120" \
        op demote interval = "0" timeout = "120"
clone MysqlClone MySQL \
        target target-role = "Started"
location cli-prefer-ClusterIP ClusterIP \
        rule $ id = "cli-prefer-rule-ClusterIP" inf: #uname eq zeus
property $ id = "cib-bootstrap-options" \
        dc-version = "1.1.6-9971ebba4494012a93c03b40a2c58ec0eb60f50c" \
        cluster-infrastructure = "openais" \
        expected-quorum-votes = "2" \
        stonith-enabled = "false" \
        no-quorum-policy = "ignore"
rsc_defaults $ id = "rsc-options" \
        resource-stickiness = "100"

This configuration shows the functional state, where all the nodes share the same IP address, it can be seen in the primitive MySQL directive ocf: heartbeat: mysql that the MySQL service is clustered. The following status also shows the status of the cluster:

#crm_mon
Last updated: Thu Mar 13 15:40:16 2014
Last change: Thu Mar 13 09:10:39 2014 by root via crm_attribute on hera
Stack: openais
Current DC: hera - partition with quorum
Version: 1.1.6-9971ebba4494012a93c03b40a2c58ec0eb60f50c
2 Nodes configured, 2 expected votes
3 Resources configured.
============
Online: [hera zeus]
ClusterIP (ocf :: heartbeat: IPaddr2): Started zeus
 Clone Set: MysqlClone [MySQL]
     Started: [hera zeus]

The cluster guarantees the availability of the service, but the integrity and availability of the information must be guaranteed with other types of tools. In this case, for the information to be kept in the same database, the Circular Replica method was used, which allows having the same information in the two databases. Replication works in the following way: the slave server starts with an exact copy of the data of the main server, then the binary record is activated in the main one and the slave connects to it periodically and check the changes made to the binary record since the last time it was connected [6]. For more information on the Circular Replica you can consult the MySQL5 reference manual at this address http://dev.mysql.com/doc/refman/5.1/en/.

Conclusion

Cluster technology has many benefits for critical function applications since it allows a high impact scalability model. In the CENSA data center this technology is applied obtaining satisfactory results. Web applications that have a critical mission for the work done in the institution, have achieved high availability using this type of technology.

Bibliography

1:, Scalability, 2013, http://es.wikipedia.org/wiki/Escalabilidad
2:, Cluster (computing), 2013, http://es.wikipedia.org/wiki/Cl%C3%BAster_%28inform%C3%A1tica%29
3:, MySQL, 2013, http://es.wikipedia.org/wiki/Mysql
4: Andrew Beekhof, Clusters from Scratch, 2013, http://clusterlabs.org/doc/en-US/Pacemaker/1.1-plugin/html-single/Clusters_from_Scratch/
5: Kristoffer Gronlund, crm – Pacemaker command line interface for configuration and management, 2013, http://crmsh.nongnu.org/crm.8.html