Configure MySQL Cluster _ MySQL in Windows

Preface MySQL clusters have been used in the project recently, so I have studied them with my friends for two days. We will share with you the results below. Xiaobian has always felt that learning new things is not better understood than looking at pictures. So let's take a look at the architecture diagram of a mysql Cluster (from Baidu Encyclopedia)

I recently used a MySQL Cluster in the project, so I studied it with my friends for two days. We will share with you the results below.

Xiaobian has always felt that learning new things is not better understood than looking at pictures. So let's take a look at the architecture diagram of a mysql Cluster (from Baidu Encyclopedia-MySQL Cluster ):

A total of four layers are divided: Applications, SQL, Storage, and Management.
If your English is not taught by a PE instructor, you must have guessed the responsibilities of each layer:

--Applications mainly refer to Applications that need to connect to the database;
--Every mysqld in SQL is an SQL node. Applications need to connect to SQL nodes to store data. you can regard it as the door for data exchange between Applications and database clusters.
--Storage has the meaning of "warehouse", so data is stored in the data node (ndbd), and the data of each data node is consistent, is a set of the latest data
--Management is the Management node. one MySQL has only one Management node to manage other nodes.

To sum up, a MySQL Cluster contains three nodes (excluding Applications): Management nodes, data nodes, and SQL nodes.

1. download the cluster version of mysql

Download mysql-cluster-gpl-7.4.7-win32 or mysql-cluster-gpl-7.4.7-winx64

: Http://yunpan.cn/cd892RtysQ3Vk (extract code: 45c8)
MySQL official website: http://dev.mysql.com/downloads/cluster/

2. configure a MySQL Cluster

Three machines are required (virtual machines can be used for unconditioned users). one configuration management node is used. The other two servers are configured with one data node and one SQL node. (you can also use five computers, configure a node for each computer ):
Management Node: 192.168.25.50
Data node A: 192.168.25.49
Data node B: 192.168.25.48
SQL node A: 192.168.25.49
SQL node B: 192.168.25.48

1. first, extract the downloaded package to the C:/mysql directory of each computer:

Of course, it can also be stored in the directory of other disks.

2. configuration management node

Create two folders: cluster-logs and config under the C: \ Mysql \ Bin directory on the computer of the configuration management node (192.168.25.50. Cluster-logs is used to store log files. in the config folder, create two configuration files, my. ini and config. ini:

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.25.50 # 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.25.49 # Hostname or IP address[ndbd] # Options for data node "B":HostName=192.168.25.48 # Hostname or IP address[mysqld] # SQL node A options:HostName=192.168.25.49 # Hostname or IP address[mysqld] # SQL node B options:HostName=192.168.25.48 # Hostname or IP address

3. configure data nodes

Create a cluster-data folder in the C: \ Mysql \ Bin directory on the computer that configures the data node (192.168.25.48, 192.168.25.49) to store data:

No configuration is required for the SQL node. so far, the entire MySQL Cluster has been built.

3. start a MySQL Cluster

When starting a MySQL Cluster, there is a startup sequence: Start the management node, then start the data node, and then start the SQL node.

1. start the management node:

Run the following command in cmd:

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

2. start each data node:

Run the following command in cmd:

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

3. start each SQL node:

Run the following command in cmd:

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

Now the entire MySQL Cluster has been started.

4. view the status of each node:

Enable ndb_mgm.exe on the management node (192.168.25.50133) or directly run it in cmd.

c:\mysql\bin\ndb_mgm

Run the "show" command to view the connection status of each node:

This indicates that each node is connected normally. The following test data.

III. test the MySQL Cluster 1. create A database on SQL node A and insert data:

Run the C: \ mysql \ bin \ mysql.exe-u root-p command in cmd on the computer of SQL node A (192.168.25.49) to log on to mysql. when you need to enter the password, the password is blank by default (press enter ).

Create a database and insert data:
-- Create a database named "MySQL_Cluster_Test:

 create database MySQL_Cluster_Test;

-- Create table "T_User ":

use MySQL_Cluster_Test;create table T_User(Name varchar(32),Age int) engine=ndbcluster;

Note that engine = ndbcluster must be added after the table creation statement.

-- Insert data:

insert into T_User values('DannyHoo',26);

-- Query data:

select * from T_User;

2. you can also query data on SQL node B.

Similarly, run the C: \ mysql \ bin \ mysql.exe-u root-p command in cmd on the computer of SQL node B (192.168.25.48) to log on to mysql.

Run the show databases command to view the database created on SQL node;
Run use MySQL_Cluster_Test;
Select * from T_User;
You can query the data inserted in SQL node.

By now, the entire cluster is created and tested. If a data node goes down, it will not affect the operation of the entire cluster. do not worry if any data node is dead or even physically damaged, because the data saved by each data node is a complete copy of the data (when you operate the data, it automatically backs up the latest data to each data node for you ). You can test it. then, you can manually stop a data node and SQL node, and the other data node and SQL node will run normally. When you restart the stopped data nodes and SQL nodes, you will find that they are re-connected to the cluster, and the data on each data node is up-to-date.