MySQL Optimization - Detailed Explanation of Cluster Building Code Steps (Picture)

1 Overview

---

MySQL Cluster is a highly practical, scalable, high-performance, and high-redundancy version of MySQL suitable for distributed computing environments. Its research and development The original intention of the design is to meet the most stringent application requirements in many industries. These applications often require database operation reliability to reach 99.999%. MySQL Cluster allows the deployment of "in-memory" database clusters in shared-nothing systems. Through the shared-nothing architecture, the system can use cheap hardware and has no special requirements for software and hardware. Additionally, since each component has its own memory and disk, there is no single point of failure.

In fact, MySQL Cluster integrates a memory cluster storage engine called NDB with the standard MySQL server. It consists of a set 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 configure the NDB storage engine with a variety of failover and load balancing options, but it is easiest to do this on the storage engine at the Cluster level. The following is the MySQL cluster structure diagram,

MySQL is composed of three types of nodes (computers or processes) from a structural perspective, namely ：

Management node: used to provide configuration, management, arbitration and other functions for other nodes in the entire cluster. In theory, it is enough to provide services through one server.

Data node: The core of MySQL Cluster, stores data and logs, and provides various management services for data. When there are more than 2 nodes, the high availability guarantee of the cluster can be achieved. When the number of DB nodes increases, the processing speed of the cluster will slow down.

SQL node (API): used to access MySQL Cluster data and provide external application services. Adding API nodes will improve the concurrent access speed and overall throughput of the entire cluster. The node can be deployed on the web application server, on a dedicated server, or on the same server as the DB.

2 NDB engine

---

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

---

2.1 Defect

---

• 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 a power outage. This needs to be verified through testing.

  Multiple nodes implement communication, data synchronization, query and other operations through the network, so the integrity is affected by the network speed,

  so the speed is relatively slow

  2.2 Advantages

  ---

• Multiple nodes can be distributed in different geographical locations, so it is also a solution to implement a distributed database.

  The scalability is very good, and the database cluster can be expanded by adding nodes.

  The redundancy is very good. There are complete database data on multiple nodes, so any node downtime will not cause service interruption.

  The cost of implementing high availability is relatively low. Unlike traditional high availability solutions that require shared storage devices and dedicated software, NDB can be implemented as long as there is enough memory.

2. Cluster Construction

A simplest MySQL Cluster system will be built. All commands in the configuration method are run with the root account. This MySQL Cluster contains one management node, two data nodes, and two SQL nodes. These five nodes will be installed on five virtual machines respectively. The names and IPs of the virtual machines are as follows:

##192.168.124.142Data nodemysql-ndbd-2##SQL

##Management node	mysql-mgm	##192.168.124.141
Data node 1	##mysql-ndbd-1
2	##192.168.124.143
Node 1##mysql-sql-1	192.168.124.144
##SQL Node2	mysql-sql-2	192.168.124.145

##1. Public configuration Please configure the configuration items here on the three virtual machines respectively.

1. Install the virtual machine

The virtual machine operating system installs the x86_64 version of CentOS 6.4, uses the NAT network, and also installs vmware-tools. The specific installation method is here Not detailed here.

2. Copy mysql cluster

Download the following version of MySQL-Cluster:

http://www.php.cn/

Copy the downloaded compressed package to the /root/Downloads directory of the virtual machine, and then 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. Turn off the security policy

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

chkconfig --level 35 iptables off

Turn off SELinux and run the following command in the 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

2. Configuration management node (192.168.124.141)

1. Configure the config.ini configuration file

Run the following command in the shell:

mkdir /var/lib/mysql-cluster
cd /var/lib/mysql-cluster
gedit config.ini

The content of the configuration file config.ini is as follows:

[ndbd default]
NoOfReplicas=2
DataMemory=80M
IndexMemory=18M
[ndb_mgmd]
NodeId=1
hostname=192.168.124.141
datadir=/var/lib/mysql-cluster
[ndbd]
NodeId=2
hostname=192.168.124.142
datadir=/usr/local/mysql/data
[ndbd]NodeId=3hostname=192.168.124.143datadir=/usr/local/mysql/data
[mysqld]NodeId=4hostname=192.168.124.144
[mysqld]
NodeId=5
hostname=192.168.124.145

2. Install the management node

To install the management node, you do not need the mysqld binary file, only the MySQL Cluster server program (ndb_mgmd ) and the listening client program (ndb_mgm). Run the following command in the shell:

cp /usr/local/mysql/bin/ndb_mgm* /usr/local/bin
cd /usr/local/bin
chmod +x ndb_mgm*

##3. Configure data nodes (192.168.124.142, 192.168.124.143)

1. Add mysql group and user

Run the following command in the shell:

groupadd mysql
useradd -g mysql mysql

2. Configure the my.cnf configuration file

in the shell Run the following command in:

gedit /etc/my.cnf

##The contents of the configuration file my.cnf are as follows:

[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/sock/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysql_cluster]
ndb-connectstring=192.168.124.141

3. Create the system database

Run the following command in the shell:

cd /usr/local/mysql
mkdir sock
scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

4. Set the data directory

in shell Run the following command in:

chown -R root .
chown -R mysql.mysql /usr/local/mysql/data
chown -R mysql.mysql /usr/local/mysql/sock
chgrp -R mysql .

##5. Configure MySQL serviceRun the following command in the shell:

##

cp support-files/mysql.server /etc/rc.d/init.d/
chmod +x /etc/rc.d/init.d/mysql.server
chkconfig --add mysql.server

4. Configuration SQL node (192.168.124.144

, 192.168.124.145)##1. Add mysql group and userRun the following command in the shell:

##

groupadd mysql
useradd -g mysql mysql

2. Configure my .cnf configuration fileRun the following command in the shell:

gedit /etc/my.cnf

The content of the configuration file my.cnf is as follows: ##

[client]
socket=/usr/local/mysql/sock/mysql.sock
[mysqld]
ndbcluster
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/sock/mysql.sock
ndb-connectstring=192.168.124.141
[mysql_cluster]
ndb-connectstring=192.168.124.141

3. Create system database

Run the following command in the shell:

cd /usr/local/mysql
mkdir sock
scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

4. 设置数据目录

在shell中运行以下命令：

chown -R root .
chown -R mysql.mysql /usr/local/mysql/data
chown -R mysql.mysql /usr/local/mysql/sock
chgrp -R mysql .

5. 配置MySQL服务

在shell中运行以下命令：

cp support-files/mysql.server /etc/rc.d/init.d/
chmod +x /etc/rc.d/init.d/mysql.server
chkconfig --add mysql.server

五、Cluster环境启动

注意启动顺序：首先是管理节点，然后是数据节点，最后是SQL节点。

1. 启动管理结点

在shell中运行以下命令：

ndb_mgmd -f /var/lib/mysql-cluster/config.ini

还可以使用ndb_mgm来监听客户端，如下：

ndb_mgm

2. 启动数据结点

首次启动，则需要添加--initial参数，以便进行NDB节点的初始化工作。在以后的启动过程中，则是不能添加该参数的，否则ndbd程序会清除在之前建立的所有用于恢复的数据文件和日志文件。

/usr/local/mysql/bin/ndbd --initial

如果不是首次启动，则执行下面的命令。

/usr/local/mysql/bin/ndbd

3. 启动SQL结点

若MySQL服务没有运行，则在shell中运行以下命令：

/usr/local/mysql/bin/mysqld_safe --user=mysql &

4. 启动测试

查看管理节点，启动成功：

六、集群测试

1. 测试一

现在我们在其中一个SQL结点上进行相关数据库的创建,然后到另外一个SQL结点上看看数据是否同步。

在SQL结点1（192.168.124.144）上执行：

shell> /usr/local/mysql/bin/mysql -u root -p
mysql>show databases;
mysql>create database aa;
mysql>use aa;
mysql>CREATE TABLE ctest2 (i INT) ENGINE=NDB; //这里必须指定数据库表的引擎为NDB,否则同步失败
mysql> INSERT INTO ctest2 () VALUES (1);
mysql> SELECT * FROM ctest2;

然后在SQL结点2上看数据是否同步过来了

经过测试，在非master上创建数据，可以同步到master上

查看表的引擎是不是NDB，>show create table 表名；

2. 测试二

关闭一个数据节点 ，在另外一个节点写输入，开启关闭的节点，看数据是否同步过来。

首先把数据结点1重启，然后在结点2上添加数据

在SQL结点2(192.168.124.145)上操作如下：

mysql> create database bb;
mysql> use bb;
mysql> CREATE TABLE ctest3 (i INT) ENGINE=NDB;
mysql> use aa;
mysql> INSERT INTO ctest2 () VALUES (3333);
mysql> SELECT * FROM ctest2;

等数据结点1启动完毕，启动数据结点1的服务

#/usr/local/mysql/bin/ndbd --initial#service mysqld start

然后登录进去查看数据

# /usr/local/mysql/bin/mysql -u root –p

可以看到数据已经同步过来了，说明数据可以双向同步了。

七、关闭集群

1. 关闭管理节点和数据节点，只需要在管理节点（ClusterMgm--134）里执行：

shell> /usr/local/mysql/bin/ndb_mgm -e shutdown

显示

Connected to Management Server at: localhost:1186
2 NDB Cluster node(s) have shutdown.
Disconnecting to allow management server to shutdown.

2. 然后关闭Sql节点（135,136），分别在2个节点里运行：

shell> /etc/init.d/mysql.server stop
Shutting down MySQL... SUCCESS!

注意：要再次启动集群，就按照第五部分的启动步骤即可，不过这次启动数据节点的时候就不要加”-initial”参数了。

The above is the detailed content of MySQL Optimization - Detailed Explanation of Cluster Building Code Steps (Picture). For more information, please follow other related articles on the PHP Chinese website!