mysql cluster配置测试

一、测试环境介绍

<OS Version>Red Hat Enterprise Linux Server release 5.4<DATABASE Version> 5.1.63-ndb-7.1.24-cluster-gpl-log

二、网络拓扑架构图，建议将服务器更名，不要都用localhost，更改方法：

[root@server2 ~]# vi /etc/sysconfig/network
NETWORKING=yes
NETWORKING_IPV6=yes
HOSTNAME=server2.localdomain
GATEWAY=192.168.1.1

[root@server2 ~]# vi /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1               server2.localdomain localhost
::1             localhost6.localdomain6 localhost6

三、软件包安装

redhat5.4的光盘是带了5.0的mysqlserver的，这里我们是做集群，建议不在系统安装的时候将数据库安装上去，否则必须卸载，按照官方文档的解释，mysql-max 相关文件在5.0不存在，可以通过进入mysql，然后show storage engine，查看数据库支持的引擎。

server1：MySQL-Cluster-gpl-management-7.1.24-1.rhel5.i386.rpm、MySQL-Cluster-gpl-server-7.1.24-1.rhel5.i386.rpm、MySQL-Cluster-gpl-tools-7.1.23-1.rhel5.i386.rpm

server2、server3：MySQL-Cluster-gpl-clusterj-7.1.24-1.rhel5.i386.rpm、MySQL-Cluster-gpl-extra-7.1.23-1.rhel5.i386.rpm、MySQL-Cluster-gpl-server-7.1.24-1.rhel5.i386.rpm、MySQL-Cluster-gpl-storage-7.1.23-1.rhel5.i386.rpm、MySQL-Cluster-gpl-tools-7.1.23-1.rhel5.i386.rpm、MySQL-Cluster-gpl-client-7.1.28-1.rhel5.i386.rpm

四、服务器配置文件介绍

server1：

/var/lib/mysql-cluster/config.ini  --管理节点配置文件

[NDB_MGMD DEFAULT]
Portnumber=1186

[NDB_MGMD]
NodeId=2
HostName=192.168.1.72
DataDir=/var/lib/mysql-cluster/
Portnumber=1186

[TCP DEFAULT]
SendBufferMemory=4M
ReceiveBufferMemory=4M

[NDBD DEFAULT]

[NDBD]
NodeId=3
HostName=192.168.1.73
DataDir=/var/lib/mysql/

[NDBD]
NodeId=1
HostName=192.168.1.71
DataDir=/var/lib/mysql/

[MYSQLD DEFAULT]
[mysqld]
hostname=192.168.1.73
[mysqld]
hostname=192.168.1.71
[mysqld]

/usr/sbin/ndb.mgmd --管理节点启动程序

/usr/bin/ndb_mgm  --管理节点管理程序

server2、server3：

/etc/my.cnf --mysql数据库配置文件

[client]
#password       = your_password
port            = 3306
socket          = /var/lib/mysql/mysql.sock

# Here follows entries for some specific programs
[mysql_cluster]
ndb-connectstring="192.168.1.72:1186"
# The MySQL server
[mysqld]
#port           = 3306
#socket         = /var/lib/mysql/mysql.sockndbcluster
ndb-connectstring="192.168.1.72:1186"
default-storage-engine=ndbcluster

ps 做集群无需从将/usr/share/mysql/my-medium.cnf 复制到/etc/my.cnf，具体原因待分析

下面介绍几种常见的错误：

1、Unable to connect with connect string: nodeid=0,localhost:1186  Retrying every 5 seconds. Attempts left: 2 1, failed.

可能原因：软件包安装错误（多装或者是少装都会出错）；config.ini或者my.cnf配置文件有误；

2、Unable to connect with connect string: nodeid=0,192.168.1.102:1186   Retrying every 5 seconds. Attempts left: 12 11 10 9 8 7 6 5 4 3 2 1, failed.

可能原因：防火墙开启，阻止集群数据通信；selinux开启，可以通过setenforce  0 关闭；

3、启动管理节点，sql节点连接失败

[root@server1 pub]# ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=1 (not connected, accepting connect from 192.168.1.71)
id=3 (not connected, accepting connect from 192.168.1.73)

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

[mysqld(API)]   4 node(s)
id=50 (not connected, accepting connect from 192.168.1.73)
id=51 (not connected, accepting connect from 192.168.1.71)
id=52 (not connected, accepting connect from any host)
id=53 (not connected, accepting connect from any host)

可能原因：数据节点的配置文件有多余的选项，例如：在[sqld]下面多了这两行：

[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock

五、启动管理节点和数据节点

(1)准备工作：关闭防火墙：service iptables stop；关闭selinux：setenforce 0；

server1上面启动管理节点  ndb_mgmd -f /var/lib/mysql-cluster/config.ini --initial；在server2和server3上面启动数据节点  ndbd

(2)进入管理程序，查看节点连接情况：

ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=1    @192.168.1.71  (mysql-5.1.63 ndb-7.1.23, Nodegroup: 0, Master)
id=3    @192.168.1.73  (mysql-5.1.63 ndb-7.1.23, Nodegroup: 0)

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

[mysqld(API)]   3 node(s)
id=4    @192.168.1.73  (mysql-5.1.63 ndb-7.1.24)
id=5    @192.168.1.71  (mysql-5.1.63 ndb-7.1.24)
id=6 (not connected, accepting connect from any host)

六、测试数据同步

(1)server2上创建表

mysql> create table t1 (  id tinyint auto_increment primary key,  name varchar(10),  password 
varchar(20),  others varchar(30) ) ; 
Query OK, 0 rows affected (0.26 sec)
mysql> desc t1;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | tinyint(4)  | NO   | PRI | NULL    | auto_increment |
| name     | varchar(10) | YES  |     | NULL    |                |
| password | varchar(20) | YES  |     | NULL    |                |
| others   | varchar(30) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

(2)server3上面插入数据

mysql> desc t1;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| id       | tinyint(4)  | NO   | PRI | NULL    | auto_increment |
| name     | varchar(10) | YES  |     | NULL    |                |
| password | varchar(20) | YES  |     | NULL    |                |
| others   | varchar(30) | YES  |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.03 sec)

mysql> insert into t1 values ('','forilen','password','administrator');
Query OK, 1 row affected, 1 warning (0.01 sec)

(3)server2上面查询数据

mysql> select * from t1;
+----+---------+----------+---------------+
| id | name    | password | others        |
+----+---------+----------+---------------+
|  1 | forilen | password | administrator |
+----+---------+----------+---------------+
1 row in set (0.00 sec)

七、集群故障测试

(1)拔掉server3的网线，查看管理节点情况

ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=1 (not connected, accepting connect from 192.168.1.71)
id=3    @192.168.1.73  (mysql-5.1.63 ndb-7.1.23, Nodegroup: 0, Master)

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

[mysqld(API)]   3 node(s)
id=4    @192.168.1.73  (mysql-5.1.63 ndb-7.1.24)
id=5 (not connected, accepting connect from 192.168.1.71)
id=6 (not connected, accepting connect from any host)

(2)server3已经断开，往server2中插入数据

+---------+------+------------------------------------------------------+
| Level   | Code | Message                                              |
+---------+------+------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: '' for column 'id' at row 1 |
+---------+------+------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from t1;
+----+---------+----------+---------------+
| id | name    | password | others        |
+----+---------+----------+---------------+
|  3 | xiaohu  | password | xiaohu        |
|  1 | forilen | password | administrator |
|  2 | xiaoli  | pwd      | user1         |
+----+---------+----------+---------------+
3 rows in set (0.00 sec)

(3)插上server3的网线，查看数据

[root@server3 ~]# ndbd
2013-12-05 11:09:09 [ndbd] INFO     -- Angel connected to '192.168.1.72:1186'
2013-12-05 11:09:09 [ndbd] INFO     -- Angel allocated nodeid: 1

(4)查看server1的管理节点连接情况

ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=1    @192.168.1.71  (mysql-5.1.63 ndb-7.1.23, starting, Nodegroup: 0)
id=3    @192.168.1.73  (mysql-5.1.63 ndb-7.1.23, Nodegroup: 0, Master)

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

[mysqld(API)]   3 node(s)
id=4    @192.168.1.73  (mysql-5.1.63 ndb-7.1.24)
id=5    @192.168.1.71  (mysql-5.1.63 ndb-7.1.24)
id=6 (not connected, accepting connect from any host)

(5)查看server3上面数据同步情况

mysql> use test;
Database changed
mysql> select * from t1;
+----+---------+----------+---------------+
| id | name    | password | others        |
+----+---------+----------+---------------+
|  3 | xiaohu  | password | xiaohu        |
|  1 | forilen | password | administrator |
|  2 | xiaoli  | pwd      | user1         |
+----+---------+----------+---------------+
3 rows in set (0.00 sec)

数据已经同步，集群搭建完毕，具体详细参数下文分析。