Project advanced cluster environment construction (2) MySQL Cluster

Project advanced cluster environment construction (2) MySQL cluster the previous blog introduces the related concepts of the cluster. Today's blog introduces the related content of the MySQL cluster. 1. MySQL cluster introduction MySQL cluster technology provides the redundancy feature for MySQL data in a distributed system, enhancing security, so that a single MySQL server will not

Project advanced cluster environment construction (2) MySQL cluster the previous blog introduces the related concepts of the cluster. Today's blog introduces the related content of the MySQL cluster. 1. MySQL cluster introduction MySQL cluster technology provides the redundancy feature for MySQL data in a distributed system, enhancing security, so that a single MySQL server will not

Project advanced cluster environment construction (2) MySQL Cluster
In the previous blog, we introduced the related concepts of the Cluster. In today's blog, we will introduce the related content of the MySQL cluster.

1. MySQL cluster introduction MySQL cluster technology provides redundancy for MySQL data in a distributed system, enhancing security, so that a single MySQL Server failure will not have a huge negative effect on the system, the system stability is guaranteed.
A MySQL cluster requires a group of computers. The roles of each computer may be different. MySQL clusters have three types of nodes: Management node, data node, and SQL node. A computer in the cluster may be a set of two or three nodes. These three nodes are logically divided, so they do not have a one-to-one relationship with physical computers.

The management node (also known as the Management Server) is mainly responsible for managing data nodes and SQL nodes, as well as cluster configuration files and cluster log files. It monitors the working status of other nodes and can start, close, or restart a node. Other nodes retrieve configuration data from the Management node. When a new event occurs on the data node, the event information is sent to the management node and written to the cluster log.
Data nodes are used to store data.
The SQL node is the same as the MySQL server. You can use it to perform SQL operations.

Shows the relationship between three cluster nodes and applications:

2. MySQL cluster installation and configuration

Download MySQL cluster: http://dev.mysql.com/downloads/cluster/

Mysql-cluster-gpl-7.3.5-winx64.zip:
Http://cdn.mysql.com/Downloads/MySQL-Cluster-7.3/mysql-cluster-gpl-7.3.5-winx64.zip
Mysql-cluster-gpl-7.3.5-win32.zip:
Http://cdn.mysql.com/Downloads/MySQL-Cluster-7.3/mysql-cluster-gpl-7.3.5-win32.zip

1) MySQL Introduction

First, find three computers, or open three virtual machines, and deploy the management nodes on one machine. The other two machines are deployed with one data node and one SQL node. Here, we use two sub-servers as an example. One (IP Address: 192.168.24.16) is used to deploy management nodes, data nodes, and SQL nodes, and the other (IP Address: 192.168.24.43) is used to deploy data nodes and SQL nodes.
In practical applications, do not deploy the Management Nodes and data nodes on a single machine, because if the data node is down, the management node becomes unavailable, and the entire MySQL cluster becomes unavailable. Therefore, a MySQL cluster ideally has at least three servers, and the management nodes are placed on one server separately. Here we use two instances as an example to illustrate how to configure and start the three nodes.

2) copy the slave node of the Configuration Management node to C: \ mysql \ bin. Create the cluster-logs directory, config. ini file, and my. ini file under the directory C: \ mysql \ bin.
2. The content of the config. ini file is as follows:

[ndbd default] # Options affecting ndbd processes on all data nodes: # Number of replicasNoOfReplicas=2 DataDir=c:/mysqlcluster/datanode/mysql/bin/cluster-data     # Directory for each data node's data files                             # Memory allocated to data storage DataMemory=80M# Memory allocated to index storage    IndexMemory=18M                      # For DataMemory and IndexMemory, we have used the                   # default values.[ndb_mgmd] # Management process options:# Hostname or IP address of management node  HostName=192.168.24.16             # Directory for management node log files DataDir=C:/mysql/bin/cluster-logs[ndbd] # Options for data node "A":                                # (one [ndbd] section per data node) # Hostname or IP addressHostName=192.168.24.16        [ndbd] # Options for data node "B": # Hostname or IP addressHostName=192.168.24.43          [mysqld] # SQL node options: # Hostname or IP addressHostName=192.168.24.16     [mysqld] # SQL node options: # Hostname or IP addressHostName=192.168.24.43  

3. The content in my. ini is as follows:

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

3) configure data nodes

1. Create a folder C: \ mysqlcluster \ datanode \ mysql on the host whose IP address is 192.168.24.16, and then create the subdirectories bin and cluster-data in this folder, create a subdirectory under bin, also called cluster-data.
2. Copy all files in the data directory of the installation package to C: \ mysqlcluster \ datanode \ mysql \ cluster-data.
3. copy the ndbd.exe file in the bindirectory of the installation package to C: \ mysqlcluster \ datanode \ mysql \ bin, and create a new my. ini file in C: \ mysqlcluster \ datanode \ mysql. The file content is:

[mysql_cluster]# Optionsfor data node process:#location of management serverndb-connectstring=192.168.24.16   

Note: Because the configuration of the data node is the same, we can directly copy the folder C: \ mysqlcluster in host 192.168.24.16 to the c drive of host 192.168.24.43.

4) Configure SQL Node 1. Create a subdirectory sqlnode under C: \ mysqlcluster on host 192.168.24.16, and create a subdirectory mysql under C: \ mysqlcluster \ sqlnode, decompress the installation package file to the mysql directory and create my under C: \ mysqlcluster \ sqlnode \ mysql. INI file with the following content:

[mysqld] # Options for mysqld process: # run NDB storage engine ndbcluster                       # location of management server ndb-connectstring=192.168.24.16

2. Remove or rename the my-default.ini file to a my-default.ini.bak
Note: Because the SQL node configuration is the same, we can directly copy the C: \ mysqlcluster \ sqlnode folder in host 192.168.24.16 TO THE C drive of host 192.168.24.43.


3. Start MySQL Cluster 1) Introduction

Before starting a MySQL Cluster, you must ensure that the MySQL service on each SQL node can be successfully started and assign relevant permissions to each SQL node to ensure remote access. Start three nodes in sequence. When the three node services are started, you must first start the management node, then start the data node, and then start the SQL node.

2) Start the MySQL Service

1. Go to the C: \ mysqlcluster \ sqlnode \ mysql \ bin directory and use mysqld-install to install the MySQL service.

2. Run the net start mysql command to start the MySQL service.

3) Configure MySQL remote connection 1. Go to the C: \ mysqlcluster \ sqlnode \ mysql \ bin directory, use mysql-uroot-p, enter the password, and set the root user password.
Note: The password entered for the first time is set as the root password by default.
2. Switch to the mysql database and enter the following command to assign permissions for remote connection to the root user.

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;

4) Start the management Node

1. Open the command line window in host 192.168.24.16, switch to the C: \ mysql \ bin directory, and enter:

ndb_mgmd -f config.ini --configdir=C:\mysql\mysql-cluster

Press enter to start the management node service. There may be no prompts on the command line. You can open the C: \ mysql \ bin \ cluster-logs \ ndb_1_ter.log file to view the startup information.
Note: This command line window cannot be closed unless you want to stop the service.
2. You can also make it a service and enter the following in the command line:

ndb_mgmd --install=ndb_mgmd -f config.ini --configdir=C:\mysql\mysql-cluster

5) Start the data node

1. Open the new command line window on host 192.168.24.16, switch to C: \ mysqlcluster \ datanode \ mysql \ bin, and enter:

ndbd

2. You can also make it a service and enter the following in the command line:

ndbd --install=ndbd

3. Start the data node service in another data node, namely 192.168.24.43, in the same way.
4. Open the new command line window in host 192.168.24.16, switch to directory C: \ mysql \ bin, and enter:

ndb_mgm

Press enter, and then enter:

ALL STATUS

Press enter to view the connection information of the data node.

6) Start the SQL Node

1. Open the new command line window on host 192.168.24.16, switch to C: \ mysqlcluster \ sqlnode \ mysql \ bin, and enter:

mysqld --console

Press enter to start the SQL node.
2. You can also make it a service and enter:

mysqld -install mysql

3. Start the SQL node in another SQL node, namely 192.168.24.43, in the same way.
4. To view the SQL node startup status, open the new command line on host 192.168.1.10 and enter:

ndb_mgm

Press enter and enter:

SHOW

Press enter to view the connection status of the SQL node.

4. Test MySQL Cluster 1). Create a new database myDB on any SQL node, that is, host 192.168.24.16. You can see that other SQL nodes, namely 192.168.24.43, have created a MyDB database.
2) Use

create table student (id int(2)) engine=ndbcluster;

Create a new student table. You can see that other SQL nodes, namely 192.168.24.43, all created a student table in the myDB database.
3) after inserting a few pieces of data into the student table in myDB on any SQL node, that is, 192.168.24.16 on the host, we can see that other SQL nodes, namely 192.168.24.43, have all made relevant changes.
Note: Compared with MySQL that does not use a Cluster, there is no big difference in the way data is operated in the MySQL Cluster. When performing such operations, remember two points:
1. Tables must be created with the ENGINE = NDB or ENGINE = NDBCLUSTER option, or changed with the alter table option to use the NDB Cluster Storage ENGINE to copy them within the Cluster. If you use mysqldump output to import tables from an existing database, you can open the SQL script in the text editor and add this option to any table creation statement, alternatively, replace any existing ENGINE (or TYPE) Options with one of these options.
2. Remember that each NDB table must have a primary key. If you do not define a primary key when creating a table, the NDB Cluster Storage engine automatically generates an implicit primary key. (Note: This implicit key also occupies space, just like any other table index. The problem is not uncommon because there is not enough memory to hold these automatically created keys ).

So far, the establishment of the MySQL cluster has been explained, but there is still a defect in the simple setup. What are the defects? Next time, let's look forward to it!