MySQL Cluster (MySQL cluster) preliminary

Make/Translator: Ye Jinlong (imysql#imysql.com>), Source: http://imysql.com, welcome reprint.

Work/Translator: Ye Jinlong (Email:), Source: http://imysql.cn, reprint please specify AS/translator and source, and can not be used for commercial purposes, offenders must investigate.

MySQL Cluster is a high-utility, high-redundancy version of MySQL suitable for distributed computing environments. It employs the NDB Cluster storage engine, allowing multiple MySQL servers to run in 1 Cluster. The storage engine is available in binary versions of MYQL 5.0 and above, and in RPM compatible with the latest Linux versions. (Note that mysql-server and Mysql-max RPM must be installed for MySQL Cluster to function.)
The operating systems currently running MySQL Cluster are Linux, Mac OS x and Solaris (some user advisories have successfully run MySQL Cluster on FreeBSD, but MySQL AB has not yet formally supported the feature).

First, MySQL cluster overview
MySQL Cluster is a technology that allows the Cluster of an "in-memory" database 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. For a relationship between these components in Cluster, see:

All of 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. The application accesses these data tables through the MySQL server, and the cluster Management Server manages the NDB storage server through the Administrative Tools (NDB_MGMD).
By introducing MySQL Cluster into the open-source world, MySQL provides highly available, high-performance, and scalable Cluster data management for everyone who needs it.

Second, MySQL Cluster basic concept
"NDB" is an "in-memory" storage engine, which features high availability and good data consistency.
MySQL Cluster is able to configure the NDB storage engine with multiple failover and load balancing options, but this is easiest to do on the storage engine at the Cluster level. The NDB storage engine for MySQL cluster contains a complete set of data, depending only on the other data within the cluster itself.
Currently, the cluster part of MySQL cluster can be configured independently of the MySQL server. In MySQL cluster, each part of cluster is treated as a 1 node.

• Management (MGM) node: the role of such nodes is to manage other nodes within 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 cluster data. 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. However, there is no need to set multiple replicas. The data node is started with the command "NDBD".
• SQL node: This is the node used to access the cluster data. For MySQL Cluster, the client node is a traditional MySQL server using the NDB Cluster storage engine. Typically, the SQL node is started with the command "Mysqld–ndbcluster", or "ndbcluster" is added to "my.cnf" after using "mysqld".

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

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 and requests a way to determine where the Management Server is located. 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.
In addition, there can be any number of cluster client processes or applications. They are divided into two types:

• Standard MySQL client: for MySQL Cluster, they are no different from standard (non-Cluster) MySQL. In other words, MySQL Cluster can be accessed from existing MySQL applications written in PHP, Perl, C, C + +, Java, Python, Ruby, and more.
• Management client: This type of client is connected to the Management Server and provides commands to start and stop nodes, start and Stop message tracking (Debug version only), display node version and status, start and stop backups, and so on.

Iii. beginning of preparation
1. Prepare the server
Now, we plan to build a MySQL cluster system with 5 nodes, so we need to use 5 machines for each of the following purposes:

Node (Purpose) IP address (host name)
Management node (MGM) 192.168.0.1 (DB1)
SQL Node 1 (SQL1) 192.168.0.2 (DB2)
SQL Node 2 (SQL2) 192.168.0.3 (DB3)
Data node 1 (NDBD1) 192.168.0.4 (DB4)
Data Node 2 (NDBD2) 192.168.0.4 (DB5)

2. Precautions and other
Each node's operating system is Linux, and the following description uses the host name, which is no longer represented by an IP address. Since MySQL cluster is connected by TCP/IP and the data transfer between nodes is not encrypted, it is best to run this system only in a separate subnet, and considering the rate of transmission, it is strongly recommended not to use this system across public networks. Please download the required MySQL software in Http://dev.mysql.com/downloads in advance.
In fact, the entire system can be run successfully on a separate entity computer, of course, different directories and ports must be set, and can only be used as a test.

Iv. start of installation
1. Hypothetical conditions
The nobody user is used on each node computer to run cluster, so the following command is added to add the relevant user (if it already exists, skip it and execute it with the root user):

root#/usr/sbin/groupadd Nobody
root#/usr/sbin/useradd Nobody-g Nobody

Suppose you have downloaded the binary installation package that MySQL can use directly, and put it in/tmp.

2, SQL node and storage node (NDB node) installation (that is, 4 machines repeat the following steps)

root# cd/tmp/
root# Tar zxf mysql-max-5.0.24-linux-i686.tar.gz
root# MV mysql-max-5.0.24-linux-i686/usr/local/mysql/
root# cd/usr/local/mysql/
root#./configure--prefix=/usr/local/mysql
root#./scripts/mysql_install_db
root# Chown-r nobody:nobody/usr/local/mysql/

3. Configuring the SQL Node

root# vi/usr/local/mysql/my.cnf

Then enter the following:

[Mysqld]
Basedir         =/usr/local/mysql/
DataDir         =/usr/local/mysql/data
User            = Nobody
Port            = 3306
Socket          =/tmp/mysql.sock

Ndbcluster
Ndb-connectstring=db1
[Mysql_cluster]
Ndb-connectstring=db1

4. Configure the storage node (NDB node)

root# vi/usr/local/mysql/my.cnf

Then enter the following:

[Mysqld]
Ndbcluster
Ndb-connectstring=db1
[Mysql_cluster]
Ndb-connectstring=db1

5. Install the Management node

root# cd/tmp/
root# Tar zxf mysql-max-5.0.24-linux-i686.tar.gz
root# mkdir/usr/local/mysql/
root# mkdir/usr/local/mysql/data/
root# CD mysql-max-5.0.24-linux-i686/bin/
root# CP ndb_mgm*/usr/local/mysql/
root# Chown-r Nobody:nobody/usr/local/mysql

6. Configuration Management Node

root# Vi/usr/local/mysql/config.ini

Then enter the following:

[NDBD DEFAULT]
Noofreplicas=1

[TCP DEFAULT]
portnumber=3306

#设置管理节点服务器
[NDB_MGMD]
Hostname=db1
#MGM上保存日志的目录
datadir=/usr/local/mysql/data/

#设置存储节点服务器 (NDB node)
[NDBD]
Hostname=db4
datadir=/usr/local/mysql/data/
#第二个NDB节点
[NDBD]
Hostname=db5
datadir=/usr/local/mysql/data/

#设置SQL节点服务器
[MYSQLD]
Hostname=db2
#第二个SQL节点
[MYSQLD]
Hostname=db3

Note: 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.

V. Start MySQL Cluster

A more reasonable boot order is to start the Management node server first, then start the Storage node server, and finally start the SQL node server:

• On the Management node server, execute the following command to start the MGM node process:
  

  root#/usr/local/mysql/ndb_mgmd-f/usr/local/mysql/config.ini

  You must use the parameter "-F" or "--config-file" to tell the NDB_MGM where the configuration file is located, by default in the same directory as NDB_MGMD.

• On each storage node server, if the NDBD process is started for the first time, you must first execute the following command:
  

  root#/USR/LOCAL/MYSQL/BIN/NDBD--initial

  Note that you should use the "--initial" parameter only when you first start ndbd, or when you restart NDBD after the backup/recovery data or configuration file has changed. Because this parameter causes the node to delete any files that were created by an earlier NDBD instance for recovery, including the log files used for recovery.
  If it is not the first time, run the following command directly:

  root#/USR/LOCAL/MYSQL/BIN/NDBD

• Finally, run the following command to start the SQL node server:
  

  root#/usr/local/mysql/bin/mysqld_safe--DEFAULTS-FILE=/USR/LOCAL/MYSQL/MY.CNF &

  If all goes well, that is, no error message occurs during startup, run the following command on the Management node server:

  root#/USR/LOCAL/MYSQL/NDB_MGM
  --NDB Cluster--Management Client--
  Ndb_mgm> SHOW
  Connected to Management Server at:localhost:1186
  Cluster Configuration
  ---------------------
  [NDBD (NDB)]     2 node (s)
  id=2    @192.168.0.4  (version:5.0.22, nodegroup:0, Master)
  id=3    @192.168.0.5  (version:5.0.22, nodegroup:0)
  
  [NDB_MGMD (MGM)] 1 node (s)
  Id=1    @192.168.0.1  (version:5.0.22)
  
  [Mysqld (SQL)]   1 node (s)
  id=2   (version:5.0.22)
  Id=3   (version:5.0.22)

The specific output may be slightly different depending on the MySQL version you are using.
Note: If you are using an earlier version of MySQL, you may see SQL nodes that are referenced as ' [Mysqld (API] '). This is an early usage and has now been abandoned.
Now, you should be able to work with databases, tables, and data in MySQL cluster.

Vi. Creating database Tables

There is not much difference in how data is manipulated within MySQL cluster than MySQL without cluster. There are two points to remember when performing this type of operation:

• Tables must be created with the ENGINE=NDB or Engine=ndbcluster option, or changed with the ALTER TABLE option to replicate them within cluster using the NDB cluster storage engine. If you import a table from an existing database using the output of mysqldump, you can open the SQL script in a text editor and add the option to any table creation statement, or replace any existing engine (or type) options with one of these options.
• Also keep in mind that each NDB table must have a primary key. If the user does not define a primary key when the table is created, the NDB cluster storage engine automatically generates an implied primary key. (Note: The implied key also takes up space, just like any other table index.) The problem is not uncommon because there is not enough memory to accommodate these auto-created keys.

Here is an example:
On DB2, create a data table and insert the data:

[db2~]root# mysql-uroot Test
[db2~]mysql> CREATE TABLE City (
[db2~]mysql> ID mediumint unsigned NOT NULL auto_increment primary key,
[db2~]mysql> name varchar (a) NOT null default ' '
[db2~]mysql>) engine = ndbcluster default CharSet UTF8;
[db2~]mysql> INSERT INTO city values (1, ' city1 ');
[db2~]mysql> INSERT INTO city values (2, ' city2 ');

On the DB3, query the data:

[db3~]root# mysql-uroot Test
[db2~]mysql> SELECT * from the city;
+-----------+
|id | Name  |
+-----------+
  | | city1 |
+-----------+
  | city2 |
+-----------+

Vii. Security Closure

To turn off Cluster, simply enter the following command in the shell on the same machine as the MGM node:

[db1~]root#/usr/local/mysql/ndb_mgm-e shutdown

Run the following command to close the SQL node's mysqld service:

[db2~]root#/usr/local/mysql/bin/mysqladmin-uroot shutdown

Viii. Other
For more detailed information on MySQL Cluster as well as backup, see the "MySQL Cluster (mysql cluster)" section of the MySQL manual.
Reference: "MySQL 5.1 Chinese Handbook"

MySQL Cluster (MySQL cluster) preliminary (turn)