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
[ndbd default]#定义在cluster环境中相同数据的份数，最大为4noofreplicas=2
#分配的数据内存大小，根据本机服务器内存适量来分配,否则会连接失败datamemory=128m
#设定用于存放索引（非主键）数据的内存段大小indexmemory=32m [ndb_mgmd]nodeid=1hostname=192.168.8.249datadir=/data/ndbdata [ndbd]nodeid=2hostname=192.168.8.249datadir=/data/ndbdatabackupdatadir=/data/backup [ndbd]nodeid=3hostname=192.168.8.245datadir=/data/ndbdatabackupdatadir=/data/backup [mysqld]nodeid=4hostname=192.168.8.249 [mysqld]nodeid=5hostname=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=2datamemory=128mindexmemory=32m [ndb_mgmd]nodeid=1hostname=192.168.8.249datadir=/data/ndbdata [ndbd]nodeid=2hostname=192.168.8.249datadir=/data/ndbdatabackupdatadir=/data/backup [ndbd]nodeid=3hostname=192.168.8.245datadir=/data/ndbdatabackupdatadir=/data/backup [mysqld]nodeid=4hostname=192.168.8.249 [mysqld]nodeid=5hostname=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/ndbdatandbclusterndb-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 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