mysql cluster 安装测试

安装包下载

请从http://dev.mysql.com/downloads/cluster/ 选择GENERIC LINUX

下载mysql-cluster-gpl-7.0.9-linux-i686-glibc23.tar.gz安装包。

 

此安装为一个管理节点，3个数据节点，3个SQL节点。一共7个节点。其中3个数据节点实现数据存储的冗余，3个SQL节点实现MYSQLD服务的冗余以及负载均衡。

 

节点名称	IP地址
管理节点	192.168.72.141
数据节点1(仅使mysql数据同步作用)	192.168.72.151
数据节点2	192.168.72.152
数据节点3	192.168.72.153
SQL节点1	192.168.72.158
SQL节点2	192.168.72.159
SQL节点3	192.168.72.161

 

安装步骤

1.  为mysqld增加一个登录用户和组：--7个节点都要做此步骤

shell> groupadd mysql

shell> useradd -g mysql mysql

 

2.安装管理节点

 

shell> tar zxvf mysql-cluster-gpl-7.0.9-linux-i686-glibc23.tar.gz

shell> mv mysql-cluster-gpl-7.0.9-linux-i686-glibc23  /opt/mysql

 

创建MYSQL-CLUSTER目录，并配置CONFIG.INI文件

shell> mkdir /apps/mysql/mysql-cluster

shell> cd /apps/mysql/mysql-cluster

shell> vi config.ini

config.ini内容为下：

 

[ndbd default]

NoOfReplicas= 3       （数量代表sql节点的副本数量）

DataMemory=500M        

indexMemory=300M      

[tcp default]

SendBufferMemory=2M

ReceiveBufferMemory=2M

 

[ndb_mgmd default]

PortNumber=1186

Datadir=/apps/mysql/mysql-cluster

[ndb_mgmd]

Id=1

HostName=192.168.72.141

[ndbd]

Id=2

HostName= 192.168.72.151

DataDir= /apps/mysql/data

[ndbd]

Id=3

HostName= 192.168.72.152

DataDir= /apps/mysql/data

[ndbd]

Id=4

HostName= 192.168.72.153

DataDir= /apps/mysql/data

[mysqld]

Id=5

Hostname=192.168.72.158

[mysqld]

Id=6

Hostname=192.168.72.159

[mysqld]

Id=7

Hostname=192.168.72.161 

 

 

 

 

 

 

2. 数据节点安装

分别在192.168.72.151, 192.168.72.152, 192.168.72.153上操作：

 

shell> tar zxvf mysql-cluster-gpl-7.0.9-linux-i686-glibc23.tar.gz

shell> mv mysql-cluster-gpl-7.0.9-linux-i686-glibc23  /opt/mysql

编辑/etc/my.cnf

内容如下：

[mysqld]

ndbcluster                          #运行NDB存储引擎

ndb-connectstring=192.168.72.141    #定位管理节点

[mysql_cluster]

ndb-connectstring=192.168.72.141    #定位管理节点

 

 

 

4.SQL节点安装

 

分别在192.168.72.158, 192.168.72.159, 192.168.72.161上操作：

 

shell> tar zxvf mysql-cluster-gpl-7.0.9-linux-i686-glibc23.tar.gz

shell> mv mysql-cluster-gpl-7.0.9-linux-i686-glibc23  /opt/mysql

shell> ./scripts/mysql_install_db --user=mysql

编辑/etc/my.cnf文件，添加内容如下。

[mysqld]

ndbcluster                             #运行NDB存储引擎

ndb-connectstring=192.168.72.141    #定位管理节点

[mysql_cluster]

Ndb-connectstring=192.168.72.141    #定位管理节点

 

 

 

5.开始启动CLUSTER

CLUSTER启动的顺序依次为：管理节点—数据节点—SQL节点

 

管理节点(192.168.72.141) 启动：

shell> cd /apps/mysql/mysql_cluster

shell> ./bin/ndb_mgmd –f /apps/mysql/mysql-cluster/config.ini --configdir=/apps/mysql/mysql-cluster/ --ndb-nodeid=1

 

数据节点(72.151,72.152,72.153)启动：

shell> cd /apps/mysql/

shell> ./bin/ndbd –-initial –-ndb-connectstring=192.168.72.141:1186

注意：仅限第一次mysql节点启动使用–-initial，后面再启动不需要此参数

 

SQL节点(72.158,72.159,72.161)启动：

shell> cd /apps/mysql

shell> ./bin/mysqld_safe –user=mysql &

 

节点全部启动后，用ndb_mgm 工具的show命令查看集群状态。

 

 

登陆管理节点72.141  /apps/mysql/mysql-cluster/bin

下面就说明 mysql cluster的节点全部启动，并且状态正常：

 

 

[root@cmgphf1 bin]# ./ndb_mgm -e show

Connected to Management Server at: localhost:1186

Cluster Configuration

---------------------

[ndbd(NDB)]     3 node(s)

id=2    @192.168.72.151  (mysql-5.1.39 ndb-7.0.9, Nodegroup: 0)

id=3    @192.168.72.152  (mysql-5.1.39 ndb-7.0.9, Nodegroup: 0)

id=4    @192.168.72.153  (mysql-5.1.39 ndb-7.0.9, Nodegroup: 0, Master)

 

[ndb_mgmd(MGM)] 1 node(s)

id=1    @192.168.72.141  (mysql-5.1.39 ndb-7.0.9)

 

[mysqld(API)]   3 node(s)

id=5    @192.168.72.158  (mysql-5.1.39 ndb-7.0.9)

id=6    @192.168.72.159  (mysql-5.1.39 ndb-7.0.9)

id=7    @192.168.72.161  (mysql-5.1.39 ndb-7.0.9)

 

 

 

 

 

 

 

 

测试过程

数据同步测试

1.    在72.158的mysql上建db和table

[root@72_158 data]# mysql -uroot -p --socket=/tmp/mysql.sock

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 4 to server version: 5.1.39-ndb-7.0.9-cluster-gpl

 

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

 

mysql> create database ctest;

Query OK, 1 row affected (0.26 sec)

 

mysql> use ctest;

Database changed

mysql> create table t1(id int not null primary key) engine=ndb;

Query OK, 0 rows affected (0.63 sec)

 

mysql> insert into t1 values(1);

Query OK, 1 row affected (0.07 sec)

 

mysql> insert into t1 values(2);

Query OK, 1 row affected (0.01 sec)

 

mysql> flush tables;

Query OK, 0 rows affected (0.01 sec)

 

 

 

2.    登陆其他sql节点，进行检查表数据是否同步

 

[root@72_159 ~]# mysql -uroot -p --socket=/tmp/mysql.sock

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 3 to server version: 5.1.39-ndb-7.0.9-cluster-gpl

 

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

 

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| ctest              |

| mysql              |

| test               |           |

+--------------------+

6 rows in set (0.00 sec)

 

mysql> use ctest;

Database changed

mysql> select * from t1; 

+----+

| id |

+----+

|  1 |

|  2 |

+----+

2 rows in set (0.01 sec)

 

 

 

 

节点重启测试

 

1.    检查各节点的状态，对红色部分进行主机重启

 

[root@cmgphf1 bin]# ./ndb_mgm -e show

Connected to Management Server at: localhost:1186

Cluster Configuration

---------------------

[ndbd(NDB)]     3 node(s)

id=2    @192.168.72.151  (mysql-5.1.39 ndb-7.0.9, Nodegroup: 0)

id=3    @192.168.72.152  (mysql-5.1.39 ndb-7.0.9, Nodegroup: 0)

id=4    @192.168.72.153  (mysql-5.1.39 ndb-7.0.9, Nodegroup: 0, Master)

 

[ndb_mgmd(MGM)] 1 node(s)

id=1    @192.168.72.141  (mysql-5.1.39 ndb-7.0.9)

 

[mysqld(API)]   3 node(s)

id=5    @192.168.72.158  (mysql-5.1.39 ndb-7.0.9)

id=6    @192.168.72.159  (mysql-5.1.39 ndb-7.0.9)

id=7    @192.168.72.161  (mysql-5.1.39 ndb-7.0.9)

 

2.     对72.152,72.153,72.158,72.159 节点重启动，只保留一个NDB和SQL节点，来测试数据库能否正常使用：

 

[root@cmgphf1 bin]# ./ndb_mgm -e show

Connected to Management Server at: localhost:1186

Cluster Configuration

---------------------

[ndbd(NDB)]     3 node(s)

id=2    @192.168.72.151  (mysql-5.1.39 ndb-7.0.9, Nodegroup: 0, Master) 已经漂移

id=3 (not connected, accepting connect from 192.168.72.152)

id=4 (not connected, accepting connect from 192.168.72.153)

 

[ndb_mgmd(MGM)] 1 node(s)

id=1    @192.168.72.141  (mysql-5.1.39 ndb-7.0.9)

 

[mysqld(API)]   3 node(s)

id=5 (not connected, accepting connect from 192.168.72.158)

id=6 (not connected, accepting connect from 192.168.72.159)

id=7    @192.168.72.161  (mysql-5.1.39 ndb-7.0.9)

 

 

3.     登陆72.161，进行数据正常查询，说明已经具备了单点故障：

 

[root@72_161 mysql]# mysql -uroot -p --socket=/tmp/mysql.sock

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 8 to server version: 5.1.39-ndb-7.0.9-cluster-gpl

 

mysql> use ctest;

Database changed

 

mysql> show tables;

+-----------------+

| Tables_in_ctest |

+-----------------+

| t1              |

+-----------------+

1 row in set (0.00 sec)

 

mysql> select * from t1;

+----+

| id |

+----+

|  2 |

|  1 |

+----+

2 rows in set (0.00 sec)

 

 

4.     在72.161 sql节点上删除表里一条记录

mysql> delete from t1 limit 1;

Query OK, 1 row affected (0.02 sec)

 

mysql> select * from t1;

+----+

| id |

+----+

|  1 |

+----+

1 row in set (0.01 sec)

 

 

 

5.     对停掉的节点进行启动，并且检查其它sql节点的t1表数据是否同步

a.     检查mysql cluster状态：

[root@72_141 bin]# ./ndb_mgm -e show

Connected to Management Server at: localhost:1186

Cluster Configuration

---------------------

[ndbd(NDB)]     3 node(s)

id=2    @192.168.72.151  (mysql-5.1.39 ndb-7.0.9, Nodegroup: 0, Master)

id=3    @192.168.72.152  (mysql-5.1.39 ndb-7.0.9, Nodegroup: 0)

id=4    @192.168.72.153  (mysql-5.1.39 ndb-7.0.9, Nodegroup: 0)

 

[ndb_mgmd(MGM)] 1 node(s)

id=1    @192.168.72.141  (mysql-5.1.39 ndb-7.0.9)

 

[mysqld(API)]   3 node(s)

id=5    @192.168.72.158  (mysql-5.1.39 ndb-7.0.9)

id=6    @192.168.72.159  (mysql-5.1.39 ndb-7.0.9)

id=7    @192.168.72.161  (mysql-5.1.39 ndb-7.0.9)

 

 

 

 

b.     检查sql节点上的t1表数据

 

[root@72_158 mysql]# mysql -uroot -p --socket=/tmp/mysql.sock

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 3 to server version: 5.1.39-ndb-7.0.9-cluster-gpl

 

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

 

mysql> use ctest;

Database changed

mysql> select * from t1;

+----+

| id |

+----+

|  1 |

+----+

1 row in set (0.00 sec)