suchen

Heim >Datenbank >MySQL-Tutorial >MySQLStudy之--MySQLCluster（集群）构建_MySQL

MySQLStudy之--MySQLCluster（集群）构建_MySQL

WBOY
WBOYOriginal
2016-06-01 12:58:471670Durchsuche

MySQL Study之--MySQL Cluster（集群）构建

一、Mysql Cluster概述与部署

MySql Cluster最显著的优点就是高可用性,高实时性,高冗余,扩展性强。
它允许在无共享的系统中部署"内存中"数据库的Cluster.通过无共享体系结构,系统能够使用廉价的硬件.此外,由于每个组件有自己的内存和磁盘,所以不存在单点故障.
它由一组计算机构成,每台计算机上均运行者多种进程,包括mysql服务器,NDB cluster的数据节点,管理服务启,以及专门的数据访问程序

所有的这些节点构成一个完整的mysql集群体系.数据保存在"NDB存储服务器"的存储引擎中,表(结构)则保存在"mysql服务器"中.应用程序通过"mysql服务器"访问这些数据表,集群管理服务器通过管理工具(ndb_mgmd)来管理"NDB存储服务器".

基本概念

"NDB"是一种"内存中"的存储引擎,它具有可用性高和数据一致性好的特点.

下面介绍mysql cluster 节点时,它表示进程.在单台计算机上可以有任意数目的节点.

管理节点(MGM):这类节点的作用是管理mysql cluster内的其他节点,如配置文件和cluster 日志,启动并停止节点,运行备份等.cluster中的每个节点从管理服务器上检索配置数据,并请求管理服务器所在位置的方式.当数据节点内出现新的事件时,节点将关于这类事件的信息传输到管理服务器上,然后,又将这类信息写入cluster日志。由于这类节点负责管理其他节点的配置,所以应在启动其他节点之前首先启动这类节点.MGM节点是用命令"ndb_mgmd"来启动
数据节点(NDB):这类节点用于保存cluster的数据.数据节点的数目与副本的数目相关,是片段的倍数.假设有2个副本,每个副本有2个片段,那么就有4个数据节点.不过没有必要设置多个副本.数据节点是用命令"ndbd"来启动的.
SQL节点：这是用来访问cluster数据的节点.对于MYSQL cluster来说,客户端节点是使用NDB cluster存储引擎的传统Mysql服务器.通常,sql节点使用将"ndb cluster"添加到"my.cnf"后使用"mysqld" 启动
此外,可以有任意数目的cluster客户端进程或应该程序.它们分为两种类型,即标准mysql客户端和管理客户端.
标准mysql客户端:能够从php,perl,c,c++,java,python,ruby等编写的现有mysql应用程序上访问mysql cluster
管理客户端:这类客户端与管理服务器相连,并提供了启动和停止节点,启动和停止消息跟踪,显示节点版本和状态,启动和停止备份等命令.

以下是mysql cluster 架构示意图:

\

二、案例分析

系统环境：

操作系统： RedHat EL6（Linux mysrv 2.6.32-358.el6.x86_64）

Cluster Soft: mysql-cluster-gpl-7.2.8.tar.gz（源码包）

 

一般在企业应用中mysql cluster最少要建立5个node，一个管理节点，2个ndb数据节点，2个sql节点；本案例为测试环境，可以将管理节点、数据节点及SQL节点放在同一台机器上。

如图所示：

\

 

管理节点1： 192.168.8.249/24 数据节点1： 192.168.8.249/24 数据节点2: 192.168.8.245/24 SQL节点1: 192.168.8.249/24 SQL节点2: 192.168.8.245/24

 

1、配置系统网络环境

[root@mysrv ~]# cat /etc/hosts
127.0.0.1 localhost
192.168.8.245 rh6.cuug.net rh6

192.168.8.249 mysrv

2、安装mysql-cluster 软件

在两个node上都需要安装：

[root@rh6 oracle]# ls -l

-rwxr--r-- 1 oracle oinstall 28540933 Aug 4 16:09 mysql-cluster-gpl-7.2.8.tar.gz

将软件解压到/usr/local下：

[root@mysrv oracle]# tar zxvf mysql-cluster-gpl-7.2.8.tar.gz -C /usr/local

安装需要cmake工具，如果是redhat el5需要下载安装；如果是redhat el6，则系统自带。

cmake install (rh55)：
[root@rh6 local]#tar zxvf cmake-3.3.0-Linux-i386.tar.gz
[root@rh6 local]# cp -r /home/oracle/cmake-3.3.0-Linux-i386 /usr/local
[root@rh6 local]# mv /home/oracle/cmake-3.3.0-Linux-i386 cmake
[root@rh6 local]# cat /etc/profile
export PATH=$PATH:/usr/local/cmake/bin
[root@rh6 local]#ln -s /usr/local/cmake/bin/cmake /bin/cmake

安装clustre 软件：

1）通过cmake测试编译环境

[root@rh6 mysql]#mkdir -p /usr/local/mysql
[root@rh6 mysql]#mkdir -p /data/ndbdata/

[root@rh6 mysql-cluster-gpl-7.2.8]# cmake

-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \

-DMYSQL_UNIX_ADDR=/tmp/mysql-cluster.sock \
-DMYSQL_DATADIR=/data/ndbdata \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DEXTRA_CHARSETS=all \
-DWITH_EMBEDDED_SERVER=0 \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_MEMORY_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITH_NDBCLUSTER_STORAGE_ENGINE=1 \
-DMYSQL_TCP_PORT=3306 \
-DENABLED_LOCAL_INFILE=1 \
-DMYSQL_USER=mysql \
-DWITH_DEBUG=0 \
-DWITH_SSL=yes\
-DWITH_NDB_JAVA=OFF

....
-- The CXX compiler identification is GNU
-- Check for working C compiler: /usr/bin/gcc
-- Check for working C compiler: /usr/bin/gcc -- works
-- Detecting C compiler ABI info
-- Detecting C compiler ABI info - done
-- Check for working CXX compiler: /usr/bin/c++
-- Check for working CXX compiler: /usr/bin/c++ -- works
-- Detecting CXX compiler ABI info
-- Detecting CXX compiler ABI info - done
-- Looking for SHM_HUGETLB
-- Looking for SHM_HUGETLB - found
-- MySQL 5.5.27-ndb-7.2.8
-- Looking for sys/types.h
-- Looking for sys/types.h - found
-- Looking for stdint.h
-- Looking for stdint.h - found
-- Looking for stddef.h
-- Looking for stddef.h - found
-- Check size of void *
-- Check size of void * - done
-- Packaging as: mysql-cluster-7.2.8-Linux-x86_64
-- Looking for floor
-- Looking for floor - not found
-- Looking for floor in m
-- Looking for floor in m - found
-- Looking for gethostbyname_r
-- Looking for gethostbyname_r - found
-- Looking for bind
-- Looking for bind - found
-- Looking for crypt
-- Looking for crypt - not found
......

2）make 编译
[root@rh6 mysql-cluster-gpl-7.2.8]# make

......
99%] Building CXX object sql/CMakeFiles/sql.dir/sql_signal.cc.o
[ 99%] Building CXX object sql/CMakeFiles/sql.dir/rpl_handler.cc.o
[ 99%] Building CXX object sql/CMakeFiles/sql.dir/mdl.cc.o
[ 99%] Building CXX object sql/CMakeFiles/sql.dir/sql_admin.cc.o
[ 99%] Building CXX object sql/CMakeFiles/sql.dir/transaction.cc.o
[ 99%] Building CXX object sql/CMakeFiles/sql.dir/sys_vars.cc.o
[ 99%] Building CXX object sql/CMakeFiles/sql.dir/sql_truncate.cc.o
[ 99%] Building CXX object sql/CMakeFiles/sql.dir/datadict.cc.o
[100%] Building CXX object sql/CMakeFiles/sql.dir/sql_reload.cc.o
[100%] Building CXX object sql/CMakeFiles/sql.dir/sql_yacc.cc.o
......
3）make install安装软件
[root@rh6 mysql-cluster-gpl-7.2.8]#make install
[ 0%] Built target INFO_BIN
[ 0%] Built target INFO_SRC
[ 0%] Built target abi_check
[ 3%] Built target edit
[ 6%] Built target strings
[ 14%] Built target mysys
[ 15%] Built target dbug
[ 16%] Built target comp_err
[ 16%] Built target GenError
[ 16%] Built target federated
[ 16%] Built target csv
[ 16%] Built target mysqlservices
[ 16%] Built target archive
[ 16%] Built target example
[ 17%] Built target ndbportlib
[ 24%] Built target ndbsignaldata
[ 24%] Built target ndblogger
[ 27%] Built target ndbgeneral
[ 27%] Built target ndbtrace
[ 28%] Built target ndbtransport
[ 28%] Built target ndbmgmcommon
[ 28%] Built target ndbconf
[ 28%] Built target ndbmgmapi
[ 32%] Built target ndbapi
......

----至此，软件安装成功！

三、配置mysql cluster

[root@rh6 mysql]#chown -R mysql.mysql /usr/local/mysql
[root@rh6 mysql]#chown -R mysql.mysql /data/ndbdata/

初始化mysql server：

[root@rh6 mysql]#/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --user=mysql --datadir=/data/ndbdata

1、Cluster 配置：(管理节点和数据节点）

[root@mysrv mysql]# cd /usr/local/mysql/bin
[root@mysrv bin]# cp ndb_mgm* /usr/local/bin
1）建立管理节点配置文件
[root@mysrv bin]# mkdir /etc/ndbdata
[root@mysrv ~]# mkdir /data/backup

[root@mysrv ~]# chown -R mysql:mysql /data/backup

[root@mysrv bin]# cat /etc/ndbdata/config.ini 
<strong>[NDBD DEFAULT]</strong>
<span style="color: rgb(0, 130, 0); font-family: Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace; font-size: 13px; line-height: 13.75px; white-space: pre;">#定义在Cluster环境中相同数据的份数，最大为4</span>
NoOfReplicas=2
<span style="color: rgb(0, 130, 0); font-family: Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace; font-size: 13px; line-height: 13.75px; white-space: pre;">#分配的数据内存大小，根据本机服务器内存适量来分配,否则会连接失败</span>
DataMemory=128M
<span style="color: rgb(0, 130, 0); font-family: Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace; font-size: 13px; line-height: 13.75px; white-space: pre;">#设定用于存放索引（非主键）数据的内存段大小</span>
IndexMemory=32M
 
<strong>[NDB_MGMD]</strong>
nodeid=1
hostname=192.168.8.249
datadir=/data/ndbdata
 
<strong>[NDBD]</strong>
nodeid=2
hostname=192.168.8.249
datadir=/data/ndbdata
backupdatadir=/data/backup
 
<strong>[NDBD]</strong>
nodeid=3
hostname=192.168.8.245
datadir=/data/ndbdata
backupdatadir=/data/backup
 
<strong>[MYSQLD]</strong>
nodeid=4
hostname=192.168.8.249
 
<strong>[MYSQLD]</strong>
nodeid=5
hostname=192.168.8.245

<strong>[MYSQLD]</strong>

在另外的node上也建立config.ini（rh6）
[root@rh6 ~]#mkdir /etc/ndbdata/
[root@rh6 ~]#mkdir /data/backup
[root@rh6 ~]#chown -R mysql.mysql /data/backup
[root@rh6 ~]#ls /etc/ndbdata/
config.ini
[root@rh6 ~]#cat /etc/ndbdata/config.ini 
<strong>[NDBD DEFAULT]</strong>

NoOfReplicas=2
DataMemory=128M
IndexMemory=32M
 
<strong>[NDB_MGMD]</strong>
nodeid=1
hostname=192.168.8.249
datadir=/data/ndbdata
 
<strong>[NDBD]</strong>
nodeid=2
hostname=192.168.8.249
datadir=/data/ndbdata
backupdatadir=/data/backup
 
<strong>[NDBD]</strong>
nodeid=3
hostname=192.168.8.245
datadir=/data/ndbdata
backupdatadir=/data/backup
 
<strong>[MYSQLD]</strong>
nodeid=4
hostname=192.168.8.249
 
<strong>[MYSQLD]</strong>
nodeid=5
hostname=192.168.8.245

<strong>[MYSQLD]</strong>

2）配置SQL节点（mysrv和rh6）

[root@mysrv bin]# cat /etc/my.cnf
<strong>[mysql_cluster]</strong>
ndb-connectstring=192.168.8.249

<strong>[mysqld]</strong>
datadir = /data/ndbdata
ndbcluster
ndb-connectstring=192.168.8.249

四、启动Cluster服务

1)先启动管理节点服务器.

2)启动NDB存储节点服务器.

3)启动SQL节点服务器.

 

1、启动管理节点

[root@mysrv bin]# /usr/local/mysql/bin/ndb_mgmd -f /etc/ndbdata/config.ini
MySQL Cluster Management Server mysql-5.5.27 ndb-7.2.8
2015-08-06 17:25:40 [MgmtSrvr] INFO -- The default config directory '/usr/local/mysql/mysql-cluster' does not exist. Trying to create it...
2015-08-06 17:25:40 [MgmtSrvr] INFO -- Sucessfully created config directory
2015-08-06 17:25:40 [MgmtSrvr] WARNING -- at line 35: Cluster configuration warning:
arbitrator with id 1 and db node with id 2 on same host 192.168.8.249
Running arbitrator on the same host as a database node may
cause complete cluster shutdown in case of host failure.

2、启动数据节点（初次启动需用 initial参数）
[root@mysrv bin]# /usr/local/mysql/bin/ndbd --initial
2015-08-06 17:26:58 [ndbd] INFO -- Angel connected to '192.168.8.249:1186'

2015-08-06 17:26:58 [ndbd] INFO -- Angel allocated nodeid: 2

管理节点启动后，会在：1186端口监听：

[root@mysrv bin]# netstat -an |grep :1186
tcp 0 0 0.0.0.0:1186 0.0.0.0:* LISTEN
tcp 0 0 127.0.0.1:38664 127.0.0.1:1186 ESTABLISHED
tcp 0 0 192.168.8.249:1186 192.168.8.249:39603 ESTABLISHED
tcp 0 0 127.0.0.1:1186 127.0.0.1:38664 ESTABLISHED
tcp 0 0 192.168.8.249:39602 192.168.8.249:1186 ESTABLISHED
tcp 0 0 192.168.8.249:1186 192.168.8.249:39602 ESTABLISHED
tcp 0 0 192.168.8.249:39603 192.168.8.249:1186 ESTABLISHED

启动另一个node的ndb：
[root@rh6 oracle]#/usr/local/mysql/bin/ndbd --initial
2015-08-06 17:27:36 [ndbd] INFO -- Angel connected to '192.168.8.249:1186'
2015-08-06 17:27:36 [ndbd] INFO -- Angel allocated nodeid: 3

查看cluster的启动状态：

[root@mysrv bin]# ndb_mgm -e show
Connected to Management Server at: 192.168.8.249:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 @192.168.8.249 (mysql-5.5.27 ndb-7.2.8, starting, Nodegroup: 0) ；；其中一个node已经连接到管理节点
id=3 (not connected, accepting connect from 192.168.8.245) ；；另一个node还未连接上

[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.8.249 (mysql-5.5.27 ndb-7.2.8)

[mysqld(API)] 3 node(s)
id=4 (not connected, accepting connect from 192.168.8.249)
id=5 (not connected, accepting connect from 192.168.8.245)
id=6 (not connected, accepting connect from any host)

3、启动SQL节点：

[root@mysrv bin]# /usr/local/mysql/bin/mysqld_safe /etc/my.cnf &

启动失败！

[root@mysrv bin]# cat /data/ndbdata/mysrv.err
150806 17:42:54 InnoDB: Waiting for the background threads to start
150806 17:42:55 InnoDB: 1.1.8 started; log sequence number 1595675
150806 17:42:55 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
150806 17:42:55 [Note] - '0.0.0.0' resolves to '0.0.0.0';
150806 17:42:55 [Note] Server socket created on IP: '0.0.0.0'.
150806 17:42:55 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist
150806 17:42:55 mysqld_safe mysqld from pid file /data/ndbdata/mysrv.pid ended
150806 17:44:38 mysqld_safe Starting mysqld daemon with databases from /data/ndbdata
150806 17:44:38 [Note] Plugin 'FEDERATED' is disabled.
/usr/local/mysql/bin/mysqld: Table 'mysql.plugin' doesn't exist
150806 17:44:38 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
150806 17:45:13 [Warning] NDB: server id set to zero - changes logged to bin log with server id zero will be logged with another server id by slave mysqlds
150806 17:45:13 [Note] Starting Cluster Binlog Thread
150806 17:45:13 InnoDB: The InnoDB memory heap is disabled
150806 17:45:13 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150806 17:45:13 InnoDB: Compressed tables use zlib 1.2.3
150806 17:45:13 InnoDB: Using Linux native AIO
150806 17:45:13 InnoDB: Initializing buffer pool, size = 128.0M
150806 17:45:13 InnoDB: Completed initialization of buffer pool
150806 17:45:13 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 49439
150806 17:45:13 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 1595675
150806 17:45:13 InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
150806 17:45:13 InnoDB: Waiting for the background threads to start
150806 17:45:14 InnoDB: 1.1.8 started; log sequence number 1595675
150806 17:45:14 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
150806 17:45:14 [Note] - '0.0.0.0' resolves to '0.0.0.0';
150806 17:45:14 [Note] Server socket created on IP: '0.0.0.0'.
150806 17:45:14 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist
150806 17:45:14 mysqld_safe mysqld from pid file /data/ndbdata/mysrv.pid ended
150806 17:45:57 mysqld_safe Starting mysqld daemon with databases from /data/ndbdata
150806 17:45:57 [Note] Plugin 'FEDERATED' is disabled.
[root@mysrv bin]# netstat -an |grep :3306
[root@mysrv bin]#

重启操作系统后。。。

第二次启动mysql-cluster：

1、启动cluster manager：
[root@mysrv ~]# /usr/local/mysql/bin/ndb_mgmd -f /etc/ndbdata/config.ini
MySQL Cluster Management Server mysql-5.5.27 ndb-7.2.8

2、启动ndb：
[root@mysrv ~]# /usr/local/mysql/bin/ndbd
2015-08-07 09:44:34 [ndbd] INFO -- Angel connected to '192.168.8.249:1186'
2015-08-07 09:44:34 [ndbd] INFO -- Angel allocated nodeid: 2

另一node：
[root@rh6 ~]# /usr/local/mysql/bin/ndbd
2015-08-07 09:51:52 [ndbd] INFO -- Angel connected to '192.168.8.249:1186'
2015-08-07 09:51:52 [ndbd] INFO -- Angel allocated nodeid: 3

查看cluster状态信息：
[root@mysrv ~]# /usr/local/mysql/bin/ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: 192.168.8.249:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 @192.168.8.249 (mysql-5.5.27 ndb-7.2.8, starting, Nodegroup: 0)
id=3 (not connected, accepting connect from 192.168.8.245)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.8.249 (mysql-5.5.27 ndb-7.2.8)

[mysqld(API)] 3 node(s)
id=4 (not connected, accepting connect from 192.168.8.249)
id=5 (not connected, accepting connect from 192.168.8.245)
id=6 (not connected, accepting connect from any host)

ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 @192.168.8.249 (mysql-5.5.27 ndb-7.2.8, starting, Nodegroup: 0, Master)
id=3 @192.168.8.245 (mysql-5.5.27 ndb-7.2.8, starting, Nodegroup: 0)

----数据节点连接成功 ！

[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.8.249 (mysql-5.5.27 ndb-7.2.8)

[mysqld(API)] 3 node(s)
id=4 (not connected, accepting connect from 192.168.8.249)
id=5 (not connected, accepting connect from 192.168.8.245)
id=6 (not connected, accepting connect from any host)

ndb_mgm> Node 2: Started (version 7.2.8)
Node 3: Started (version 7.2.8)

3、启动SQL节点：
[root@mysrv ~]# cd /usr/local/mysql
[root@mysrv mysql]# cd bin
[root@mysrv bin]# ./mysqld_safe --user=mysql &
[1] 2619
[root@mysrv bin]# 150807 10:11:20 mysqld_safe Logging to '/data/ndbdata/mysrv.err'.
150807 10:11:20 mysqld_safe Starting mysqld daemon with databases from /data/ndbdata

[root@mysrv bin]# tail /data/ndbdata/mysrv.err
150807 10:11:25 [Note] NDB: Cleaning stray tables from database &#39;ndb_2_fs&#39;
150807 10:11:25 [Note] NDB: Cleaning stray tables from database &#39;ndbinfo&#39;
150807 10:11:25 [Note] NDB: Cleaning stray tables from database &#39;performance_schema&#39;
150807 10:11:25 [Note] NDB: Cleaning stray tables from database &#39;test&#39;
150807 10:11:25 [Note] NDB: missing frm for mysql.ndb_index_stat_sample, discovering...
150807 10:11:25 [Note] NDB: missing frm for mysql.ndb_index_stat_head, discovering...
2015-08-07 10:11:25 [NdbApi] INFO     -- Flushing incomplete GCI:s < 559/10
2015-08-07 10:11:25 [NdbApi] INFO     -- Flushing incomplete GCI:s < 559/10
150807 10:11:25 [Note] NDB Binlog: starting log at epoch 559/10
150807 10:11:25 [Note] NDB Binlog: ndb tables writable
[root@mysrv bin]# netstat -an |grep 3306

tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN

[root@mysrv bin]# ndb_mgm -e show
Connected to Management Server at: 192.168.8.249:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 @192.168.8.249 (mysql-5.5.27 ndb-7.2.8, Nodegroup: 0, Master)
id=3 @192.168.8.245 (mysql-5.5.27 ndb-7.2.8, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.8.249 (mysql-5.5.27 ndb-7.2.8)

[mysqld(API)] 3 node(s)
id=4 @192.168.8.249 (mysql-5.5.27 ndb-7.2.8) ；；一个sql node连接到管理节点
id=5 (not connected, accepting connect from 192.168.8.245)
id=6 (not connected, accepting connect from any host)

启动另外的sql node：

初始化mysql server：

[root@rh6 mysql]#/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --user=mysql --datadir=/data/ndbdata

[root@rh6 mysql]#ls /data/ndbdata/
mysql mysql-bin.index ndb_3_out.log ndb_3_trace.log.2 ndb_3_trace.log.next test
mysql-bin.000001 ndb_3_error.log ndb_3.pid ndb_3_trace.log.3 ndbinfo
mysql-bin.000002 ndb_3_fs ndb_3_trace.log.1 ndb_3_trace.log.4 performance_schema
[root@rh6 mysql]#cd bin
[root@rh6 bin]#./mysqld_safe --user=mysql &
[1] 2679
[root@rh6 bin]#150807 10:14:43 mysqld_safe Logging to '/data/ndbdata/rh6.cuug.net.err'.
150807 10:14:44 mysqld_safe Starting mysqld daemon with databases from /data/ndbdata

[root@rh6 bin]#netstat -an |grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN
unix 3 [ ] STREAM CONNECTED 13306 @/tmp/.X11-unix/X0

查看管理节点：
[root@mysrv bin]# ndb_mgm -e show
Connected to Management Server at: 192.168.8.249:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 @192.168.8.249 (mysql-5.5.27 ndb-7.2.8, Nodegroup: 0, Master)
id=3 @192.168.8.245 (mysql-5.5.27 ndb-7.2.8, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.8.249 (mysql-5.5.27 ndb-7.2.8)

[mysqld(API)] 3 node(s)
id=4 @192.168.8.249 (mysql-5.5.27 ndb-7.2.8)
id=5 @192.168.8.245 (mysql-5.5.27 ndb-7.2.8)

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

----sql node已经连接成功！

五、测试mysql cluster

 

注意: 与没有使用Cluster的Mysql相比,在mysql cluster内操作数据的方式没有太大的区别.操作时注意 1)表必须用engine=NDB或engine=NDBCLUSTER选项创建

 

 

2)每个NDB表必须有一个主键.如果在创建表时用户未定义主键,NDB Cluster存储引擎会自动生成隐含的主键.

 

该隐含键也将占用空间,就像任何其他的表索引一样.由于没有足够的内存来容纳这些自动创建的键,所以很容易出现问题.
1、设置客户端访问mysql server：

[root@mysrv bin]# ./mysqladmin -u root password 'oracle'
[root@mysrv bin]# ./mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.27-ndb-7.2.8 Source distribution
Copyright (c) 2000, 2011, 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 |
| ndb_2_fs |
| ndbinfo |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.02 sec)

在其中一个节点建立table，在另外的节点都能访问：
mysql> use test;
Database changed
mysql> show tables;
Empty set (0.01 sec)

mysql> create table t1 (id int primary key,
-> name varchar(10)) engine=ndb;
Query OK, 0 rows affected (0.71 sec)

mysql> show create table t1 \G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8
1 row in set (0.02 sec)

插入数据：
mysql> insert into t1 values (10,'tom');
Query OK, 1 row affected (0.04 sec)
mysql> insert into t1 values (20,'rose');
Query OK, 1 row affected (0.03 sec)
mysql> insert into t1 values (30,'jerry');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+----+-------+
| id | name |
+----+-------+
| 10 | tom |
| 20 | rose |
| 30 | jerry |
+----+-------+
3 rows in set (0.00 sec)

mysql> explain select * from t1 where id =10;
+----+-------------+-------+--------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | t1 | eq_ref | PRIMARY | PRIMARY | 4 | const | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

在另外的sql节点访问：

[root@rh6 bin]#./mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.27-ndb-7.2.8 Source distribution
Copyright (c) 2000, 2011, 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 |
| ndb_3_fs |
| ndbinfo |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.08 sec)

mysql> use test;
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_test |
+----------------+
| t1 |
+----------------+
1 row in set (0.03 sec)
----可以看到，在另外的node上有t1表
mysql> select * from t1;
+----+-------+
| id | name |
+----+-------+
| 30 | jerry |
| 10 | tom |
| 20 | rose |
+----+-------+
3 rows in set (0.05 sec)

mysql> explain select * from t1 where id =10;
+----+-------------+-------+--------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | t1 | eq_ref | PRIMARY | PRIMARY | 4 | const | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+-------+------+-------+
1 row in set (0.07 sec)

在当前node上插入数据：
mysql> insert into t1 values (40,'john');
Query OK, 1 row affected (0.01 sec)
mysql> insert into t1 values (50,'ellen');
Query OK, 1 row affected (0.02 sec)

mysql> select * from t1;
+----+-------+
| id | name |
+----+-------+
| 40 | john |
| 30 | jerry |
| 10 | tom |
| 20 | rose |
| 50 | ellen |
+----+-------+
5 rows in set (0.00 sec)

另一个节点查询：

[root@mysrv bin]# ./mysql -u root -p

Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.27-ndb-7.2.8 Source distribution
Copyright (c) 2000, 2011, 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> use test;
Database changed

mysql> select * from t1;
+----+-------+
| id | name |
+----+-------+
| 10 | tom |
| 20 | rose |
| 50 | ellen |
| 40 | john |
| 30 | jerry |
+----+-------+
5 rows in set (0.01 sec)
---可以访问新插入的数据

测试2：关闭一个节点，在另外的节点创建table；被关闭节点启动后，自动同步tables


关闭node2（rh6）：
[root@rh6 bin]#./mysqladmin -u root -p shutdown
Enter password:
150807 10:44:46 mysqld_safe mysqld from pid file /data/ndbdata/rh6.cuug.net.pid ended
[1]+ Done ./mysqld_safe --user=mysql
[root@rh6 bin]#netstat -an |grep :3306

在node1 上建立新的table：
[root@mysrv bin]# ./mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.27-ndb-7.2.8 Source distribution
Copyright (c) 2000, 2011, 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> use test;
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> create table t2 engine=ndb as select * from t1;
Query OK, 5 rows affected (0.59 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
| t2 |
+----------------+
2 rows in set (0.01 sec)

mysql> show create table t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`id` int(11) NOT NULL,
`name` varchar(10) DEFAULT NULL
) ENGINE=ndbcluster DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select * from t2;
+----+-------+
| id | name |
+----+-------+
| 50 | ellen |
| 30 | jerry |
| 20 | rose |
| 10 | tom |
| 40 | john |
+----+-------+
5 rows in set (0.00 sec)

然后启动node2，新建的table自动同步到node2：

[root@rh6 mysql]#cd support-files/
[root@rh6 support-files]#ls
binary-configure config.medium.ini magic my-innodb-heavy-4G.cnf my-medium.cnf mysqld_multi.server mysql.server
config.huge.ini config.small.ini my-huge.cnf my-large.cnf my-small.cnf mysql-log-rotate ndb-config-2-node.ini
[root@rh6 support-files]#cp mysql.server /etc/rc.d/init.d/mysql
[root@rh6 support-files]#service mysql start
Starting MySQL.....[ OK ]
[root@rh6 support-files]#netstat -an |grep :3306

tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN

[root@rh6 support-files]#mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.27-ndb-7.2.8 Source distribution
Copyright (c) 2000, 2011, 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> use test;
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_test |
+----------------+
| t1 |
| t2 |
+----------------+
2 rows in set (0.01 sec)
mysql> select * from t2;
+----+-------+
| id | name |
+----+-------+
| 10 | tom |
| 40 | john |
| 50 | ellen |
| 30 | jerry |
| 20 | rose |
+----+-------+
5 rows in set (0.00 sec)

mysql> show create table t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`id` int(11) NOT NULL,
`name` varchar(10) DEFAULT NULL
) ENGINE=ndbcluster DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

----表同步成功 ！
----至此，mysql cluster初步构建成功 ！

六、访问ndb cluster元数据

mysql> use ndbinfo;
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_ndbinfo |
+----------------------+
| blocks |
| cluster_operations |
| cluster_transactions |
| config_params |
| counters |
| diskpagebuffer |
| logbuffers |
| logspaces |
| memoryusage |
| nodes |
| resources |
| server_operations |
| server_transactions |
| threadblocks |
| threadstat |
| transporters |
+----------------------+
16 rows in set (0.00 sec)

mysql> select * from ndbinfo.memoryusage;
+---------+--------------+---------+------------+-----------+-------------+
| node_id | memory_type | used | used_pages | total | total_pages |
+---------+--------------+---------+------------+-----------+-------------+
| 2 | Data memory | 1015808 | 31 | 134217728 | 4096 |
| 2 | Index memory | 204800 | 25 | 33816576 | 4128 |
| 3 | Data memory | 1015808 | 31 | 134217728 | 4096 |
| 3 | Index memory | 204800 | 25 | 33816576 | 4128 |
+---------+--------------+---------+------------+-----------+-------------+
4 rows in set (0.11 sec)

mysql> desc nodes;
+-------------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+---------------------+------+-----+---------+-------+
| node_id | int(10) unsigned | YES | | NULL | |
| uptime | bigint(20) unsigned | YES | | NULL | |
| status | varchar(10) | YES | | NULL | |
| start_phase | int(10) unsigned | YES | | NULL | |
| config_generation | int(10) unsigned | YES | | NULL | |
+-------------------+---------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

---注意:使用量写满会访问不了,这时需要调整配置DataMemory,IndexMemory参数.各配置文件都需调整重启生效.
mysql> select node_id ,status ,config_generation from nodes;
+---------+---------+-------------------+
| node_id | status | config_generation |
+---------+---------+-------------------+
| 2 | STARTED | 1 |
| 3 | STARTED | 1 |
+---------+---------+-------------------+
2 rows in set (0.01 sec)


七、关闭mysql cluster

1、关闭mysql cluster manager：

[root@mysrv ~]# /usr/local/mysql/bin/ndb_mgm -e shutdown

2、关闭mysql cluster sql node：
[root@mysrv ~]# mysqladmin -u root -p shutdown

Stellungnahme：
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Vorheriger Artikel：MySQL5.6初学者入门教程_MySQLNächster Artikel：mysql嵌套查询_MySQL

In Verbindung stehende Artikel

Mehr sehen