Mysql Cluster 7.6.4 Environment setup

MySQL Cluster Introduction

???? MySQL cluster is a complete distributed database system based on the NDB cluster storage engine. Not only high availability, but also automatic segmentation of data, redundant data and other advanced features. Unlike Oracle Real Cluster application, MySQL Cluster is a share nothing architecture that does not share any data between MySQL servers. The highly scalable and highly available aspect of outstanding performance is its greatest feature.

??? Simply put, MySQL Cluster is actually a fully distributed database system implemented in the absence of shared storage devices, mainly through the NDB Cluster (NDB) storage engine. MySQL Cluster was just born to be a memory database that can persist data, all data and indexes must be loaded in memory to function properly, but now the MySQL Cluster version is divided into memory table and disk table, memory table, All data is loaded into memory to run, the disk table simply loads the primary key, the index field is loaded in memory, and the other fields are stored on the magnetic field.

A MySQL Cluster environment consists of three main parts:

(1) The Manage node host is responsible for the management of each node , and the management node is responsible for the management of the nodes in the whole cluster cluster, including the configuration of the cluster, initiating and shutting down the nodes, routine maintenance of each node, and the backup and recovery of the implementation data. The management node obtains the status and error information of each node in the entire Cluster environment, and feeds the individual nodes in each Cluster cluster to all other nodes in the entire cluster. Since the configuration of the entire cluster environment is maintained on the management node, and the basic communication work of each node in the cluster is held, he must be the first node to be started.

(2) The NDB data node of the Storage layer , that is, NDB Cluster. The initial NDB is an in-memory storage engine, and of course the data is persisted to the storage device. But the latest NDB cluster storage engine has improved this by choosing whether the data is all loaded into memory or simply loading the index data. NDB node is mainly to implement the underlying data storage function, to save cluster data. Each cluster node holds a fragment of the complete data, that is, a data shard (or a complete set of data, depending on the number of nodes and configuration), so as long as it is properly configured, the MySQL cluster does not have a single point problem with the storage layer. In general, NDB nodes are organized into a single NDB group, and a NDB group is actually a group of NDB nodes with exactly the same physical data.

The above mentioned NDB each node to the data organization, perhaps each node has all the data may also save only a subset of data, mainly by the number of nodes and parameters to control. First, in the MySQL cluster master profile (above the management node, it is generally Config.ini), there is a very important parameter called Noofreplicas, which specifies the number of copies of each data is stored redundantly on the different nodes, the parameter should be set at least 2, and only need to be set to 2. As a normal case, the probability of the simultaneous failure of two redundant nodes is very small, of course, if the machine and memory enough, you can continue to increase to further reduce the probability of failure. In addition, whether a node is saving all or part of the data is also limited by the number of storage nodes. The NDB storage engine first guarantees the requirements of the Noofreplicas parameter configuration to use the storage nodes, redundancy the data, and then segment the data according to the number of nodes to continue using the redundant NDB nodes. The number of segments is the total number of nodes divided by Noofreplicas.

(3) SQL Server node of SQL layer (hereafter referred to as SQL node); That's what we often call MySQL server. Mainly responsible for the implementation of a database on the storage layer of all things, such as connection management, Query optimization and response, Cache management, and so on, only the work of the storage layer to the NDB data node to deal with. That is, the SQL node in the pure MySQL Cluster environment can be thought of as a MySQL server that does not need to provide any storage engine, because his storage engine has a NDB node in the Cluster environment to serve. Therefore, the SQL layer of the MySQL server startup and normal MySQL server boot also have a certain difference, you must add the ndbcluster parameter option. We can add them in the MY.CNF configuration file, or you can specify them by starting the command line.

MySQL Cluster Environment Setup

To build MySQL cluster first requires at least one Management node host to implement management functions, a SQL node host to implement the MySQL server function and two NDB node hosts to implement NDB cluster functions. I'm here to test the use of a dual SQL node to build a test environment with the following information:

1. Server Preparation

192.168.1.2???? Management node

192.168.1.3??? MySQL node, NDB node

192.168.1.4??? MySQL node, NDB node

I'm putting the MYSQ node L and the NDB data node on a single machine.

2. Software Installation

Test environment (3 servers are the same, not required, but the NDB node is the best, not the same, memory to match the same size, the server has been shut down iptables, production environment, please open the relevant port)

To install the MySQL node:

SQL Node 1:192.168.1.3
SQL Node 2:192.168.1.4

Download the installation package: mysql-cluster-gpl-7.6.4-linux-glibc2.12-x86_64.tar.gz, I use binary compiled here, you can also download the source package compiled. Here you operate one SQL node server, the other SQL node server is the same, perform the following installation steps.

wget *.*.*/mysql-cluster-gpl-7.6.4-linux-glibc2.12-x86_64.tar.gz

[[email protected] src]# Groupadd MySQL
[[email protected] src]# useradd-r-g MySQL MySQL
[Email protected] src]# TAR–ZVXF mysql-cluster-gpl-7.6.4-linux-glibc2.12-x86_64.tar.gz

[Email protected] src]# mv mysql-cluster-gpl-7.6.4-linux-glibc2.12-x86_64? /usr/local/mysql
[Email protected] src]# Cd/usr/local/mysql
[[email protected] mysql]# chown-r MySQL.
[Email protected] mysql]# mkdir/data/
[Email protected] mysql]# chown-r mysql.mysql/data
[Email protected] mysql]#/usr/local/mysql/bin/mysqld–initialize? --user=mysql--datadir=/data/--basedir=/usr/local/mysql

[email protected] mysql]# CP support-files/mysql.server   /etc/init.d/mysql

[Email protected]]# echo "Export path= $PATH:/usr/local/mysql/bin" >>/root/.bash_profile      #添加环境变量

Modify the/ETC/MY.CNF configuration file, append the following configuration

[Mysqld]
datadir=/data/
Basedir=/usr/local/mysql
Ndbcluster??????????????????????????????????????? # Running the NDB storage engine
ndb-connectstring=192.168.1.2?????? # Management Node
[Mysql_cluster]
ndb-connectstring=192.168.1.2????? #管理节点

NDB node installation (data node)

Because my data node is together with the SQL node, I don't need to install it. If the NDB data node and the SQL node are separate, the installation method is the same as the SQL node installation method. It is also necessary to configure the MY.CNF.

Install the Management node

The installation required to manage the nodes is simpler, in fact only requires NDB_MGM and NDB_MGMD two programs, both executable programs can be found in the MySQL installation directory of the above MySQL node under the bin directory. Copy the two programs to the appropriate location above the management node (for your own consideration, I will usually place the/usr/local/mysql/bin below) and add the environment variable. (You can also Ndb_config, ndb_perror?) Copy it over, Ndb_config-q maxnoofattributes? #查看变量修改是否生效; Ndb_perror--ndb 2308? 2308 is the error code)

1. Create the directory Mysql-cluster in/usr/local/, which will hold the relevant log file, as well as the PID number. and create a configuration file in the directory Config.ini

[Email protected] ~]# mkdir/usr/local/mysql-cluster[[email protected] ~]# Cd/usr/local/mysql-cluster/[[email Protected] mysql-cluster]# Touch Config.ini

2. According to the environment we provided above, the Config.ini file is configured as follows

[TCP Default]
Sendbuffermemory=2m
Receivebuffermemory=2m

[NDBD Default]
noofreplicas=2?????????????????????????? #每个数据节点的镜像数量, usually the lowest setting is 2, otherwise it doesn't make sense.
datamemory=3072m??????????????????? #每个数据节点中给数据分配的内存,
indexmemory=1024m?????????????????? #每个数据节点中给索引分配的内存?? , the 7.6 version has been renamed

maxnooforderedindexes=4000
maxnoofuniquehashindexes=4000
maxnoofattributes=4000

maxnoofconcurrentoperations=1500000
maxnooflocaloperations=2000000
maxnoofconcurrenttransactions=16000
transactiondeadlockdetectiontimeout=50000??????????????? #这是毫秒单位, equivalent to 50 seconds

#ODirect =1

maxnooftables=1024

#UndoDataBuffer =64m
#RedoBuffer =64m

#FragmentLogFileSize =512m
nooffragmentlogfiles=300

[NDB_MGMD]
Nodeid=1
hostname=192.168.1.2??????????????? #管理节点ip
Datadir=/var/lib/mysql-cluster?????? #管理节点数据目录, store related logs, and PID files

[NDBD]
nodeid=2
hostname=192.168.1.3???????? #数据节点ip地址
datadir=/data/??????????????????????? #NDB点数据存放目录

[NDBD]
Nodeid=3
hostname=192.168.1.4????????? #数据节点ip地址
Datadir=/data???????????????????????? #NDB点数据存放目录

[Mysqld]
Nodeid=4
hostname=192.168.1.3????????? #SQL节点ip地址

[Mysqld]
Nodeid=5
hostname=192.168.1.4??????? #SQL节点ip地址

[Mysqld]

In the above configuration file, including a lot of groups, the group name with "[]", here we are most concerned about the configuration of the Class 3 node group, respectively, defined as follows

NDB_MGMD] Represents the configuration of the management node and can only have one.

[NDBD default] represents the defaults for each data node, and there can be only one in the [ndbd] of each node without having to write these options.

[NDBD] Represents the configuration of each data node and can have more than one.

[MYSQLD] Represents the configuration of the SQL node, can have more than one, write the IP address of different SQL nodes, or do not write, only one empty node, indicating that any IP address can be accessed. The number of this node indicates the total number of SQL nodes that can be used to connect to the data node.

Each node has a separate ID number, can be filled out, such as nodeid=2, (old version using ID, the new version has not been identified with the ID) also can not fill, the system will be based on the configuration file filling order automatically assigned.

Getting Started with cluster

We have already configured the above, the following instructions to start, close and use the method

Boot order: Management node, data node->sql node (very important)

(1) Start the Management node:

[Email protected] ~]# ndb_mgmd-f/usr/local/mysql-cluster/config.ini  --initial   #第一次启动加-initial parameters, Later modified Config.ini plus-reloadmysql Cluster Management Server mysql-5.7.20 ndb-7.6.4[[email protected] ~]# NETSTAT-NTLP | grep 1186tcp        0      0 0.0.0.0:1186                0.0.0.0:*                   LISTEN      1329/NDB_MGMD     

(2) Start NDB (data node)

[Email protected] ~]# ndbd--initial2018-03-30 16:23:46 [ndbd] INFO     --Angel connected to ' 192.168.1.2:1186 ' 2018-03 -30 16:23:46 [NDBD] INFO     --Angel allocated Nodeid:3

[email protected]/]#? Ps-ef | grep ndbd
Root?????? 2913????? 1? 0 16:23???????? 00:00:00 NDBD
Root?????? 2914?? 2913 16:23???????? 00:00:43 NDBD

The NDBD process is a process that uses the NDB storage engine to process data in a table. Through this process, the storage node can realize distributed transaction management, node recovery, and online backup related tasks.

(3) Start the SQL node (start MySQL service)

[[Email protected]/]# service MySQL start
? success! MySQL Running (2588)

(4) After the node has started successfully, the management node uses the NDB_MGM tool's show command to view the cluster status:

[[Email protected]]#/NDB_MGM
--NDB Cluster--Management Client--

Ndb_mgm> Show
Cluster Configuration
---------------------
[NDBD (NDB)]???? 2 node (s)
Id=2??? @192.168.1.3? (mysql-5.7.20 ndb-7.6.4, nodegroup:0, *)
Id=3??? @192.168.1.4? (mysql-5.7.20 ndb-7.6.4, nodegroup:0)

[NDB_MGMD (MGM)] 1 node (s)
Id=1??? @192.168.1.2? (mysql-5.7.20 ndb-7.6.4)

[Mysqld (API)]?? 2 node (s)
Id=4??? @192.168.1.3? (mysql-5.7.20 ndb-7.6.4)
Id=5??? @192.168.1.4 (mysql-5.7.20 ndb-7.6.4)

The NDB_MGM tool is a NDB_MGMD (MySQL Cluster Server) Client Management tool that allows you to easily check the status of Cluster, initiate backups, and turn off functions. A more detailed approach can be viewed through the NDB_MGM--help command.

The MySQL cluster has been built here. The next article describes the dynamic plus node.

Mysql Cluster 7.6.4 Environment setup