5.5.3 The set Command

5.5.3 The `set` Command

set [--sequential-restart] [--retry] [--restart=restart_level] attribute_assignment_list cluster_name

attribute_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 (see Performing 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 a set 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 the set 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 the set command a sequential 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;

See Setting Up Encryption, for more information.

To set an attribute on the process level, use a set statement that contains an attribute assignment having the form attribute_name:process_name=value.

For example, to set DataMemory to 500 MB on the ndbd process level, so that the new value applies to all ndbd processes in the cluster, you can issue a set command containing the attribute assignment DataMemory: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 following get 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)

Note

For more information about this command, see Section 5.5.1, “The get 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 is attribute_name:process_name:process_id=value. For example, to set the wait_timeout attribute for the mysqld process that has process ID 50 to 200, you would issue a set command that contains the attribute assignment wait_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 applicable get 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 marked Read 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 the DataDir configuration attribute was set to a user-defined value.

Warning

The mandatory NoOfReplicas 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 the get command, you cannot issue a set 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 syntax

mcm> 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 single set command, supplying the assignments as a comma-separated list. This set command assigns /var/cdata as the data directory (DataDir) for all MySQL NDB Cluster processes in the cluster named mycluster:

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 the get command just shown, the attribute assignments were successful, and took effect on the process level.

Note

In MySQL Cluster Manager, configuration attribute names are not case-sensitive. See Case 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 does not work:

mcm> set DataMemory:ndbd:1,2=750M mycluster;
ERROR 3 (00MGR): Illegal syntax

Instead, 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 in mycluster, then the command set DataMemory:ndbd=750M mycluster also accomplishes the same task.)

Note

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; see Setting 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 do not work:

mcm> set UndoDataBuffer=32M,UndoIndexBuffer=8M:ndbd mycluster;
ERROR 3 (00MGR): Illegal syntax

mcm> set DataMemory,IndexMemory:ndbd=1G mycluster;
ERROR 3 (00MGR): Illegal syntax

However, 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 single set 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 single set command, since this makes it more difficult to spot errors.

On Windows, when setting attributes whose values contain paths (such as DataDir), you must replace any backslash characters in the path with forward slashes. Suppose that you want to use C:\temp\node50 for the tmpdir attribute of the mysqld process having node ID 50 in a MySQL NDB Cluster named mycluster that is running on Windows. The original value for this attribute can be seen using the appropriate get 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 correct set 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 using get—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 the set 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.

Performing a reset with a set command

You can reset a configuration attribute's value using a set command by putting a tilde (~) before the attribute name. For example, this command is equivalent to reset ndb_batch_size:mysqld:146 mycluster:

mcm> set ~ndb_batch_size:mysqld:146 mycluster

The notation allows you to run a set 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

Setting Attributes for mysqld nodes

When a dynamic variable is set, mcmd sends a SET GLOBAL statement to the mysqld to apply the value and saves the value to the mysqld configuration file, so that the value can be applied again the next time this mysqld process is restarted. Setting a variable which is not dynamic triggers an immediate restart.

When no data nodes are available, a set command that restarts a mysqld 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 the mysqld restart actually succeeds.

Setting TCP Connection Attributes

For a few attributes that apply only when using TCP connections (such as the SendBufferMemory and ReceiveBufferMemory 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 named mycluster2, 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)

(See Section 5.6.3, “The list processes Command”, for more information about this command.)

TCP connection attributes are not shown in the output from the get command unless they have been set. This means that, prior to setting SendBufferMemory 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 the SendBufferMemory 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 using get, you can see that the value is applied to all possible connections between each of the two ndbd processes and each of the two mysqld processes in mycluster2, 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 ID 2 and the mysqld process (process ID 4), 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 a get command, you can see that the new setting applies on the instance level, and only to the connection between processes having IDs 2 and 4; 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 syntax
mcm> set SendBufferMemory:ndbd:2+mysqld=2M mycluster2;
ERROR 3 (00MGR): Illegal syntax

Setting up mysqld connection pooling

Enabling connection pooling for mysqld can be done by setting the ndb-cluster-connection-pool attribute to the desired number of connections, but also requires an extra step in creating the cluster.

Because the mysqld 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) unused ndbapi entries in the process_host list used in the create 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 a set command like this one to set the size of the connection pool according to the number of excess connections available in the config.ini file:

mcm> set ndb_cluster_connection_pool:mysqld=4;

Note

Trying to set the user attribute for a mysqld process is not supported, and results in a warning being written to the MySQL Cluster Manager log.

Setting Up Encryption

NDB Cluster 8.0.31 and later supports transparent data encryption (TDE) for user data stored in NDB tables (see File 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 the EncryptedFileSystem 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 a set command that uses the filesystem-password-file option (introduced in MySQL Cluster Manager 8.0.31). This must be done before setting EncryptedFileSystem = 1, as shown later in this section.

The following example makes use of the cluster mycluster running as shown in the output of this show status command in the mcm 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 the mcm 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; see Using 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 following set 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, the set command just shown is rejected with an error.

You can verify that encryption is enabled using a get 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 the ndbxfrm 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 issuing set --retry EncryptedFilesystem:ndbmtd=1 to trigger an initial rolling restart. Alternatively, you can use stop process followed by start process --initial to replace the password used by each data node process, one at a time.

Overriding the Default Restart Type

Warning

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 the MaxNoOfExecutionThreads or ThreadConfig parameter for data nodes, their default restart type (which is SI, System Initial) could be overridden with the --restart option to become NI (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 successfully

Use of the option requires the following:

At least one ndb_mgmd node is running.
All data nodes and ndb_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 a ndb_mgmd or mysqld node.