MySQL入门学习之——MySQL Cluster初体验

    最近有一位朋友咨询了我一个MySQL Cluster的问题，实话实说，这个东东我还没玩过，不过既然遇到了它，那么就开始体验一把吧。朋友的问题如下：

“MySQL集群是不是只能新增数据同步，而不能减少数据呢？例如 三个节点  A ,B ,C ,A是管理节点，B 与C 是SQL节点 数据节点，B 新增数据库和表 ，C 也就新增了，但 B 删除数据和表后， C 没有跟着删除，什么原因？   这不就和master 同步一样了，怎么做才是分布式存储的？”

    接下来抱着这个疑问来部署并体验一把MySQL Cluster，整个部署步骤如下：

*****************************************************************************

0.环境介绍
主机名       IP地址            角色
hadoop1    10.1.245.72    管理节点
hadoop2    10.1.245.73    Data节点1
hadoop3    10.1.245.74    Data节点2
hadoop2    10.1.245.73    SQL节点1
hadoop3    10.1.245.74    SQL节点2

1.下载MySQL Cluster
wget http://download.softagency.net/MySQL/Downloads/MySQL-Cluster-7.3/mysql-cluster-gpl-7.3.12-linux-glibc2.5-x86_64.tar.gz

2.配置SQL nodes(hadoop2~hadoop3)
2.1 创建用户
groupadd -g 312 mysql
mkdir -p /aifs01/users
useradd -u 312   -g mysql -d /aifs01/users/mysql  mysql
passwd mysql --stdin <<<oracle< oracle</oracle<>

2.2 解压软件包
tar -C /opt/freeware -xzvf /opt/freeware/mysql-cluster-gpl-7.3.12-linux-glibc2.5-x86_64.tar.gz
ln -s /opt/freeware/mysql-cluster-gpl-7.3.12-linux-glibc2.5-x86_64 /opt/freeware/mysql

2.3 初始化数据库
cd  /opt/freeware/mysql
scripts/mysql_install_db --user=mysql

2.4 设置data目录权限
chown -R root .
chown -R mysql data
chgrp -R mysql .

2.5 配置SQL nodes
vi /etc/my.cnf
[mysqld]
# Options for mysqld process:
ndbcluster                      # run NDB storage engine

[mysql_cluster]
# Options for MySQL Cluster processes:
ndb-connectstring=10.1.245.72  # location of management server

2.6 配置mysql服务开机自启动
cp support-files/mysql.server /etc/rc.d/init.d/
chmod +x /etc/rc.d/init.d/mysql.server
chkconfig --add mysql.server

3.配置Data nodes(hadoop2~hadoop3)
3.1 拷贝程序到默认的PATH路径
cp bin/ndbd /usr/local/bin/ndbd
cp bin/ndbmtd /usr/local/bin/ndbmtd
cd /usr/local/bin
chmod +x ndb*

3.2 创建数据节点的数据目录
mkdir -p /aifs01/users/mysql/ndb-data
chown -R mysql:mysql /aifs01/users/mysql/ndb-data

4.配置管理节点(hadoop1)
4.1 解压软件包
cd /opt/freeware
tar -zxvf mysql-cluster-gpl-7.3.12-linux-glibc2.5-x86_64.tar.gz
cd mysql-cluster-gpl-7.3.12-linux-glibc2.5-x86_64
cp bin/ndb_mgm* /usr/local/bin
cd /usr/local/bin
chmod +x ndb_mgm*

4.2 配置管理节点
mkdir /var/lib/mysql-cluster
cd /var/lib/mysql-cluster
vi config.ini
[ndbd default]
# Options affecting ndbd processes on all data nodes:
NoOfReplicas=2    # Number of replicas
DataMemory=80M    # How much memory to allocate for data storage
IndexMemory=18M   # How much memory to allocate for index storage
                  # For DataMemory and IndexMemory, we have used the
                  # default values. Since the "world" database takes up
                  # only about 500KB, this should be more than enough for
                  # this example Cluster setup.

[tcp default]
# TCP/IP options:
portnumber=2202   # This the default; however, you can use any
                  # port that is free for all the hosts in the cluster
                  # Note: It is recommended that you do not specify the port
                  # number at all and simply allow the default value to be used
                  # instead

[ndb_mgmd]
# Management process options:
hostname=10.1.245.72            # Hostname or IP address of MGM node
datadir=/var/lib/mysql-cluster  # Directory for MGM node log files

[ndbd]
# Options for data node "A":
# (one [ndbd] section per data node)
hostname=10.1.245.73            # Hostname or IP address
datadir=/aifs01/users/mysql/ndb-data   # Directory for this data node's data files

[ndbd]
# Options for data node "B":
hostname=10.1.245.74            # Hostname or IP address
datadir=/aifs01/users/mysql/ndb-data   # Directory for this data node's data files

[mysqld]
# SQL node options:
hostname=10.1.245.73            # Hostname or IP address
hostname=10.1.245.74 
                                # (additional mysqld connections can be
                                # specified for this node for various
                                # purposes such as running ndb_restore)

5.初始化启动MySQL Cluster
5.1 启动管理节点
ndb_mgmd -f /var/lib/mysql-cluster/config.ini

5.2 启动Data nodes
ndbd

5.3 启动SQL nodes
bin/mysqld_safe &

6.测试MySQL Cluster
6.1 查看集群状态
[root@hadoop1 mysql-cluster]# ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=2    @10.1.245.73  (mysql-5.6.28 ndb-7.3.12, Nodegroup: 0, *)
id=3    @10.1.245.74  (mysql-5.6.28 ndb-7.3.12, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @10.1.245.72  (mysql-5.6.28 ndb-7.3.12)

[mysqld(API)]   2 node(s)
id=4    @10.1.245.73  (mysql-5.6.28 ndb-7.3.12)
id=5    @10.1.245.74  (mysql-5.6.28 ndb-7.3.12)

注：从show命令输出看到data nodes和sql nodes的信息，说明整个集群配置成功

6.2 在SQL node1上模拟新增数据
[root@hadoop2 bin]# ./mysql -uroot -p -S /var/lib/mysql/mysql.sock -s
Enter password:
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| ndbinfo            |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> create database clusterdb;
Query OK, 1 row affected (0.05 sec)

mysql> use clusterdb
Database changed
mysql> create table t(id int);
Query OK, 0 rows affected (0.20 sec)

mysql> insert into t values(1),(2),(3);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql> create table t_cluster(id int);
Query OK, 0 rows affected (0.37 sec)

mysql> drop table t_cluster;
Query OK, 0 rows affected (0.96 sec)

mysql> create table t_cluster(id int) engine=ndb;
Query OK, 0 rows affected (0.85 sec)

mysql> insert into t_cluster values(4),(5),(6);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_cluster;
+------+
| id   |
+------+
|    6 |
|    5 |
|    4 |
+------+
3 rows in set (0.00 sec)

6.3 在SQL node2查看数据
[root@hadoop3 bin]# ./mysql -uroot -p -S /var/lib/mysql/mysql.sock
Enter password:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| clusterdb          |
| mysql              |
| ndbinfo            |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)

mysql> use clusterdb

Database changed
mysql> show tables;
+---------------------+
| Tables_in_clusterdb |
+---------------------+
| t_cluster           |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from t_cluster;
+------+
| id   |
+------+
|    4 |
|    6 |
|    5 |
+------+
3 rows in set (0.00 sec)
注：从SQL node2上也可以查询到一致的数据（当然前提是针对ndb引擎的表,非ndb引擎只能在当前sql node查看到数据）

6.4 在SQL node2上模拟删除数据
--SQL node2上执行删除操作
mysql> delete from t_cluster;
Query OK, 3 rows affected (0.10 sec)

mysql> drop table t_cluster;
Query OK, 0 rows affected (0.24 sec)

--SQL node1上的操作
mysql> select * from t_cluster;
Empty set (0.00 sec)

mysql> select * from t_cluster;
ERROR 1146 (42S02): Table 'clusterdb.t_cluster' doesn't exist
注：从SQL node1上同样可以查询到一致的数据视图

总结：整个Mysql Cluster是由三部分组成的，管理节点，SQL节点以及数据节点。管理节点负责管理整个集群并且维护节点成员之间的关系。数据节点自然是存储整个集群的数据，SQL节点作为对整个集群操作的入口，对集群的增删查改都是通过SQL节点来完成的，所以当你无论你通过哪一个SQL节点操作集群，集群给你展示的数据视图是一致的，底层的数据分布由数据节点来控制，对上层应用来说是透明的。 最后附上Mysql Cluster的架构图，加强对其的理解。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20801486/viewspace-1983428/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20801486/viewspace-1983428/