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

朱雀 2022-08-03 12:56 186阅读 0赞

**一、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 架构示意图:**

![Center][]

**二、案例分析**

**系统环境：**

**  操作系统：     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节点放在同一台机器上。**

**如图所示：**

**![Center 1][]**

管理节点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 **

[NDBD DEFAULT]
 #定义在Cluster环境中相同数据的份数，最大为4
 NoOfReplicas=2

#设定用于存放索引（非主键）数据的内存段大小
 IndexMemory=32M
 
 [NDB_MGMD]
 nodeid=1
 hostname=192.168.8.249
 datadir=/data/ndbdata
 
 [NDBD]
 nodeid=2
 hostname=192.168.8.249
 datadir=/data/ndbdata
 backupdatadir=/data/backup
 
 [NDBD]
 nodeid=3
 hostname=192.168.8.245
 datadir=/data/ndbdata
 backupdatadir=/data/backup
 
 [MYSQLD]
 nodeid=4
 hostname=192.168.8.249
 
 [MYSQLD]
 nodeid=5
 hostname=192.168.8.245
 
 [MYSQLD]

**在另外的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 **

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

**2）配置SQL节点（mysrv和rh6）  
  
\[root@mysrv bin\]\# cat /etc/my.cnf**

[mysql_cluster]
 ndb-connectstring=192.168.8.249
 
 [mysqld]
 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 '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/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 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**

[Center]: /images/20220731/c2f75bd8160849ac9f0a327735027936.png
[Center 1]: /images/20220731/9fa9f12e3f884fff944dee62d9251a10.png