MySQL Cluster Manager 9.0 Release Notes
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 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 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 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 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.
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
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.
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
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.
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.
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 successfullyUse 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.