MySQL clustering

AgileApps is now certified with MySQL v5.7 enterprise edition and supports clustering for on-premises installation. MySQL cluster is designed to avoid single point of failure. The cluster is set up to avoid any node, system, or hardware failure.

MySQL cluster implementation

To implement MySQL cluster, you have to install three types of nodes:

• Management node: This node is used to manage the other nodes of the server.
• Data node: This node facilitates the process of data synchronization and data replication between the nodes.
• SQL node: This node acts as an interface server to connect the applications to the database cluster.

Each node should be installed in its own server.

Pre-requisites

Operating System	RedHat Enterprise Linux Server - Version 5 or Version 6 SUSE Linux Enterprise Server - Version 11 CentOS Linux - Release 7
Database	Management node: mgmt node1 = 10.60.30.75 Data node: data node 1 = 10.60.29.119, data node 2 = 10.60.29.176 SQL node: sql node 1 = 10.60.30.44, sql node 2 = 10.60.30.75
Libraries	MySQL Cluster client library MySQL Cluster server library MySQL Cluster shared library

Setting up nodes

To set up management, data, and SQL nodes:

1. Remove mariadb-libs and install perl-Data-Dumper required for MySQL Cluster server.
2. Download MySQL-Cluster-gpl-7.4.13-1.el7.x86_64.rpm-bundle.tar file from MySQL website.
3. Install MySQL cluster client, server, and shared packages using rpm commands.

Configuring management node

1. Create a new cluster management configuration file config.ini inside the /var/lib/mysql-cluster directory.
2. Save the configuration file after customizing the settings.
3. Start the Management node using: ndb_mgmd --config-file=/var/lib/mysql-cluster/config.ini.
4. Monitor the Management node using: ndb_mgm command.

Configuration settings

• HostName: Defines the hostname of the computer on which the data node resides.
• NoOfReplicas: Defines the number of replicas for each table stored in the cluster. This global parameter can be set only in the ndbd default section.
• DataMemory: Defines the amount of space (bytes) available for storing database records.
• IndexMemory: Controls the amount of storage used for hash indexes in NDB Cluster.
• MaxNoOfAttributes: Sets a suggested maximum number of attributes that can be defined in the cluster.
• MaxNoOfOrderedIndexes: Sets the total number of ordered indexes that can be in use in the system at any one time. For each ordered index in the cluster, an object is allocated to describe what is being indexed and to define the storage segments. For more information, see https://dev.mysql.com/doc/mysql-cluster-excerpt/5.7/en/mysql-cluster-ndbd-definition.html#ndbparam-ndbd-maxnooforderedindexes.

For more information on configuration settings, see https://dev.mysql.com/doc/mysql-cluster-excerpt/5.7/en/mysql-cluster-ndbd-definition.html.

Configuring data nodes

1. Configure data nodes using two servers:
   • data node 1 = 10.60.29.119
   • data node 2 = 10.60.29.176
2. Create a new configuration file my.cnf in the /etc directory using the VI editor.
3. Create a new data directory for the database defined in the management node config file.
4. Start the data node using ndbd command.
   Follow the same steps to configure other data nodes.

Configuration settings

• nbd-connectstring: Defines the IP address of the server on which the management node resides.

Configuring SQL nodes

1. Configure SQL nodes using two servers:
   1. sql node 1 = 10.60.30.44
   2. sql node 2 = 10.60.30.75
2. Create a new configuration file my.cnf in the /etc directory.
3. Start the MySQL server to start the SQL node.

Configuration settings

• ndb-connectstring: Defines the IP address of the server on which the management node resides.
• default_storage_engine: Defines the default storage engine being used by MySQL.
• ndbcluster: Enables the NDB cluster.
• sql-mode: Defines supported syntax and data validation performed by MySQL.

Monitoring the cluster

Use ndb_mgm command to view the cluster status.

Testing the cluster

1. Change the default MySQL password stored in ".mysql_secret" file in root directory (for SQL node) using: mysql_secure_installation.
2. Log on to MySQL cluster using the newly created password.
3. Create a new database from sql node 1 server.
   The database created on sql node 1 appears on sql node 2 also
4. Install the AgileApps application on SQLNode.