Configuring the MySQL cluster in a Windows environment

Objective

MySQL cluster was recently used in the project, so it was studied with the small partners for two days. Let us share the results below.

Small series always feel that the learning of new things, there is no better than to see the picture of the way to understand. So first look at a MySQL cluster architecture diagram (from Baidu Encyclopedia-mysql Cluster):

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

-Applications primarily refers to applications that need to connect to a database;
-Each mysqld in SQL is a SQL node, applications needs to connect to the SQL node to store the data, and you can think of it as the gateway to the data exchange between the application and the DB cluster.
-Storage has a ' warehouse ' meaning, so the data is in the Data node (NDBD), and each data node data is consistent, is a complete set of the latest data
-Management is the management node, there is only one Management node in MySQL to manage the other nodes

In summary, a MySQL cluster consists of three nodes (not including applications): Management node, data node, SQL node.

First, 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/

Second, configure MySQL cluster

Three machines are required (a friend with no condition can consider using a virtual machine), one Configuration Management node: the other two each configure a data node and a SQL node (you can also configure a node with five computers, 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 unzip the download package to each computer's C:/mysql directory:

Of course, it is also possible to put it in a directory of other disks.

2. Configuration Management node

Cluster-logs and config Two folders are established under the C:\Mysql\Bin directory on the computer in the Configuration Management node (192.168.25.50). Cluster-logs is used to store log files and to establish My.ini and config.ini two configuration files in the Config folder:

My.ini

# 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 replicasDataDir=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 textdatamemory= theM# Memory allocated to data storageindexmemory= -M# Memory allocated to index storage                                    # for Datamemory and indexmemory, we have used the                                    # default values. Since the ' World ' database takes up                                    # only on 500KB, this should is more than enough for                                    # This example Cluster setup.[NDB_MGMD]# Management Process options:HostName=192.168.25.50              # Hostname or IP Address of management nodeDataDir=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 node

The Cluster-data folder is created under the C:\Mysql\Bin directory on the computer that configures the data node (192.168.25.48, 192.168.25.49) to hold the data:

The SQL node does not have any configuration, so the entire MySQL cluster is built.

Third, start the MySQL cluster

When starting a MySQL cluster, there is a boot order: Start the Management node, 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 started.

4. View the status of each node:

Open Ndb_mgm.exe on the same computer as the Management node (192.168.25.50), or run directly in cmd

c:\mysql\bin\ndb_mgm

You can view the connection status of each node by executing the show command:

This indicates that each node is connected properly. Test the data below.

Test MySQL cluster 1, set up the database in SQL Node A and insert the data:

Run the C:\mysql\bin\mysql.exe-u root-p command in cmd on SQL Node A on the computer (192.168.25.49) to log in to MySQL, and then when you need to enter a password, the password defaults to null (direct carriage return).

To 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 you must add engine=ndbcluster after the build statement

--Insert data:

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

-Query data:

select * from T_User;

2, in SQL Node B can also query to the data.

Also run the C:\mysql\bin\mysql.exe-u root-p command to log on to MySQL on the computer (192.168.25.48) of SQL Node B (cmd).

Execute show databases; command to view the newly created database in SQL Node A;
Execute use mysql_cluster_test;
SELECT * from T_user;
You can query the data that is inserted in SQL Node A.

Here, the entire cluster of building and testing is complete. If a data node goes down, it does not affect the entire cluster, and any data node dies or even physical damage is not to worry, because each data node holds the data is a complete copy of the data (when you manipulate the data, it has been automatically for you to back up the latest data to each data node). You can test it by manually stopping a data node and the SQL node, and another data node and SQL node will work as well. When you restart the stopped data node and the SQL node, you will find it reconnected to the cluster, and the data for each data node is up-to-date.

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

Configuring the MySQL cluster in a Windows environment