MySQL高可用集群解决方案之：MySql Cluster搭建

MySQL是当前使用最广泛的关系型数据库管理系统之一，但MySQL在高并发访问和大量数据处理方面存在较为明显的性能瓶颈。为了解决MySQL单点故障带来的不便和可扩展性问题，我们需要构建稳定性极高的MySQL集群方案。本文将介绍一种快速搭建MySQL集群的解决方案，并提供相应的代码实现。

我们采用了MySQL官方提供的集群方案——MySQL Cluster来构建稳定性极高的MySQL集群。MySQL Cluster是一种高可用、高可扩展、高性能的MySQL集群方案，它的数据节点（NDB）具有自动分片、自动负载均衡、自动容错等特点。

一：实验环境

二：实验步骤

1：安装包下载

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

我下载的是：mysql-cluster-gpl-7.2.15-linux2.6-x86_64.tar.gz

2：解压

在所有节点上进行解压：

tar -xvf mysql-cluster-gpl-7.2.15-linux2.6-x86_64.tar.gz 
mv mysql-cluster-gpl-7.2.15-linux2.6-x86_64 /usr/local/mysql
1
2

3：管理节点的配置和启动

mkdir -p /data/mysql/mysql-cluster
cd /data/mysql/mysql-cluster
1
2

建立全局配置文件vi mgmd.cnf，内容为：

[ndbd default]
NoOfReplicas=2
DataMemory=80M
IndexMemory=18M

[ndb_mgmd]
hostname=192.168.6.66
datadir=/data/mysql/mysql-cluster

[ndbd]
hostname=192.168.6.68
datadir=/usr/local/mysql/data

[ndbd]
hostname=192.168.6.69
datadir=/usr/local/mysql/data

[mysqld]
hostname=192.168.6.66

[mysqld]
hostname=192.168.6.67


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

NoOfReplicas表示数据节点的冗余数量。

如果是2 表有两份数据,如果是1表示只有一份数据. 当NoOfReplicas=2时, 你可以试验一下如果一个data node 停掉了,cluster 可以照常工作; 如果NoOfReplicas=1, 一个data node 停掉了,cluster 就会自动停掉.

启动MGM node：

/usr/local/mysql/bin/ndb_mgmd -f mgmd.cnf 
1

ps -ef|grep mgmd|grep -v grep查看MGM node 进程是否已经起来。

[root@ser6-66 mysql-cluster]# ps -ef|grep mgmd|grep -v grep
root      1225     1  0 13:31 ?        00:00:00 /usr/local/mysql/bin/ndb_mgmd -f mgmd.cnf 
1
2

登录MGM控制台（ndb_mgm 192.168.6.66），用show命令查看各个节点的状态。

[root@ser6-66 mysql-cluster]# /usr/local/mysql/bin/ndb_mgm 192.168.6.66
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: 192.168.6.66:1186
Cluster Configuration
---------------------
[ndbd(NDB)]2 node(s)
id=2 (not connected, accepting connect from 192.168.6.68)
id=3 (not connected, accepting connect from 192.168.6.69)
 
[ndb_mgmd(MGM)]1 node(s)
id=1@192.168.6.66  (mysql-5.5.35 ndb-7.2.15)
 
[mysqld(API)]3 node(s)
id=4 (not connected, accepting connect from 192.168.6.66)
id=5 (not connected, accepting connect from 192.168.6.67)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

#默认管理节点使用的是1186端口。

4：数据节点配置与启动

我是先把/etc/my.cnf文件先删除，然后再创建的。

编辑/etc/my.cnf

内容如下：

[mysqld]
ndbcluster                        #运行NDB存储引擎
ndb-connectstring=192.168.6.66    #定位管理节点
[mysql_cluster]
ndb-connectstring=192.168.6.66    #定位管理节点
1
2
3
4
5

[root@ser6-68 ndbdata]# /usr/local/mysql/bin/ndbd --initial --ndb-connectstring=192.168.6.66

2015-08-17 14:21:05 [ndbd] INFO – Angel connected to ‘192.168.6.66:1186’

2015-08-17 14:21:05 [ndbd] INFO – Angel allocated nodeid: 2

（注意：–initial只需在第一次启动时加该参数）

查看ndb进程启动了

[root@ser6-68 ~]# ps -ef|grep ndb
root     26571     1  0 14:38 ?        00:00:01 /usr/local/mysql/bin/ndbd --ndb-connectstring=192.168.6.66
root     26572 26571  8 14:38 ?        00:10:50 /usr/local/mysql/bin/ndbd --ndb-connectstring=192.168.6.66
root     26921 26901  0 14:38 pts/1    00:00:00 grep ndb 
1
2
3
4

同理配置Data node 2（ndbd2.cnf）…….

在MGM node的控制台中show命令可以看到Data node不再是not connected状态了

ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]2 node(s)
id=2@192.168.6.68  (mysql-5.5.35 ndb-7.2.15, starting, Nodegroup: 0)
id=3@192.168.6.69  (mysql-5.5.35 ndb-7.2.15, starting, Nodegroup: 0)
 
[ndb_mgmd(MGM)]1 node(s)
id=1@192.168.6.66  (mysql-5.5.35 ndb-7.2.15)
 
[mysqld(API)]3 node(s)
id=4 (not connected, accepting connect from 192.168.6.66)
id=5 (not connected, accepting connect from 192.168.6.67)
1
2
3
4
5
6
7
8
9
10
11
12
13

5：sql节点配置与启动

我是先把/etc/my.cnf文件先删除，然后再创建的。

以配置SQL node 1为例。

建基本库：

cd /usr/local/mysql
[root@ser6-66 mysql]# ./scripts/mysql_install_db --user=mysql
WARNING: The host 'ser6-66' could not be looked up with resolveip.
This probably means that your libc libraries are not 100 % compatible
with this binary MySQL version. The MySQL daemon, mysqld, should work
normally with the exception that host name resolving will not work.
This means that you should use IP addresses instead of hostnames
when specifying MySQL privileges !
Installing MySQL system tables...
OK
Filling help tables...
OK
 
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
 
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
 
./bin/mysqladmin -u root password 'new-password'
./bin/mysqladmin -u root -h ser6-66 password 'new-password'
 
Alternatively you can run:
./bin/mysql_secure_installation
 
which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.
 
See the manual for more instructions.
 
You can start the MySQL daemon with:
cd . ; ./bin/mysqld_safe &
 
You can test the MySQL daemon with mysql-test-run.pl
cd ./mysql-test ; perl mysql-test-run.pl
 
Please report any problems with the ./bin/mysqlbug script!
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

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

[client]
socket=/usr/local/mysql/mysql.sock
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
ndbcluster
ndb-connectstring=192.168.6.66  #管理节点ip
[mysql_cluster]
ndb-connectstring=192.168.6.66  #管理节点ip
1
2
3
4
5
6
7
8
9

启动MySQL Server：
cd /usr/local/mysql/bin

[root@ser6-66 bin]# ./mysqld_safe -user=mysql &

[1] 7338

[root@ser6-66 bin]# 150824 13:06:05 mysqld_safe Logging to ‘/usr/local/mysql/data/ser6-66.err’.

150824 13:06:05 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

或者可以这样启动：

cp /usr/local/mysql/support-files/mysql.server /etc/rc.d/init.d/mysql

service mysql start

ndb_mgm> show

Cluster Configuration
---------------------
[ndbd(NDB)]2 node(s)
id=2@192.168.6.68  (mysql-5.5.35 ndb-7.2.15, Nodegroup: 0, *)
id=3@192.168.6.69  (mysql-5.5.35 ndb-7.2.15, Nodegroup: 1)
 
[ndb_mgmd(MGM)]1 node(s)
id=1@192.168.6.66  (mysql-5.5.35 ndb-7.2.15)
 
[mysqld(API)]2 node(s)
id=4@192.168.6.66  (mysql-5.5.35 ndb-7.2.15)
id=5@192.168.6.67  (mysql-5.5.35 ndb-7.2.15)
1
2
3
4
5
6
7
8
9
10
11
12

显示都连上了。

同理配置和启动SQL node 2。

6：配PATH

在各节点配置PATH

vi /root/.bash_profile
1

在PATH这一行后面，添加/usr/local/mysql/bin，如：

PATH=$PATH:$HOME/bin:/usr/local/mysql/bin
source /root/.bash_profile
1
2

7：修改root密码

在sql节点上修改：

/usr/local/mysql/bin/mysqladmin -u root password '123456' 
1

假如报错：

Enter password: 

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/usr/local/mysql/mysql.sock' (2) 

[root@ser6-67 ~]# find / -name mysql.sock

/tmp/mysql.sock

[root@ser6-67 ~]# ln -s /tmp/mysql.sock /usr/local/mysql/mysql.sock
1
2
3
4
5
6
7
8
9

8：测试

登录sql节点192.168.6.67：

8.1 测试sql节点是否同步

[root@ser6-67 bin]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.35-ndb-7.2.15-cluster-gpl MySQL Cluster Community Server (GPL)
 
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| ndbinfo            |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)
 
--看到mysql cluster比mysql多了一个ndbinfo数据库。
 
--测试sql节点是否同步
在6.67上建个dba库，在dba下建个表t,t2。
mysql> create database dba;
Query OK, 1 row affected (0.07 sec)
 
mysql> use dba;
Database changed
mysql> create table t(id int);
Query OK, 0 rows affected (0.02 sec)
mysql> create table t2(id int) engine=ndb;
Query OK, 0 rows affected (0.11 sec)
 
mysql> show tables;
+---------------+
| Tables_in_dba |
+---------------+
| t             |
| t2            |
+---------------+
2 rows in set (0.00 sec)
 
 
在6.66上登录mysql，看到存储引擎为ndb的t2同步过来了，t1没同步过来：
mysql> use dba;
Database changed
mysql> show tables;
Empty set (0.03 sec)
 
 
mysql> show tables;
+---------------+
| Tables_in_dba |
+---------------+
| t2            |
+---------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64

– 总结：只有表存储引擎为 ndb 时，才能同步。

8.2 分片测试

mysql> insert into t2 values(1);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into t2 values(2);
Query OK, 1 row affected (0.00 sec)
 
mysql> select partition_name,table_rows from information_schema.PARTITIONS where     table_name='t2' and table_schema='dba';
    
+----------------+------------+
| partition_name | table_rows |
+----------------+------------+
| p0             |          1 |
| p1             |          1 |
 
+----------------+------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

看到数据被自动分到了两个数据节点。

8.3 模拟NDB节点宕机

当ndb节点宕掉一个，其上面的数据还可以通过sql节点查询吗

影响后续的插入删除吗？

在192.168.6.68上杀掉数据节点：
mgm控制台会自动显示：

ndb_mgm> Node 2: Node shutdown completed. Initiated by signal 15.
ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]	2 node(s)
id=2 (not connected, accepting connect from 192.168.6.68)
id=3	@192.168.6.69  (mysql-5.5.35 ndb-7.2.15, Nodegroup: 0, *)
 
[ndb_mgmd(MGM)]	1 node(s)
id=1	@192.168.6.66  (mysql-5.5.35 ndb-7.2.15)
 
[mysqld(API)]	2 node(s)
id=4	@192.168.6.66  (mysql-5.5.35 ndb-7.2.15)
id=5	@192.168.6.67  (mysql-5.5.35 ndb-7.2.15)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

看到其他节点没有宕掉。

登录sql节点192.168.6.66或67，发现数据都也还在：

mysql> select * from t2;
+------+
| id   |
+------+
|    2 |
|    1 |
+------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8

再插入测试数据，仍然可以正常插入。

mysql>  insert into t2 values(1);
Query OK, 1 row affected (0.00 sec)
 
mysql>  insert into t2 values(4);
Query OK, 1 row affected (0.00 sec)
 
mysql>  select partition_name,table_rows from information_schema.PARTITIONS where     table_name='t2' and table_schema='dba';
+----------------+------------+
| partition_name | table_rows |
+----------------+------------+
| p0             |          2 |
| p1             |          2 |
+----------------+------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

我们看到杀掉其中一个NDB节点，mysql依然高可用。

实验完毕，现在，我们再启动该数据节点。

/usr/local/mysql/bin/ndbd   --ndb-connectstring=192.168.6.66
--注意，这次启动，就不要再指定initial参数了。
1
2

8.4 模拟sql节点宕机

测试关闭其中一个sql节点，看集群能否正常使用

[root@ser6-66 mysql-cluster]# service mysql stop
Shutting down MySQL....                                    [  OK  ]
 
192.168.6.67上看还能否正常插入：
mysql> use dba;
Database changed
 
mysql> insert into t2(id) values(3);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into t2(id) values(5);
Query OK, 1 row affected (0.00 sec)
 
说明只存在一个sql节点，不影响使用，验证了集群的高可用。
启动6.66的sql节点：
[root@ser6-66 mysql-cluster]# service mysql start
Starting MySQL..                                           [  OK  ]
 
mysql> select * from t2;
No connection. Trying to reconnect...
Connection id:    2
Current database: dba
 
+------+
| id   |
+------+
|    3 |
|    5 |
|    1 |
|    2 |
|    1 |
|    4 |
+------+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

看到数据同步过来了。