Configure MySQL Cluster in Windows

I. Preparations

First, you have to prepare the hardware facilities. Here, we have three machines in the cluster and the structure is as follows:

Management node (MGM) 172.16.0.162 (db1)

SQL Node 1 (SQL1) 172.16.0.161 (db2)

SQL Node 2 (SQL2) 172.16.0.202 (db3)

Data Node 1 (NDBD1) 172.16.0.161 (db4)

Data Node 2 (NDBD2) 172.16.0.202 (db4)

This hardware is done, and now we are engaged in Software

It is best to download Versions later than 7 because of good performance. The new features of version 7.2 are described as follows: the complex connection speed of adaptive query localization (AQL) is increased by more than 70 times. Of course, it is not clear whether this is the case I have not tested it.

　　Ii. Install software

Decompress the mysql-cluster-gpl-7.2.9-win32.zip package

Management node installation configuration.

The Management node must be installed on disk C and the Directory below (this is an error when running this node, that is, the corresponding directory cannot be found ). On the host whose IP address is 172.16.0.162

Generate c:/mysql/bin, C:/mysql-cluster (after the first startup, a file similar to ndb_1_config.bin.1 will be generated in this folder, as if to start the loaded configuration later)

And c:/mysql/bin/cluster-logs directory. Copy ndb_mgmd.exeand ndb_mgm.exe to the c:/mysql/bin directory of 172.16.0.162.

Generate two files, my. ini and config. ini, under c:/mysql/bin of 172.16.0.162.

The content of my. ini is:

[Plain] view plaincopyprint?

[Mysql_cluster]

# Options for management node process

Config-file = C:/mysql/bin/config. ini

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

Config. ini content: (Note: ID cannot start from 0, must be greater than 0)

[Html] view plaincopyprint?

[Ndbd default]

NoOfReplicas = 2

DataDir = D:/Program Files/mysqlcluster/datanode/mysql/bin/cluster-data

DataMemory = 80 M

IndexMemory = 18 M

[Mysqld default]

[NDB_MGMD DEFAULT]

[Tcp default]

[NDB_MGMD]

ID = 1

HostName = 172.16.0.162 # manage node servers

# Storage Engines

DataDir = C:/mysql/bin/cluster-logs

[NDBD]

ID = 2

HostName = 172.16.0.161 # IP address of MySQL cluster db1

# DataDir = D:/Program Files/mysqlcluster/datanode/mysql/bin/cluster-data # create

[NDBD]

ID = 3

HostName = 172.16.0.202 # IP address of MySQL cluster db2

# DataDir = D:/Program Files/mysqlcluster/datanode/mysql/bin/cluster-data # create

[MYSQLD]

ID = 4

HostName = 172.16.0.161

[MYSQLD]

ID = 5

HostName = 172.16.0.202

[Ndbd default] NoOfReplicas = 2 DataDir = D: /Program Files/mysqlcluster/datanode/mysql/bin/cluster-dataDataMemory = 80 MIndexMemory = 18 M [mysqld default] [NDB_MGMD DEFAULT] [tcp default] [NDB_MGMD] ID = 1 HostName = 172.16.0.162 # management node server # Storage EnginesDataDir = C: /mysql/bin/cluster-logs [NDBD] ID = 2 HostName = 172.16.0.161 # IP address of MySQL cluster db1 # DataDir = D: /Program Files/mysqlcluster/datanode/mysql/bin/cluster-data # create a [NDBD] ID = 3 HostName = 172.16.0.202 # MySQL cluster db2 IP address # DataDir = D: /Program Files/mysqlcluster/datanode/mysql/bin/cluster-data # create a [MYSQLD] ID = 4 HostName = 172.16.0.161 [MYSQLD] ID = 5 HostName = 172.16.0.202

Install and configure Data nodes

Generate D:/Program Files/mysqlcluster/datanode/mysql/bin, D:/Program Files/mysqlcluster/datanode/mysql/cluster-data,

D:/Program Files/mysqlcluster/datanode/mysql/bin/cluster-data. Copy ndbd.exe to the decompressed Folder/bin.

172.16.0.161 sub-D:/Program Files/mysqlcluster/datanode/mysql/bin directory,

And generate the my. ini file in the Directory D:/Program Files/mysqlcluster/datanode/mysql/bin. The file content is:

[Html] view plaincopyprint?

[Mysql_cluster]

# Options for data node process:

Ndb-connectstring = 172.16.0.162 # location of management server

[Mysql_cluster] # Options for data node process: ndb-connectstring = 172.16.0.162 # location of management server. Similarly, if the same configuration is configured on the 172.16.0.202 sub-host, it can be directly copied to the 172.16.0.202 sub-host.

Installation and configuration of SQL node

Generate the Directory D:/Program Files/mysqlcluster/sqlnode on the server with the IP address 172.16.0.161, and copy the downloaded folder to the Directory D:/ProgramFiles/mysqlcluster/sqlnode/mysql, generate my. INI file with the following content:

[Html] view plaincopyprint?

[Html] view plaincopyprint?

[Mysqld]

# Options for mysqld process: ndbcluster

[Mysqld] # Options for mysqld process: ndbcluster

[Html] view plaincopyprint?

# Run NDB storage engine

Ndb-connectstring = 172.16.0.154

# Location of management server

# Run NDB storage engine ndb-connectstring = 172.16.0.154 # Same as location of management server, copy the entire folder of D:/Program Files/mysqlcluster/sqlnode to the same directory of 172.16.0.202.

　3. Start the Cluster

Each node is started in order, including Management node, Data nodes, and SQL nodes.

A. Start the Management node and go to the command line under the 172.16.0.162 host, go to the c:/mysql/bin directory, and enter:

Ndb_mgmd-f config. ini

(

If the following error is reported: MySQL Cluster Management Server mysql-5.5.28 ndb-7.2.9

2013-05-03 10:13:10 [MgmtSrvr] INFO -- The default config directory 'C:/Prog

Ram Files/MySQL Server 5.5/mysql-cluster 'does not exist. Trying to create

It...

Failed to create directory 'C:/Program Files/MySQL Server 5.5/mysql-cluste

R', error: 3

10:13:10 [MgmtSrvr] ERROR -- cocould not create directory 'C:/Progra

M Files/MySQL Server 5.5/mysql-cluster '. Either create it manually or spec

Ify a different directory with -- configdir =

Create the following folder: C: \ Program Files \ MySQL Server 5.5

)

B. Start Data node

Go to the command line under the 172.16.0.161 host, go to the D:/Program Files/mysqlcluster/datanode/mysql/bin directory, and enter:

Ndbd -- connect-string = "nodeid2; host = 172.16.0.162: 1186"

Similarly, when the 172.16.0.202 sub-host is started, nodeid2 is based on the configuration file of the Management node.

The id in config. ini is determined. If the id is 2, it is nodeid2. It is not specified in the configuration file.

Id.

(Note) you can open a new command line in the Management node and go

C:/mysql/bin directory:

Ndb_mgm

Start ndb_mgm.exe and enter the following command:

ALL STATUS

Check whether the Data node connection is successful. You can continue to start after the connection is started normally.

SQLnode

C. Start SQL node

Go to the command line under 172.16.0.161 and go to D:/Program.

Files/mysqlcluster/sqlnode/mysql/bin directory, enter:

Mysqld -- console

Start the SQL node under 172.16.0.202 in the same way.

(Note): You can go to the c:/mysql/bin directory under the machine of the Management node.

Run the following command:

Ndb_mgm

Start ndb_mgm.exe and enter the following command:

SHOW

You can view the connection status of each node.

The correct display should be:

Iv. Test

(Note: when creating a table, you must add engine = ndbcluster default charset utf8; ndbcluster: indicates that the table can be operated as a data node; default charset: Indicates setting character sets)

C: \> mysql-u root test

Mysql> create table city (nId mediumint unsigned not null

Auto_increment primary key, sName varchar (20) not null)

Engine = ndbcluster default charset utf8;

Mysql> insert city values (1, 'city-1 ′);

Mysql> insert city values (1, 'city-2 ′);

Log on to mysql on another SQL node and obtain the record from the city table:

C: \> mysql-u root test

Mysql> select * from city;

When the cluster system works normally, all records previously inserted should be available. Remember to add ";" (semicolon) after the statement is complete!

Additional tests (single point of failure test ):

1. You can also stop a certain data node (ctrlw.cdisable doscommand ndbd.exe to stop the service) to see if all SQL nodes can work normally.

2. perform database operations after a data node is stopped. Enable the data node again to check whether all SQL nodes in the cluster can obtain complete data.