MYSQL集群

集群成员：

==管理节点(MGM)                  进程名ndb_mgmd

==数据节点(Data Nodes)        进程名ndbd

==SQL节点(Sql Nodes)             进程名mysqld

注意：

1. 所有节点均不安装和启动mysql-server软件包，仅安装集群软件包mysql-cluster-gpl-7.3.6-linux-glibc2.5-x86_64.tar

2. Mysql Cluster采用的是NDB存储引擎，因此在建表时必须指定ENGINE为ndbcluster，这是一种内存式的存储引擎，因此对内存要求很高。

实验环境

操作系统：CentOS release 5.9 (Final) 64位

集群软件包：mysql-cluster-gpl-7.3.6-linux-glibc2.5-x86_64.tar.gz

拓扑结构

192.168.3.206                                                    mgm

192.168.3.200/192.168.3.233                          sql1/sql2

192.168.3.219/192.168.3.228                          note1/note2

实现过程

一.初始配置（所有节点）

ip,iptables,selinux

如果之前有安装过mysql服务，执行以下步骤

service mysqld stop

chkconfig mysqld off

mv /etc/my.cnf /etc/my.cnf.bak

二.Installing a MySQL Cluster Binary Release on Linux

SQL nodes.

[root@sql1 ~]# ls mysql-cluster-gpl-7.3.6-linux-glibc2.5-x86_64.tar.gz
mysql-cluster-gpl-7.3.6-linux-glibc2.5-x86_64.tar.gz

[root@sql1 ~]# useradd mysql
[root@sql1 ~]# cat /etc/passwd | grep mysql
mysql:x:501:501::/home/mysql:/sbin/nologin

[root@sql1 ~]# tar xzvf mysql-cluster-gpl-7.3.6-linux-glibc2.5-x86_64.tar.gz -C /usr/local

[root@sql1 ~]# ln -s mysql-cluster-gpl-7.3.6-linux-glibc2.5-x86_64/ /usr/local/mysql

[root@sql1 ~]# cd /usr/local/mysql

[root@sql1 mysql]# scripts/mysql_install_db --user=mysql

[root@sql1 mysql]# chown -R root .

[root@sql1 mysql]# chown -R mysql data

[root@sql1 mysql]# chgrp -R mysql .

[root@sql1 mysql]# cp support-files/mysql.server /etc/rc.d/init.d/

[root@sql1 mysql]# chmod +x /etc/rc.d/init.d/mysql.server

[root@sql1 mysql]# echo "PATH=\$PATH:/usr/local/mysql/bin" >> /etc/profile

[root@sql1 mysql]# source /etc/profile

[root@sql1 mysql]# service mysql.server start

[root@sql1 mysql]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.19-ndb-7.3.6-cluster-gpl MySQL Cluster Community Server (GPL)

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

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.

mysql>

[root@sql1 mysql]# service mysql.server stop

[root@sql2 ~]#同sql1

Data nodes.

[root@note1 ~]# rsync -va 192.168.3.200:/usr/local/mysql/bin/ndbd /usr/local/bin/

[root@note1 ~]# rsync -va 192.168.3.200:/usr/local/mysql/bin/ndbmtd /usr/local/bin/

[root@note1 ~]# chmod a+x /usr/local/bin/ndb*

[root@note2 ~]#同note1

Management nodes. 

[root@mgm ~]# rsync -va 192.168.3.200:/usr/local/mysql/bin/ndb_mgm* /usr/local/bin/

[root@mgm ~]# chmod a+x /usr/local/bin/ndb_mgm*

[root@mgm ~]# mkdir -p /usr/local/mysql/mysql-cluster

三.Initial Configuration of MySQL Cluster

Configuring the data nodes and SQL nodes.

[root@sql1 mysql]# vi /etc/my.cnf

[mysqld]
# Options for mysqld process:
ndbcluster

[mysql_cluster]
# Options for MySQL Cluster processes:
ndb-connectstring=192.168.3.206

[root@sql1 mysql]# rsync -va /etc/my.cnf 192.168.3.233:/etc

[root@sql1 mysql]# rsync -va /etc/my.cnf 192.168.3.219:/etc

[root@sql1 mysql]# rsync -va /etc/my.cnf 192.168.3.228:/etc

Configuring the management node.

[root@mgm ~]# mkdir /var/lib/mysql-cluster

[root@mgm ~]# cd /var/lib/mysql-cluster

[root@mgm mysql-cluster]# vi config.ini

[ndbd default]
# Options affecting ndbd processes on all data nodes:
NoOfReplicas=2                  # Number of replicas
DataMemory=80M    # How much memory to allocate for data storage
IndexMemory=18M   # How much memory to allocate for index storage
                                                # For DataMemory and IndexMemory, we have used the
                                                # default values. Since the "world" database takes up
                                                # only about 500KB, this should be more than enough for
                                                # this example Cluster setup.

[tcp default]
# TCP/IP options:
portnumber=3306         # This the default; however, you can use any
                                                # port that is free for all the hosts in the cluster
                                                # Note: It is recommended that you do not specify the port
                                                # number at all and simply allow the default value to be used
                                                # instead

[ndb_mgmd]
# Management process options:
id=1
hostname=192.168.3.206          # Hostname or IP address of MGM node
datadir=/var/lib/mysql-cluster  # Directory for MGM node log files

[ndbd]
# Options for data node "note1":
id=10
hostname=192.168.3.219       # Hostname or IP address
datadir=/usr/local/mysql/data   # Directory for this data node‘s data files

[ndbd]
# Options for data node "note2":
id=11
hostname=192.168.3.228         # Hostname or IP address
datadir=/usr/local/mysql/data   # Directory for this data node‘s data files

[mysqld]
# SQL node options:
id=20
hostname=192.168.3.200

[mysqld]
# SQL node options:
id=21
hostname=192.168.3.233

四.Initial Startup of MySQL Cluster

1. Management nodes

[root@mgm mysql-cluster]# ndb_mgmd -f /var/lib/mysql-cluster/config.ini
MySQL Cluster Management Server mysql-5.6.19 ndb-7.3.6

[root@mgm mysql-cluster]# ndb_mgmd -f /var/lib/mysql-cluster/config.ini --reload

2. data node

[root@note1 ~]# mkdir -p /usr/local/mysql/data
[root@note1 ~]# ndbd
2014-07-30 11:41:31 [ndbd] INFO     -- Angel connected to ‘192.168.3.206:1186‘
2014-07-30 11:41:31 [ndbd] INFO     -- Angel allocated nodeid: 10

[root@note2 ~]# mkdir -p /usr/local/mysql/data
[root@note2 ~]# ndbd
2014-07-30 11:41:24 [ndbd] INFO     -- Angel connected to ‘192.168.3.206:1186‘
2014-07-30 11:41:24 [ndbd] INFO     -- Angel allocated nodeid: 11

3. SQL node

[root@sql1 mysql]# /etc/init.d/mysql.server start
Starting MySQL.................[  OK  ]
[root@sql1 mysql]# chkconfig mysql.server on

[root@sql2 mysql]# /etc/init.d/mysql.server start
Starting MySQL..............[  OK  ]
[root@sql2 mysql]# chkconfig mysql.server on

4. Management nodes

[root@mgm mysql-cluster]# ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=10   @192.168.3.219  (mysql-5.6.19 ndb-7.3.6, Nodegroup: 0)
id=11   @192.168.3.228  (mysql-5.6.19 ndb-7.3.6, Nodegroup: 0, *)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @192.168.3.206  (mysql-5.6.19 ndb-7.3.6)

[mysqld(API)]   2 node(s)
id=20   @192.168.3.200  (mysql-5.6.19 ndb-7.3.6)
id=21   @192.168.3.233  (mysql-5.6.19 ndb-7.3.6)

测试：

[root@sql2 mysql]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.19-ndb-7.3.6-cluster-gpl MySQL Cluster Community Server (GPL)

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

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.

mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql> grant all on *.* to root@‘%‘ identified by ‘456‘;
Query OK, 0 rows affected (1.73 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.25 sec)

mysql> quit
Bye

[root@sql1 mysql]# mysql -h 192.168.3.233 -u root -p456
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.19-ndb-7.3.6-cluster-gpl MySQL Cluster Community Server (GPL)

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

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.

mysql>

测试一：所有节点均正常，写入或查询

用户分别访问两台SQL节点，创建数据库，表（存储引擎为ndbcluster）

[root@sql1 mysql]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.19-ndb-7.3.6-cluster-gpl MySQL Cluster Community Server (GPL)

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

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| ndbinfo            |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.11 sec)

mysql> create database itttw;
Query OK, 1 row affected (0.24 sec)

mysql> use itttw;
Database changed
mysql> create table t1(id int)engine=ndbcluster;
Query OK, 0 rows affected (0.14 sec)

mysql> insert into t1 values(10);
Query OK, 1 row affected (0.10 sec)

mysql>

[root@sql2 mysql]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.6.19-ndb-7.3.6-cluster-gpl MySQL Cluster Community Server (GPL)

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

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| itttw              |
| mysql              |
| ndbinfo            |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)

mysql> use itttw;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables
    -> ;
+-----------------+
| Tables_in_itttw |
+-----------------+
| t1              |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from t1;
+------+
| id   |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

测试二：停掉Master数据节点

当前集群状态：

ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=10   @192.168.3.219  (mysql-5.6.19 ndb-7.3.6, Nodegroup: 0)
id=11   @192.168.3.228  (mysql-5.6.19 ndb-7.3.6, Nodegroup: 0, *)   -----节点11是Master节点

[ndb_mgmd(MGM)] 1 node(s)
id=1    @192.168.3.206  (mysql-5.6.19 ndb-7.3.6)

[mysqld(API)]   2 node(s)
id=20   @192.168.3.200  (mysql-5.6.19 ndb-7.3.6)
id=21   @192.168.3.233  (mysql-5.6.19 ndb-7.3.6)

[root@note2 ~]# pkill ndbd

ndb_mgm> Node 11: Node shutdown completed. Initiated by signal 15.   -----终端提示关闭完成

ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=10   @192.168.3.219  (mysql-5.6.19 ndb-7.3.6, Nodegroup: 0, *)        -----Master数据节点切换成功
id=11 (not connected, accepting connect from 192.168.3.228)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @192.168.3.206  (mysql-5.6.19 ndb-7.3.6)

[mysqld(API)]   2 node(s)
id=20   @192.168.3.200  (mysql-5.6.19 ndb-7.3.6)
id=21   @192.168.3.233  (mysql-5.6.19 ndb-7.3.6)

重新启动192.168.3.228
ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=10   @192.168.3.219  (mysql-5.6.19 ndb-7.3.6, Nodegroup: 0, *)
id=11   @192.168.3.228  (mysql-5.6.19 ndb-7.3.6, starting, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @192.168.3.206  (mysql-5.6.19 ndb-7.3.6)

[mysqld(API)]   2 node(s)
id=20   @192.168.3.200  (mysql-5.6.19 ndb-7.3.6)
id=21   @192.168.3.233  (mysql-5.6.19 ndb-7.3.6)

ndb_mgm> Node 11: Started (version 7.3.6)                                         ----节点11开始启动

ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=10   @192.168.3.219  (mysql-5.6.19 ndb-7.3.6, Nodegroup: 0, *)
id=11   @192.168.3.228  (mysql-5.6.19 ndb-7.3.6, Nodegroup: 0)       -----启动成功

[ndb_mgmd(MGM)] 1 node(s)
id=1    @192.168.3.206  (mysql-5.6.19 ndb-7.3.6)

[mysqld(API)]   2 node(s)
id=20   @192.168.3.200  (mysql-5.6.19 ndb-7.3.6)
id=21   @192.168.3.233  (mysql-5.6.19 ndb-7.3.6)

本文出自 “sanyuan” 博客，请务必保留此出处http://sanyuan.blog.51cto.com/873226/1532732

MYSQL集群,古老的榕树,5-wow.com