MySQL Cluster Tutorial-Basic Concepts of MySQL Cluster

1. The concept and difference between cluster and master-slave

Simple Application Server
USD1.00 New User Coupon
* Only 3,000 coupons available.
* Each new user can only get one coupon(except users from distributors).
* The coupon is valid for 30 days from the date of receipt.

1. Master-Slave:
The master-slave is to divide the database into a master and a slave, and then the master is responsible for writing operations, and the slave is responsible for reading data but cannot write data. You can complete the installation by directly using the mysql-community version in the installation process.


The master-slave setup process is very simple, I refer to the following website for the configuration process:
https://laravel-china.org/topics/3174/mysql-master-slave-replication-simple-configuration-simple-absolutely-can-be-used

2. Cluster:

MySQL cluster is a shared-nothing, distributed node architecture storage solution, its purpose is to provide fault tolerance and high performance.

Data update uses read-committed isolation to ensure the consistency of all node data, and uses two-phased commit to ensure that all nodes have the same data (if any write operation fails, update failure).

No shared peer nodes make the update operation on one server immediately visible on other servers. Propagating updates uses a complex communication mechanism that is dedicated to provide high throughput across the network.

Distribute the load through multiple MySQL servers, so as to maximize the program to achieve high performance, and ensure high availability and redundancy by storing data in different locations.

Topological structure diagram of the cluster:

As can be seen from the figure above, the cluster is mainly divided into three parts:

1) SQL node (SQL node-the figure below corresponds to mysqld): distributed database. Including own data and query center node data

2) Data node (Data node-ndbd): Cluster shared data (in memory)

3) Management Server (Management Server-ndb_mgmd): Management cluster SQL node, Data node

Cluster data storage process:

1) When the master executes the commit statement, the transaction is sent to the slave, and the slave starts to prepare for the commit of the transaction.

2) Each slave must prepare the transaction, and then send an OK (or ABORT) message to the master, indicating that the transaction is ready (or the transaction cannot be prepared).

3) The Master waits for all the slaves to send OK or ABORT messages. If the Master receives all the OK messages from all the slaves, it will send a commit message to all the slaves, telling them to submit the transaction; if the Master receives an ABORT message from any one of the slaves, It sends an ABORT message to all slaves, telling the slave to abort the transaction.

4) Each Slave waits for an OK or ABORT message from the Master. If the Slave receives a commit request, they will commit the transaction and send a confirmation to the Master that the transaction has been committed; if the Slave receives a cancellation request, they will undo all changes and release the resources they occupy, thereby aborting the transaction, and then send the Masterv Send confirmation that the transaction has been aborted.

5) After the Master receives the confirmation from all slaves, it will report that the transaction is submitted (or aborted), and then proceed to the next transaction.

Since synchronous replication requires a total of 4 message transfers, the data update speed of mysql cluster is slower than that of stand-alone mysql. Therefore, mysql cluster is required to run in a local area network above gigabit, the nodes can use dual network cards, and the node groups are directly connected.

Concept reference:
http://blog.csdn.net/wutian713/article/details/50682360

2, windows build mysql cluster
First, let’s sort out the final result:
There are currently two machines, 192.168.100.239 and 192.168.100.192, 192.168.100.239 is used as the management node, and both machines are used as data nodes and sql nodes to finally achieve the effect of a cluster. The construction process is given below:

1) Download mysql-cluster, the version I downloaded is mysql-cluster-gpl-7.4.9-winx64.zip, here is the download link for all versions on the official website:

Download links for all mysql products:
https://downloads.mysql.com/archives/

mysql-cluster download link:
https://downloads.mysql.com/archives/cluster/

2) Unzip the downloaded mysql cluster compression package to the C:\mysql directory of the management node 192.168.100.239:
Write picture description here

3) Create two folders, cluster-logs and config, in the C:\mysql\bin directory on the computer configuring the management node (192.168.100.239). cluster-logs is used to store log files. Two configuration files, my.ini and config.ini, are created in the config folder:

my.ini

[mysql_cluster]
# Options for management node process
config-file=C:/mysql/bin/config/config.ini

config.ini

[ndbd default]
# Options affecting ndbd processes on all data nodes:
NoOfReplicas=2 # Number of replicas
DataDir=C:/mysql/bin/cluster-data # Directory for each data node's data files
                                    # Forward slashes used in directory path,
                                    # rather than backslashes. This is correct;
                                    # see Important note in text

DataMemory=80M # Memory allocated to data storage
IndexMemory=18M # Memory allocated to index storage
                                    # For DataMemory and IndexMemory, we have used the
                                    # default values. Since the "world" database takes up
                                    # only about 500KB, this should be more than enough for
                                    # this example Cluster setup.

[ndb_mgmd]
# Management process options:
HostName=192.168.100.239 # Hostname or IP address of management node
DataDir=C:/mysql/bin/cluster-logs # Directory for management node log files

[ndbd]
# Options for data node "A":
HostName=192.168.100.192 # Hostname or IP address


[ndbd]
# Options for data node "B":
HostName=192.168.100.239 # Hostname or IP address




[mysqld]
# SQL node A options:
HostName=192.168.100.192 # Hostname or IP address

[mysqld]
# SQL node B options:
HostName=192.168.100.239 # Hostname or IP address

[mysqld]
[mysqld]
[mysqld]
[mysqld]
[mysqld]

3) Configure data nodes

Create a cluster-data folder in the C:\Mysql\Bin directory on the computer to store data: SQL nodes do not need any configuration, so far, the entire MySQL cluster is built.

Copy the C:\mysql file of the management node 192.168.100.239 computer to the C drive of the 192.168.100.192 computer

4) Start the mysql cluster (management node→data node→sql node)

a. Start the management node in the management node server (192.168.100.239)

c:\mysql\bin\ndb_mgmd.exe --configdir=c:\mysql\bin\config --config-file=c:\mysql\bin\config\config.ini --ndb-nodeid=1 --reload –Initial

Here a warning is thrown back when starting, because the management node and data node sql node in the configuration file are at the same IP address. This is not recommended by mysql, but because of the limited number of machines, this point is ignored~

b. Start the data nodes in 192.168.100.239 and 192.168.100.192 respectively

c:\mysql\bin\ndbd.exe --ndb-connectstring=192.168.22.238

ps: Pay attention here-the IP address behind -ndb-connectstring= is the address of the management node, not the IP address of the machine, so the running commands here are the same

c, start each sql node

c:\mysql\bin\mysqld.exe --ndbcluster --ndb-connectstring=192.168.100.239 --console

ps: Pay attention here-the IP address behind -ndb-connectstring= is the address of the management node, not the IP address of the machine, so the running commands here are the same

d. View the status of each node

Run in the cmd command: c:\mysql\bin\ndb_mgm, and then execute the show command, you can view the connection status of each node