MySQL Database cluster construction in "Data Cluster" real environment

Read Catalogue

• About MySQL Cluster
• Mysql-cluster Construction
• Virtual machine Installation centos6.6
• Mysql-cluster Download
• centos6.6 Build Environment Configuration
• Mysql-cluster installation package import in cent
• Cluster configuration
• Run Mysql-cluster
• Problems
• Test

Mysql-cluster construction document in real machine environment

absrtact: at the beginning of this year, MySQL cluster was built up because of the requirements of laboratory testing for different database performance. Acquisition of host, switch, twisted pair, and so on a series of preparations ready to start the cluster build. At first, I do not understand this very much, access to a lot of information, and ultimately are not perfect. Therefore, the author of the Real Machine environment test success, sorting out this building document, a prevention of forgotten knowledge Summary, and others to share. The day before the completion of the document due to the text is too much, to the unfamiliar configurator to bring a small pressure. After changing the document version, reduce the text to increase the picture, so that a freshman of the MySQL cluster does not understand, according to this configuration. Can be configured successfully, so published. (This article original/collation, reproduced please mark the original source: Real machine Environment Mysql-cluster building documents)

Bai Ningsu

July 14, 2015 17:22:04

Back to top MySQL cluster profile

The MySQL cluster is completely different from the Oracle RAC, which employs a shared nothing architecture. The entire cluster consists of a management node (NDB_MGMD), a processing node (mysqld) , and a storage node (ndbd), and there is no shared storage device. MySQL cluster is primarily implemented using the NDB storage engine, which is a memory-based storage engine that requires data to be fully loaded into memory. The data is automatically distributed across the different storage nodes in the cluster, and each storage node holds only one shard of the complete data (fragment). At the same time, users can set up the same data to be stored on multiple different storage nodes to ensure that a single point of failure does not result in data loss. MySQL cluster is composed mainly of 3 parts:

• SQL server Node
• NDB Data Storage Node
• Monitoring and Managing nodes

Such hierarchies are also related to the architecture in which MySQL itself separates SQL processing and storage. The advantage of MySQL cluster is that it is a distributed database cluster, processing nodes and storage nodes can be linearly increased, the whole cluster has no single point of failure, the availability and scalability can be high, more suitable for OLTP applications. But the problem with it is:

• NDB ("NDB" is an "in-memory" storage engine with high availability and good data consistency. The storage engine must require that all data be loaded into memory, the limit is large, but the new version of NDB has been improved to allow only the index data to be loaded in memory, and the data can be saved on disk.
• The current MySQL cluster performance is not ideal, because the data is distributed to different storage nodes according to the primary key hash, if the application is not through the primary key to obtain data, it must be scanned on all storage nodes, return the results to the processing node processing. Moreover, the write operation needs to write multiple copies of the data to different storage nodes, and the network requirement between nodes is very high.

Although MySQL cluster current performance is not ideal, but share nothing architecture must be the future trend, Oracle after taking over MySQL, also vigorously develop MySQL cluster, I have great expectations of the future of MySQL cluster.

Back to top Mysql-cluster construction

Installation Mysql-cluster first to understand the structure of cluster, cluster is largely composed of management nodes (MGMD), Data nodes (NDBD), SQL nodes (MYSQLD).

Back to the top of the virtual machine installation centos6.6

System Requirements:

Memory: 4G (minimum 4G)

CPU: Need to support VT

If you are a laptop user then CPU virtualization may be turned off, please set virtualization under BIOS to enable

Create a new virtual machine, select a typical installation, and click Next

Click Next

The best user name here is MySQL because it needs to be used later. Set it to other users as well.

Next

Location is where you save this virtual CentOS system.

Next

Select, Next

Choose Custom Hardware, it is recommended to allocate 1G of memory per virtual machine. Each cent allocates at least 512M of memory.

Click Finish to do it.

After the virtual machine installation is complete, enter it with root permission. The password is the same as the password for the MySQL account.

Back to top Mysql-cluster download

Www.mysql.com/downloads/cluster Select the version under Linux-generic according to your cent system selection 32or64

Back to top centos6.6 build environment configuration

First go to Linux terminal, right-click Desktop

Open in terminal (terminal)

Before installing the Mysql-cluster, first uninstall the original system's own MySQL

Network offload (execute command under terminal)

#yum Remove MySQL

Manual Cleanup (execute command under terminal)

#rpm –qa|grep mysql* (This is the MySQL that exists in the query system)

#rpm –e–nodeps mysql-libs-5.1.71-1.el6.x86_64 (different versions of CentOS are also different)

Back to top Mysql-cluster install package import cent

First, query the IP address of the cent that need to be imported, enter it under terminal

#ifconfig

Where inet addr:192.168.191.129 is the IP address of this cent.

Download software WinSCP

Open Software

New site, host name is cent IP, user name using root

Click Login, enter the password, connect successfully

On the left is windows right is cent

cent go to directory/USR/LOCAL/SRC put your Mysql-cluster installation file in this directory

Open cent terminal

Add MySQL users and groups (skip this step if you are using MySQL as the user who installed the virtual machine earlier)

Add MySQL User

1. # Groupadd MySQL

2. # useradd mysql-g MySQL

Installing Mysql-cluster 7.4.4-linux

1. # cd/usr/local/src/(the cluster version has been downloaded)

2. # TAR-XVF mysql-cluster-gpl-7.4.4-linux-glibc2.5-x86_64.tar.gz

3. # mv Mysql-cluster-gpl-7.4.4-linux-glibc2.5-x86_64. /mysql

4. # CD:

5. # chown-r Mysql:mysql mysql/

6. # CD MySQL

7. # scripts/mysql_install_db--user=mysql

When this page appears, the Mysql-cluster installation is successful.

The above step is to install the mysql-cluster must step, all the machines have to carry out the above steps

Back to top cluster configuration first for Management node configuration (terminal input)

# Vi/var/lib/mysql-cluster/config.ini (directories and files not please new, add the following content)

+ View Code

After entering the above content, press ESC and enter: Wq to save the exit

Copy NDB_MGM , NDB_MGMD , Config.ini to Bin directory.

# Cd/usr/local/mysql/bin

# CP./ndb_mgm/usr/local/bin/

# CP./ndb_mgmd/usr/local/bin/

# cp/var/lib/mysql-cluster/config.ini/usr/local/bin/

Data node configuration (terminal input)

# VI/ETC/MY.CNF (Add the following)

[Mysqld]

Datadir=/var/mysql/data

Socket=/var/mysql/mysql.sock

User=mysql

# Disabling Symbolic-links is recommended to prevent assorted security risks

Symbolic-links=0

#运行NDB存储引擎

Ndbcluster

#指定管理节点

ndb-connectstring=192.168.191.132 (Management node IP)

[Mysql_cluster]

ndb-connectstring=192.168.191.132 (Management node IP)

[NDB_MGM]

connect-string=192.168.191.132 (Management node IP)

[Mysqld_safe]

Log-error=/var/mysql/log/mysqld.log

Pid-file=/var/run/mysqld/mysqld.pid

SQL node configuration (terminal input)

# cd/usr/local/mysql/

Set up MySQL service for boot

# CP Support-files/mysql.server/etc/rc.d/init.d/mysqld

# chmod +x/etc/rc.d/init.d/mysqld

# chkconfig--add mysqld

# VI/ETC/MY.CNF (Add the following)

[Mysqld]

Datadir=/var/mysql/data

Socket=/var/mysql/mysql.sock

User=mysql

# Disabling Symbolic-links is recommended to prevent assorted security risks

Symbolic-links=0

Log-bin =/var/mysql/log/mysql-bin.log

max_connections=1000

#以下为mysql The main main mode configuration file

# Ignore MySQL database replication

Binlog-ignore-db=mysql

# 2 Increase each time

auto-increment-increment=2

# Set the offset of the automatically growing field, which is the initial value of 2

Auto-increment-offset=1

Ndbcluster

ndb-connectstring=192.168.191.132 (Management node IP)

[Mysql_cluster]

ndb-connectstring=192.168.191.132 (Management node IP)

[NDB_MGM]

connect-string=192.168.191.132 (Management node IP)

[Mysqld_safe]

Log-error=/var/mysql/log/mysqld.log

Pid-file=/var/run/mysqld/mysqld.pid

Note here to create a new MySQL directory under the Var directory and create a log and data directory under the new MySQL directory.

Also give these two directories authorization code as follows

# Cd/var

# chown–r Mysql:mysql mysql/

The following two commands are a link to the MySQL command, otherwise you cannot use the MySQL command

# Ln–s/usr/local/mysql/bin/mysql/usr/bin

# Ln–s/var/mysql/mysql.sock/tmp/mysql.sock

Back to top run Mysql-cluster

MySQL the boot order of the cluster is: Management node, Data Node->sql node

MySQL The cluster is closed in order, management node - Data Node->sql node

Pay attention to firewall issues when you open the service

Note the firewall shutdown command for the management node is as follows:

#/etc/init.d/iptables Stop

First open the Management node service

# ndb_mgmd-f/var/lib/mysql-cluster/config.ini--initial (note the first time or modify the config file, open the service must add initial otherwise the new additions will not be used)

Then open the NDBD node service

#/usr/local/mysql/bin/ndbd--initial (IBID.)

A message appears indicating that the NDBD node started successfully.

2012-03-28 02:01:38 [NDBD] INFO--Angel connected to ' 10.32.33.120:1186 '

2012-03-28 02:01:38 [NDBD] INFO--Angel allocated nodeid:36

Last open two SQL nodes 131 and 133

#service mysqld Start

The presence of MySQL success indicates that the service started successfully

Open Management node, terminal input

# NDB_MGM

#ndb_mgm > Show

The following information is displayed and the Mysql-cluster installation was successfully deployed

Cluster Configuration

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

[NDBD (NDB)] 2 node (s)

id=2 @192.168.191.130 (mysql-5.5.20 ndb-7.4.4, nodegroup:0)

[NDB_MGMD (MGM)] 1 node (s)

Id=1 @192.168.191.132 (mysql-5.5.20 ndb-7.4.4)

[Mysqld (API)] 4 node (s)

Id=3 @192.168.191.131 (mysql-5.5.20 ndb-7.4.4)

Id=4 @192.168.191.133 (mysql-5.5.20 ndb-7.4.4)

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

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

You can see that the individual nodes are connected.

The following is about MGM Related Commands

# NDB_MGM

# ndb_mgm> Show  View the individual node conditions.

# ndb_mgm> all report memory To view the usage of each data node

# ndb_mgm>create Nodegroup 3 Creating data node groupings

# mysql> alter online table data_house reorganize partition; Adjust partition data

# Ndb_mgm>shutdown Close Node

# Ndb_mgm>exit Exit

Back to top FAQ mysqld service failed to start

[[email protected] Desktop] #service mysqld start

Starting mysql.the service quit without updating PID file (/[failed]/var/mysql/log/localhost.localdomain.pid).

Check the logs first.

#cd/var/mysql/data

#less Localhost.localdomain.err

If the error is

Fatal Error:can ' t open and Lock Privilege tables:table ' mysql.user ' doesn ' t exist

Workaround:

Terminal input

To the MySQL directory/usr/local/mysql

Input

Scripts/mysql_install_db–user=mysql (Initialize database)

Other issues Please note check the folder permissions issue where the problem is located

Back to top of the test

1. Log in from SQL Node 131, create a database and table for simple testing.

mysql> CREATE DATABASE Tltest;

mysql> use tltest;

Database changed

Mysql> CREATE TABLE test1 (ID int,name varchar (ten)) engine=ndb;

mysql> INSERT INTO test1 values (1, ' TL ');

Mysql> select * from Test1;

+------+---------+

| ID | name |

+------+---------+

|    1 | tl |

+------+---------+

Log in to the 133 node to see the effects, libraries, tables and data already synchronized.

Insert a data from the 133 node, also log in 131, you can see the data is synchronized.

Tail Note: This series of documents, the author of the real Machine environment test can be shared, purely original, if reproduced, please note the source.: mysql-cluster Construction document under real machine environment

MySQL Database cluster construction in "Data Cluster" real environment