mysql cluster 7.2在线添加数数据节点
本文主要是摘抄和翻译了一下官方文档，7.2是最新版本，集成mysq5.5，memcached的API。据说性能很好。由于资料不是很多，网络文档又怕有错误的地方。所以翻译了一下官方的manual。在线添加数据节点大致分为以下几个步骤：
1. Edit the cluster configuration config.ini file, adding new sections corresponding to the nodes to be added. In the case where the cluster uses multiple management servers, these changes need to be made to all config.ini files used by the management servers.
2. Perform a rolling restart of all MySQL Cluster management servers. All management servers must be restarted with the --reload or --initial option to force the reading of the new configuration.
3. Perform a rolling restart of all existing MySQL Cluster data nodes. It is not necessary (or usually even desirable) to use --initial when restarting the existing data nodes.If you are using API nodes with dynamically allocated> 4. Perform a rolling restart of any SQL or API nodes connected to the MySQL Cluster.
5. Perform an initial start of the new data nodes.
6. Execute one or more CREATE NODEGROUP commands in the MySQL Cluster management client to create the new node group or node groups to which the new data nodes will belong.
7. Execute one or more CREATE NODEGROUP commands in the MySQL Cluster management client to create the new node group or node groups to which the new data nodes will belong.（This needs to be done only for tables already existing at the time the new node group is added. Data in tables created after the new node group is added is distributed automatically; however, data added to any given table tbl that existed before the new nodes were added is not distributed using the new nodes until that table has been reorganized using ALTER ONLINE TABLE tbl REORGANIZE PARTITION.）
8. Reclaim the space freed on the “old” nodes by issuing, for each NDBCLUSTER table, an OPTIMIZE TABLE statement in the mysql client.
一个例子：
假设现在的配置文件config.ini如下：
DataMemory = 100M
IndexMemory = 100M
NoOfReplicas = 2
DataDir = /usr/local/mysql/var/mysql-cluster
Id = 1
HostName = 192.168.0.1
Id = 2
HostName = 192.168.0.2
HostName = 192.168.0.10
Id = 10
Id=20
HostName = 192.168.0.2
Id=21
HostName = 192.168.0.21
注意：我们留下一个空白的序列在数据节点和其他节点。这样容易为新添加的数据服务器分配新的未使用的节点id。
现在我们show以下整个cluster的信息：
ndb_mgm> SHOW
Connected to Management Server at: 192.168.0.10:1186
Cluster Configuration
---------------------
2 node(s)
id=1 @192.168.0.1(5.1.61-ndb-7.1.20, Nodegroup: 0, Master)
id=2 @192.168.0.2(5.1.61-ndb-7.1.20, Nodegroup: 0)
1 node(s)
id=10 @192.168.0.10(5.1.61-ndb-7.1.20)
2 node(s)
id=20 @192.168.0.20(5.1.61-ndb-7.1.20)
id=21 @192.168.0.21(5.1.61-ndb-7.1.20)
最终，我们假设只有一张ndbcluster的表。
USE n;
CREATE TABLE ips (
>
country_code CHAR(2) NOT NULL,
type CHAR(4) NOT NULL,
ip_address varchar(15) NOT NULL,
addresses BIGINT UNSIGNED DEFAULT NULL,
date BIGINT UNSIGNED DEFAULT NULL
) ENGINE NDBCLUSTER;
注意：如果使用多线程的ndbmtd会出现bug（这里不知道翻译的对不对）。
步骤一
升级配置文件（config.ini）
假设我们新添加两个新数据节点192.168.0.3和192.168.0.4
DataMemory = 100M
IndexMemory = 100M
NoOfReplicas = 2
DataDir = /usr/local/mysql/var/mysql-cluster
Id = 1
HostName = 192.168.0.1
Id = 2
HostName = 192.168.0.2
Id = 3
HostName = 192.168.0.3
Id = 4
HostName = 192.168.0.4
HostName = 192.168.0.10
Id = 10
Id=20
HostName = 192.168.0.20
Id=21
HostName = 192.168.0.21
红色字体为新添加的配置.
步骤二
重启管理节点
找到管理节点ID
ndb_mgm> 10 STOP
Node 10 has shut down.
Disconnecting to allow Management Server to shutdown
重新启动管理节点（使用—reload选项）
shell> ndb_mgmd -f config.ini --reload
现在再show一下：
ndb_mgm> SHOW
Connected to Management Server at: 192.168.0.10:1186
Cluster Configuration
---------------------
2 node(s)
id=1 @192.168.0.1(5.1.61-ndb-7.1.20, Nodegroup: 0, Master)
id=2 @192.168.0.2(5.1.61-ndb-7.1.20, Nodegroup: 0)
id=3 (not connected, accepting connect from 192.168.0.3)
id=4 (not connected, accepting connect from 192.168.0.4)
1 node(s)
id=10 @192.168.0.10(5.1.61-ndb-7.1.20)
2 node(s)
id=20 @192.168.0.20(5.1.61-ndb-7.1.20)
id=21 @192.168.0.21(5.1.61-ndb-7.1.20)
看到新添加的节点已经被管理节点读取。
步骤三
对已经存在的数据节点执行环状重启
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.20)
Node 1: Started (version 7.1.20)
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.20)
ndb_mgm> Node 2: Started (version 7.1.20)
注意：一定要等到管理端报告 Node X ….
步骤四
对mysql节点执行环状重启
shell>service mysqld restart
步骤五
对新数据节点进行初始化启动
shell> ndbd -c 192.168.0.3 --initial
shell> ndbd -c 192.168.0.4 –initial
注意：这里不需要一个数据节点启动后在启动另一个，可以同时启动
现在show一下：
ndb_mgm> SHOW
Connected to Management Server at: 192.168.0.10:1186
Cluster Configuration
---------------------
2 node(s)
id=1 @192.168.0.1(5.1.61-ndb-7.1.20, Nodegroup: 0, Master)
id=2 @192.168.0.2(5.1.61-ndb-7.1.20, Nodegroup: 0)
id=3 @192.168.0.3(5.1.61-ndb-7.1.20, no nodegroup)
id=4 @192.168.0.4(5.1.61-ndb-7.1.20, no nodegroup)
1 node(s)
id=10 @192.168.0.10(5.1.61-ndb-7.1.20）
2 node(s)
id=20 @192.168.0.20(5.1.61-ndb-7.1.20)
id=21 @192.168.0.21(5.1.61-ndb-7.1.20)
步骤六
为新数据节点分配新的组
ndb_mgm> CREATE NODEGROUP 3,4
Nodegroup 1 created
ndb_mgm> SHOW
Connected to Management Server at: 192.168.0.10:1186
Cluster Configuration
---------------------
2 node(s)
id=1 @192.168.0.1(5.1.61-ndb-7.1.20, Nodegroup: 0, Master)
id=2 @192.168.0.2(5.1.61-ndb-7.1.20, Nodegroup: 0)
id=3 @192.168.0.3(5.1.61-ndb-7.1.20, Nodegroup: 1)
id=4 @192.168.0.4(5.1.61-ndb-7.1.20, Nodegroup: 1)
1 node(s)
id=10 @192.168.0.10(5.1.61-ndb-7.1.20）
2 node(s)
id=20 @192.168.0.20(5.1.61-ndb-7.1.20)
id=21 @192.168.0.21(5.1.61-ndb-7.1.20)
步骤七
重新分配集群数据
当新加入数据节点时，已经存在的数据或者索引不会被自动分配到新的节点上，如下所示：
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_desc –p 可以看到分区信息。拿上面的那张表为例，看到它还是在使用2个分区：
shell> 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 countFrag fixed memory Frag varsized memory
0 26086 26086 1572864 557056
1 26329 26329 1605632 557056
NDBT_ProgramExit: 0 - OK
-c后面的地址是管理节点地址，-d后面是数据库，数据表
现在进行数据的重新分配
在mysql节点上执行对所有的ndbcluster引擎的表：
Myql>alter online table … reorganize partition；
现在在看一下上面那张表：
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) - OrderedInde
-- Per partition info --
Partition Row count Commit countFrag 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
重新分配数据存储分区之后，经常使用 optimize table去掉浪费的空间 。
想得到所有使用ndbcluster引擎的表，可以使用下面的查询：
Mysql>select table_schema,table_name from information_schema.tables where engine= ‘ndbcluster’;
现在使用 all report memory查看一下数据和索引的存储情况：
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)
页:
[1]