【原创】Windows下Mysql Cluster集群扩容方法
3.2.7.3 集群扩容
由于MySQL集群属于内存存储模式，所以如果出现存储空间不足时，需要扩容，也就是为集群中增加ndbd数据存储节点，以扩大存储空间。根据官方文档（mysql-cluster-excerpt-5.1-en.pdf的5.12章节Adding MySQL Cluster Data Nodes Online），Mysql集群支持在线扩容，具体步骤如下：
a) 首先必须确保新加入的数据节点作为新的group加入，默认2个node为1个group，Group数量=Node总数/NoOfReplicas数；
b) 修改管理节点的config.ini文件，从
|
DataMemory = 100M IndexMemory = 100M NoOfReplicas = 2 DataDir = /usr/local/mysql/var/mysql-cluster [ndbd] Id = 1 HostName = 192.168.0.1 [ndbd] Id = 2 HostName = 192.168.0.2 [mgm] HostName = 192.168.0.10 Id = 10 [api] Id=20 HostName = 192.168.0.20 [api] Management of MySQL Cluster 308 Id=21 HostName = 192.168.0.21 Note |
修改为：
|
[ndbd default] DataMemory = 100M IndexMemory = 100M NoOfReplicas = 2 DataDir = /usr/local/mysql/var/mysql-cluster [ndbd] Id = 1 HostName = 192.168.0.1 [ndbd] Id = 2 HostName = 192.168.0.2[ndbd] Id = 3 HostName = 192.168.0.3 [ndbd] Id = 4 HostName = 192.168.0.4 [mgm] HostName = 192.168.0.10 Id = 10 [api] Id=20 HostName = 192.168.0.20 [api] Id=21 HostName = 192.168.0.21 |
其中蓝色字体为新增加内容，在1、2两个node的基础上，再增加3、4两个node；需要注意增加的NodeID不能与之前已经存在的NodeID重复，不能是1、2、10、20、21，这里取3和4；修改之前用ndb_mgm客户端的show命令查看集群信息如下：
|
-- NDB Cluster -- Management Client -- ndb_mgm> show Connected to Management Server at: 192.168.0.10:1186 Cluster Configuration --------------------- [ndbd(NDB)] 2 node(s) id=1 @192.168.0.1 (5.1.56-ndb-7.1.17, Nodegroup: 0, Master) id=2 @192.168.0.2 (5.1.56-ndb-7.1.17, Nodegroup: 0) [ndb_mgmd(MGM)] 1 node(s) id=10 @192.168.0.10 (5.1.56-ndb-7.1.17) [mysqld(API)] 2 node(s) id=20 @192.168.0.20 (5.1.56-ndb-7.1.17) id=21 @192.168.0.21 (5.1.56-ndb-7.1.17) |
c) 停止管理节点，用reload方式启动管理节点，加载新的配置文件：
|
ndb_mgm> 10 stop Node 10 has shut down. Disconnecting to allow Management Server to shutdown ndb_mgm>
C:\mysql\mysql-cluster\bin>ndb_mgmd -f config.ini --reload 2008-12-08 17:29:23 [MgmSrvr] INFO -- NDB Cluster Management Server. 5.1.56-ndb-7.1.17 2008-12-08 17:29:23 [MgmSrvr] INFO -- Reading cluster configuration from 'config.ini' |
启动成功后，使用ndb_mgm客户端的show命令查看集群信息如下，橙色字体显示内容就是我们新加入的数据节点，目前处于未连接状态：
|
-- NDB Cluster -- Management Client -- ndb_mgm> SHOW Connected to Management Server at: 192.168.0.10:1186 Cluster Configuration --------------------- [ndbd(NDB)] 2 node(s) id=1 @192.168.0.1 (5.1.56-ndb-7.1.17, Nodegroup: 0, Master) id=2 @192.168.0.2 (5.1.56-ndb-7.1.17, Nodegroup: 0) id=3 (not connected, accepting connect from 192.168.0.3) id=4 (not connected, accepting connect from 192.168.0.4) [ndb_mgmd(MGM)] 1 node(s) id=10 @192.168.0.10 (5.1.56-ndb-7.1.17) [mysqld(API)] 2 node(s) id=20 @192.168.0.20 (5.1.56-ndb-7.1.17) id=21 @192.168.0.21 (5.1.56-ndb-7.1.17) |
d) 滚动重启旧的数据存储节点服务（滚动重启，不能所有节点同时重启，如果同时停止，数据库前端查询就会出现错误）：
|
ndb_mgm> 1 RESTART Node 1: Node shutdown initiated Node 1: Node shutdown completed, restarting, no start. Node 1 is being restarted ndb_mgm> Node 1: Start initiated (version 7.1.17) Node 1: Started (version 7.1.17) ndb_mgm> 2 RESTART Node 2: Node shutdown initiated Node 2: Node shutdown completed, restarting, no start. Node 2 is being restarted ndb_mgm> Node 2: Start initiated (version 7.1.17) ndb_mgm> Node 2: Started (version 7.1.17) |
e) 滚动重启所有的数据查询节点服务
|
C:\mysql\mysql-cluster\bin > mysqladmin -uroot -ppassword shutdown C:\mysql\mysql-cluster\bin > mysqld.exe --console --ndbcluster --ndb-connectstring=192.168.0.10 |
f) 以初始化方式启动新增加的数据存储节点：
|
C:\mysql\mysql-cluster\bin >ndbd -c 192.168.0.10 --initial |
启动两个存储后，使用ndb_mgm客户端运行show命令，我们可以看到新增加的节点已经启动，并处于未分组状态：
|
ndb_mgm> SHOW Connected to Management Server at: 192.168.0.10:1186 Cluster Configuration --------------------- [ndbd(NDB)] 2 node(s) id=1 @192.168.0.1 (5.1.56-ndb-7.1.17, Nodegroup: 0, Master) id=2 @192.168.0.2 (5.1.56-ndb-7.1.17, Nodegroup: 0) id=3 @192.168.0.3 (5.1.56-ndb-7.1.17, no nodegroup) id=4 @192.168.0.4 (5.1.56-ndb-7.1.17, no nodegroup) [ndb_mgmd(MGM)] 1 node(s) id=10 @192.168.0.10 (5.1.56-ndb-7.1.17) [mysqld(API)] 2 node(s) id=20 @192.168.0.20 (5.1.56-ndb-7.1.17) id=21 @192.168.0.21 (5.1.56-ndb-7.1.17) |
g) 为新节点增加分组, 使用ndb_mgm客户端运行如下命令：
|
ndb_mgm> CREATE NODEGROUP 3,4 Nodegroup 1 created |
分组完成后，使用ndb_mgm客户端运行show命令，我们可以看到新增加的节点已经启动，并处于已经分组状态：
|
ndb_mgm> SHOW Connected to Management Server at: 192.168.0.10:1186 Cluster Configuration --------------------- [ndbd(NDB)] 2 node(s) id=1 @192.168.0.1 (5.1.56-ndb-7.1.17, Nodegroup: 0, Master) id=2 @192.168.0.2 (5.1.56-ndb-7.1.17, Nodegroup: 0) id=3 @192.168.0.3 (5.1.56-ndb-7.1.17, Nodegroup: 1) id=4 @192.168.0.4 (5.1.56-ndb-7.1.17, Nodegroup: 1) [ndb_mgmd(MGM)] 1 node(s) id=10 @192.168.0.10 (5.1.56-ndb-7.1.17) [mysqld(API)] 2 node(s) id=20 @192.168.0.20 (5.1.56-ndb-7.1.17) id=21 @192.168.0.21 (5.1.56-ndb-7.1.17) |
h) 重新分配集群数据
- 当一个新的数据组创建后，已经存在的数据与索引不会自动分配到这些个新的数据组上，我们可以在ndb_mgm客户端用all report memory命令查看到如下信息：
|
ndb_mgm> ALL REPORT MEMORY Node 1: Data usage is 5%(177 32K pages of total 3200) Node 1: Index usage is 0%(108 8K pages of total 12832) Node 2: Data usage is 5%(177 32K pages of total 3200) Node 2: Index usage is 0%(108 8K pages of total 12832) Node 3: Data usage is 0%(0 32K pages of total 3200) Node 3: Index usage is 0%(0 8K pages of total 12832) Node 4: Data usage is 0%(0 32K pages of total 3200) Node 4: Index usage is 0%(0 8K pages of total 12832) |
- 在ndb_mgm客户端上用ndb_desc -p 命令查看分区使用情况（其中ips为表名）：
|
C:\mysql\mysql-cluster\bin > ndb_desc -c 192.168.0.10 -d n ips -p -- ips -- Version: 1 Fragment type: 9 K Value: 6 Min load factor: 78 Max load factor: 80 Temporary table: no Number of attributes: 6 Number of primary keys: 1 Length of frm data: 340 Row Checksum: 1 Row GCI: 1 SingleUserMode: 0 ForceVarPart: 1 FragmentCount: 2 TableStatus: Retrieved -- Attributes -- id Bigint PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY AUTO_INCR country_code Char(2;latin1_swedish_ci) NOT NULL AT=FIXED ST=MEMORY type Char(4;latin1_swedish_ci) NOT NULL AT=FIXED ST=MEMORY ip_address Varchar(15;latin1_swedish_ci) NOT NULL AT=SHORT_VAR ST=MEMORY addresses Bigunsigned NULL AT=FIXED ST=MEMORY date Bigunsigned NULL AT=FIXED ST=MEMORY -- Indexes -- PRIMARY KEY(id) - UniqueHashIndex PRIMARY(id) - OrderedIndex -- Per partition info -- Partition Row count Commit count Frag fixed memory Frag varsized memory 0 26086 26086 1572864 557056 1 26329 26329 1605632 557056 NDBT_ProgramExit: 0 - OK |
说明新加入的节点上并未存储数据内容；
- 我们可以用一个命令让这些表的数据在所有数据节点上重新分配空间，也就是让数据分布到所有的存储节点上，对于每张NDBCLUSTER存储类型的表, 在mysql客户端执行ALTER ONLINE TABLE ... REORGANIZE PARTITION 语句。即可让这张表的数据重新分布，例如：执行ALTER ONLINE TABLE ips REORGANIZE PARTITION后，我们再次用ndb_desc命令查看ips表的分布信息，这时这张表的数据存储在四个节点node分区上：
|
shell> ndb_desc -c 192.168.0.10 -d n ips -p -- ips -- Version: 16777217 Fragment type: 9 K Value: 6 Min load factor: 78 Max load factor: 80 Temporary table: no Number of attributes: 6 Number of primary keys: 1 Length of frm data: 341 Row Checksum: 1 Row GCI: 1 SingleUserMode: 0 ForceVarPart: 1 FragmentCount: 4 TableStatus: Retrieved -- Attributes -- id Bigint PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY AUTO_INCR country_code Char(2;latin1_swedish_ci) NOT NULL AT=FIXED ST=MEMORY type Char(4;latin1_swedish_ci) NOT NULL AT=FIXED ST=MEMORY ip_address Varchar(15;latin1_swedish_ci) NOT NULL AT=SHORT_VAR ST=MEMORY addresses Bigunsigned NULL AT=FIXED ST=MEMORY date Bigunsigned NULL AT=FIXED ST=MEMORY -- Indexes -- PRIMARY KEY(id) - UniqueHashIndex PRIMARY(id) - OrderedIndex -- Per partition info -- Partition Row count Commit count Frag fixed memory Frag varsized memory 0 12981 52296 1572864 557056 1 13236 52515 1605632 557056 2 13105 13105 819200 294912 3 13093 13093 819200 294912 NDBT_ProgramExit: 0 - OK |
- 重新分布后，在ndb_mgm客户端用ALL REPORT MEMORY命令验证存储的使用状态，可以发现节点3、4上都已经存储了数据：
|
ndb_mgm> ALL REPORT MEMORY Node 1: Data usage is 5%(176 32K pages of total 3200) Node 1: Index usage is 0%(76 8K pages of total 12832) Node 2: Data usage is 5%(176 32K pages of total 3200) Node 2: Index usage is 0%(76 8K pages of total 12832) Node 3: Data usage is 2%(80 32K pages of total 3200) Node 3: Index usage is 0%(51 8K pages of total 12832) Node 4: Data usage is 2%(80 32K pages of total 3200) Node 4: Index usage is 0%(50 8K pages of total 12832) |
