MySQLStudy之
| 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/hosts127.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-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/ndbdata1、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.iniMySQL 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 :1186tcp 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 showConnected 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.err150806 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/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[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 showConnected 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 -pEnter 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; 七、关闭mysql cluster 1、关闭mysql cluster manager： [root@mysrv ~]# /usr/local/mysql/bin/ndb_mgm -e shutdown2、关闭mysql cluster sql node： [root@mysrv ~]# mysqladmin -u root -p shutdown |
- 上一篇：MySQL5.6初学者入门教程_MySQL
- 下一篇：mysql嵌套查询_MySQL