Shulou(Shulou.com)05/31 Report--

In order to solve the problem of how to build a cluster in mysql, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

MySQL cluster is a highly available, highly redundant version of MySQL suitable for distributed computing environments. It uses the NDB Cluster storage engine, which allows multiple MySQL servers to run in one cluster. The storage engine is available in binary versions of MySQL 5.0 and above, as well as in RPM packages compatible with the latest version of Linux.

MySQL clustering is a technology that allows Cluster for "in-memory" and "on-disk" databases to be deployed in systems that are not shared. Through the non-shared architecture, the system can use cheap hardware, and there are 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 consists 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 configuration files 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.

At present, the operating systems that can run MySQL Cluster are Linux, Mac OS X and Solaris, and the latest version already supports the Windows operating system.

The communication between the data nodes of the MySQL cluster is unencrypted and requires high-speed bandwidth, so it is recommended that the cluster be established in a high-speed local area network, not across network segments and public networks.

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

Management node: mainly used to manage each node, can restart, shut down, start and other operations on a node through commands. You can also monitor the working status of all nodes.

Data node: mainly stores the data, does not provide other services.

SQL node: mainly provides SQL function, similar to an ordinary MySQL Server.

The SQL node and the data node can be the same machine, that is, the machine is both the SQL node and the data node. They are just logical partitions, and in actual deployment, even all phases can be on the same physical machine, but the configuration is more complex.

Software download machine operating environment

The MySQL cluster must be configured with its cluster version, which is different from the MySQL Server version. This article takes the MySQL cluster version MySQL Cluster 7.1.3 under the Windows platform as an example (the highest version as of early June 2010), which is one of the MySQL Server 5.1 series versions and adds clustering features. Download address: http://dev.mysql.com/downloads/cluster, select

Mysql-cluster-gpl-noinstall-7.1.3-win32.zip file, which is a 32-bit, installation-free binary version of for Windows.

According to 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 needs to be compiled when downloaded, and can be used by experienced advanced users.

This lab was carried out on 2 machines with Windows Server 2003 (sp2) installed. The node assignment and IP configuration are as follows:

The first machine, IP 10.0.0.201, serves as the management node (MGM), SQL node 1 (SQL1), and data node 1 (NDBD1).

The second machine, IP 10.0.0.202, acts as SQL Node 2 (SQL2) and data Node 2 (NDBD2).

It is best not to deploy the management node on the same server as the data node, otherwise it may lead to the problem of the management node server because of the downtime of the data node server, resulting in the crash of the whole cluster system.

Second, configure the management node:

On the first machine, create a folder D:/mysql-cluster and a file config.ini under it, 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. Since MySQL 5.0.3, this limit has been relaxed, and Cluster can automatically assign ports to data nodes based on idle ports. If your version is less than 5.0.22, please pay attention to this detail.

The Cluster management node, as a server (started by running the db_mgmd.exe program to read this configuration file), connects and operates through the client ndb_mgm.exe on the local machine.

Configure the MySQL database server:

Operate in turn on the two machines.

Extract the mysql-cluster-gpl-noinstall-7.1.3-win32.zip file to the D:/mysql-cluster-gpl-noinstall-7.1.3-win32 folder and add D:/mysql-cluster-gpl-noinstall-7.1.3-win32/bin to Windows's system path.

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

Mysqld.exe-install mysql

Then in the Windows service management interface, configure the mysql service and start it manually (do not start automatically).

Copy some alternate configuration file (such as my-small.ini) under D:/mysql-cluster-gpl-noinstall-7.1.3-win32 as a my.ini file.

Create a D:/tmp folder.

4. Configure SQL nodes and data nodes:

Operate in turn on the two machines.

Create a folder D:/data.

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

# SQL Cluster Node

Ndbcluster

Ndb-connectstring=10.0.0.201

With the ndbcluster statement, the mysql service starts as the SQL node of the cluster. The mysqld.exe command runs with the argument-ndbcluster has the same effect.

The mysql service connects to the 2202 default port of the data node, or to another available port that is automatically assigned.

(question: how does a SQL node get the IP address of a data node? Do you connect to the management node to get a list of the IP addresses of all data nodes? )

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

Add a statement at the end:

# NDB Cluster Node

[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 to connect to the management node.

Fifth, start each server of the cluster

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:

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

Note: without the-configdir=d:/mysql-cluster parameter, it defaults to the C:/mysql/mysql-cluster folder.

The screen shows:

Cvv / > 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'dVOUGUM mysqlmurcluster.confAccess config.ini'

2010-06-10 01:16:57 [MgmtSrvr] INFO-Got initial configuration from'dVele started will try to set it when all ndb_mgmd (s) cluster

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 the DOS window of each of the two servers.

The first time, or when the cluster node is initialized, run the command:

Ndbd.exe-initial

After initialization, just run ndbd.exe. If you run with the parameter-initial, you will lose all the data of the data nodes in the normal running cluster system.

The data node relies on the management node server to automatically copy and synchronize the data so that the data of each data node is consistent, and the data is restored and rebuilt after a data node is shut down and restored unexpectedly.

3. Start the SQL node

With the ndbcluster statement, the mysql service is started, and the SQL node is started. It should be done on two servers after the first two nodes are started.

VI. Cluster management

Before all data nodes and SQL nodes are started, the client ndb_mgm.exe running the cluster management node service can only get the following information:

CRV / > 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 >

Indicates that neither the data node nor the SQL node is connected to the management node service.

After all the data nodes and SQL nodes start correctly, you will get the following information:

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 >

The DOS command to shut down the cluster:

Ndb_mgm-e shutdown

Or execute the shutdown command in the ndb_mgm environment.

The above command or turn off the management node service and all data nodes. Arbitrarily and forcibly shutting down the cluster system (shutting down or shutting down the process) will result in data loss when all data is not written back to disk.

Turn off the mysqld service for the SQL node:

CRAV / > net stop mysql, or:

CJV / > mysqladmin-u root shutdown

VII. Testing

In the normal operation of the MySQL cluster system, the SQL node can operate on the database of the data node, and each data node can synchronize the data automatically. After a data node is shut down, the use of the SQL node is not affected. Some data nodes can be restored after an error. It is important to note that when a SQL node builds a database, it must select the "ndbcluster" database engine. 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 the primary key is not defined when the table is created, the NDB Cluster storage engine automatically generates the implied primary key. The implied key will also take up space, just like any other table index. Problems are not uncommon because there is not enough memory to hold these automatically created keys.

Test example:

Log in to mysql on a SQL node, create a table city, and insert data:

CJV / > 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 in to mysql on another SQL node and get the record from the table city:

CJV / > mysql-u root test

Mysql > select * from city

When the cluster system is working properly, all previously inserted records should be available.

Additional tests (single point of failure testing):

1, you can also artificially stop a data node (Ctrl+C interrupts the DOS command ndbd.exe, stop the service) and see if all SQL nodes are working properly.

2, after a data node is stopped, the database operation is performed. Then reopen the data node to see if all the SQL nodes in the cluster can get the complete data.

VIII. Frequently asked questions and extensions

Configuring and managing MySQL cluster systems is not a simple task. System problems caused by unreasonable configuration are very common, such as network failures, memory limitations, etc., which will lead to the arbitration system to force the shutdown of data nodes.

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

It can also be combined with load balancing systems to provide stronger availability and data access efficiency.

Some unexpected failures of the cluster system may occur due to unknown Bug or other reasons. Therefore, it is recommended that when selecting a version, choose the version that has been recognized by most people, not necessarily the latest version, which is often unstable. Of course, do not choose a version that is too old and has limited functions.

This is the answer to the question about how to build a cluster in mysql. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.