MySQL Learning note 02MySQL cluster related concepts

1.1.1. MySQL cluster basic framework

This article describes the MySQL cluster refers to the MySQL cluster based on Ndbcluster . A MySQL cluster contains one or more Management node,Data node , and SQL Node .

Management Node Management Node: For the management of the cluster itself, run the NDB_MGMD service. You should start the Management node first, and then start the other nodes.

Data Node Data Node: Used to store the cluster, run the ndbmtd or ndbd service. The data node supports replication.

SQL nodes SQL node: Used to provide SQL Query service externally, run mysqld Service, Data tables in which the ndbcluster storage engine is used.

Ndbcluster:ndbcluster is A memory-based storage engine provided by MySQL that provides high availability and data persistence features. MySQL also offers other storage engines, including MyISAM and InnoDB , as well as memory and several others.

the relationship between the nodes of the MySQL cluster can be described using.

1.1.2. node grouping, data partitioning, and replication

(1) How MySQL Cluster 's data tables are distributed in data nodes:

the Data node in MySQL Cluster can be one or more node groups . A data table can be partitioned into one or more partitions (Partition). The data for each partition exists in multiple nodes in one node group, and the partitions in those nodes are replicated (replicas)to each other.

shows a data table divided into4a partitionP1,P2,P3,P4when each partition is in4a nodeN1,n2,n3,n4composed of2Group of nodesg1,g2in the distribution of the information. P1and theP2distributed inG1in whichP3and theP4distributed inG2the. Any one partition is stored at least at the same time2nodes in a single node.

(2) The advantages of this method based on the distribution of node grouping and data partitioning:

When a single point of failure occurs on any data node, the data stored by that node also has at least one copy in the other node. This avoids a single point of failure resulting in cluster failure.

1.1.3. Comparison of ndbcluster and InnoDB

The comparison of the two storage engines, ndbcluster and InnoDB , is shown in the following table:

This table data is derived from MySQL Cluster Official document https://dev.mysql.com/doc/index-cluster.html.

Characteristics	InnoDB	Ndbcluster
MySQL Server version	5.7	5.7
InnoDB version	5.7.20	5.7.20
Ndbcluster version	N/A	7.5.8/7.6.4
Data volume	64TB	128TB
Transaction ISOLATION LEVEL	All levels	READ COMMITTED
MVCC	Support	Not supported
Data compression	Support	Not supported
Large data rows (>14KB)	Varbinary,varchar, Blob,text	Blob,text
Copy	MySQL Replication: Asynchronous replication, semi-synchronous replication	Intra-cluster: automatic synchronous replication; Between clusters: Asynchronous replication (MySQL Replication).
Read operations scale out	MySQL Replication	Intra-cluster: automatic partitioning; Between clusters: replication.
Write operations scale -out (scaleout)	Not supported. Application-Level partitioning	Support. Intra-cluster: automatic partitioning.
High Availability (HA)	InnoDB Cluster	Ndbcluter 99.999%
Node failure recovery and failover	MySQL Group Replication	Ndbcluster Architecture provides support
Node Failure recovery time	>=30 sec	<=1 sec
Real-time performance	N/A	Support
Concurrent Write	Support	Up to one concurrent write
Replication Conflict Detection and resolution	Support	Support
Hash index	Not supported	Support
Add Nodes Online	MySQL Packet replication adds read-write nodes	Add any node

1.1.4. Limitations of Ndbcluster

Use The following limitations exist when ndbcluster the storage engine:

(1) Temporary data tables are not supported.

(2) cannot be a The TEXT and BLOG type columns are indexed.

(3) not supported Fulltext Full-text indexing.

(4) you should not have a hash index when using NULL, otherwise a full table scan is caused.

(5) prefix index is not supported.

(6) bit (BIT) column cannot be a primary key.

(7) The auto_increment column can only be used for primary keys.

(8) FOREIGN KEY restriction: If the referenced foreign key field is not a primary key, you must have UNIQUE Index.

(9) On UPDATE CASCADE: is not supported when the primary key of the primary table is a field in the main table.

(10) Transaction ISOLATION Level: Supports only READ COMMITTED Isolation level.

MySQL Learning note 02MySQL cluster related concepts