MySQL Cluster, mysqlcluster

MySQL Cluster, mysqlcluster

A Cluster is a group of "nodes". A node is a logical concept. A computer can represent a node or multiple nodes. These nodes have different functions, some nodes are used to store data (data nodes), store data table structures (SQL nodes), and some are used to manage (manage nodes) other nodes. These nodes are combined, provides highly available, high-performance, and Scalable Cluster data management for applications.
MySQL uses the NDB storage engine to store data nodes. In theory, MySQL Cluster satisfies large-scale applications through distributed data storage and scalable system architecture, and greatly improves system reliability and data effectiveness through redundancy policies.

1. MySQL Cluster architecture

MySQL Cluster is divided into three parts by Node Type

Management Node

It is used to manage other nodes. In actual operations, it is called config. ini configuration file maintenance and play a management role, this file can be used to configure the number of copies to be maintained, the amount of memory to be allocated to the data and index on each data node, and the location of the data node, the disk location and SQL node location of the stored data on each data node. Only one management node can be configured.

SQL Node

It can be understood as a bridge between applications and data nodes. applications cannot directly access data nodes. They can only access SQL nodes first, and then SQL nodes can access data nodes to return data, the cluster can have multiple SQL nodes. Generally, the more SQL nodes are allocated to each node, the smaller the load.

Data Node

It is used to store data in the cluster. There can be multiple data nodes, and each data node can have multiple image nodes. If the image node is normal, the cluster can run normally.

MySQL Cluster access process:

The front-end application of a certain load balancing algorithm will distribute access to the database to different SQL nodes, then the SQL node to access data nodes and return results from the data node, finally, the SQL node returns the result to the foreground application. Management Nodes are not involved in the access process. They are only used to manage the configuration of SQL nodes and data nodes.

 

1.1 MySQL Cluster configuration

Management node configuration, Example

1. Create the mysql-cluster folder under/home/zzx2/on the server 192.168.7.187 and create the configuration file config. ini.

mkdir /home/zzx2/mysql-clustercd /home/zzx2/mysql-clustertouch config.ini

The configuration of config. ini is as follows:

[Ndbd default] NoOfReplicas = 1 # image DataMemory = 500 M for each data node # Memory allocated to data in each data node IndexMemory = 300 M # Memory allocated to indexes by each data node [tcp default] portnumber = 2202 # DEFAULT connection port of the data node [NDB_MGMD] id = 1 hostname = 192.168.7.187datadir =/home/zzx2/mysql-cluster [NDBD] id = 2 hostname = 192.168.7.187datadir =/home/zzx2/mysql/data [NDBD] id = 3 hostname = 192.168.7.55datadir =/home/zzx2/mysql/data [MYSQLD] hostname = 192.168.7.187 [MYSQLD] hostname = 192.168.7.55 [MYSQLD] # Options for mysqld process;

 

[NDB_MGMD] indicates the management node configuration. Only one
[Ndbd default] indicates the DEFAULT configuration of each data node, with only one
[NDBD] configuration of each data node, with multiple
[MYSQLD] indicates the SQL configuration. There are multiple IP addresses, respectively.

 

SQL node configuration

In the MySQL configuration file, add

# Options for mysqld process: [MYSQLD] ndbcluster # NDB-connectstring = 192.168.7.187 # manage nodes # Options for ndbd procsess: [MYSQL_CLUSTER] ndb-connectstring = 192.168.7.187

 

 

2. Start with Cluster2.1.Cluster

Node startup sequence: Management node-data node-SQL Node

Ndb_mgmd is the management server of the cluster.

Ndbd process: a process that uses the NDB storage engine to process table data. With this process, the storage node can implement distributed transaction management, node recovery, and online backup.

Ndb_mgm tool: After all nodes are successfully started, you can run the show command to view the cluster status.

 

2.2.Cluster Test

Test the NDB storage engine

Cluster can only use NDB storage engine tables,

Create a test table in the test database of any SQL node and set the storage engine to NDB.

Query the test table on another SQL Node

Change the storage engine of the test table to MyISAM and insert it here

An error occurred while querying again.

 

Spof Test

Any node may have a single point of failure. In Cluster settings, we should try to set redundancy for each type of nodes. For management nodes, special configuration is not required. You only need to place the management tools and configuration files on multiple hosts.

SQL node faults do not cause data query faults

The impact of data node faults depends on whether the data is stored in mutual images or data is divided into several pieces of storage.

 

2.3.disable Cluster

Close command: ndb_mgm-e shutdown

Note that the MySQL service does not stop after the SQL node is disabled.

 

3. Maintain Cluster3.1. Data Backup

Method 1: Use the mysqldump tool,

Method 2: physical backup method of the cluster, start the Management Server (ndb_mgm), and run "start backup ",

The backup data is stored under each data node,

For backup of large data volumes, MySQL cluster provides several backup parameters for adjustment:

Backdatabuffersize: The amount of memory used to buffer data before writing data to the disk.
Backlogbuffersize: The amount of memory that is buffered before logs are written to the disk.
Backupmemory: total memory allocated to the database node for backup only.
 

3. 2. Data Recovery

The ndb_restore tool must be used to recover the data of the cluster backed up by "start backup". The shell command is as follows:

ndb_restore -b 3 -n 2 -c host=192.168.7.187:1186 -m -r /home/zzx2/mysql/data/backup/backup-3

 

Meanings of command line parameters:

Parameters	Description
-B	Backup id
-N	Node id
-M	Restore table definition
-R	Recovery path
-C	Cluster Manager connection string

 

 

 

 

 

 

3. Log Management

MySQL provides two types of logs: Cluster logs and node logs. The former provides logs generated by all Cluster nodes, and the latter only records local events of data nodes.

You can use the ndb_mgm client tool to open or close logs.

Run the ndb_mgm command in shell, run the clusterlog info command to view the current log status, and run the terlog off command to check which clusterlog on is used to open the current log.

The Cluster has many log types and can be filtered according to the following categories:

1. Category (Category), which can be startup, shutdown, checkpoint, noderestart, connection, error, etc.
2. Prior (priority), with 1 ~ 15 indicates that 1 is the most important. Each category has a default priority threshold, which is recorded when the threshold is reached.
3. Severity Level (Severity Level), which can make one of the following values: alert, critical, error, warning, info or debug

The ndb_mgm tool can be used for filtering.