Cluster Technology (II) MySQL cluster introduction and configuration detailed

When? Why? With MySQL cluster?

Reduce data center node pressure and large data processing (read/write separation), using MySQL distribution, one or more application corresponding to a MySQL database. Make shared data from several MySQL databases, such as shopping carts, user objects, etc., in the presence of data nodes. Other data that is not shared are also maintained in the MySQL database itself, which is distributed separately.

  cluster name concept in MySQL

a MySQL cluster requires a set of computers, and the roles of each computer may be different. There are three types of nodes in the MySQL cluster: management nodes, data nodes, and SQL nodes. a computer in a cluster may be a node or a collection of two or three nodes. These three nodes are logically divided, so they do not necessarily correspond to the physical computer one by one.

Management nodes (also known as Management Servers) are primarily responsible for managing data nodes and SQL nodes, as well as cluster configuration files and cluster log files. It monitors the working state of other nodes and is capable of starting, shutting down, or restarting a node. Other nodes retrieve configuration data from the management node, sending event information to the management node and writing to the cluster log when a new event occurs on the data node.

         data nodes are used to store data.

        sql node is the same as the normal MySQL server, we can do SQL operation through it.

Detailed configuration is not explained on the Web many reference blogs

Note: With no use Cluster of the MySQL compared to the MySQL Cluster there is not much difference in the way data is manipulated. There are two points to remember when performing this type of operation:

1. Tables must be created with the ENGINE=NDB or Engine=ndbcluster option, or changed with the ALTER TABLE option to replicate them within cluster using the Ndbcluster storage engine. If you import a table from an existing database using the output of mysqldump, you can open the SQL script in a text editor and add the option to any table creation statement, or replace any existing engine (or type) options with one of these options.

2. Also keep in mind that each NDB table must have a primary key. If the user does not define a primary key when the table is created, the Ndbcluster storage engine automatically generates an implied primary key. (Note: The implied key also takes up space, just like any other table index.) The problem is not uncommon because there is not enough memory to accommodate these auto-created keys.

Of course, there is only one Management node in the MySQL cluster architecture that we build, so the cluster can be represented by the structure shown below.

       MySQL cluster exists when the only management node due to network, power outage, After a variety of reasons such as excessive pressure (in the cluster to remember the single point of failure is worth noting), the Data node and SQL node will be fighting each other, become disunity. This is a low-availability MySQL cluster, so we want to change the single-management node cluster into a multi-management node cluster as shown.

       

in the multi-management node MySQL cluster, because the management node exists multiple, so when one of the management node in the outage, the other management node automatically take the MySQL cluster management permissions, to ensure the normal operation of MySQL cluster.

Summarize

Allocate loads with multiple MySQL servers to maximize performance and ensure high availability and redundancy by storing data in different locations.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Cluster Technology (II) MySQL cluster introduction and configuration detailed