MySQL cluster NDB (MySQL cluster)

I. MySQL cluster Overview

MySQL cluster is a highly practical and redundant MySQL version suitable for distributed computing environments. It uses the NDB Cluster Storage engine and allows multiple MySQL servers to run in one cluster. This storage engine is available in MySQL 5.0 and later binary versions, and in RPM compatible with the latest Linux version. (Note: To obtain the functions of MySQL cluster, you must install mysql-server and MySQL-cluster rpm ).

Currently, the operating systems that can run MySQL cluster include Linux, Mac OS X, and Solaris (some users have reported that MySQL cluster has been successfully run on FreeBSD, but MySQL AB does not officially support this feature ).

MySQL cluster is a distributed design designed to achieve zero point of failure. Therefore, any component should have its own memory and disk. This technology allows the deployment of "in memory" database clusters in a non-shared system. Any shared storage solutions such as network sharing, network file systems, and San devices are not recommended or supported. PassNo shared ArchitectureThe system can use inexpensive hardware without special requirements for hardware and software. With this redundant design, MySQL claims that the data availability can reach 99.999%.

 

MySQL cluster is composed of a group of computers, each of which runs a variety of processes, includingMySQL Server,NDB cluster data node,Manage serversAnd (possibly)Dedicated Data Access Program. For the relationship between these components in the cluster, see:

 

All these nodes form a complete MySQL cluster system. The data is stored in the storage engine of the "NDB storage server", and the table (structure) is saved in the "MySQL server. Applications access these data tables through the "MySQL Server", and the cluster management server manages the "NDB storage server" through the management tool (ndb_mgmd ".

Ii. Basic concepts of MySQL Cluster

NDB is a "in-memory" storage engine with high availability and good data consistency.

MySQL cluster can use a variety of failover and load balancing options to configure the NDB storage engine. The NDB storage engine of MySQL cluster contains the complete dataset, which depends only on other data in the cluster.

Currently, the cluster of the MySQL cluster can be configured independently of the MySQL server. In MySQL cluster, each part of the cluster is considered as one node.

• Manage (MGM) nodes: This type of node is used to manage other nodes in the MySQL cluster, such as providing configuration data, starting and stopping nodes, and running backups. Because these nodes are responsible for managing the configurations of other nodes, they should be started before other nodes are started. The MGM node is started with the command "ndb_mgmd ".
• Data Node: These nodes are used to save cluster data. The number of data nodes is related to the number of copies, which is a multiple of the segments. For example, if two copies have two segments, each of them has four data nodes. However, there is no need to set multiple copies. The data node is started with the "ndbd" command.
• SQL Node: This is the node used to access cluster data. For MySQL cluster, the client node is a traditional MySQL server that uses the NDB Cluster Storage engine. Generally, an SQL node is started with the command "mysqld-ndbcluster", or "ndbcluster" is added to "my. CNF" and then started with "mysqld.

Note: in many cases, the term "Node" is used for computers, but when discussing MySQL cluster, it represents a process. A single computer can have any number of nodes. Therefore, we use the term "cluster host ".

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 and requests to determine the location of the management server. When a new event occurs in the data node, the node transmits the information about the event to the management server, and then writes the information to the cluster log.

In addition, there can be any number of cluster client processes or applications. They are divided into two types:

• Standard MYSQL client: For MySQL clusters, they are no different from standard (non-cluster) MySQL. In other words, you can access the MySQL cluster from existing MySQL applications written in PHP, Perl, C, C ++, Java, Python, and Ruby.
• Management Client: This type of client is connected to the Management Server, and supports starting and stopping nodes, starting and stopping message tracing (debugging only) displays the node version and status, starts and stops the backup, and other commands.

 

Iii. NDB installation and configuration

Node (Purpose)	IP address (host name)
Management node (MGM)	192.168.0.55
SQL Node 1 (sql1)	192.168.0.56
SQL Node 2 (sql2)	192.168.0.57
Data Node 1 (ndbd1)	192.168.0.58
Data Node 2 (ndbd2)	192.168.0.59

 

 

1. Install MySQL Server(Skip this step if it has been installed)

$wget http://dev.mysql.com/get/Downloads/MySQL-5.1/mysql-5.1.59.tar.gz/from/http://mysql.cs.pu.edu.tw/
$tar -xzvf mysql-5.1.59.tar.gz
$cd mysql-5.1.59
$./configure --with-charset=utf8 --with-collation=utf8_bin --with-extra-charsets=gbk,gb2312,big5,utf8,binary,ascii 
                 --prefix=/usr/local/mysql  --with-plugins=partition,ndbcluster
$make 
$make install

The following error may occur when you execute the./configure command:

checking for termcap functions library... configure: error: No curses/termcap library found

Solve the problem as follows:

$ Wget http://ftp.gnu.org/pub/gnu/ncurses/ncurses-5.6.tar.gz
$ Tar-xzvf ncurses-5.6.tar.gz
$ Ncurses-5.6 CD
$./Configure -- prefix =/usr -- without-Debug -- With-shared
$ Make & make install
$/Etc/init. d/MySQL start # Start the MySQL server

2. Install MySQL Cluster

$wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.1/mysql-cluster-gpl-7.1.15a-linux-x86_64-glibc23.tar.gz/from/http://mysql.ntu.edu.tw/
$tar -xzvf mysql-cluster-gpl-7.1.15a-linux-x86_64-glibc23.tar.gz
$mv mysql-cluster-gpl-7.1.15a-linux-x86_64-glibc23/ /usr/local/mysql-cluster
$cd /usr/local/mysql-cluster
$groupadd mysql 
$useradd -g mysql mysql 
$passwd mysql 
$cd /usr/local/mysql-cluster
$chown -R mysql:mysql . 
$scripts/mysql_install_db --user=mysql 
$cp -rp bin/ndb* /usr/local/bin/

3. Configuration Management node (mgmd)

$ CP support-files/ndb-config-2-node.ini/etc/ndb_mgmd.ini
$ VI/etc/ndb_mgmd.ini
[Ndbd default]
Noofreplicas = 2
Maxnoofconcurrentoperations = 10000
Datamemory = 80 m
Indexmemory = 24 m
Timebetweenwatchdogcheck= 30000
Datadir =/usr/local/MySQL-cluster/Data
Maxnooforderedindexes = 512
# Set a management node Server
[Ndb_mgmd default]
Datadir =/usr/local/MySQL-cluster/data # directory for storing logs on MGM
[Ndb_mgmd]
Id = 1
Hostname = 192.168.0.55
# Set storage node server (NDB node)
[Ndbd]
Id = 2
Hostname = 192.168.0.56
Datadir =/usr/local/MySQL-cluster/Data
[Ndbd]
Id = 3
Hostname = 192.168.0.57
Datadir =/usr/local/MySQL-cluster/Data
# Setting an SQL node Server
[Mysqld]
Id = 5
Hostname = 192.168.0.58
[Mysqld] # second SQL Node
Id = 6
Hostname = 192.168.0.59
# Select an unused Port
[TCP default]
Portnumber = 63132

4. Configure SQL nodes

$vi /etc/my.cnf

Then write the following content:

[mysqld]
basedir         = /usr/local/mysql/
datadir         = /usr/local/mysql/data
user            = mysql
port            = 3306
socket          = /tmp/mysql.sock
ndbcluster
ndb-connectstring=192.168.0.55
[MYSQL_CLUSTER]
ndb-connectstring=192.168.0.55

5. Configure the storage node (NDB node)

$vi /etc/ndbd.cnf

Then write the following content:

[mysqld]
ndbcluster
DataDir=/usr/local/mysql-cluster/data
[MYSQL_CLUSTER]
ndb-connectstring=nodeid=192.168.0.55

4. Start MySQL Cluster

A reasonable sequence of startup is: first start the management node server, then start the storage node server, and finally start the SQL node server: 1. on the Management node server, run the following command to start the MGM node process:

$/Usr/local/bin/ndb_mgmd-F/etc/ndb_mgmd.ini -- configdir =/usr/local/MySQL-Cluster
$ Netstat-ntpl | grep NDB # check whether it is successful

The "-F" or "-- config-file" parameter must be used to tell the location of the ndb_mgm configuration file, which is in the same directory as ndb_mgmd by default.
2. On each storage node server, if the ndbd process is started for the first time, you must first execute the following command:

$/usr/local/bin/ndbd --initial --defaults-file=/etc/ndbd.cnf

Note that the "-- Initial" parameter should be used only when ndbd is started for the first time or when data is backed up/restored or ndbd is restarted after the configuration file changes. This parameter causes the node to delete any files created by an earlier ndbd instance for restoration, including log files for restoration.
3. Run the following command to start the SQL node Server:

$mysqld_safe --defaults-file=/etc/my.cnf &

Note: If you are using an earlier MySQL version, you may see the SQL node referenced as '[mysqld (API. This is an early usage and has been abandoned.

5. Disable MySQL Cluster

To disable cluster, you can simply enter the following command in shell on the machine where the MGM node is located:

$/usr/local/bin/ndb_mgm -e shutdown

Run the following command to disable the mysqld service of the SQL node:

$/usr/local/bin/mysqladmin -uroot shutdown

 

Vi. Database Operations

Compared with MySQL that does not use the cluster, there is no big difference in the way data is operated in the MySQL cluster. When performing such operations, remember two points:

1. Tables must be created with the engine = NDB or engine = ndbcluster option, or changed with the alter table option to use the NDB Cluster Storage engine to copy them within the cluster. If you use mysqldump output to import tables from an existing database, you can open the SQL script in the text editor and add this option to any table creation statement, alternatively, replace any existing engine (or type) Options with one of these options.
2. Remember that each NDB table must have a primary key. If you do not define a primary key when creating a table, the NDB Cluster Storage engine automatically generates an implicit primary key. (Note: This implicit key also occupies space, just like any other table index. The problem is not uncommon because there is not enough memory to hold these automatically created keys ).

 

References:

Http://ginge.iteye.com/blog/320205

Http://www.linuxidc.com/Linux/2010-04/25588p2.htm

Http://blog.itpub.net/post/41982/516838

Http://space.itpub.net/101629/viewspace-694325

Http://imysql.cn /? Q = node/96