Shulou(Shulou.com)06/01 Report--

This article mainly introduces how to build the environment of Mysql Cluster, hoping to supplement and update some knowledge for you. If you have any other questions you need to know, you can continue to follow my updated article in the industry information.

MySQL Cluster introduction

MySQL Cluster is a complete distributed database system based on NDB Cluster storage engine. It not only has high availability, but also can automatically split data, redundant data and other advanced functions. Unlike Oracle Real Cluster Application, MySQL Cluster is a Share Nothing architecture that does not share any data among MySQL Server, and its outstanding features are highly scalable and highly available.

To put it simply, MySQL Cluster is actually a completely distributed database system without shared storage devices, which is mainly implemented by NDB Cluster (NDB) storage engine. When MySQL Cluster was born, it can be said to be an in-memory database that can persist data, all data and indexes must be loaded in memory in order to run normally, but now the MySQL Cluster version is divided into memory table and disk table, memory table, all data is loaded into memory to run, disk table only loads the primary key and index fields in memory, and other fields are stored on magnets.

A MySQL Cluster environment mainly consists of the following three parts:

(1) responsible for managing the Manage node hosts of each node; the management node is responsible for the management of each node in the whole Cluster cluster, including cluster configuration, startup and shutdown of each node, routine maintenance of each node, and implementation of data backup and recovery. The management node will obtain the status and error information of each node in the entire Cluster environment, and feedback the information of each node in each Cluster cluster to all other nodes in the entire cluster. Because the management node saves the configuration of the entire Cluster environment and acts as the basic communication between the nodes in the cluster, it must be the first node to be started.

(2) the NDB data node of the Storage layer; that is, NDB Cluster mentioned above. The original NDB was a memory storage engine that, of course, persisted data to storage devices. But the latest NDB Cluster storage engine has improved on this, allowing you to choose whether all the data is loaded into memory or just index data. The NDB node mainly implements the underlying data storage function to save the data of Cluster. Each Cluster node stores a fragment of complete data, that is, a data shard (or a complete data, depending on the number and configuration of nodes), so as long as it is properly configured, MySQL Cluster will not have a single point of problem in the storage layer. Generally speaking, NDB nodes are organized into a NDB Group, and a NDB Group is actually a group of NDB nodes with exactly the same physical data.

It is mentioned above that each node of NDB organizes the data, maybe each node has all the data or only a part of the data, which is mainly controlled by the number of nodes and parameters. First of all, in the MySQL Cluster main configuration file (above the management node, usually config.ini), there is a very important parameter called NoOfReplicas, which specifies the number of copies of each piece of data redundant stored on different nodes. Generally, this parameter should be set to at least 2, and only need to be set to 2. Because normally, the probability of failure of two redundant nodes at the same time is very small, of course, if the machine and memory are enough, it can continue to increase to further reduce the probability of failure. In addition, whether all the data or part of the data is saved on a node is also limited by the number of storage nodes. The NDB storage engine first ensures the requirements of NoOfReplicas parameter configuration to use storage nodes, redundancy the data, and then segments the data according to the number of nodes to continue to use redundant NDB nodes. The number of segments is the total number of nodes divided by NoOfReplicas.

(3) SQL CVM node in SQL layer (hereinafter referred to as SQL node); that is, MySQL Server. It is mainly responsible for implementing everything above the storage layer of a database, such as connection management, Query optimization and response, Cache management, etc., only the work of the storage layer is left to the NDB data node to handle. In other words, a SQL node in a pure MySQL Cluster environment can be thought of as a MySQL server that does not need to provide any storage engine, because its storage engine has a NDB node in the Cluster environment. Therefore, the startup of each MySQL server in the SQL layer is also different from the ordinary MySQL Server startup, and the ndbcluster parameter option must be added. We can add it to the my.cnf configuration file or specify it by starting the command line.

MySQL Cluster environment building

To build MySQL Cluster, we need at least one management node host to realize the management function, one SQL node host to realize the MySQL server function and two ndb node hosts to realize the NDB Cluster function. I am here to test the use of double SQL nodes to build the test environment. The details are as follows:

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 put the mysq node l and the ndb data node on a machine here.

2. Software installation

Test environment (all three servers are the same, it is not necessary, but the ndb node had better be the same, if different, the memory should be the same, the server has turned off iptables, and the production environment should open the relevant ports on its own)

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 the binary compiled here, you can also download the source package to compile. One SQL node server is operated here, and the other SQL node server is the same, all of which perform the following installation steps.

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

[root@192.168.1.2 src] # groupadd mysql

[root@192.168.1.2 src] # useradd-r-g mysql mysql

[root@192.168.1.2 src] # tar-zvxf mysql-cluster-gpl-7.6.4-linux-glibc2.12-x86_64.tar.gz

[root@192.168.1.2 src] # mv mysql-cluster-gpl-7.6.4-linux-glibc2.12-x86_64 / usr/local/mysql

[root@192.168.1.2 src] # cd / usr/local/mysql

[root@192.168.1.2 mysql] # chown-R mysql.

[root@192.168.1.2 mysql] # mkdir / data/

[root@192.168.1.2 mysql] # chown-R mysql.mysql / data

[root@192.168.1.2 mysql] # / usr/local/mysql/bin/mysqld-initialize-user=mysql-datadir=/data/-basedir=/usr/local/mysql

[root@192.168.1.2 mysql] # cp support-files/mysql.server / etc/init.d/mysql [root@192.168.1.2] # echo "export PATH=$PATH:/usr/local/mysql/bin" > > / root/.bash_profile # add environment variable [root@192.168.1.2 mysql] # source / root/.bash_profile modify / etc/my.cnf configuration file, and append the following configuration

[mysqld]

Datadir=/data/

Basedir= / usr/local/mysql

Ndbcluster # run the NDB storage engine

Ndb-connectstring=192.168.1.2 # Management Node

[MYSQL_CLUSTER]

Ndb-connectstring=192.168.1.2 # Management Node

NDB node installation (data node)

Because my data node is 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. You also need to configure my.cnf.

Install the management node

The installation required for the administrative node is simpler, actually requiring only two programs, ndb_mgm and ndb_mgmd, which can be found under the bin directory in the MySQL installation directory of the MySQL node above. Copy these two programs to the appropriate location above the management node (for my own sake, I usually put it under / usr/local/mysql/bin) and add environment variables. (alternatively, you can copy ndb_config and ndb_perror, and ndb_config-Q MaxNoOfAttributes # to see whether the variable modification takes effect; ndb_perror-- ndb 2308 2308 is the error code)

1. Create the directory mysql-cluster in / usr/local/, which will hold the relevant log files, as well as the pid number. And create the configuration file config.ini in the directory

[root@192.168.1.2 ~] # mkdir / usr/local/mysql-cluster [root@192.168.1.2 ~] # cd / usr/local/mysql-cluster/ [root@192.168.1.2 mysql-cluster] # touch config.ini2. 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 # the number of mirrors per data node, usually set to a minimum of 2, otherwise it will be meaningless

DataMemory=3072M # memory allocated to data in each data node

IndexMemory=1024M # memory allocated for indexes in each data node has been renamed in version 7.6

MaxNoOfOrderedIndexes=4000

MaxNoOfUniqueHashIndexes=4000

MaxNoOfAttributes=4000

MaxNoOfConcurrentOperations=1500000

MaxNoOfLocalOperations=2000000

MaxNoOfConcurrentTransactions=16000

TransactionDeadLockDetectionTimeOut=50000 # this is a millisecond unit, 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 # Management Node ip

DataDir=/var/lib/mysql-cluster # manages the node data directory, stores related logs, and pid files

[ndbd]

NodeId=2

HostName=192.168.1.3 # data Node ip address

DataDir=/data/ # NDB point data storage directory

[ndbd]

NodeId=3

HostName=192.168.1.4 # data Node ip address

DataDir=/data # NDB point data storage directory

[mysqld]

NodeId=4

HostName=192.168.1.3 # SQL Node ip address

[mysqld]

NodeId=5

HostName=192.168.1.4 # SQL Node ip address

[mysqld]

In the above configuration file, there are many groups whose names are enclosed by "[]". Here we are most concerned about the configuration of three types of node groups, which are defined as follows.

NDB_MGMD] indicates the configuration of the management node, and there can be only one.

[NDBD DEFAULT] represents the default configuration of each data node. Instead of writing these options in the [NDBD] of each node, there can only be one.

[NDBD] represents the configuration of each data node, which can have more than one.

[MYSQLD] indicates the configuration of SQL nodes. You can have multiple ip addresses of different SQL nodes, or you can leave only one empty node without writing, 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, which can be entered, such as nodeid=2 (the old version uses id, but the new version no longer uses the id logo), and the system will automatically assign it according to the order in which the configuration file is entered.

Start using Cluster

We have all been configured above, and the following instructions are given for startup, shutdown and use.

The startup sequence is: manage node-> data node-> SQL node (very important)

(1) start the management node:

[root@192.168.1.2 ~] # ndb_mgmd-f / usr/local/mysql-cluster/config.ini-- initial # first startup plus-initial parameter Later modified config.ini plus-reloadMySQL Cluster Management Server mysql-5.7.20 ndb-7.6.4 [root@192.168.0.30 ~] # netstat-ntlp | grep 1186tcp 00 0.0.0.0 root@192.168.0.30 1186 0.0.0.0 ntlp * LISTEN 1329/ndb_mgmd (2) start NDB (data node) [root@192.168.1.4 ~] # ndbd-- initial2018-03-30 16:23:46 [ndbd] INFO-- Angel connected to '192.168.1.2 purl 1186 August 2018-03-30 16:23:46 [ndbd] INFO-- Angel allocated nodeid: 3

[root@192.168.1.4 /] # ps-ef | grep ndbd

Root 2913 1 0 16:23? 00:00:00 ndbd

Root 2914 2913 27 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 SQL node (start mysql service)

[root@192.168.1.4 /] # service mysql start

SUCCESS! MySQL running (2588)

(4) after all the nodes are started successfully, use the show command of the ndb_mgm tool to check the cluster status in the management node:

[root@192.168.1.2] # / 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)

Ndb_mgm tool is the client management tool of ndb_mgmd (MySQL Cluster Server). Through this tool, you can easily check the status of Cluster, start backup, close and other functions. More detailed methods can be seen with the ndb_mgm-- help command.

At this point, the MySQL Cluster has been built.

After reading the above about how to build the environment of Mysql Cluster, I hope it can bring some help to you in practice. Due to the limited space in this article, it is inevitable that there will be deficiencies and need to be supplemented. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.