mysql 服务器集群_快速/简单实现mysql数据库集群(mysql cluster)

最近一直在研究mysql的高可用性和负载均衡技术，对于数据库/数据表数目比较少的情况下，还是推荐mysql官方提供的mysql-cluster技术。

下面列出mysql相关HA技术方面的比较，可以根据你的需要来实现：

我采用debian系统来担任mysql服务器，操作起来比较方便。

准备两台机器：

192.168.10.172 ndb management, sql_node, data_node

192.168.10.173 sql_node, data_node

1、apt-get install mysql-server；两台机器分别安装mysql-server 5.0，5.0自带cluster功能；

2、 cp /usr/share/doc/mysql-server-5.0/examples/ndb_mgmd.cnf /etc/mysql/ndb_mgmd.cnf，复制cluster management模板，并编辑成类似如下的信息：

cat /etc/mysql/ndb_mgmd.cnf

[NDBD DEFAULT]

NoOfReplicas=2

DataMemory=10MB

IndexMemory=25MB

MaxNoOfTables=256

MaxNoOfOrderedIndexes=256

MaxNoOfUniqueHashIndexes=128

[MYSQLD DEFAULT]

[NDB_MGMD DEFAULT]

[TCP DEFAULT]

[NDB_MGMD]

Id=1 # the NDB Management Node (this one)

HostName=192.168.10.172

[NDBD]

Id=2 # the first NDB Data Node

HostName=192.168.10.172

DataDir= /var/lib/mysql

[NDBD]

Id=3 # the second NDB Data Node

HostName=192.168.10.173

DataDir=/var/lib/mysql

[MYSQLD]

Id=4 # the first SQL node

HostName=192.168.10.172

[MYSQLD]

Id=5 # the second SQL node

HostName=192.168.10.173

3、编辑/etc/mysql/my.cnf，类似如下信息，其实就是增加了红色部分内容：

more my.cnf |grep -v ^#

[client]

port = 3306

socket = /var/run/mysqld/mysqld.sock

[mysqld_safe]

socket = /var/run/mysqld/mysqld.sock

nice = 0

[mysqld]

user = mysql

pid-file = /var/run/mysqld/mysqld.pid

socket = /var/run/mysqld/mysqld.sock

port = 3306

basedir = /usr

datadir = /var/lib/mysql

tmpdir = /tmp

language = /usr/share/mysql/english

skip-external-locking

bind-address = 127.0.0.1

key_buffer = 16M

max_allowed_packet = 16M

thread_stack = 128K

thread_cache_size = 8

myisam-recover = BACKUP

query_cache_limit = 1M

query_cache_size = 16M

expire_logs_days = 10

max_binlog_size = 100M

skip-bdb

ndbcluster

ndb-connectstring=192.168.10.172

[mysqldump]

quick

quote-names

max_allowed_packet = 16M

[mysql]

[isamchk]

key_buffer = 16M

[MYSQL_CLUSTER]

ndb-connectstring=192.168.10.172

!includedir /etc/mysql/conf.d/

4、scp my.cnf 192.168.10.173:/etc/mysql/ ，把mysql配置文件复制到另一台机器；

5、在192.168.10.172上运行 /etc/init.d/mysql-ndb-mgm restart; /etc/init.d/mysql-ndb restart; /etc/init.d/mysql restart 三个脚本；

6、在192.168.10.173运行 /etc/init.d/mysql-ndb restart; /etc/init.d/mysql restart 两个脚本；

7、任何一台机器运行下列命令，如果显示如下信息，证明全部运行成功：

ndb_mgm -e show

Connected to Management Server at: 192.168.10.172:1186

Cluster Configuration

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

[ndbd(NDB)] 2 node(s)

id=2 @192.168.10.172 (Version: 5.0.51, Nodegroup: 0, Master)

id=3 @192.168.10.173 (Version: 5.0.51, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)

id=1 @192.168.10.172 (Version: 5.0.51)

[mysqld(API)] 2 node(s)

id=4 @192.168.10.172 (Version: 5.0.51)

id=5 @192.168.10.173 (Version: 5.0.51)

8、登录mysql，验证数据同步和复制功能，DB2机器上必须要创建一个test的数据库，其他不用做就可以实现数据的同步和复制；

192.168.10.172

DB1:/etc/mysql# mysql -uroot -p

Enter password:

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

Your MySQL connection id is 31

Server version: 5.0.51a-24+lenny4 (Debian)

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

mysql> create database test;

Query OK, 1 row affected (0.00 sec)

mysql> use test

Database changed

mysql> create table mytable(id int(10),name varchar(10)) engine=ndbcluster;

Query OK, 0 rows affected (0.30 sec)

mysql> insert into mytable values(1,'Jone');

Query OK, 1 row affected (0.01 sec)

mysql> insert into mytable values(3,'Jane');

Query OK, 1 row affected (0.01 sec)

mysql> select * from mytable;

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

| id | name |

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

| 3 | Jane |

| 2 | Mark |

| 1 | Jone |

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

3 rows in set (0.01 sec)

192.168.10.173

DB2:~# mysql -uroot -p

Enter password:

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

Your MySQL connection id is 30

Server version: 5.0.51a-24+lenny4 (Debian)

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

mysql> create database test;

Query OK, 1 row affected (0.00 sec)

mysql> use test;

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_test |

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

| mytable |

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

1 row in set (0.01 sec)

mysql> insert into mytable values(2,'Mark');

Query OK, 1 row affected (0.01 sec)

mysql> select * from mytable;

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

| id | name |

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

| 1 | Jone |

| 3 | Jane |

| 2 | Mark |

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

3 rows in set (0.00 sec)

经过以上操作就实现了mysql-cluster，快速/简单。

更多信息请查看官方参考手册：http://dev.mysql.com/doc/refman/5.1/zh/ndbcluster.html