MySQL cluster exploration and practices

Introduction

 

MySQL cluster is a technology that applies memory database clusters in a non-shared architecture system. This non-shared architecture can make the system use very inexpensive and minimum-configuration hardware.

A MySQL cluster is a distributed design designed to achieve zero point of failure. Therefore, any component should have its own memory and disk. Any shared storage solutions such as network sharing, network file systems and San devices are not recommended or supported. With this redundant design, MySQL claims that the data availability can reach 99. 999%.

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. Shows the relationship between them:

 

Storage Engine

MySQL cluster uses a dedicated memory-based storage engine. The advantage of this is that it is fast and has no disk I/O bottleneck,

However, because it is based on memory, the database size is limited by the total memory of the system. If the MySQL server running NDB must have enough memory,

For example, 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.

This storage engine has the following drawbacks:

• 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,
• Therefore, the speed is also slow.

Of course, it also has its 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.

 

Architecture

A MySQL cluster consists of three services with different functions. Each service is provided by a dedicated daemon. A service is also called a node. The following describes the functions of each node.

The management (MGM) node

Management Nodes are used to manage the entire cluster. Theoretically, only one node is started, and the downtime does not affect the cluster service.

The cluster takes effect when it is started and the node is added to the cluster. Therefore, this node does not need to be redundant. Theoretically, a server can provide services.

Run the ndb_mgmd command and use the config. ini configuration file.

The storage or database (db) node:

Database nodes, used to store data, can be different from management nodes (MGM), user-side nodes (APIS) can be on different machines, or on the same machine

The cluster must have at least one DB node. When there are more than two dB nodes, the high availability of the cluster can be ensured. When the DB nodes increase, the processing speed of the cluster will slow down.

Start with the ndbd command. When creating a cluster dB node for the first time, use the-init parameter for initialization.

Example: Bin/ndbd-NDB-connectstring = ndb_mgmd.mysqlcluster.net-initial

The client (API) node:

Client node, which can be used to access cluster dB. This node is also a common mysqld process and needs to be configured in the configuration file.

The ndbcluster command enables the NDB Cluster Storage engine. Adding an API node will increase the concurrent access speed and overall speed of the entire cluster.

The node can be deployed on a Web application server, a dedicated server, and a DB server.

Start with the mysqld_safe command,

These three types of nodes can be distributed on different hosts. For example, databases can be multiple dedicated servers, or each database has an API.

Usually, the more APIs, the better the cluster performance.

 

MySQL cluster exploration and practices

1. 3 machines are installed, and the latest mysqlcluster is downloaded from the official network. This is used in the mysql-cluster-gpl-7.1.5.tar.gz source code package, configure and install it. Remember to add

-- With-plugins = Innobase, ndbcluster (Innobase optional)

The three machines are 192.168.207.153, 192.168.208.3, and 192.168.208.9. The specific allocation is as follows:

Management node (ndb_mgmd): 192.168.207.153

Data Node (ndbd): 192.168.208.3

Data Node (ndbd): 192.168.208.9

SQL node (mysqld): 192.168.208.3

SQL node (mysqld): 192.168.208.9

2. Create a MySQL-cluster folder under the MySQL directory, switch to MySQL-cluster, and create config. ini.

[Ndbd default] noofreplicas = 2 # backup and copy. In this way, the data of the two data nodes will be synchronized to datamemory = 200 mindexmemory = 100 m
[TCP default] portnumber = 2202
[Ndb_mgmd] # management node id = 1 hostname = 192.168.207.153datadir =/home/taozi/MySQL-Cluster
[Ndbd] # data node id = 2 hostname = 192.168.208.3datadir =/home/taozi/MySQL/Data
[Ndbd] # data node id = 3 hostname = 192.168.208.9datadir =/home/taozi/MySQL/Data
[Mysqld] # SQL node id = 4 hostname = 192.168.208.3
[Mysqld] # SQL node id = 5 hostname = 192.168.208.9
[Mysqld] # SQL node id = 6

3. Start the management node service on the Management node server (if ndb_mgmd does not exist, copy it from libexec)

~/mysql/bin/ndb_mgmd -f ~/mysql/mysql-cluster/config.ini

4. Enter two data node servers and start the data node services respectively.

~ /MySQL/bin/ndbd (used for the first time ~ /MySQL/bin/ndbd -- Initial)

5. log on to the SQL node server, modify my. CNF, and add

[MYSQL_CLUSTER]ndb-connectstring=192.168.207.153
[MYSQLD]ndbclusterndb-connectstring=192.168.207.153

Start MySQL Service

/home/taozi/mysql/bin/mysqld_safe --ledir=/home/taozi/mysql/bin /--log-error=/home/taozi/mysql/data/t.err --datadir=/home/taozi/mysql/data /--socket=/home/taozi/mysql/tmp/mysql.sock --pid-file=/home/taozi/mysql/data/mysqld.pid &

6. Return to the management node.

~/mysql/bin/ndb_mgm -e show

As shown in the following figure:

[taozi@search153 mysql]$ ./show.sh Connected to Management Server at: localhost:1186Cluster Configuration---------------------[ndbd(NDB)]     2 node(s)id=2    @192.168.208.3  (mysql-5.1.47 ndb-7.1.5, Nodegroup: 0, Master)id=3    @192.168.208.9  (mysql-5.1.47 ndb-7.1.5, Nodegroup: 0)
[ndb_mgmd(MGM)] 1 node(s)id=1    @192.168.207.153  (mysql-5.1.47 ndb-7.1.5)
[mysqld(API)]   3 node(s)id=4    @192.168.208.3  (mysql-5.1.47 ndb-7.1.5)id=5    @192.168.208.9  (mysql-5.1.47 ndb-7.1.5)id=6 (not connected, accepting connect from any host)

7. Go to the SQL node and create a table in the test database.

CREATE TABLE `t1` (  `id` int(11) NOT NULL AUTO_INCREMENT,  PRIMARY KEY (`id`)) ENGINE=ndbcluster  DEFAULT CHARSET=gbk

Switch to two data node servers ~ /MySQL/data/ndb_2_fs and ~ /MySQL/data/ndb_3_fs,

Or go to the database to check whether the data has been synchronized!

8. Disable the cluster service.

Disabling an SQL node is equivalent to stopping the MySQL service. In this case, external data will not come in again. Close the management Node

~ /MySQL/bin/ndb_mgm-e shutdownrm ~ /MySQL-cluster/ndb_appsconfig.bin.1 # is not required. If config. INI is changed, add

After this operation, the management node and data node will stop the service.

Notes:

1: If you find that the ndbd process of one machine is disabled, and the ndbd process of the other machine is also disabled, You need to modify the noofreplicas parameter. 2 :. /ndbd -- Initial cannot be executed on all data nodes at the same time. If yes, all data will be deleted. 3: You can operate on mysqld node 4 like a non-cluster database: config. INI file. When you restart ndb_mgmd, You need to delete the ndb_assistconfig.bin.1 file under the mysql-cluster file, because it calls this file by default. 5: NDB clusters do not support automatic discovery of database functions, which is very important, once a world database and its tables are created on a data node, the create database world command must be issued on each SQL node in the cluster, followed by flush tables. In this way, the node can recognize the database and read its table definitions. (The database will also be automatically synchronized in this version of MySQL cluster 7.1.5.) 6. If the node server is started, check ~ Related log files in the/MySQL-cluster directory to obtain error information. 7. In the configuration file of the Management node, the order of Option values configured by [mysqld], [ndbd], and [ndb_mgmd] should be as follows: [mysqld] id = 4 hostname = 192.168.208.3id is followed by the hostname at the top. If the order is incorrect, when the SQL or data node connects to the management node, the management node cannot be correctly located on its corresponding node configuration. because the corresponding node configuration cannot be located, when no [empty node] exists, the client node starts (. /mysqld or. /ndbd) also reports: configuration error: Error: cocould not alloc node ID at 192.168.0.231 port 1186: No free node ID found for mysqld (API ). failed to initialize consumers 8: [empty node] indicates that node configurations without the hostname option are empty nodes. Empty nodes can be used to dynamically configure Dynamic IP nodes, generally, more than three empty nodes need to be reserved in the configuration file of the Management node, because a node needs to be connected during backup, as shown below: [mysqld] id = 6

Ndbcluster storage engine synchronization test, dynamic addition of data node configuration steps, recovery of backup updates... not complete to be continued

 

Please try again later. Please try again later.