mysql cluster 测试_mysql cluster 安装测试

安装包下载

下载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 zxvfmysql-cluster-gpl-7.0.9-linux-i686-glibc23.tar.gz

shell> mvmysql-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 zxvfmysql-cluster-gpl-7.0.9-linux-i686-glibc23.tar.gz

shell> mvmysql-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 zxvfmysql-cluster-gpl-7.0.9-linux-i686-glibc23.tar.gz

shell> mvmysql-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)