当前位置: 代码迷 >> MySQL >> MySQL Study之-MySQL Cluster(集群)构建
  详细解决方案

MySQL Study之-MySQL Cluster(集群)构建

热度:147   发布时间:2016-05-05 16:49:39.0
MySQL Study之--MySQL Cluster(集群)构建
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、配置系统网络环境

[[email protected] ~]# 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上都需要安装:

[[email protected] oracle]# ls -l

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

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

[[email protected] oracle]# tar zxvf mysql-cluster-gpl-7.2.8.tar.gz  -C /usr/local

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

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

安装clustre 软件:

1)通过cmake测试编译环境

[[email protected] mysql]#mkdir -p  /usr/local/mysql
[[email protected] mysql]#mkdir -p  /data/ndbdata/

[[email protected] 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 编译
[[email protected] 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安装软件
[[email protected] 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

[[email protected] mysql]#chown -R mysql.mysql /usr/local/mysql
[[email protected] mysql]#chown -R mysql.mysql /data/ndbdata/

初始化mysql server:

[[email protected] mysql]#/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --user=mysql --datadir=/data/ndbdata

1、Cluster 配置:(管理节点和数据节点)

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

[[email protected] ~]# chown -R mysql:mysql /data/backup


[[email protected] 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=1hostname=192.168.8.249datadir=/data/ndbdata <strong>[NDBD]</strong>nodeid=2hostname=192.168.8.249datadir=/data/ndbdatabackupdatadir=/data/backup <strong>[NDBD]</strong>nodeid=3hostname=192.168.8.245datadir=/data/ndbdatabackupdatadir=/data/backup <strong>[MYSQLD]</strong>nodeid=4hostname=192.168.8.249 <strong>[MYSQLD]</strong>nodeid=5hostname=192.168.8.245<strong>[MYSQLD]</strong>

在另外的node上也建立config.ini(rh6)
[[email protected] ~]#mkdir /etc/ndbdata/
[[email protected] ~]#mkdir /data/backup
[[email protected] ~]#chown -R mysql.mysql /data/backup
[[email protected] ~]#ls /etc/ndbdata/
config.ini
[[email protected] ~]#cat /etc/ndbdata/config.ini 

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

2)配置SQL节点(mysrv和rh6)

[[email protected] bin]# cat /etc/my.cnf

<strong>[mysql_cluster]</strong>ndb-connectstring=192.168.8.249<strong>[mysqld]</strong>datadir = /data/ndbdatandbclusterndb-connectstring=192.168.8.249

四、启动Cluster服务

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

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

3)启动SQL节点服务器.


1、启动管理节点

[[email protected] 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参数)
[[email protected] 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端口监听:

[[email protected] 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:
[[email protected] 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的启动状态:

[[email protected] 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节点:

[[email protected] bin]# /usr/local/mysql/bin/mysqld_safe    /etc/my.cnf &

启动失败!

[[email protected] 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.
[[email protected] bin]# netstat -an |grep :3306
[[email protected] bin]# 

重启操作系统后。。。

第二次启动mysql-cluster:

1、启动cluster manager:
[[email protected] ~]# /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:
[[email protected] ~]#  /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:
[[email protected] ~]# /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状态信息:
[[email protected] ~]#  /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节点:
[[email protected] ~]# cd /usr/local/mysql
[[email protected] mysql]# cd bin
[[email protected] bin]# ./mysqld_safe --user=mysql &

[1] 2619
[[email protected] 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

[[email protected] bin]# tail /data/ndbdata/mysrv.err
150807 10:11:25 [Note] NDB: Cleaning stray tables from database 'ndb_2_fs'150807 10:11:25 [Note] NDB: Cleaning stray tables from database 'ndbinfo'150807 10:11:25 [Note] NDB: Cleaning stray tables from database 'performance_schema'150807 10:11:25 [Note] NDB: Cleaning stray tables from database 'test'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/102015-08-07 10:11:25 [NdbApi] INFO     -- Flushing incomplete GCI:s < 559/10150807 10:11:25 [Note] NDB Binlog: starting log at epoch 559/10150807 10:11:25 [Note] NDB Binlog: ndb tables writable
[[email protected] bin]# netstat -an |grep 3306

tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      

[[email protected] 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:

[[email protected] mysql]#/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --user=mysql --datadir=/data/ndbdata

[[email protected] 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
[[email protected] mysql]#cd bin
[[email protected] bin]#./mysqld_safe --user=mysql &

[1] 2679
[[email protected] 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

[[email protected] 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

查看管理节点:
[[email protected] 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:

[[email protected] bin]# ./mysqladmin -u root password  'oracle'
[[email protected] 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节点访问:

[[email protected] 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)

另一个节点查询:

[[email protected] 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):
[[email protected] 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
[[email protected] bin]#netstat -an |grep :3306

在node1 上建立新的table:
[[email protected] 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:

[[email protected] mysql]#cd support-files/

[[email protected] 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
[[email protected] support-files]#cp mysql.server /etc/rc.d/init.d/mysql
[[email protected] support-files]#service mysql start

Starting MySQL.....[  OK  ]
[[email protected] support-files]#netstat -an |grep :3306

tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      

[[email protected] 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:

[[email protected] ~]#  /usr/local/mysql/bin/ndb_mgm -e shutdown

2、关闭mysql cluster sql node:
[[email protected] ~]# mysqladmin -u root -p shutdown


版权声明:本文为博主原创文章,未经博主允许不得转载。

  相关解决方案