MySQL also support High Availability, NDB Storage engine provides high availability- shared-nothing system, NDB Cluster integrates the standard MySQL server with an in-memory clustered storage engine called NDB (which stands for “Network DataBase”
NDB Cluster required 3 Nodes required complete the setup:
- Management node(mgmd):
This node is used to manage the cluster, using this node we can configured, start and stop the cluster, using this node only we can run the backup- ndb_mgmd.
- Data node(ndbd):
As the name suggest, this node contains the actual data. There should be more than 1 data node required for data redundancy (replica). Default set to 2- these replca’s will contain the same information and if any one node goes down data will be available on other node.
NDB Cluster tables are normally stored completely in memory rather than on disk (this is why we refer to NDB Cluster as an in-memory database). The data will be flushed from memory to data nodes periodically using LCP and GCP.
Local Checkpoint (LCP):
This is checkpoint to data node, it save the data from memory to disk occurs every few minutes. It depends upon the amount of data stored by the node, the level of cluster activity, and other factors.
Global Checkpoint (GCP):
GCP occurs every few seconds, when transactions for all nodes are synchronized and the redo-log is flushed to disk.
- SQL Node (mysqld):
This node contains MySQLD mysql services, using this node Cluster data will be accessed. This is also called as API node.
Each data node or SQL node requires a my.cnf file as follows:
The management node needs a config.ini file, this is important and contains all the information about NDB Cluster (SQL Node, Data Node and management node) For our representative setup, the config.ini file should read as follows:
NoOfReplicas=2 # replicas
DataMemory=80M # memory allocate – data storage
IndexMemory=18M # memory allocate – index storage
HostName=HostNm # MGM node
DataDir=/var/lib/mysql-cluster #MGM node log files location
The management node should be started first, followed by the data nodes, and then finally by any SQL nodes:
It support sonly READ COMMITTED transaction isolation level