MySQL Windows cluster

The http://blog.csdn.net/zhangking/article/details/5670070 MySQL cluster is a highly available, highly redundant version of MySQL suitable for distributed computing environments. It employs the NDB Cluster storage engine, which allows multiple MySQL servers to be run in 1 clusters. The storage engine is available in MySQL 5.0 and later in binary versions, as well as in RPM packages compatible with the latest Linux versions.

MySQL clustering is a technology that allows the Cluster of "in-memory" and "on-disk" databases to be deployed in a system that is not shared. With no shared architecture, the system can use inexpensive hardware and has 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. MySQL Cluster is made up of a group of computers, each running a variety of processes, 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 profiles and Cluster logs. Each node in the Cluster retrieves configuration data from the Management server. When a new event occurs 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 systems currently running MySQL Cluster are Linux, Mac OS X and Solaris, and the latest version already supports the Windows operating system.

MySQL cluster data nodes between the communication is not encrypted, and requires high-speed bandwidth, it is recommended to set up a cluster in a high-speed LAN, do not recommend cross-network segment, cross-public network deployment of this system system.

The MySQL cluster is divided into three nodes: the management node, the data node, and the SQL node.

Management node: It is mainly used to manage each node, which can be restarted, shut down, and started by command. It is also possible to monitor the working status of all nodes.
Data node: mainly for the storage of data, do not provide other services.
SQL node: mainly provides SQL functions to the outside, like a common MySQL Server.

The SQL node and the data node can be the same machine, meaning that the machine is the SQL node and the data node. They are just a division of the logical relationship, when actually deployed, even all stages can be on the same physical machine, but the configuration is more complex.

First, the software download machine operating environment

Configuring a MySQL cluster must use its cluster version, note the difference from the MySQL Server version. This article takes the MySQL cluster version 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 and adds the functionality of the cluster. As: Http://dev.mysql.com/downloads/cluster, select the Mysql-cluster-gpl-noinstall-7.1.3-win32.zip file, which is a for Windows 32-bit version, A binary version that is free of installation.

Depending on the number of bits of your operating system, you can also choose the 64-bit version. There is also a 27.2M version of Windows (x86, 32-bit) that is downloaded to be compiled by itself and experienced advanced users can choose.

This experiment is performed on 2 machines that have Windows Server 2003 (SP2) installed. Node assignment and IP configuration are as follows:

The first machine, IP 10.0.0.201, as the 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 of the node server because of the data node server, resulting in a crash of the entire cluster system.

Second, configure the management node:

On the first machine, build the folder D:/mysql-cluster, under which the file Config.ini, the contents are 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 limit has been relaxed and Cluster is able to automatically assign ports to data nodes based on free ports. If your version is below 5.0.22, please pay attention to this detail.

The Cluster management node is connected and manipulated as a service side (started by reading this profile by running the Db_mgmd.exe program), through the 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:/ The Mysql-cluster-gpl-noinstall-7.1.3-win32/bin is added to the Windows system path.

Open the DOS command-line window (after configuring the system path and opening a new command-line window again), execute the following statement to have MySQL run as a Windows service:

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 to 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:

#SQL群集节点
Ndbcluster
ndb-connectstring=10.0.0.201

With the Ndbcluster statement, the MySQL service will start as a clustered SQL node. Mysqld.exe command with parameter –ndbcluster run is the same effect.
The MySQL service connects to the 2202 default port of the data node, or another available port that is automatically assigned.
(Question: How does the SQL node get the IP address of the data node?) is the list of IP addresses for all data nodes to be obtained through the connection management node? ）
The value of the connection string here is used when the MySQL service starts and is used to connect to the management node.

Add a statement at the end:

#NDB集群节点
[Mysql_cluster]
ndb-connectstring=10.0.0.201

Note: It seems that the following forms are also available:
[NDBD]
ndb-connectstring=10.0.0.201

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

Five. Start the cluster servers

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

1. Start the Management node

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

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

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

Screen display:

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 the Data node

Run the command in a DOS window of 2 servers, respectively.

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

Ndbd.exe–initial

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

The data node relies on the Management node server, carries on the automatic replication and the synchronization of the data, keeps the data of each data node consistent, and recovers and rebuilds the data after a data node shuts down and resumes unexpectedly.

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 have been started.

VI. Cluster Management

Until all data nodes and SQL nodes are not started, the client ndb_mgm.exe that runs the cluster Management node service can only obtain the following information:

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 are properly started, the following information is available:

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>

Turn off the DOS command for the cluster:

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 the process) can result in data loss that is not fully written back to the disk.

Close the SQL node's mysqld service:

C:/>net stop MySQL, or:

C:/>mysqladmin-u Root shutdown

Seven, testing

The normal running 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 are faulted, recovery can occur. It is important to note that the SQL node must select the "Ndbcluster" database engine when it establishes the database. If you do not select the "Ndbcluster" engine, the established database will not enter the MySQL cluster system, but can be used independently.

In addition, each NDB table must have a primary key. If a primary key is not defined when the table is created, the NDB Cluster storage engine automatically generates an implied primary key. The implied key will also occupy space, just like any other table index. The problem is not uncommon because there is not enough memory to accommodate these auto-created keys.

Test Example:

Log in to MySQL on one of the SQL nodes, build table city, and insert the data:
C:/>mysql-u Root Test
Mysql>create Table City (nId mediumint unsigned NOT NULL auto_increment primary key, SName varchar (a) NOT null)
Engine = ndbcluster default CharSet UTF8;
Mysql>insert City VALUES (1, ' city-1′);
Mysql>insert City VALUES (1, ' city-2′);

Log in to MySQL on another SQL node and get a record from the table city:
C:/>mysql-u Root Test
Mysql>select * from the city;

In the event that the cluster system is working properly, you should be able to fetch all previously inserted records.

Additional tests (single point of failure testing):

1, you can also artificially 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, after a certain data node is stopped, the database operation. Then turn the data node back on to see if all the SQL nodes in the cluster can get the complete data.

Viii. FAQs and Extensions

Configuring and managing the MySQL cluster system is not an easy task. System problems caused by unreasonable configuration, such as network failures, memory limitations, and so on, can cause the quorum decency to forcibly shut down the data node.

If you have already installed MySQL Server on your machine, be sure to uninstall the original database system. Note the Mysql.exe program in a clustered system differs from the management Client of a non-clustered system.

It can also be combined with a load-balancing system to provide greater availability and data access efficiency.

There may be some unexpected failures in the cluster system due to unknown bugs or other reasons. So when choosing a version, it is recommended that you choose a version that has been approved by most people, not necessarily the latest version, and the latest version is often less stable. Of course, do not choose a version that is too old to have limited functionality.

MySQL Windows cluster (GO)