MySQL cluster construction (1)

MySQL cluster construction (1)

Overview

MySQL Cluster is a highly practical, scalable, high-performance, and highly redundant MySQL version suitable for distributed computing environments. Its original design is to meet the most stringent application requirements in many industries, these applications often require that the database operation reliability reach 99.999%. MySQL Cluster allows you to deploy "in memory" database clusters in a non-shared system. Without a shared architecture, the system can use inexpensive hardware without special requirements on hardware and software. In addition, since each component has its own memory and disk, there is no single point of failure.

In fact, a MySQL cluster integrates a memory Cluster Storage engine called NDB with a standard MySQL server. It contains a group of computers, each running one or more processes, which may include a MySQL server, a data node, a Management Server, and a proprietary data access program.

MySQL Cluster can use a variety of failover and load balancing options to configure the NDB storage engine, but it is the easiest to do this on the storage engine at the Cluster level. The following figure shows the structure diagram of the MySQL cluster,

From the perspective of structure, MySQL consists of three types of nodes (computer or process:

Management node: used to provide configuration, management, arbitration, and other functions for other nodes in the cluster. Theoretically, you can provide services through a server.

Data Node: the Core of MySQL Cluster, which stores data and logs and provides various data management services. When there are more than two nodes, the high availability of the cluster can be ensured. When the DB nodes increase, the processing speed of the cluster will be slow.

SQL node (API): used to access MySQL Cluster data and provide external application services. Adding an API node increases the concurrent access speed and overall throughput of the entire cluster. This node can be deployed on a Web application server or a dedicated server, it is also deployed on the same server as the database.

NDB Engine

MySQL Cluster uses a dedicated memory-based storage engine-NDB engine. The advantage of this is that the speed is fast and there is no bottleneck on disk I/O, but because it is memory-based, therefore, the size of the database is limited by the total memory of the system. If the MySQL server running NDB must have enough memory, such as 4G, 8G, or even 16G. The NDB engine is distributed and can be configured on multiple servers for data reliability and scalability, theoretically, by configuring two NDB storage nodes, we can achieve the redundancy of the entire database cluster and solve the single point of failure (spof) problem.

Defects

• Based on memory, the size of the database is limited by the total memory size of the cluster.
• Based on memory, data may be lost after power failure, which also needs to be verified by testing.
• Multiple nodes implement communication, data synchronization, query, and other operations through the network. Therefore, the overall performance is affected by the network speed, so the speed is also slow.

2.2 advantages

• Multiple nodes can be distributed across different geographic locations. Therefore, it is also a solution for implementing distributed databases.
• The scalability is good. You can expand the database cluster by adding nodes.
• Redundancy is good, and multiple nodes have complete database data. Therefore, any node downtime will not cause service interruption.

The cost of achieving high availability is relatively low. Unlike traditional high availability solutions, shared storage devices and dedicated software are required for implementation, and NDB can be implemented with enough memory.

In this article, we will build a most simplified MySQL Cluster system. All the commands in the configuration method are run with the root account. This MySQL Cluster contains a management node, two data nodes, and two SQL nodes. These five nodes are installed on five virtual machines respectively. The virtual machine name and IP address are as follows:

I. Public Configuration

Configure the configuration items here on the three virtual machines.

1. Install virtual machines

Install CentOS 6.4 x86_64 in the virtual machine operating system, use the NAT network, and install vmware-tools. The installation method is not described here.

2. Copy mysql cluster

Download MySQL-Cluster of the following versions:

Http://cdn.mysql.com/Downloads/MySQL-Cluster-7.3/mysql-cluster-gpl-7.3.4-linux-glibc2.5-x86_64.tar.gz

Copy the downloaded package to the/root/Downloads directory of the VM and run the following command in the shell:

 
 

  
  
cd /root/Downloads 
  
  
 
  
  
tar -xvzf mysql-cluster-gpl-7.3.4-linux-glibc2.5-x86_64.tar.gz 
  
  
 
  
  
mv mysql-cluster-gpl-7.3.4-linux-glibc2.5-x86_64 /usr/local/mysql 
 
 

3. disable security policies

Disable iptables firewall (or open ports 1186 and 3306 of the firewall) and run the following command in Shell:

 
 

  
  
chkconfig --level 35 iptables off 
 
 

Close SELinux and run the following command in Shell:

 
 

  
  
gedit /etc/selinux/config 
 
 

Change the SELINUX item in the config file to disabled. The contents of the modified config file are as follows:

 
 

  
  
# This file controls the state of SELinux on the system. 
  
  
 
  
  
# SELINUX= can take one of these three values: 
  
  
 
  
  
# enforcing - SELinux security policy is enforced. 
  
  
 
  
  
# permissive - SELinux prints warnings instead of enforcing. 
  
  
 
  
  
# disabled - No SELinux policy is loaded. 
  
  
 
  
  
SELINUX=disabled 
  
  
 
  
  
# SELINUXTYPE= can take one of these two values: 
  
  
 
  
  
# targeted - Targeted processes are protected, 
  
  
 
  
  
# mls - Multi Level Security protection. 
  
  
 
  
  
SELINUXTYPE=targeted 
 
 

Finally, restart the system.