Configure the MySQL cluster (Cluster) tutorial under Windows Server 2003 _mysql

MySQL clustering is a highly available, highly redundant version of MySQL for distributed computing environments. It employs a NDB Cluster storage engine that allows multiple MySQL servers to be run in 1 clusters. The storage engine is available in the MySQL 5.0 and above binaries and in the RPM package compatible with the latest Linux version.

MySQL clustering is a technology that allows you to deploy the Cluster of the in-memory and disk databases on systems that are not shared. With no shared architecture, the system can use inexpensive hardware and no special requirements for hardware and software. In addition, because each component has its own memory and disk, there is no single point of failure. The MySQL Cluster consists of a group of computers running multiple processes on each computer, including MySQL servers, NDB Cluster data nodes, Management servers, and (possibly) specialized data access programs.

The Management Server (MGM node) is responsible for managing Cluster configuration files and Cluster logs. Each node in the Cluster retrieves configuration data from the Management server. When new events occur within the data node, the node transmits information about such events to the Management Server, and then writes such information to the Cluster log.

The operating system currently capable of running MySQL Cluster has Linux, Mac OS X, and Solaris, and the latest version already supports the Windows operating system.

The communication between data nodes of the MySQL cluster is unencrypted and requires high speed bandwidth, so it is recommended that the cluster be built on a high-speed LAN, and it is not recommended to deploy this system across the network segment and across the public network.

MySQL clusters are divided into three nodes: management nodes, data nodes, and SQL nodes.

Management node: mainly for the management of each node, can be ordered to a node to restart, shutdown, start and other operations. can also monitor the working status of all nodes.

Data nodes: Mainly for data storage, does not provide other services.
SQL node: mainly provides SQL functionality externally, similar to a common MySQL Server.

and the SQL node and data node can be the same machine, that is, this machine is the SQL node is also a data node. They are only logical divisions, and when they are actually deployed, even all stages can be on the same physical machine, but the configuration is more complex.

software Download machine operating environment

Configuring a MySQL cluster must use its cluster version, noting the difference from the MySQL Server version. This article takes the MySQL-cluster version of MySQL Cluster 7.1.3 under the Windows platform as an example (as of the highest version in early June 2010), which is one of the MySQL Server 5.1 series versions, adding functionality to the cluster. Download Address: http://dev.mysql.com/downloads/cluster, select Mysql-cluster-gpl-noinstall-7.1.3-win32.zip file, this is a for Windows 32-bit version, installation-free binary version.

Depending on the number of digits of your operating system, you can also select a 64-bit version. There is also a 27.2M of Windows (x86, 32-bit) version, download it to compile their own, experienced advanced users can choose.

This experiment is done on 2 machines that have Windows Server 2003 (SP2) installed. node allocation and IP configuration are as follows:

First machine, IP 10.0.0.201, as Management node (MGM), SQL Node 1 (SQL1), Data node 1 (NDBD1).
The second machine, IP 10.0.0.202, as SQL Node 2 (SQL2), Data Node 2 (NDBD2).

The management node is best not to be deployed on the same server as the data node, or it may cause problems with the Management node server due to the server of the data node, causing the entire cluster system to crash.

Second, configure the management node:

On the first machine, build the folder D:\mysql-cluster, and create a file config.ini under it, as follows:

Copy Code code as follows:

[NDBD DEFAULT]
Noofreplicas=1

[MYSQLD DEFAULT]

[NDB_MGMD DEFAULT]

[TCP DEFAULT]

# managment Server
[NDB_MGMD]
hostname=10.0.0.201

# Storage Engines
[NDBD]
hostname=10.0.0.201
Datadir= D:\data

[NDBD]
hostname=10.0.0.202
Datadir= D:\data

# SQL Engines
[MYSQLD]
hostname=10.0.0.201

[MYSQLD]
hostname=10.0.0.202

The default port for the Cluster management node is 1186, and the default port for the data node is 2202. Starting with MySQL 5.0.3, this restriction has been relaxed, Cluster can automatically allocate ports to data nodes based on idle ports. If your version is below 5.0.22, please pay attention to this detail.

The Cluster Management node acts as a server (starts by reading this profile by running the Db_mgmd.exe program) and connects and operates through client ndb_mgm.exe on this computer.

Third, configure the MySQL database server:

On 2 machines, operate separately.

Unzip the Mysql-cluster-gpl-noinstall-7.1.3-win32.zip file into the D:\mysql-cluster-gpl-noinstall-7.1.3-win32 folder and put the D:\ Mysql-cluster-gpl-noinstall-7.1.3-win32\bin is added to the Windows system path.

Open the DOS command line window (after you have configured the system path, open the new command Line window again), execute the following statement, and let MySQL run as a Windows service:

Copy Code code as follows:

Mysqld.exe-install MySQL

In the Windows service management interface, configure the MySQL service to start manually (do not start automatically).

Copy an alternate configuration file (for example, My-small.ini) under D:\mysql-cluster-gpl-noinstall-7.1.3-win32 as a My.ini file.

Create the D:\tmp folder.

Iv. Configuring SQL Nodes and Data nodes:

On 2 machines, operate separately.

Create a folder D:\data.

Edit the Nysql configuration file D:\mysql-cluster-gpl-noinstall-7.1.3-win32\my.ini and add the statement at the end of the [mysqld] section:

Copy Code code as follows:

#SQL Cluster node
Ndbcluster
ndb-connectstring=10.0.0.201

With the Ndbcluster statement, the MySQL service is started as a clustered SQL node. Mysqld.exe command with Parameters –ndbcluster Run is the same effect.
The MySQL service connects to the 2202 default port of the data node, or to an automatically allocated other available port.
(Question: How does the SQL node obtain the IP address of the data node?) Do you get a list of IP addresses for all data nodes by connecting to the management node? ）
The value of the connection string here is used when the MySQL service is started and is used to connect to the management node.

Add a statement at the end:

Copy Code code as follows:

#NDB Cluster node
[Mysql_cluster]
ndb-connectstring=10.0.0.201

Note: It seems that the following forms can also be:

Copy Code code as follows:

[NDBD]
ndb-connectstring=10.0.0.201

The value of the connection string here is used when the data node is started and is used to connect to the management node.

Five, start the cluster servers

The startup order is: Management node, data node, SQL node.

1, start the management node

In the DOS window of the first server, run the command:

Copy Code code as follows:

C:\>ndb_mgmd.exe-f D:\mysql-cluster.conf\config.ini–configdir=d:\mysql-cluster

Note: If you do not take the –configdir=d:\mysql-cluster parameter, the default is the C:\mysql\mysql-cluster folder.

Screen display:

Copy Code code as follows:

C:\>ndb_mgmd.exe-f D:\mysql-cluster.conf\config.ini–configdir=d:\mysql-cluster
2010-06-10 01:16:57 [mgmtsrvr] info-ndb Cluster Management Server. mysql-5.1.44 ndb-7.1.3
2010-06-10 01:16:57 [MGMTSRVR] info-reading cluster configuration from ' D:\mysql-cluster.conf\config.ini '
2010-06-10 01:16:57 [MGMTSRVR] info-got initial configuration from ' D:\mysql-cluster.conf\config.ini ', would try to s Et it when all NDB_MGMD (s) started
2010-06-10 01:16:57 [mgmtsrvr] info-mgmt server State:nodeid 1 reserved for IP 10.0.0.201, m_reserved_nodes 1.
2010-06-10 01:16:57 [Mgmtsrvr] info-id:1, Command port: *:1186
==initial==
2010-06-10 01:16:57 [MGMTSRVR] info-starting Initial configuration change
2010-06-10 01:16:57 [Mgmtsrvr] info-configuration 1 commited
2010-06-10 01:16:57 [mgmtsrvr] info-config Change completed! New Generation:1
==confirmed==

2, Start Data node

Run the command on the 2 server's DOS window, respectively.

The first time, or when initializing a cluster node, run the command:

Copy Code code as follows:

Ndbd.exe–initial

After initialization, only the Ndbd.exe can be run. Running with the parameter –initial will cause the data node data to be lost in the normal running cluster system.

The data node relies on the Management node server to automate the replication and synchronization of data, keep the data of each data node consistent, and restore the data after a data node is unexpectedly closed and restored.

3, start the SQL node

With the Ndbcluster statement, starting the MySQL service, the SQL node is started. It should be done on 2 servers after the first 2 nodes are started.

VI. Cluster Management

Before all data nodes and SQL nodes are started, the client running the cluster Management node service is Ndb_mgm.exe and can only obtain the following information:

Copy Code code as follows:

C:\>ndb_mgm.exe
-ndb cluster-management client–
Ndb_mgm> Show
Cluster Configuration
———————
[NDBD (NDB)] 2 node (s)
id=2 (not connected, accepting connect from 10.0.0.201)
Id=3 (not connected, accepting connect from 10.0.0.202)

[NDB_MGMD (MGM)] 1 node (s)
Id=1 @10.0.0.201 (mysql-5.1.44 ndb-7.1.3)

[Mysqld (API)] 2 node (s)
Id=4 (not connected, accepting connect from 10.0.0.201)
Id=5 (not connected, accepting connect from 10.0.0.202)

Ndb_mgm>

Description data nodes, SQL nodes are not connected to the Management node service.

After all data nodes and SQL nodes have started correctly, the following information is available:

Copy Code code as follows:

Ndb_mgm> Show
Cluster Configuration
———————
[NDBD (NDB)] 2 node (s)
id=2 @10.0.0.201 (mysql-5.1.44 ndb-7.1.3, nodegroup:0, Master)
Id=3 @10.0.0.202 (mysql-5.1.44 ndb-7.1.3, nodegroup:1)

[NDB_MGMD (MGM)] 1 node (s)
Id=1 @10.0.0.201 (mysql-5.1.44 ndb-7.1.3)

[Mysqld (API)] 2 node (s)
Id=4 @10.0.0.201 (mysql-5.1.44 ndb-7.1.3)
Id=5 @10.0.0.202 (mysql-5.1.44 ndb-7.1.3)

Ndb_mgm>

To turn off the cluster DOS command:

Copy Code code as follows:

NDB_MGM-E shutdown

or execute the shutdown command in a NDB_MGM environment.

The above command or close the Management node service and all data nodes. Arbitrarily, forcibly shutting down the cluster system (shutting down or shutting down processes) can result in data loss due to data not being fully written back to disk.

To turn off the mysqld service for SQL nodes:

Copy Code code as follows:

C:\>net stop MySQL, or:

C:\>mysqladmin-u Root shutdown

VII. Testing

The normal operation MySQL cluster system, through the SQL node can carry on the database operation to the data node, each data node can automatically carry on the data synchronization. When a data node is closed, the use of the SQL node is not affected. After some data nodes have failed, you can recover. It is important to note that when the SQL node establishes the database, it must select the "Ndbcluster" Database engine. If you do not select the "Ndbcluster" engine, the established database will not go into the MySQL cluster system, but it can be used independently.

In addition, each NDB table must have a primary key. If you do not define a primary key when you create a table, the NDB Cluster storage engine automatically generates an implied primary key. The implied key also takes up space, just like any other table index. Because there is not enough memory to accommodate these automatically created keys, it is not uncommon for problems to occur.

Test instance:

Log on to MySQL on a SQL node, build table city, and insert data:

Copy Code code as follows:

C:\>mysql-u Root Test
Mysql>create Table City (nId mediumint unsigned NOT NULL auto_increment key, primary sname () 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 get records from Table city:
C:\>mysql-u Root Test
Mysql>select * from city;

When the cluster system is working properly, you should be able to take all the records that were previously inserted.

Additional tests (single point of failure test):

1, you can also stop a data node (CTRL + C interrupt DOS command Ndbd.exe, stop the service), to see if all the SQL node is working properly.
2, the database operation is performed after a data node is stopped. Then reopen the data node to see if all the SQL nodes in the cluster can get the full data.

Viii. FAQ and Extension

Configuring and managing a MySQL cluster system is not an easy task. The system problems caused by unreasonable configuration are very common, such as network failure, memory limit, etc., which can cause the quorum decency to force the data node to close.

If the MySQL Server is already installed on the machine, be sure to uninstall the original database system. Note that the Mysql.exe program in the cluster system is different from the management Client of the non-clustered system.

It can also be combined with load-balancing systems to provide greater availability and data-access efficiencies.

There may be some unexpected failure of the cluster system due to an unknown bug or other cause. So when choosing a version, choose a version that has been approved by most people, not necessarily the latest version, and the latest version is often unstable. Of course, do not use too old limited version of the feature.