[Original] resizing of MySQL cluster in Windows

ArticleDirectory

• 3.2.7.3 Cluster Expansion

3.2.7.3 Cluster Expansion

Because the MySQL cluster is in the memory storage mode, if there is insufficient storage space, you need to expand the storage space by adding ndbd data storage nodes to the cluster. The official document (adding MySQL cluster data nodes online in Chapter 5.12 of mysql-cluster-excerpt-5.1-en.pdf). The steps are as follows:

A) first, make sure that the newly added data node is added as the new group. The default value of 2 nodes is 1 group, and the number of groups = Total number of nodes/noofreplicas;

B) modify the config. ini file of the Management node from

Datamemory = 100 m Indexmemory = 100 m 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

To:

[Ndbd default] Datamemory = 100 m Indexmemory = 100 m 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

The blue font adds new content, and adds 3 and 4 nodes based on node 1 and node 2. Note that the added nodeid cannot be the same as the existing nodeid, it cannot be 1, 2, 10, 20, or 21. Here, take 3 and 4. Run the show command on the ndb_mgm client to view the cluster information as follows:

-- 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) Stop the management node, start the management node in reload mode, and load the new configuration file:

Ndb_mgm>10 stop Node 10 has shut down. Disconnecting to allow management server to shutdown Ndb_mgm>   C: \ mysql-cluster \ bin>Ndb_mgmd-F config. ini -- reload 17:29:23 [mgmsrvr] info -- NDB cluster management server. 5.1.56-ndb-7.1.17 17:29:23 [mgmsrvr] info -- Reading cluster configuration from 'config. ini'

After the cluster is successfully started, run the show command on the ndb_mgm client to view the cluster information as follows. The Orange font shows the newly added data node, which is currently in the unconnected status:

-- 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) rolling restart the old data storage node service (rolling restart means that all nodes cannot be restarted at the same time. If both nodes are stopped at the same time, an error will occur in the Database Front-end query ):

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) rolling restart all data query node services

C: \ mysql-cluster \ bin>Mysqladmin-uroot-ppassword Shutdown C: \ mysql-cluster \ bin>Mysqld.exe -- console -- ndbcluster -- NDB-connectstring = 192.168.0.10

F) start the new data storage node in Initialization mode:

C: \ mysql-cluster \ bin>Ndbd-C 192.168.0.10 -- Initial

After the two storages are started, run the show command using the ndb_mgm client. We can see that the newly added nodes have been started and are in the ungrouped status:

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) Add a group for the new node and run the following command on the ndb_mgm client:

Ndb_mgm>Create nodegroup 3, 4 Nodegroup 1 created

After grouping, run the show command on the ndb_mgm client. We can see that the newly added node has been started and is in the group status:

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) Reassign cluster data

1. After a new data group is created, existing data and indexes are not automatically allocated to these new data groups. We can useAll report memoryCommand to view the following information:

Ndb_mgm>All report memory Node 1: data usage is 5% (177 32 K pages of total 3200) Node 1: Index usage is 0% (108 8 K pages of total 12832) Node 2: data usage is 5% (177 32 K pages of total 3200) Node 2: Index usage is 0% (108 8 K pages of total 12832) Node 3: data usage is 0% (0 32 K pages of total 3200) Node 3: Index usage is 0% (0 8 K pages of total 12832) Node 4: data usage is 0% (0 32 K pages of total 3200) Node 4: Index usage is 0% (0 8 K pages of total 12832)

1. Used on the ndb_mgm ClientNdb_desc-PCommand to view partition usage (where IPs is the table name ):

C: \ 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

It indicates that the newly added node does not store data;

1. We can use a command to re-allocate space for data in these tables on all data nodes, that is, to distribute data to all storage nodes.NdbclusterFor tables of the storage typeMySQLClient executionAlter online table... reorganize PartitionStatement. To re-distribute the data in this table, for example, executeAlter online table IPS reorganize PartitionAfter, We useNdb_descCommand to viewIPSTable distribution information. The data in this table is stored in the node partitions of the four nodes:

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

1. After redistribution, useAll report memoryCommand to verify the storage usage status. You can find that data is stored on nodes 3 and 4:

ndb_mgm> All report memory node 1: data usage is 5% (176 32 K pages of total 3200) node 1: index usage is 0% (76 8 K pages of total 12832) node 2: data usage is 5% (176 32 K pages of total 3200) node 2: index usage is 0% (76 8 K pages of total 12832) node 3: data usage is 2% (80 32 K pages of total 3200) node 3: index usage is 0% (51 8 K pages of total 12832) node 4: data usage is 2% (80 32 K pages of total 3200) node 4: index usage is 0% (50 8 K pages of total 12832)