set [--sequential-restart] [--retry] [--restart=restart_level]attribute_assignment_listcluster_nameattribute_assignment_list:attribute_assignment[,attribute_assignment][,...]attribute_assignment: [~]attribute_name:process_specification[+process_specification][=value]process_specification: [process_name][:process_id]process_name: {ndb_mgmd|ndbd|ndbmtd|mysqld|ndbapi}restart_level: {N|NI}This command is used to set values for one or more configuration attributes. Attributes can be set on either the process level or instance level.
set commands are executed whether or not the cluster has been started. In a cluster that is not running, the MySQL Cluster Manager merely updates the configuration files. However, in a running cluster, the MySQL Cluster Manager in addition automatically performs any node restarts or rolling restarts (seePerforming a Rolling Restart of an NDB Cluster) that are required to cause the attribute changes to take effect. However, since restart operations—particularly rolling restarts—can take a great deal of time, it is preferable to make configuration changes before starting the cluster and putting it into use.
For any configuration options that normally require the nodes of the cluster to be restarted for the a running cluster to be reconfigured, if aset command is attempted with the same value as was already in use, the command returns an error, telling the user that the command results in no changes to the cluster. If it is really necessary to run theset command in the situation and force a restart of the relevant processes, use the--retry option.
Use the--sequential-restart option to make the rolling restart performed by theset command asequential one.
Sets the path to a password file when NDB Cluster TDE is in use. This is actually implemented as a configuration attribute. Example:
mcm> set filesystem-password-file:ndbmtd:=/home/myndb/myc.pwd mycluster;SeeSetting Up Encryption, for more information.
To set an attribute on the process level, use aset statement that contains an attribute assignment having the formattribute_name:process_name=value.
For example, to setDataMemory to 500 MB on thendbd process level, so that the new value applies to allndbd processes in the cluster, you can issue aset command containing the attribute assignmentDataMemory:ndbd=500M, as shown here:
mcm> set DataMemory:ndbd=500M mycluster;+-----------------------------------+| Command result |+-----------------------------------+| Cluster reconfigured successfully |+-----------------------------------+1 row in set (5.68 sec) To verify that the new setting is being used, you can issue the followingget command:
mcm> get DataMemory mycluster;+------------+-------+----------+------+----------+------+---------+---------+| Name | Value | Process1 | Id1 | Process2 | Id2 | Level | Comment |+------------+-------+----------+------+----------+------+---------+---------+| DataMemory | 500M | ndbd | 1 | | | Process | || DataMemory | 500M | ndbd | 2 | | | Process | |+------------+-------+----------+------+----------+------+---------+---------+2 rows in set (0.79 sec) For more information about this command, seeSection 5.5.1, “Theget Command”.
To set an attribute for a specific process instance, include the process ID in the attribute assignment; the form of such an attribute assignment isattribute_name:process_name:process_id=value. For example, to set the wait_timeout attribute for themysqld process that has process ID50 to 200, you would issue aset command that contains the attribute assignmentwait_timeout:mysqld:51=200, like this:
mcm> set wait_timeout:mysqld:50=200 mycluster;+-----------------------------------+| Command result |+-----------------------------------+| Cluster reconfigured successfully |+-----------------------------------+1 row in set (6.18 sec) You can verify that the setting has taken effect using an applicableget command:
mcm> get wait_timeout mycluster;+--------------+-------+----------+------+----------+------+-------+---------+| Name | Value | Process1 | Id1 | Process2 | Id2 | Level | Comment |+--------------+-------+----------+------+----------+------+-------+---------+| wait_timeout | 200 | mysqld | 50 | | | | |+--------------+-------+----------+------+----------+------+-------+---------+1 row in set (0.50 sec) Attributes that are markedRead only cannot be set. Attempting to do so fails with an error, as shown here:
mcm> get :ndbd mycluster;+--------------+-------------+----------+-----+----------+-----+-------+-----------+| Name | Value | Process1 | Id1 | Process2 | Id2 | Level | Comment |+--------------+-------------+----------+-----+----------+-----+-------+-----------+| DataDir | /opt/c2data | ndbd | 1 | | | | || HostName | tonfisk | ndbd | 1 | | | | Read only || NodeId | 2 | ndbd | 1 | | | | Read only || DataDir | /opt/c3data | ndbd | 2 | | | | || HostName | grindval | ndbd | 2 | | | | Read only || NodeId | 3 | ndbd | 2 | | | | Read only |+--------------+-------------+----------+-----+----------+-----+-------+-----------+6 rows in set (1.42 sec)mcm> set HostName:ndbd:1=lax mycluster;ERROR 6008 (00MGR): Config attribute HostName is read only and cannot be changed However, you can set mandatory attributes, such as in the example shown previously in this section where theDataDir configuration attribute was set to a user-defined value.
The mandatoryNoOfReplicas attribute must be set on the process level only. Attempting to set it on the instance level may leave the cluster, the MySQL Cluster Manager, or both in an unusable configuration.
Unlike the case with theget command, you cannot issue aset acting on a“global” scope—that is, you cannot, in a single attribute assignment, set a single value for an attribute such that the new attribute value applies to all processes regardless of process type, even if the attribute having that name can be applied to all process types. Nor can you specify multiple process types in a single attribute assignment. Attempting to do either of these things causes an error, as shown here:
mcm> set DataDir=/var/cluster-data mycluster;ERROR 3 (00MGR): Illegal syntaxmcm> set DataDir:ndb_mgmd,ndbd,mysqld=/var/cluster-data mycluster;ERROR 3 (00MGR): Illegal syntax Instead, you must use a process-level attribute assignment for each process type. However, you are not necessarily required to issue a separate set command for each process type. Instead, you can also make multiple attribute assignments in a singleset command, supplying the assignments as a comma-separated list. Thisset command assigns/var/cdata as the data directory (DataDir) for all MySQL NDB Cluster processes in the cluster namedmycluster:
mcm> set DataDir:ndb_mgmd=/var/cdata, \ DataDir:ndbd=/var/cdata, \ DataDir:mysqld=/var/cdata mycluster;+-----------------------------------+| Command result |+-----------------------------------+| Cluster reconfigured successfully |+-----------------------------------+1 row in set (7.66 sec)mcm> get DataDir mycluster;+---------+------------+----------+---------+----------+---------+-------+---------+| Name | Value | Process1 | NodeId1 | Process2 | NodeId2 | Level | Comment |+---------+------------+----------+---------+----------+---------+-------+---------+| DataDir | /var/cdata | ndbmtd | 1 | | | | || DataDir | /var/cdata | ndbmtd | 2 | | | | || DataDir | /var/cdata | ndb_mgmd | 49 | | | | || datadir | /var/cdata | mysqld | 50 | | | | || datadir | /var/cdata | mysqld | 51 | | | | |+---------+------------+----------+---------+----------+---------+-------+---------+5 rows in set (0.08 sec) As you can see from theget command just shown, the attribute assignments were successful, and took effect on the process level.
In MySQL Cluster Manager, configuration attribute names are not case-sensitive. SeeCase Sensitivity in String Searches for more information about case-sensitivity issues in MySQL Cluster Manager.
Similarly, you cannot reference multiple process IDs in a single attribute assignment, even if they are processes of the same type; the following command doesnot work:
mcm> set DataMemory:ndbd:1,2=750M mycluster;ERROR 3 (00MGR): Illegal syntaxInstead, you would need to use the following command:
mcm> set DataMemory:ndbd:1=750M,DataMemory:ndbd:2=750M mycluster;+-----------------------------------+| Command result |+-----------------------------------+| Cluster reconfigured successfully |+-----------------------------------+1 row in set (7.70 sec) (Of course, if these are the only two data nodes inmycluster, then the commandset DataMemory:ndbd=750M mycluster also accomplishes the same task.)
A few configuration attributes apply to connections between processes and so require you to refer to both processes in the course of setting them. In such cases, you must use a special process specification syntax; seeSetting TCP Connection Attributes, for information about how this is done.
You also cannot set values for multiple attributes in a single attribute assignment; this means that the following commands donot work:
mcm> set UndoDataBuffer=32M,UndoIndexBuffer=8M:ndbd mycluster;ERROR 3 (00MGR): Illegal syntaxmcm> set DataMemory,IndexMemory:ndbd=1G mycluster;ERROR 3 (00MGR): Illegal syntaxHowever, if you write a complete and valid attribute assignment for each attribute whose value you wish to update, you can rewrite these two commands so that they execute successfully, as shown here:
mcm> set UndoDataBuffer:ndbd=32M,UndoIndexBuffer:ndbd=8M mycluster;+-----------------------------------+| Command result |+-----------------------------------+| Cluster reconfigured successfully |+-----------------------------------+1 row in set (6.62 sec)mcm> set DataMemory:ndbd=1G,IndexMemory:ndbd=1G mycluster;+-----------------------------------+| Command result |+-----------------------------------+| Cluster reconfigured successfully |+-----------------------------------+1 row in set (7.04 sec) In fact, there is no reason that you cannot perform all four assignments in a singleset command, using a list of four attribute assignments, like this:
mcm> set UndoDataBuffer:ndbd=32M,UndoIndexBuffer:ndbd=8M, \ DataMemory:ndbd=1G, IndexMemory:ndbd=1G mycluster;+-----------------------------------+| Command result |+-----------------------------------+| Cluster reconfigured successfully |+-----------------------------------+1 row in set (6.24 sec) However, it a good idea not to perform too many attribute assignments in any singleset command, since this makes it more difficult to spot errors.
On Windows, when setting attributes whose values contain paths (such asDataDir), you must replace any backslash characters in the path with forward slashes. Suppose that you want to useC:\temp\node50 for thetmpdir attribute of themysqld process having node ID 50 in a MySQL NDB Cluster namedmycluster that is running on Windows. The original value for this attribute can be seen using the appropriateget command:
mcm> get tmpdir mycluster;+--------+----------------+----------+-----+----------+-----+-------+---------+| Name | Value | Process1 | Id1 | Process2 | Id2 | Level | Comment |+--------+----------------+----------+-----+----------+-----+-------+---------+| tmpdir | c:\c50data\tmp | mysqld | 50 | | | | |+--------+----------------+----------+-----+----------+-----+-------+---------+1 row in set (0.22 sec) The correctset command to make the desired configuration change is shown here:
mcm> set tmpdir:mysqld:50=c:/temp/node50 mycluster;+-----------------------------------+| Command result |+-----------------------------------+| Cluster reconfigured successfully |+-----------------------------------+1 row in set (2.62 sec) When you check the value usingget—even though it was originally shown using backslashes—the forward slashes are used when displaying the new value:
mcm> get tmpdir mycluster;+--------+----------------+----------+-----+----------+-----+-------+---------+| Name | Value | Process1 | Id1 | Process2 | Id2 | Level | Comment |+--------+----------------+----------+-----+----------+-----+-------+---------+| tmpdir | c:/temp/node50 | mysqld | 50 | | | | |+--------+----------------+----------+-----+----------+-----+-------+---------+1 row in set (0.22 sec) However, if you try to use backslashes in the path when issuing theset command, the command fails:
mcm> set tmpdir:mysqld:4=c:\temp\4 mycluster;Outfile disabled.ERROR:Unknown command '\4'.ERROR 6014 (00MGR): Path name for parameter tmpdir must be absolute.The value 'c:mp4' is illegal. You can reset a configuration attribute's value using aset command by putting a tilde (~) before the attribute name. For example, this command is equivalent toreset ndb_batch_size:mysqld:146 mycluster:
mcm> set ~ndb_batch_size:mysqld:146 mycluster The notation allows you to run aset and a reset command together, which can potentially save the cluster from going through an extra rolling restart. For example:
mcm> set ndb_recv_thread_activation_threshold:mysqld:146=8,~ndb_batch_size:mysqld mycluster When a dynamic variable is set,mcmd sends aSET GLOBAL statement to themysqld to apply the value and saves the value to themysqld configuration file, so that the value can be applied again the next time thismysqld process is restarted. Setting a variable which is not dynamic triggers an immediate restart.
When no data nodes are available, aset command that restarts amysqld node without also restarting the data nodes is rejected. This is to make sure that any issues with the data nodes are handled first, so that themysqld restart actually succeeds.
For a few attributes that apply only when using TCP connections (such as theSendBufferMemory andReceiveBufferMemory attributes), it is necessary to use a modified syntax for attribute value assignments. In this case, the attribute assignment contains two process specifications, one for each process type or instance to which the setting applies, joined with a plus sign (+). For the following example, consider the cluster namedmycluster2, consisting of the processes shown here:
mcm> list processes mycluster2;+------+----------+-------------+| Id | Name | Host |+------+----------+-------------+| 49 | ndb_mgmd | grindval || 1 | ndbd | tonfisk || 2 | ndbd | flundra || 50 | mysqld | haj || 51 | mysqld | torsk |+------+----------+-------------+5 rows in set (0.16 sec) (SeeSection 5.6.3, “Thelist processes Command”, for more information about this command.)
TCP connection attributes are not shown in the output from theget command unless they have been set. This means that, prior to settingSendBufferMemory for the first time, you obtain an empty result if you try to retrieve its value, as shown here:
mcm> get SendBufferMemory mycluster2;Empty set (0.18 sec)mcm> get --include-defaults SendBufferMemory mycluster2;Empty set (0.93 sec) To set theSendBufferMemory to 4 MB for all TCP connections between data nodes and SQL nodes, you can use the command shown here:
mcm> set SendBufferMemory:ndbd+mysqld=4M mycluster2;+-----------------------------------+| Command result |+-----------------------------------+| Cluster reconfigured successfully |+-----------------------------------+1 row in set (6.44 sec) If you check the attribute's value afterwards usingget, you can see that the value is applied to all possible connections between each of the twondbd processes and each of the twomysqld processes inmycluster2, thus there are four rows in the output:
mcm> get SendBufferMemory mycluster2;+------------------+-------+----------+-----+----------+-----+---------+---------+| Name | Value | Process1 | Id1 | Process2 | Id2 | Level | Comment |+------------------+-------+----------+-----+----------+-----+---------+---------+| SendBufferMemory | 4M | ndbd | 2 | mysqld | 4 | Process | || SendBufferMemory | 4M | ndbd | 2 | mysqld | 5 | Process | || SendBufferMemory | 4M | ndbd | 3 | mysqld | 4 | Process | || SendBufferMemory | 4M | ndbd | 3 | mysqld | 5 | Process | |+------------------+-------+----------+-----+----------+-----+---------+---------+4 rows in set (1.63 sec) To override this setting for only the connection between the data node with process ID2 and the mysqld process (process ID4), you can include the process ID in each of the two parts of the process specification, as shown here:
mcm> set SendBufferMemory:ndbd:2+mysqld:4=8M mycluster2;+-----------------------------------+| Command result |+-----------------------------------+| Cluster reconfigured successfully |+-----------------------------------+1 row in set (7.95 sec) When you check the result using aget command, you can see that the new setting applies on the instance level, and only to the connection between processes having IDs2 and4; the process-level setting made previously still applies to the remaining 3 connections:
mcm> get SendBufferMemory mycluster2;+------------------+-------+----------+-----+----------+-----+---------+---------+| Name | Value | Process1 | Id1 | Process2 | Id2 | Level | Comment |+------------------+-------+----------+-----+----------+-----+---------+---------+| SendBufferMemory | 8M | ndbd | 2 | mysqld | 50 | | || SendBufferMemory | 4M | ndbd | 2 | mysqld | 51 | Process | || SendBufferMemory | 4M | ndbd | 3 | mysqld | 50 | Process | || SendBufferMemory | 4M | ndbd | 3 | mysqld | 51 | Process | |+------------------+-------+----------+-----+----------+-----+---------+---------+4 rows in set (0.24 sec)You cannot set a connection attribute on the process level in one part of the process specification (that is, for one end of the connection) and on the instance level in the other. Attempting to do so fails with an error, as shown here:
mcm> set SendBufferMemory:ndbd+mysqld:4=2M mycluster2;ERROR 3 (00MGR): Illegal syntaxmcm> set SendBufferMemory:ndbd:2+mysqld=2M mycluster2;ERROR 3 (00MGR): Illegal syntax Enabling connection pooling formysqld can be done by setting thendb-cluster-connection-pool attribute to the desired number of connections, but also requires an extra step in creating the cluster.
Because themysqld process attempts to make multiple connections to the cluster when connection pooling is enabled, the cluster must be configured with“spare” or“empty” connections. You can do this by adding (otherwise) unusedndbapi entries in the process_host list used in thecreate cluster command, as shown here:
mcm> create cluster -P mypackage > -R ndb_mgmd@10.100.10.97,ndbd@10.100.10.98,ndbd@10.100.10.99, \ mysqld@10.100.10.100,ndbapi@10.100.10.100, \ ndbapi@10.100.10.100,ndbapi@10.100.10.100 > mycluster;+------------------------------+| Command result |+------------------------------+| Cluster created successfully |+------------------------------+1 row in set (6.58 sec) After this, you can use aset command like this one to set the size of the connection pool according to the number of excess connections available in theconfig.ini file:
mcm> set ndb_cluster_connection_pool:mysqld=4; Trying to set theuser attribute for amysqld process is not supported, and results in a warning being written to the MySQL Cluster Manager log.
NDB Cluster 8.0.31 and later supports transparent data encryption (TDE) for user data stored inNDB tables (seeFile System Encryption for NDB Cluster); this is also supported by MySQL Cluster Manager 8.0.31 and later. File system encryption is enabled on the data nodes by setting theEncryptedFileSystem configuration parameter equal to 1 on all data nodes. (Disable encryption by setting the parameter to 0.)
Encrypting and decrypting data require that the data nodes have the encryption password, which must be stored in a file readable by the data node processes. You can supply this to the data nodes with aset command that uses thefilesystem-password-file option (introduced in MySQL Cluster Manager 8.0.31). This must be done before settingEncryptedFileSystem = 1, as shown later in this section.
The following example makes use of the clustermycluster running as shown in the output of thisshow status command in themcm client:
mcm> show status -r mycluster;+--------+----------+--------+---------+-----------+-----------+| NodeId | Process | Host | Status | Nodegroup | Package |+--------+----------+--------+---------+-----------+-----------+| 145 | ndb_mgmd | myndb3 | running | | mypackage || 1 | ndbmtd | myndb1 | running | 0 | mypackage || 2 | ndbmtd | myndb2 | running | 0 | mypackage || 146 | mysqld | myndb3 | running | | mypackage || 147 | mysqld | myndb4 | running | | mypackage || 148 | ndbapi | * | added | | |+--------+----------+--------+---------+-----------+-----------+Setting the password directly from the command line in themcm client is not supported. Using a file on disk instead helps protect against unprivileged user access, provided that file system access rights are sufficiently strict. (On Linux and similar platforms, this file must have its permissions set to 0600.) This file should contain only the encryption password, which follows the same rules as passwords for encrypted NDB backups; seeUsing The NDB Cluster Management Client to Create a Backup, for more information.
Assuming that the password file exists and has the proper permissions, you can supply the password to the data nodes using the followingset command:
mcm> set filesystem-password-file:ndbmtd=/opt/mcm_data/my.pwd mycluster;+-----------------------------------+| Command result |+-----------------------------------+| Cluster reconfigured successfully |+-----------------------------------+Once the path to the password file has been set, you can enable encryption, like this:
mcm> set EncryptedFileSystem:ndbmtd=1 mycluster;+-----------------------------------+| Command result |+-----------------------------------+| Cluster reconfigured successfully |+-----------------------------------+ If the encryption password file has not been set, theset command just shown is rejected with an error.
You can verify that encryption is enabled using aget command similar to this one:
mcm> get -d filesystem-pass*,encrypt* mycluster;+--------------------------+---------------------------+----------+---------+----------+---------+---------+----------+| Name | Value | Process1 | NodeId1 | Process2 | NodeId2 | Level | Comment |+--------------------------+---------------------------+----------+---------+----------+---------+---------+----------+| EncryptedFileSystem | 1 | ndbmtd | 1 | | | Process | || filesystem-password-file | /opt/mcm_data/my.pwd | ndbmtd | 1 | | | Process | MCM only || EncryptedFileSystem | 1 | ndbmtd | 2 | | | Process | || filesystem-password-file | /opt/mcm_data/my.pwd | ndbmtd | 2 | | | Process | MCM only |+--------------------------+---------------------------+----------+---------+----------+---------+---------+----------+You can also verify, outside of MySQL Cluster Manager or even a running NDB Cluster, that cluster data files have been encrypted using thendbxfrm utility supplied with NDB Cluster, similarly to what is shown here:
$> ndbxfrm -i /home/mcm/clusters/mycluster/1/data/ndb_1_fs/LCP/0/T10F0.Data File=/home/mcm/clusters/mycluster/1/data/ndb_1_fs/LCP/0/T10F0.Data, compression=no,encryption=yes You can rotate file system passwords by changing the existing file (or setting a new file), then issuingset--retryEncryptedFilesystem:ndbmtd=1 to trigger an initial rolling restart. Alternatively, you can usestop process followed bystart process--initial to replace the password used by each data node process, one at a time.
The overriding of default restart type using the--restart option may cause unintended consequences. It should only be performed under guidance by the support personnel from Oracle.
When setting theMaxNoOfExecutionThreads orThreadConfig parameter for data nodes, their defaultrestart type (which isSI, System Initial) could be overridden with the --restart option to becomeNI (Node Initial) or N (Node). This can be used to change the configuration parameter without actually reconfiguring the number of LDM threads. For example:
mcm> set --restart=N ThreadConfig:ndbmtd='main={count=1},tc={count=0},ldm={count=4},io={count=1}, rep={count=1},recv={count=1},send={count=0}', MaxNoOfExecutionThreads:ndbmtd=10 mycluster;Cluster reconfigured successfullyUse of the option requires the following:
At least onendb_mgmd node is running.
All data nodes andndb_mgmd nodes are running for
--restart=NI.The cluster remains alive while restarting the data nodes (i.e., there are at least two data nodes running in each nodegroup) for
--restart=N.The set statement does not contain any additional parameters that only affect andb_mgmd ormysqld node.