Configuration of MySQL Cluster

recently, when learning the configuration of a MySQL cluster, first understand what cluster, why to use the cluster, and what software to install the cluster is and what processes are in the cluster.

1. What is a cluster?

A cluster is a group of servers that provide the same services .

2. Why should I use a cluster?

solve the problem of single point of failure and data backup.

description of the operating system and the MySQL configuration file.

Linux system: redhat6.5

Installing the Cluster Software: Mysql-cluster-gpl-7.3.3-1.el6.x86_64.rpm-bundle.tar

Role of the server:

Experimental requirements: 5 servers, respectively, IP address 192.168.1.10 Do management node, 192.168.1.20/192.168.1.30 Do data node so that data can be simultaneously backed up when any one of the servers down, the database operation, When the server of the outage is restored and the data is synchronized automatically, 192.168.1.40/192.168.1.50 does the SQL node when any MySQL node fails, it can log into the database.

192.168.1.10 (MGMD)

192.168.1.20 (NDBD)

192.168.1.30 (NDBD)

192.168.1.40 (SQL)

192.168.1.50 (SQL)
One. Install the Software mysql-cluster (. RPM source) that provides the cluster service on all servers

The simplest is the PRM package, the following method of binary package installation

1. Unpack the package.

[Email protected] opt]# TAR-XVF Mysql-cluster-gpl-7.3.3-1.el6.x86_64.rpm-bundle.tar
2. Install the package, the tar is extracted from the RPM package, the direct installation can be.

[email protected] opt]# RPM-UVH mysql-cluster-*.rpm

3. See if the package is installed .

[Email protected] opt]# Rpm-qa | Grep-i MySQL

Mysql-cluster-shared-compat-gpl-7.3.3-1.el6.x86_64
Mysql-cluster-devel-gpl-7.3.3-1.el6.x86_64
Mysql-cluster-embedded-gpl-7.3.3-1.el6.x86_64
Perl-dbd-mysql-4.013-3.el6.x86_64
Mysql-cluster-test-gpl-7.3.3-1.el6.x86_64
Mysql-cluster-server-gpl-7.3.3-1.el6.x86_64
Mysql-cluster-client-gpl-7.3.3-1.el6.x86_64
Mysql-cluster-shared-gpl-7.3.3-1.el6.x86_64

4. Configure the Management node on 192.168.1.10.

The management node is running the management process, the runtime loads its own master profile, and the master profile needs to write itself.

For example, the contents of the configuration file for the Config.ini configuration file include:

1 public configuration of data nodes [NDBD default]
2 Specifying the Management node [NDB_MGMD]
3 Specifying data nodes [NDBD]
4 Specifying the SQL node [mysqld]

[Email protected] ~]# Vim/etc/config.ini

[NDBD Default]
Noofreplicas=2 (represents the number of copies of a data node, several data nodes are written)
datamemory=80m (indicates the size of the data cache)
indexmemory=18m (indicates the size of the index cache)
[NDB_MGMD]
Nodeid=1 (used to set the number of the current host in the cluster)
hostname=192.168.1.10 (Specifies the IP address of the management node)
Datadir=/var/log/mysql-cluster (Specifies to put the running information in/var/log/mysql-cluster, this folder must exist in the system if the folder is not created.) ）
[NDBD]
nodeid=2 (number of data nodes)
hostname=192.168.1.20 (IP address of the data node)
Datadir=/var/log/mysql-cluster/data (location where data is stored)
[NDBD]
Nodeid=3
hostname=192.168.1.30
Datadir=/var/log/mysql-cluster/data
[Mysqld]
Nodeid=4
hostname=192.168.1.40
[Mysqld]
Nodeid=5
hostname=192.168.1.50

Create/var/log/mysql-cluster folder in 192.168.1.10

[Email protected] ~]# mkdir-p/var/log/mysql-cluster/

5. Configure the data node on the 192.168.1.20/192.168.1.30

Create/var/log/mysql-cluster folders on 192.168.1.20 and 192.168.1.30 to store data in each location

  [[email protected] ~]# mkdir-p/var/log/mysql-cluster/
Create a master profile.
 [[email protected] ~]# vim/etc/my.cnf
 [mysqld]
 datadir=/var/log/mysql-cluster/ data   (Specify database directory)
 ndb-connectstring=192.168.1.10   (Specify the server IP for the connection management cluster)
  ndbcluster        (the storage engine for the table must be Ndbcluster)
 [mysql_cluster]   (Specify the machine that manages the cluster)
 ndb-connectstring=192.168.1.10 (Specify who manages the server IP for the cluster)

6. Configuring the SQL node on 192.168.1.40/192.168.1.50
[[email protected] ~]# vim/etc/my.cnf
[mysqld]
Ndbcluster
default-storage-engine=ndbcluster     (Specify the default storage engine)
Ndb-connectstring= 192.168.1.10 (Specifies the server IP for the connection management cluster)
[Mysql_cluster]
ndb-connectstring=192.168.1.10 (specifies who manages the server IP for the cluster)

7. Start the corresponding process for different role services (start in the following order).
1 Start the management process on 192.168.1.10.
[[email protected] ~]# ndb_mgmd-f/etc/config/init start MySQL Cluster service
MySQL Cluster Management Server mysql-5.6.14 ndb-7.3.3

[Email protected] ~]# NETSTAT-ANUPTL | grep:1186 to see if the cluster port is turned on

TCP 0 0 0.0.0.0:1186 0.0.0.0:* LISTEN 4341/NDB_MGMD
TCP 0 0 127.0.0.1:38177 127.0.0.1:1186 established 4341/NDB_MGMD
TCP 0 0 127.0.0.1:1186 127.0.0.1:38177 established 4341/NDB_MGMD
TCP 0 0 192.168.1.10:1186 192.168.1.50:43075 established 4341/NDB_MGMD
TCP 0 0 192.168.1.10:1186 192.168.1.40:37459 established 4341/NDB_MGMD

[[email protected] ~]# NDB_MGM Enter interface command

--NDB Cluster--Management Client--
Ndb_mgm> Show View Info command

Connected to Management Server at:localhost:1186
Cluster Configuration
---------------------
[NDBD (NDB) ]     2 node (s)
id=2 (not connected, accepting connect from 192.168.1.20)
Id=3 (not connected, Accepting connect from 192.168.1.30)

[NDB_MGMD (MGM)] 1 node (s)
id=1    @192.168.1.10  ( mysql-5.6.14 ndb-7.3.3)

[Mysqld (API)]   2 node (s)
Id=4 (not connected, accepting connect from 192.168.1.40)
Id=5 (not connected, accepting connect from 192.168.1.50)
   2  start the data process on 192.168.1.20/192.168.1.30
   [[email protected] ~]# ndbd            command to start the data process in 192.168.1.20

2015-07-21 15:49:24 [NDBD] INFO--Angel connected to ' 192.168.1.10:1186 '
2015-07-21 15:49:24 [NDBD] INFO--Angel allocated Nodeid:2 3
[[email protected] ~]# NDBD command to start the data process in 192.168.1.30
2015-07-21 10:16:16 [NDBD] INFO--Angel connected to ' 192.168.1.10:1186 '
2015-07-21 10:16:16 [NDBD] INFO--Angel allocated nodeid:3 service MySQL start

3. After starting the data process, see if the data process has started on the management node 192.168.1.10.

Ndb_mgm> Show
Cluster Configuration
---------------------
[NDBD (NDB)] 2 node (s)
id=2 @192.168.1.20 (mysql-5.6.14 ndb-7.3.3, starting, nodegroup:0, *)
Id=3 @192.168.1.30 (mysql-5.6.14 ndb-7.3.3, starting, nodegroup:0)

[NDB_MGMD (MGM)] 1 node (s)
Id=1 @192.168.1.10 (mysql-5.6.14 ndb-7.3.3)

[Mysqld (API)] 2 node (s)
Id=4 (not connected, accepting connect from 192.168.1.40)
Id=5 (not connected, accepting connect from 192.168.1.50)

Ndb_mgm> Node 2:started (version 7.3.3)
Node 3:started (Version 7.3.3)
4. Start the MySQL database service in 192.168.1.40/192.168.1.50 .

[[Email protected] ~]# service MySQL start
Starting MySQL success!

5. After starting the MySQL database service , see if the MySQL process has started on the management node 192.168.1.10.

Ndb_mgm> Show
Cluster Configuration
---------------------
[NDBD (NDB)] 2 node (s)
id=2 @192.168.1.20 (mysql-5.6.14 ndb-7.3.3, nodegroup:0, *)
Id=3 @192.168.1.30 (mysql-5.6.14 ndb-7.3.3, nodegroup:0)

[NDB_MGMD (MGM)] 1 node (s)
Id=1 @192.168.1.10 (mysql-5.6.14 ndb-7.3.3)

[Mysqld (API)] 2 node (s)
Id=4 @192.168.1.40 (mysql-5.6.14 ndb-7.3.3)
Id=5 @192.168.1.50 (mysql-5.6.14 ndb-7.3.3)

8. Client-side testing
1. Single point of failure of the Access node.
Build a table on the MySQL database service to build a library to view.

Log in to MySQL server 192.168.1.40/192.168.1.50

[[Email protected]~]# Cat/root/.mysql_secret
# The random password set for the root user at Mon Jul 6 04:54:27 (local time): UMDVQWXZ
[Email protected] ~]# MYSQL-HLOCALHOST-UROOT-PUMDVQWXZ

Mysql>set password for [email Protected]=password ("123")

Mysql>quit

[Email protected] ~]# mysql-hlocalhost-uroot-p123
Warning:using a password on the command line interface can is insecure.
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 3
Server VERSION:5.6.14-NDB-7.3.3-CLUSTER-GPL MySQL cluster Community server (GPL)

Copyright (c) and/or, Oracle, its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names trademarks of their respective
Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input Stateme NT.

View Storage Engine default is not Dbcluster

Mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+-------------- +------+------------+
| Engine | Support | Comment | Transactions | XA | savepoints |
+--------------------+---------+----------------------------------------------------------------+-------------- +------+------------+
| Ndbcluster | DEFAULT | Clustered, fault-tolerant Tables | YES | NO | NO |

Turn off the MySQL service on 192.168.1.50

[[Email protected] ~]# service MySQL stop
View information on 192.168.1.10

Ndb_mgm> Show
Cluster Configuration
---------------------
[NDBD (NDB)] 2 node (s)
id=2 @192.168.1.20 (mysql-5.6.14 ndb-7.3.3, nodegroup:0, *)
Id=3 @192.168.1.30 (mysql-5.6.14 ndb-7.3.3, nodegroup:0)

[NDB_MGMD (MGM)] 1 node (s)
Id=1 @192.168.1.10 (mysql-5.6.14 ndb-7.3.3)

[Mysqld (API)] 2 node (s)
Id=4 @192.168.1.40 (mysql-5.6.14 ndb-7.3.3)
Id=5 (not connected, accepting connect from 192.168.1.50)

Use 192.168.1.40 to see if you can log in to the database, if you can login instructions to resolve a single point of failure.

[Email protected] ~]# mysql-hlocalhost-uroot-p123
Warning:using a password on the command line interface can is insecure.
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 2
Server VERSION:5.6.14-NDB-7.3.3-CLUSTER-GPL MySQL cluster Community server (GPL)

Copyright (c) and/or, Oracle, its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names trademarks of their respective
Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

Mysql>

2. Data node single point of failure. (Close 1 Data Services, insert in the table, view, and so on, when another recovery service, see if you can synchronize data, if possible to solve a single point of failure)
To verify a data node single point of failure:

1. Close the service of 192.168.1.20 Data node.

[Email protected] ~]# pkill-9 NDBD

2. View the status of the 192.168.1.20 of the management node.

Ndb_mgm> Show
Cluster Configuration
---------------------
[NDBD (NDB)] 2 node (s)
id=2 (not connected, accepting connect from 192.168.1.20)
Id=3 @192.168.1.30 (mysql-5.6.14 ndb-7.3.3, nodegroup:0, *)

[NDB_MGMD (MGM)] 1 node (s)
Id=1 @192.168.1.10 (mysql-5.6.14 ndb-7.3.3)

[Mysqld (API)] 2 node (s)
Id=4 @192.168.1.40 (mysql-5.6.14 ndb-7.3.3)
Id=5 @192.168.1.50 (mysql-5.6.14 ndb-7.3.3)

3. View the database information on the SQL node, and the information that is viewed when 192.168.1.20 is down is 192.168.1.30.

After a select on the database, insert into and other information, when the 192.168.1.30 recovery can not synchronize data, if it can be explained that can solve a single point of failure.

This article is from the "Down to earth" blog, make sure to keep this source http://343614597.blog.51cto.com/7056394/1676940

Configuration of MySQL Cluster