mysql cluster 7 動態增加資料節點配置步驟

vi /usr/local/mysql/mysql-cluster/config.ini

[ndbd default]

DataMemory = 100M

IndexMemory = 100M

NoOfReplicas = 2

DataDir = /usr/local/mysql/var/mysql-cluster

[ndbd]

Id = 1

HostName = 172.20.86.188

[ndbd]

Id = 2

HostName = 172.20.86.189

[mgm]

HostName = 172.20.86.185

Id = 10

[mysqld]

Id=20

HostName = 172.20.86.185

2 在185 上執行ndb_mgm

Ndb_mgm>show

-- NDB Cluster -- Management Client --

ndb_mgm> SHOW

Connected to Management Server at: 172.20.86.185:1186

Cluster Configuration

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

[ndbd(NDB)]     2 node(s)

id=1    @172.20.86.188  (5.1.32-ndb-7.0.5, Nodegroup: 0, Master)

id=2    @172.20.86.189  (5.1.32-ndb-7.0.5, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)

id=10   @172.20.86.185  (5.1.32-ndb-7.0.5)

[mysqld(API)]   2 node(s)

id=20   @172.20.86.185  (5.1.32-ndb-7.0.5)

[ndbd default]

DataMemory = 100M

IndexMemory = 100M

NoOfReplicas = 2

DataDir = /usr/local/mysql/var/mysql-cluster

[ndbd]

Id = 1

HostName = 172.20.86.188

[ndbd]

Id = 2

HostName = 172.20.86.189

[ndbd]

Id = 3

HostName = 172.20.86.187

[ndbd]

Id = 4

HostName = 172.20.86.186

[mgm]

HostName = 172.20.86.185

Id = 10

[mysqld]

Id=20

HostName = 172.20.86.185

停止管理節點

ndb_mgm> 10 STOP

Node 10 has shut down.

Disconnecting to allow Management Server to shutdown

shell>

重新載入配置檔案

shell> ndb_mgmd -f config.ini --reload

2008-12-08 17:29:23 [MgmSrvr] INFO     -- NDB Cluster Management Server. 5.1.34-ndb-7.0.7

2008-12-08 17:29:23 [MgmSrvr] INFO     -- Reading cluster configuration from 'config.ini'

檢視新叢集狀態

-- NDB Cluster -- Management Client --

ndb_mgm> SHOW

Connected to Management Server at: 172.20.86.185:1186

Cluster Configuration

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

[ndbd(NDB)]     2 node(s)

id=1    @172.20.86.188  (5.1.32-ndb-7.0.5, Nodegroup: 0, Master)

id=2    @172.20.86.189  (5.1.32-ndb-7.0.5, Nodegroup: 0)

id=3 (not connected, accepting connect from 172.20.86.186)

id=4 (not connected, accepting connect from 172.20.86.187)

[ndb_mgmd(MGM)] 1 node(s)

id=10   @172.20.86.185  (5.1.32-ndb-7.0.5)

[mysqld(API)]   1 node(s)

id=20   @172.20.86.185 (5.1.32-ndb-7.0.5)

輪流重新啟動連線的資料節點

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.0.5)

Node 1: Started (version 7.0.5)

ndb_mgm> 2 RESTART

Node 2: Node shutdown initiated

Node 2: Node shutdown completed, restarting, no start.

Node 2 is being restarted

輪流重啟sql節點

本案僅有一個sql節點

shell> mysqladmin -uroot -ppassword shutdown

shell> mysqld_safe &

初始化新的資料節點

在新增的資料節點上 186 和187 上執行

Shell>ndbmtd –initial

或者

Shell>ndbd –initial

登陸管理節點檢視叢集狀態

Connected to Management Server at: 172.20.86.185:1186

Cluster Configuration

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

[ndbd(NDB)]     2 node(s)

id=1    @172.20.86.188  (5.1.32-ndb-7.0.5, Nodegroup: 0, Master)

id=2    @172.20.86.189  (5.1.32-ndb-7.0.5, Nodegroup: 0)

id=3    @172.20.86.186  (5.1.32-ndb-7.0.5, no nodegroup)

id=4    @172.20.86.187  (5.1.32-ndb-7.0.5, no nodegroup)

[ndb_mgmd(MGM)] 1 node(s)

id=10   @172.20.86.185  (5.1.32-ndb-7.0.5)

[mysqld(API)]   2 node(s)

id=20   @172.20.86.185 (5.1.32-ndb-7.0.5)

ndb_mgm> CREATE NODEGROUP 3,4

Nodegroup 1 created

ndb_mgm> SHOW

Connected to Management Server at: 172.20.86.185:1186

Cluster Configuration

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

[ndbd(NDB)]     2 node(s)

id=1    @172.20.86.188  (5.1.32-ndb-7.0.5, Nodegroup: 0, Master)

id=2    @172.20.86.189  (5.1.32-ndb-7.0.5, Nodegroup: 0)

id=3    @172.20.86.186 (5.1.32-ndb-7.0.5, Nodegroup: 1)

id=4    @172.20.86.187 (5.1.32-ndb-7.0.5, Nodegroup: 1)

[ndb_mgmd(MGM)] 1 node(s)

id=10   @172.20.86.185 (5.1.32-ndb-7.0.5)

[mysqld(API)]   1 node(s)

id=20   @172.20.86.185 (5.1.32-ndb-7.0.5)

資料的重新分配

對於新增的資料節點一些訪問量較高的資料和比較重要的資料需要從新分配。

分發資料命令：

Alter online table ips reorganize partition;

檢視資料的分配情況：

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)

或者

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 count  Frag fixed memory   Frag varsized memory

0           26086       26086         1572864             557056

1           26329       26329         1605632             557056

NDBT_ProgramExit: 0 - OK

You can cause the data to be redistributed among all of the data nodes by performing, for each NDBCLUSTER table, an ALTER ONLINE TABLE ... REORGANIZE PARTITION statement in the mysql client. After issuing the statement ALTER ONLINE TABLE ips REORGANIZE PARTITION, you can see using ndb_desc that the data for this table is now stored using 4 partitions, as shown here (with the relevant portions of the output in bold type):

shell> ndb_desc -c 172.20.86.185 -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