MySQL Cluster in a MySQL Cluster

MySQL Cluster in a MySQL Cluster
1. References

2. Introduction

MySQL-Cluster is a technology that allows you to deploy "in memory" database clusters in a non-shared system. Without a shared architecture, the system can use cheap hardware without special requirements for hardware and software. In addition, each component has its own memory and disk, so there is no single point of failure.

The MySQL cluster integrates the standard MySQL server with the "in-memory" Cluster Storage engine named NDB. The term NDB refers to the settings related to the storage engine, and the term "MySQL cluster" refers to the combination of MySQL and the NDB storage engine.

A MySQL cluster consists of a group of computers, each of which runs a variety of processes, including MySQL servers, NDB cluster data nodes, Management Servers (MGM), and (possibly) dedicated Data Access Program. The relationship between components in the cluster, such:

 

All these programs constitute a MySQL cluster. When you save data to the NBD cluster engine, the table is saved in the data node. These tables can be directly accessed from all other MySQL servers in the cluster. Therefore, if an application updates the salary of an employee, all other MySQL servers that query this data can immediately detect this change.

For MySQL clusters, data stored on data nodes can be mapped, and the cluster can handle faults of individual data nodes. Except for a few things, they will be abandoned due to the loss of their status, there will be no other impact. A transaction application can handle failures, so it is not the source of the problem.

MySQL Cluster backup and recovery

MySQL Cluster installation Configuration

Create a Cluster environment using three MySQL clusters. DOC

MySQL Cluster7.2 online addition of data nodes has major drawbacks

3. Basic concepts of MySQL Clusters

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

The NDB storage engine can be configured using multiple failover and load balancing options, but the storage engine at the cluster level is the easiest to start. The NDB storage engine of the MySQL cluster contains the complete dataset, which depends only on other data in the cluster.

Next, we will introduce how to set up a MySQL cluster consisting of the NDB storage engine and some MySQL servers.

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

Note: in many cases, the term "Node" is used for computers, but it represents processes when discussing MySQL clusters. A single computer can have any number of nodes, so we can configure multiple nodes with different functions on the same computer. Therefore, 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 Nodes (MGM): these nodes manage other nodes in the MySQL cluster, such as providing configuration data, starting and stopping nodes, and running backups. Because such nodes are responsible for managing the configurations of other nodes, management nodes should be started before other nodes. The MGM node is started with the command ndb_mgm.

Data nodes (NDB): these nodes are used to save the data of the cluster. The number of data set points is related to the number of copies, which is a multiple of fragments. For example, there are four data nodes for two replicas and two fragments for each copy. There is no need to have more than one copy .. The data node is started with the command ndbd.

SQL nodes: these nodes are used to access cluster data. For MySQL clusters, client nodes are traditional MySQL servers that use the NDB Cluster Storage engine. In typical cases, the SQL node is started with the mysql-ndbcluster command, or the ndbcluster is added to my. cnf and started with mysqld.

The cluster configuration includes the configuration of individual nodes in the cluster and the individual communication links between nodes. The current design of MySQL clusters aims to ensure that the storage nodes are homogeneous in terms of processor capabilities, memory space, and bandwidth. In addition, to provide a single configuration point as a whole, all configurations of the cluster are in the same file.

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 an interesting event occurs in a data node, the node transmits the information about the event to the management server, and then writes the information to the cluster log.

 

4. Implementation Environment

Now, we plan to establish a MySQL CLuster System with five nodes, so we need to use three machines (SQL and data nodes are shared) for the following purposes:

Node (Purpose) IP address (host name)
Management node (MGM) 10.24.0.101 (db1) nodeid = 1

Data Node 1 (NDBD1) 10.24.6.4 (db4) nodeid = 11
Data Node 2 (NDBD2) 10.24.6.6 (db5) nodeid = 12
SQL Node 1 (SQL1) 10.24.6.4 (db2) nodeid = 21
SQL Node 2 (SQL2) 10.24.6.6 (db3) nodeid = 22

5. Download the MySQL-Cluster installation package

For installation package downloads for MySQL-Cluster, see http://dev.mysql.com/downloads/cluster/

Mysql-cluster-gpl-7.4.7-debian7-x86_64.deb

6. Install mysql 6.1. Clear earlier mysql traces

In addition, if you have installed mysql-server before, you need to uninstall mysql-server before this experiment, and execute the following command to uninstall mysql

Sudo apt-get autoremove -- purge mysql-server

Sudo apt-get remove mysql-server

Sudo apt-get autoremove mysql-server

Sudo apt-get remove mysql-common (very important)

6.2. Install the deb File

Sudo dpkg-I mysql-cluster-gpl-7.4.7-debian7-x86_64.deb

Installation directory/opt/mysql/server-5.6

6.3. Storage node/SQL node Installation

The installation steps for SQL nodes and data nodes are basically the same. Therefore, perform the following steps as the system root user on each machine designed as a storage node or SQL node:

Mysql group and mysql user

Check the/etc/passwd and/etc/group/files to check whether the mysql group and mysql user already exist on the system, in this case, some operating systems will create it as part of the installation process. You can run the following command to View Details:

Cat show/etc/passwd

Cat show/etc/group

If they do not exist, create a new mysql user group and add a mysql user group to the group.

Groupadd mysql

Useradd-g mysql

6.4. Create a script for the system database

Sudo/opt/mysql/server-5.6/scripts/mysql_install_db -- user = mysql

If the script cannot be run and the host name does not match, the download version may be incorrect. Check whether the OS is 32-bit or 64-bit and select the correct version. If the default file cannot be found, it is likely that the previous mysql-server was not uninstalled. Run the commands provided above to thoroughly uninstall mysql-server.

Successful results:

To start mysqld at boot time you have to copy

Support-files/mysql. server to the right place for your system

Please remember to set a password for the MySQL root USER!

To do so, start the server, then issue the following commands:

/Opt/mysql/server-5.6/bin/mysqladmin-u root password 'new-password'

/Opt/mysql/server-5.6/bin/mysqladmin-u root-h drbd01 password 'new-password'

Alternatively you can run:

/Opt/mysql/server-5.6/bin/mysql_secure_installation

Which will also give you the option of removing the test

Databases and anonymous user created by default. This is

Stronugly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon:

Cd/opt/mysql/server-5.6;/opt/mysql/server-5.6/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl

Cd mysql-test; perl mysql-test-run.pl

Support MySQL by buying support/licenses at http://shop.mysql.com

WARNING: Found existing config file/opt/mysql/server-5.6/my. cnf on the system.

Because this file might be in use, it was not replaced,

But was used in bootstrap (unless you used -- defaults-file)

And when you later start the server.

The new default config file was created as/opt/mysql/server-5.6/my-new.cnf,

Please compare it with your file and take the changes you need.

. Set necessary permissions for the MySQL server and Data Directory

Chown-R root.

Chown-R mysql data

3	Chgrp-R mysql.

6.6. Copy mysql. server

Sudo cp/opt/mysql/server-5.6/support-files/mysql. server/etc/init. d/mysql

Chmod + x/etc/init. d/mysql

6.7. Copy my. cnf

Sudo cp/opt/mysql/server-5.6/my-new.cnf/etc/my. cnf

Sudo vim/etc/my. cnf

 

6.8. Set the root password

Sudo apt-get install mysql-client

Mysqladmin-u root flush-privileges password "123456"

6.9. Management node Installation

For management (MGM) nodes, you do not need to install the mysqld executable file. You only need to install the binary files for the MGM server and client. Such files can be found in the downloaded file. Assume that the downloaded file is stored in the/var/tmp file. As a system administrator, perform the following steps to install ndb_mgmd and ndb_mgm on the cluster management node host.

Ndb_mgmd: ndb Management Server

Ndb_mgm: ndb management client

6.10. Install the deb File

Sudo dpkg-I mysql-cluster-gpl-7.4.7-debian7-x86_64.deb

Installation directory/opt/mysql/server-5.6

6.11. Create a management directory

Sudo mkdir/usr/local/mysql/

6.12. Copy the ndb hypervisor

Sudo cp/opt/mysql/server-5.6/bin/ndb_mgm */usr/local/mysql/

6.13. Port

Note: The default port of the cluster management node is 1186, and the default end of the data node is 2202.

 

6.14. Configure the ndb management Node

Cd/usr/local/mysql/

Sudo vim config. ini:

# Options affecting ndbd processes on all data nodes:

[Ndbd default]

NoOfReplicas = 2

DataMemory = 80 M

IndexMemory = 18 M

# TCP/IP options:

[Tcp default]

# Portnumbers = 2202

# Management process options:

[NDB_MGMD]

Nodeid = 1

HostName = 10.24.0.101

DataDir =/usr/local/mysql

# Options for data node:

[NDBD]

Nodeid = 11

HostName = 10.24.6.4

DataDir =/opt/mysql/server-5.6/data/

# Options for data node:

[NDBD]

Nodeid = 12

HostName = 10.24.6.6

DataDir =/opt/mysql/server-5.6/data/

# SQL node options:

[MYSQLD]

Nodeid = 21

HostName = 10.24.6.4

[MYSQLD]

Nodeid = 22

HostName = 10.24.6.6

For more details, please continue to read the highlights on the next page:

1 2 3 Next Page