Mysql 集群配置详解

环境:
Vmware workstation6.5
操作系统:Centos 5.2 (2台)
第一台:
主机名:mysql1 IP: 192.168.1.30
第二台
主机名:mysql2 IP: 192.168.1.32
软件下载安装
下载软件地址:http://dev.mysql.com/downloads/select.php?id=14 
选择操作系统是RedHat Enterprise 版本

如下:
MySQL-Cluster-gpl-client-7.0.8a-0.rhel5.i386.rpm
MySQL-Cluster-gpl-debuginfo-7.0.8a-0.rhel5.i386.rpm
MySQL-Cluster-gpl-devel-7.0.8a-0.rhel5.i386.rpm
MySQL-Cluster-gpl-embedded-7.0.8a-0.rhel5.i386.rpm
MySQL-Cluster-gpl-extra-7.0.8a-0.rhel5.i386.rpm
MySQL-Cluster-gpl-management-7.0.8a-0.rhel5.i386.rpm
MySQL-Cluster-gpl-server-7.0.8a-0.rhel5.i386.rpm
MySQL-Cluster-gpl-shared-7.0.8a-0.rhel5.i386.rpm
MySQL-Cluster-gpl-storage-7.0.8a-0.rhel5.i386.rpm
MySQL-Cluster-gpl-test-7.0.8a-0.rhel5.i386.rpm
MySQL-Cluster-gpl-tools-7.0.8a-0.rhel5.i386.rpm

将软件包上传到服务器

Rpm –ivh *.rpm
具体配置 在两台机器上做(同样的配置文件)

1、创建配置文件目录:( mysql1 mysql2)
#mkdir /var/lib/mysql-cluster –p
在/var/lib/mysql-cluster 下创建集群配置文件config.ini
编辑,添加如下内容:

[NDBD DEFAULT]

NoOfReplicas=2

DataMemory=128M

IndexMemory=64M

MaxNoOfConcurrentOperations=2000000

[MYSQLD DEFAULT]

[NDB_MGMD DEFAULT]
DataDir= /var/lib/mysql-cluster

[TCP DEFAULT]

# Section for the cluster management node

[NDB_MGMD]

# IP address of the management node (this system)
ID=1
HostName= 192.168.1.30

[NDB_MGMD]

# IP address of the management node (this system)
ID=2
HostName= 192.168.1.32

# Section for the storage nodes

[NDBD]
ID=3
# IP address of the first storage node
HostName=192.168.1.30

[NDBD]
ID=4
# IP address of the second storage node
HostName=192.168.1.32

# one [MYSQLD] per storage node
[MYSQLD]
[MYSQLD]
[MYSQLD]
[MYSQLD]
[MYSQLD]
[MYSQLD]
[MYSQLD]

2、创建API文件( mysql1 mysql2)
编辑/etc/my.cnf  添加如下内容:

[mysqld]
default-storage-engine=ndbcluster
log=queryLog
log-slow-queries=slowquerylog
max_connections = 1000


#NDBCLUSTER
ndbcluster
ndb-connectstring = 192.168.1.30,192.168.1.32

[ndb_mgm]
connect-string = 192.168.1.30,192.168.1.32

[ndbd]
connect-string = 192.168.1.30,192.168.1.32

[mysql_cluster]
ndb-connectstring= 192.168.1.30,192.168.1.32

[ndb_mgmd]
config-file=/var/lib/mysql-cluster/config.ini


集群的启动:
启动管理节点
在mysql1 上 启动管理节点
# ndb_mgmd –ndb_nodeid=1

在mysql2上启动管理节点

# ndb_mgmd –ndb_nodeid=2

第一次会有警告:
Cluster configuration warning:
arbitrator with id 1 and db node with id 3 on same host 192.168.1.30
arbitrator with id 2 and db node with id 4 on same host 192.168.1.32
Running arbitrator on the same host as a database node may
cause complete cluster shutdown in case of host failure.
是说:节点1和3,2和4的arbitrator一样,可能引起整个集群失败 这个无所谓,继续

启动NDB 
在mysql1 上运行:
# ndbd –nodeid=3 –initial

在mysql2上运行:
# ndbd –nodeid=4 –iniitial

注意:在第一次启动ndb或者修改了config.ini 文件才加上-initiail 参数

查看状态:
在任意一台机器上查看:

[root@mysql1 ~]# ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: 192.168.1.30:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=5    @192.168.1.30  (mysql-5.1.37 ndb-7.0.8, Nodegroup: 0, Master)
id=6    @192.168.1.32  (mysql-5.1.37 ndb-7.0.8, Nodegroup: 0)

[ndb_mgmd(MGM)] 2 node(s)
id=1    @192.168.1.30  (mysql-5.1.37 ndb-7.0.8)
id=2    @192.168.1.32  (mysql-5.1.37 ndb-7.0.8)

[mysqld(API)]   9 node(s)
id=7 (not connected, accepting connect from any host)
id=8 (not connected, accepting connect from any host)
id=9 (not connected, accepting connect from any host)
id=10 (not connected, accepting connect from any host)
id=11 (not connected, accepting connect from any host)
id=12 (not connected, accepting connect from any host)
id=13 (not connected, accepting connect from any host)
id=14 (not connected, accepting connect from any host)
id=15 (not connected, accepting connect from any host)

看到上面的信息就正常了

启动API

在mysql1中:
#mysqld_safe –ndb_nodeid=5 –user=mysql &

在mysql2 中:
#mysqld_safe –ndb_nodeid=6 –user=mysql &

再次查看:
[root@mysql1 ~]# ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: 192.168.1.30:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=5    @192.168.1.30  (mysql-5.1.37 ndb-7.0.8, Nodegroup: 0, Master)
id=6    @192.168.1.32  (mysql-5.1.37 ndb-7.0.8, Nodegroup: 0)

[ndb_mgmd(MGM)] 2 node(s)
id=1    @192.168.1.30  (mysql-5.1.37 ndb-7.0.8)
id=2    @192.168.1.32  (mysql-5.1.37 ndb-7.0.8)

[mysqld(API)]   9 node(s)
id=7    @192.168.1.30  (mysql-5.1.37 ndb-7.0.8)
id=8    @192.168.1.32  (mysql-5.1.37 ndb-7.0.8)
id=9 (not connected, accepting connect from any host)
id=10 (not connected, accepting connect from any host)
id=11 (not connected, accepting connect from any host)
id=12 (not connected, accepting connect from any host)
id=13 (not connected, accepting connect from any host)
id=14 (not connected, accepting connect from any host)
id=15 (not connected, accepting connect from any host)

显示两个API节点 正常

集群测试
在mysql1中创建库
# /usr/local/mysql/bin/mysql -u root -p
mysql >create database love;
mysql > use love;
mysql > create table love (i INT) 
mysql > insert into love () VALUES (1);

在mysql2上查询:

mysql > select * from love;
mysql> show tables;
+----------------+
| Tables_in_love |
+----------------+
| love           | 
+----------------+
1 row in set (0.03 sec)

mysql> select * from love;
+------+
| i    |
+------+
|    1 | 
+------+
1 row in set (0.08 sec)

已经同步:

下面我将mysql1 停掉网卡:看一下状态:

[root@mysql2 ~]# ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: 192.168.1.32:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=5 (not connected, accepting connect from 192.168.1.30)
id=6    @192.168.1.32  (mysql-5.1.37 ndb-7.0.8, Nodegroup: 0, Master)

[ndb_mgmd(MGM)] 2 node(s)
id=1 (not connected, accepting connect from 192.168.1.30)
id=2    @192.168.1.32  (mysql-5.1.37 ndb-7.0.8)

[mysqld(API)]   9 node(s)
id=7 (not connected, accepting connect from any host)
id=8 (not connected, accepting connect from any host)
id=9 (not connected, accepting connect from any host)
id=10 (not connected, accepting connect from any host)
id=11 (not connected, accepting connect from any host)
id=12 (not connected, accepting connect from any host)
id=13 (not connected, accepting connect from any host)
id=14 (not connected, accepting connect from any host)
id=15 (not connected, accepting connect from any host)

mysql2 对外提供服务成为主服务器
看能否查询:
mysql> select * from love;
ERROR 1296 (HY000): Got error 157 'Unknown error code' from NDBCLUSTER
mysql> select * from love;
ERROR 1296 (HY000): Got error 157 'Unknown error code' from NDBCLUSTER
暂时不能查询,出现错误:
稍等片刻再次查询,没有错误,这段时间是故障转移,时间不是很长
具体时间没计算

在mysql2 上创建 表:
mysql > create table love1 (i INT) 
mysql > insert into love1 () VALUES (1);

接下来将mysql1的网卡启动

在mysql1上查询数据:

[root@mysql1 ~]# 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.1.37-ndb-7.0.8a-cluster-gpl-log MySQL Cluster Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use love;
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_love |
+----------------+
| love           | 
| love1          | 
+----------------+
2 rows in set (0.01 sec)

mysql> select * from love1;
+------+
| i    |
+------+
|    1 | 
+------+
1 row in set (0.00 sec)

可以看到在mysql2上创建的表love2

正常运行但是mysql1成为主服务器,这个怎么改变过来,还不清楚

The end !




     本文转自andylhz 51CTO博客,原文链接:http://blog.51cto.com/andylhz2009/213666,如需转载请自行联系原作者


上一篇:增长难题如何破?20天后引擎大会或给出“答题指引”


下一篇:效果营销,太难了