Mysql-cluster Cluster principle Introduction and construction steps (four Data/sql nodes) (RPM)

MySQL Cluster overview


The MySQL cluster is a technique that allows clusters of "in-memory" 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.


The MySQL cluster integrates the standard MySQL server with the "in-memory" clustered storage engine called NDB. In our documentation, the term NDB refers to the settings section associated with the storage engine, while the term "MySQL cluster" refers to a combination of MySQL and NDB storage engines.

The 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. For a relationship between these components in a cluster, see:

All of these programs together form the MySQL cluster. When you save data to the NDB cluster storage engine, the table is saved within the data node. These tables can be accessed directly from all other MySQL servers in the cluster. Therefore, in a payroll application that saves data in a cluster, if an application updates the Payroll for 1 employees, all other MySQL servers that query that data can immediately see the change.


For MySQL clusters, the data stored in the data node can be mapped, the cluster can handle the failure of the individual data nodes, except that a few transactions will be discarded due to the loss of the transaction state, without any other impact. Because transactional applications can handle transaction failures, it is not the source of the problem.


By introducing MySQL clusters into the open-source world, MySQL provides high-availability, high-performance, and scalable cluster data management for all those who need it.


Basic concepts of MySQL Cluster


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


The NDB storage engine can be configured with multiple failover and load balancing options, but it is easiest to start with a storage engine at the cluster level. The NDB storage engine for the MySQL cluster contains the complete data set, depending on the other data within the cluster itself.


Below, we describe how to set up a MySQL cluster consisting of a NDB storage engine and some MySQL servers.


Currently, the cluster portion of the MySQL cluster can be configured independently of the MySQL server. In a MySQL cluster, each part of a cluster is considered to be 1 nodes.


Note: In many cases, the term "node" is used to refer to a computer, but when discussing a MySQL cluster, it represents a process. There can be any number of nodes on a single computer, so we use the term cluster host.


There are three types of cluster nodes, in the lowest MySQL cluster configuration, there are at least three nodes, these three types of nodes are:


Management (MGM) node: the role of such nodes is to manage other nodes within the MySQL cluster, such as providing configuration data, starting and stopping nodes, running backups, and so on. Because such nodes are responsible for managing the configuration of other nodes, they should be started first before other nodes are started. The MGM node is started with the command NDB_MGMD.


Data node: This type of node is used to hold the data of the cluster. The number of data nodes is related to the number of replicas, which is a multiple of the fragment. For example, for two replicas with two fragments per copy, there are 4 data nodes. There is no need to have more than one copy. The data node is started with the command ndbd.


SQL node: This is the node that is used to access the cluster data. For MySQL clusters, the client node is a traditional MySQL server using the NDB cluster storage engine. Typically, SQL nodes are started using the command Mysqld–ndbcluster, or Ndbcluster are added to MY.CNF after you use MYSQLD.


Cluster configurations include the configuration of individual nodes in a cluster, and the setting of separate communication links between nodes. The intent of the currently designed MySQL cluster is that the storage node is homogeneous from the processor's capabilities, memory space, and bandwidth, and that, in order to provide a single configuration point, all configuration data for the cluster is located in 1 configuration files as a whole.


The Management Server (MGM node) is responsible for managing the cluster configuration files and cluster logs. Each node in the cluster retrieves configuration data from the Management Server and requests a way to determine where the Management Server is located. When interesting 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.


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


Standard MySQL client: for MySQL clusters, they are no different from standard (non-clustered) MySQL. In other words, MySQL clusters can be accessed from existing MySQL applications written in PHP, Perl, C, C + +, Java, Python, Ruby, and so on.


Management client: This type of client is connected to the Management Server and provides commands to gracefully start and stop nodes, start and stop message tracking (only for debug builds), display node versions and status, start and stop backups, and so on.

-----------------------------------------------------------------------------------The above section is excerpted from official documents

Build SQL Node (4), Data node (4), manage node (1) Steps


Environment Introduction

1. Manage node: 10.10.54.154
2. Sql/data node: 10.10.54.154/155/156/157

Compiling and installing cmake2.8

[154/155/156/157]

1. Download Mysql-cluster cmake
shell> wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.2/mysql-cluster-gpl-7.2.15-linux2.6-x86_64.tar.gz
shell> wget http://www.cmake.org/files/v2.8/cmake-2.8.12.2.tar.gz

2. Compile and install cmake2.8---(154/155/156/157)
Shell> Tar xvf cmake-2.8.12.2.tar.gz
Shell> CD cmake-2.8.12.2
Shell>./configure
Shell> make
shell> make Install

3. Compile and install Mysql-cluster---(154/155/156/157)
Shell> Tar xvf mysql-cluster-gpl-7.2.15.tar.gz
Shell> CD mysql-cluster-gpl-7.2.15
Shell> cmake-dcmake_install_prefix=/usr/local/mysql \
-dmysql_unix_addr=/usr/local/mysql/tmp/mysql-cluster.sock \
-ddefault_charset=utf8 \
-DDEFAULT_COLLATION=UTF8_GENERAL_CI \
-dextra_charsets=all \
-dwith_embedded_server=0 \
-dwith_ndb_java=off \
-dwith_myisam_storage_engine=1 \
-dwith_innobase_storage_engine=1 \
-dwith_memory_storage_engine=1 \
-dwith_blackhole_storage_engine=1 \
-dwith_federated_storage_engine=1 \
-dwith_partition_storage_engine=1 \
-dwith_ndbcluster_storage_engine=1 \
-denabled_local_infile=1 \
-dmysql_user=mysql \
-dwith_debug=0 \
-dwith_ssl=yes

4.shell> make && make install

Mysql-cluster Configuration---(154/155/156/157)


Shell> mkdir/data/ndb


Shell> cd/home/mysql-cluster-gpl-7.2.15


shell> CP Support-files/mysql.server/etc/init.d/mysqld


shell> CP support-files/my-large.cnf/etc/my.cnf


Shell> Chown Mysql.mysql/usr/local/mysql-r


shell> chmod 755/usr/local/mysql/scripts/*


shell> chmod 755/etc/init.d/mysqld


Shell> sed-i "/

mysqld

/a Datadir=/data/ndb "/etc/my.cnf


shell>/usr/local/mysql/scripts/mysql_install_db--user=root--basedir=/usr/local/mysql--datadir=/data/ndb


Shell> chown Mysql:mysql-r/data/ndb






DATA/SQL node Configuration---(154/155/156/157)


Shell> cd/usr/local/mysql/bin/


shell> CP ndb_mgm*/usr/local/bin/






Shell> vim/etc/my.cnf


-------------------------


[Mysql_cluster]


ndb-connectstring=10.10.54.154


[Mysqld]


Datadir=/data/ndb


Ndbcluster


ndb-connectstring=10.10.54.154


-------------------------






Managing node 154 configuration


Shell> mkdir-p/etc/ndb/config.ini


Shell> Vim/etc/ndb/config.ini


--------------------------------------


[NDBD DEFAULT]


noofreplicas=2


datamemory=128m


indexmemory=64m






[TCP DEFAULT]


portnumber=2202






[NDB_MGMD]


Nodeid=1


hostname=10.10.54.154


Datadir=/var/lib/mysql-cluster






[NDBD]


nodeid=2


hostname=10.10.54.154


Datadir=/data/ndb


Backupdatadir=/data/backup






[NDBD]


Nodeid=3


hostname=10.10.54.155


Datadir=/data/ndb


Backupdatadir=/data/backup






[NDBD]


Nodeid=4


hostname=10.10.54.156


Datadir=/data/ndb


Backupdatadir=/data/backup






[NDBD]


Nodeid=5


hostname=10.10.54.157


Datadir=/data/ndb


Backupdatadir=/data/backup






[MYSQLD]


Nodeid=7


hostname=10.10.54.154






[MYSQLD]


Nodeid=8


hostname=10.10.54.155






[MYSQLD]


Nodeid=9


hostname=10.10.54.156






[MYSQLD]


nodeid=10


hostname=10.10.54.157






[MYSQLD]


nodeid=11






[MYSQLD]


Nodeid=12






[MYSQLD]


Nodeid=13






[MYSQLD]


Nodeid=14


------------------------------------

Start Mysql-cluster


---boot order Management node--data node---SQL node





#启动管理节点


shell> ndb_mgmd-f/var/lib/mysql-cluster/config.ini--initial


The ##--initial parameter is added at the first start


#启动数据节点


shell> ndbd #--initial # #初始化数据库, used in backup, when recovering


2014-02-27 18:14:23 [NDBD] INFO--Angel connected to ' 10.10.54.154:1186 '


2014-02-27 18:14:23 [NDBD] INFO--Angel allocated Nodeid:2





#启动sql节点


Shell>/etc/init.d/mysqld Start









View MySQL cluster status


Shell> NDB_MGM


Ndb_mgm> Show


Cluster Configuration


---------------------


[NDBD (NDB)] 4 node (s)


id=2 @10.10.54.154 (mysql-5.5.35 ndb-7.2.15, nodegroup:0, *)


Id=3 @10.10.54.155 (mysql-5.5.35 ndb-7.2.15, nodegroup:0)


Id=4 @10.10.54.156 (mysql-5.5.35 ndb-7.2.15, nodegroup:1)


Id=5 @10.10.54.157 (mysql-5.5.35 ndb-7.2.15, nodegroup:1)





[NDB_MGMD (MGM)] 1 node (s)


Id=1 @10.10.54.154 (mysql-5.5.35 ndb-7.2.15)






[Mysqld (API)] 8 node (s)


Id=7 @10.10.54.154 (mysql-5.5.35 ndb-7.2.15)


Id=9 @10.10.54.155 (mysql-5.5.35 ndb-7.2.15)


id=10 @10.10.54.156 (mysql-5.5.35 ndb-7.2.15)


id=11 @10.10.54.157 (mysql-5.5.35 ndb-7.2.15)


Id=12 (not connected, accepting connect from any host)


Id=13 (not connected, accepting connect from any host)


Id=14 (not connected, accepting connect from any host)


Id=15 (not connected, accepting connect from any host)





Test Mysql-cluster


1. Create a table on the management machine


mysql> CREATE database D1;


mysql> use D1;


mysql> CREATE TABLE ' t_e ' (


' Emp_no ' int (one) is not NULL,


' Birth_date ' date not NULL,


' first_name ' varchar (not NULL),


' last_name ' varchar (+) not NULL,


' Gender ' enum (' M ', ' F ') is not NULL,


' Hire_date ' date not NULL,


PRIMARY KEY (' Emp_no ')


) Engine=ndbcluster DEFAULT Charset=utf8





2.mysql> INSERT INTO t_e select * from Employees.employees;


# #插入出错


Error 1297 (HY000): Got temporary error 233 ' out of Operation Records in transaction Coordinator (Increase Maxnoofconcurre ntoperations) ' from Ndbcluster


#原因时插入数据量太大




mysql> INSERT INTO t_e select * from Employees.employees limit 10000;


#插入成功



View on 3.data nodes, data synchronized





Close Mysql-cluster


1. Close the SQL node first


/etc/init.d/mysqld stop


2. On the management machine


Shell> NDB_MGM


ndb_mgm> shutdown #关闭所有cluster进程

Mysql-cluster Cluster principle Introduction and construction steps (four Data/sql nodes) (RPM)