Mysql ndb cluster初试

上图为NDB的架构图

环境：
管理节点：192.168.1.32
NDB/SQL-A:192.168.1.31
NDB/SQL-B:192.168.1.35

Mysql Cluster安装方法大概如下：
软件可以采用rpm包、bin包或源码的方式。

本次采用的是bin的方式安装。

一、SQL节点的安装配置
1、增加mysql用户或组
shell> groupadd mysql
shell> useradd -g mysql mysql

2、下载相关的软件包并进行解压，放在/usr/local下面或其它自定义目录下都可以
shell> cd /var/tmp
shell> tar -C /usr/local -xzvf mysql-cluster-gpl-7.2.17-linux2.6.tar.gz
shell> ln -s /usr/local/mysql-cluster-gpl-7.2.17-linux2.6-i686 /usr/local/mysql

3、安装数据库
shell> cd mysql
shell> scripts/mysql_install_db --user=mysql

4、设置权限
shell> chown -R root .
shell> chown -R mysql data
shell> chgrp -R mysql .

5、建立自启动脚本
shell> cp support-files/mysql.server /etc/rc.d/init.d/
shell> chmod +x /etc/rc.d/init.d/mysql.server
shell> chkconfig --add mysql.server

简而言之：
就是把tar解开，随便放在一个目录下，然后赋权并安装数据库即可。
如：
tar -xzvf mysql-cluster-gpl-7.2.17-linux2.6.tar.gz
mv mysql-cluster-gpl-7.2.17-linux2.6/*  /mysql
chown -R mysql:mysql /mysql
scripts/mysql_install_db --user=mysql --basedir=/mysql  --datadir=/mysql/data  --defaults-file=/mysql/my.cnf

 

修改/mysql/my.cnf
增加
[mysqld]
# Options for mysqld process:
ndbcluster                      # run NDB storage engine

[mysql_cluster]
# Options for data node process:
ndb-connectstring=192.168.1.32  # 这个是管理 server地址

 

 

 

二、数据节点(与mysql共用一台主机)
tar -xzvf mysql-cluster-gpl-7.2.17-linux2.6.tar.gz
cp /mysql/bin/ndbd /usr/local/bin/ndbd
cp /mysql/bin/ndbmtd /usr/local/bin/ndbmtd  
cd /usr/local/bin
chmod a+x ndb*

配置同上。
如增加如下：
[mysql_cluster]
# Options for data node process:
ndb-connectstring=192.168.1.32  # 这个是管理 server地址

三、管理节点

tar -xzvf mysql-cluster-gpl-7.2.17-linux2.6.tar.gz
mv mysql-cluster-gpl-7.2.17-linux2.6/*  /mysql
chown -R mysql:mysql /mysql
cd /mysql
cp bin/ndb_mgm* /usr/local/bin
cd /usr/local/bin
chmod a+x nd_mgmb*

mkdir /mysql/mysql-cluster
cd /mysql/mysql-cluster
vi config.ini

增加如下内容，也可以利用模板
[ndbd default]
# Options affecting ndbd processes on all data nodes:
NoOfReplicas=2    # Number of replicas
DataMemory=80M    # How much memory to allocate for data storage
IndexMemory=18M  

 

[ndb_mgmd]
# Management process options:
hostname=192.168.0.10           # 这个是管理节点的地址
datadir=/mysql/mysql-cluster    # Directory for MGM node log files

[ndbd]
# Options for data node "A":
                                # (one [ndbd] section per data node)
hostname=192.168.1.31           # Hostname or IP address
datadir=/mysql/mysql-cluster/data   # 数据节点的数据文件

[ndbd]
# Options for data node "B":
                                # (one [ndbd] section per data node)
hostname=192.168.1.35           # Hostname or IP address
datadir=/mysql/mysql-cluster/data   # 数据节点的数据文件

[mysqld]                        ##SQL节点1
# SQL node options:
hostname=192.168.1.31

[mysqld]                       ##SQL节点1
# SQL node options:
hostname=192.168.1.35

四、初始化mysql cluster        ###在管理节点上执行
shell> ndb_mgmd -f /mysql/mysql-cluster/config.ini

数据节点上分别执行
ndbd        ##可以指定配置文件。

在SQL节点上启动数据库即可。
mysqld_safe --user=mysql --defaults-file=/mysql/my.cnf &

五、测试

/mysql/ndb/bin]./ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=2    @192.168.1.31  (mysql-5.1.63 ndb-7.1.24, Nodegroup: 0)
id=3    @192.168.1.35  (mysql-5.1.63 ndb-7.1.24, Nodegroup: 0, Master)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @192.168.1.32  (mysql-5.1.63 ndb-7.1.24)

[mysqld(API)]   2 node(s)
id=4    @192.168.1.31  (mysql-5.1.63 ndb-7.1.24)
id=5    @192.168.1.35  (mysql-5.1.63 ndb-7.1.24)

ndb_mgm>

===模拟测试
把mysqld 35模拟down，信息如下：

[mysql@skfweb2 /mysql/ndb/bin]./ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=2    @10.204.96.31  (mysql-5.1.63 ndb-7.1.24, Nodegroup: 0)
id=3    @10.204.96.35  (mysql-5.1.63 ndb-7.1.24, Nodegroup: 0, Master)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @10.204.96.32  (mysql-5.1.63 ndb-7.1.24)

[mysqld(API)]   2 node(s)
id=4    @10.204.96.31  (mysql-5.1.63 ndb-7.1.24)
id=5 (not connected, accepting connect from 10.204.96.35)

ndb_mgm>

再启sql2节点的日志
140926 20:55:52 mysqld_safe mysqld from pid file /mysql/ndb/data/skfweb5.pid ended
140926 20:58:25 mysqld_safe Starting mysqld daemon with databases from /mysql/ndb/data
140926 20:58:25 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
140926 20:58:25 [Note] Plugin 'FEDERATED' is disabled.
140926 20:58:25  InnoDB: Initializing buffer pool, size = 8.0M
140926 20:58:25  InnoDB: Completed initialization of buffer pool
140926 20:58:25  InnoDB: Started; log sequence number 0 44233
140926 20:58:25 [Note] NDB: NodeID is 5, management server '10.204.96.32:1186'
140926 20:58:26 [Note] NDB[0]: NodeID: 5, all storage nodes connected
140926 20:58:26 [Note] Starting Cluster Binlog Thread
140926 20:58:26 [Note] Event Scheduler: Loaded 0 events
140926 20:58:27 [Note] NDB Binlog: Ndb tables initially read only.
140926 20:58:27 [Note] NDB Binlog: DISCOVER TABLE Event: REPL$mysql/ndb_schema
140926 20:58:27 [Note] NDB Binlog: logging ./mysql/ndb_schema (UPDATED,USE_WRITE)
140926 20:58:27 [Note] NDB Binlog: DISCOVER TABLE Event: REPL$mysql/ndb_apply_status
140926 20:58:27 [Note] NDB Binlog: logging ./mysql/ndb_apply_status (UPDATED,USE_WRITE)
140926 20:58:27 [Note] NDB: Cleaning stray tables from database 'information_schema'
140926 20:58:27 [Note] NDB: Cleaning stray tables from database 'aa'
140926 20:58:27 [Note] NDB Binlog: DISCOVER TABLE Event: REPL$aa/aa
140926 20:58:27 [Note] NDB Binlog: logging ./aa/aa (UPDATED,USE_WRITE)
140926 20:58:27 [Note] NDB Binlog: DISCOVER TABLE Event: REPL$aa/bb
140926 20:58:27 [Note] NDB Binlog: logging ./aa/bb (UPDATED,USE_WRITE)
140926 20:58:27 [Note] NDB Binlog: DISCOVER TABLE Event: REPL$aa/cc
140926 20:58:27 [Note] NDB Binlog: logging ./aa/cc (UPDATED,USE_WRITE)
140926 20:58:27 [Note] NDB Binlog: DISCOVER TABLE Event: REPL$aa/lixf
140926 20:58:27 [Note] NDB Binlog: logging ./aa/lixf (UPDATED,USE_WRITE)
140926 20:58:27 [Note] NDB: Cleaning stray tables from database 'mysql'
140926 20:58:27 [Note] NDB: Cleaning stray tables from database 'ndbinfo'
140926 20:58:27 [Note] NDB: Cleaning stray tables from database 'test'
140926 20:58:27 [Note] NDB Binlog: DISCOVER TABLE Event: REPL$test/a1
140926 20:58:27 [Note] NDB Binlog: logging ./test/a1 (UPDATED,USE_WRITE)
140926 20:58:27 [Note] NDB Binlog: DISCOVER TABLE Event: REPL$test/City
140926 20:58:27 [Note] NDB Binlog: logging ./test/City (UPDATED,USE_WRITE)
2014-09-26 20:58:27 [NdbApi] INFO     -- Flushing incomplete GCI:s < 48669/5
2014-09-26 20:58:27 [NdbApi] INFO     -- Flushing incomplete GCI:s < 48669/5
140926 20:58:27 [Note] NDB Binlog: starting log at epoch 48669/5
140926 20:58:27 [Note] NDB Binlog: ndb tables writable
140926 20:58:27 [Note] /mysql/ndb/bin/mysqld: ready for connections.
Version: '5.1.63-ndb-7.1.24-cluster-gpl-log'  socket: '/tmp/mysqlndb.sock'  port: 3309  MySQL Cluster Server (GPL)
140926 20:58:27 [Note] NDB Binlog: Node: 3, subscribe from node 4, Subscriber bitmask 010
140926 20:58:27 [Note] NDB Binlog: Node: 2, subscribe from node 4, Subscriber bitmask 010

ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=2    @192.168.1.31  (mysql-5.1.63 ndb-7.1.24, Nodegroup: 0)
id=3    @192.168.1.35  (mysql-5.1.63 ndb-7.1.24, Nodegroup: 0, Master)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @192.168.1.32  (mysql-5.1.63 ndb-7.1.24)

[mysqld(API)]   2 node(s)
id=4    @192.168.1.31  (mysql-5.1.63 ndb-7.1.24)
id=5    @192.168.1.35  (mysql-5.1.63 ndb-7.1.24)

ndb_mgm>

 

在A节点上进行测试。
CREATE TABLE `City` (
  `ID` int(11) NOT NULL auto_increment,
  `Name` char(35) NOT NULL default '',
  `CountryCode` char(3) NOT NULL default '',
  `District` char(20) NOT NULL default '',
  `Population` int(11) NOT NULL default '0',
  PRIMARY KEY  (`ID`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1;

INSERT INTO `City` VALUES (1,'Kabul','AFG','Kabol',1780000);
INSERT INTO `City` VALUES (2,'Qandahar','AFG','Qandahar',237500);
INSERT INTO `City` VALUES (3,'Herat','AFG','Herat',186800);

执行完后，在B节点上都会存在表。

停管理节点
shell> ndb_mgm -e shutdown

1、如果a机ndbd进程掉了，在B机中写入数据后，不会同步到a机的sql库中，只有当ndbd对应的进程起来才会自动进行同步；
2、创建表时，表的存储引擎必须为ndbcluster或ndb属性，否则不会进行同步；