Documentation Home
MySQL Cluster Manager 1.3 User Manual
Related Documentation Download this Manual

3.5.2.1 Creating and Configuring the Target Cluster

The first task when preparing to import a standalone MySQL Cluster into MySQL Cluster Manager is to create a target cluster, Once this is done, we modify the target's configuration until it matches that of the wild cluster that we want to import. At a later point in the example, we also show how to test the configuration in a dry run before attempting to perform the actual import.

To create and then configure the target cluster, follow the steps listed here:

  1. Install MySQL Cluster Manager and start mcmd on all hosts; we assume that you have installed MySQL Cluster Manager to the recommended location, in this case the directory /opt/mcm-1.3.6. (See Section 2.3, “MySQL Cluster Manager Installation”, for more information.) Once you have done this, you can start the mcm client (see Section 3.3, “Starting the MySQL Cluster Manager Client”) on any one of these hosts to perform the next few steps.

  2. Create a MySQL Cluster Manager site encompassing all four of these hosts, using the create site command, as shown here:

    mcm> create site --hosts=alpha,beta,gamma,delta newsite;
    +---------------------------+
    | Command result            |
    +---------------------------+
    | Site created successfully |
    +---------------------------+
    1 row in set (0.15 sec)

    We have named this site newsite. You should be able to see it listed in the output of the list sites command, similar to what is shown here:

    mcm> list sites;
    +---------+------+-------+------------------------+
    | Site    | Port | Local | Hosts                  |
    +---------+------+-------+------------------------+
    | newsite | 1862 | Local | alpha,beta,gamma,delta |
    +---------+------+-------+------------------------+
    1 row in set (0.01 sec)
  3. Add a MySQL Cluster Manager package referencing the MySQL Cluster binaries, using the add package command; this command's --basedir option can be used to point to the correct location. The command shown here creates such a package, named newpackage:

    mcm> add package --basedir=/usr/local/mysql newpackage;
    +----------------------------+
    | Command result             |
    +----------------------------+
    | Package added successfully |
    +----------------------------+
    1 row in set (0.70 sec)

    You do not need to include the bin directory containing the MySQL Cluster executables in the --basedir path. Since the executables are in /usr/local/mysql/bin, it is sufficient to specify /usr/local/mysql; MySQL Cluster Manager automatically checks for the binaries in a bin directory within the one specified by --basedir.

  4. Create the target cluster including at least some of the same processes and hosts used by the standalone cluster. Do not include any processes or hosts that are not part of this cluster. In order to prevent potentially disruptive process or cluster operations from interfering by accident with the import process, it is strongly recommended that you create the cluster for import, using the --import option for the create cluster command.

    You must also take care to preserve the correct node ID (as listed in the config.ini file shown previously) for each node. In MySQL Cluster Manager 1.3.1 and later, using the --import option allows you to specify node IDs under 49 for nodes other than data nodes, which is otherwise prevented when using create cluster (the restriction has been lifted since MySQL Cluster Manager 1.3.4).

    The following command creates the cluster newcluster for import, and includes the management and data nodes, but not the SQL or free API node (which we add in the next step):

    mcm> create cluster --import --package=newpackage \
            --processhosts=ndb_mgmd:50@alpha,ndbd:1@beta,ndbd:2@gamma \
            newcluster;
    +------------------------------+
    | Command result               |
    +------------------------------+
    | Cluster created successfully |
    +------------------------------+
    1 row in set (0.96 sec)

    You can verify that the cluster was created correctly by checking the output of show status with the --process (-r) option, like this:

    mcm> show status -r newcluster;
    +--------+----------+-------+--------+-----------+------------+
    | NodeId | Process  | Host  | Status | Nodegroup | Package    |
    +--------+----------+-------+--------+-----------+------------+
    | 50     | ndb_mgmd | alpha | import |           | newpackage |
    | 5      | ndbd     | beta  | import | n/a       | newpackage |
    | 6      | ndbd     | gamma | import | n/a       | newpackage |
    +--------+----------+-------+--------+-----------+------------+
    3 rows in set (0.01 sec)
  5. If necessary, add any remaining processes and hosts from the wild cluster not included in the previous step using one or more add process commands. We have not yet accounted for 2 of the nodes from the wild cluster: the SQL node with node ID 100, on host delta; and the API node which has node ID 101, and is not bound to any specific host. You can use the following command to add both of these processes to newcluster:

    mcm> add process --processhosts=mysqld:100@delta,ndbapi:101@* newcluster;
    +----------------------------+
    | Command result             |
    +----------------------------+
    | Process added successfully |
    +----------------------------+
    1 row in set (0.41 sec)

    Once again checking the output from show status -r, we see that the mysqld and ndbapi processes were added as expected:

    mcm> show status -r newcluster;
    +--------+----------+-------+--------+-----------+------------+
    | NodeId | Process  | Host  | Status | Nodegroup | Package    |
    +--------+----------+-------+--------+-----------+------------+
    | 50     | ndb_mgmd | alpha | import |           | newpackage |
    | 5      | ndbd     | beta  | import | n/a       | newpackage |
    | 6      | ndbd     | gamma | import | n/a       | newpackage |
    | 100    | mysqld   | delta | import |           | newpackage |
    | 101    | ndbapi   | *     | import |           |            |
    +--------+----------+-------+--------+-----------+------------+
    5 rows in set (0.08 sec)

    You can also see that, since newcluster was created using the create cluster command's --import option, the status of all processes in this cluster—including those we just added—is import. This means we cannot yet start newcluster or any of its processes, as shown here:

    mcm> start process 50 newcluster;
    ERROR 5317 (00MGR): Unable to perform operation on cluster created for import
    mcm> start cluster newcluster;
    ERROR 5317 (00MGR): Unable to perform operation on cluster created for import

    The import status and its effects on newcluster and its cluster processes persist until we have completed importing another cluster into newcluster.

    Following the execution of the add process command shown previously, the target newcluster cluster now has the same processes, with the same node IDs, and on the same hosts as the original standalone cluster. We are ready to proceed to the next step.

  6. Duplicate the wild cluster's configuration attributes in the target cluster. In MySQL Cluster Manager 1.3.1 and later, you can handle most of these using the import config command. Test out first the effects of the command by running it with the --dryrun option (the step only works if you have created the mcmd user on the cluster's mysqld nodes):

    mcm> import config --dryrun newcluster;
    +---------------------------------------------------------------------------+
    | Command result                                                            |
    +---------------------------------------------------------------------------+
    | Import checks passed. Please check log for settings that will be applied. |
    +---------------------------------------------------------------------------+
    1 row in set (5.36 sec)
    Important

    Before executing this command it is necessary to set any non-default ports for ndb_mgmd and mysqld processes using the set command in the mcm client.

    As indicated by the output from import config --dryrun, you can see the configuration attributes and values that would be copied to newcluster by the unimpeded command in the agent log file (mcmd.log), which by default is created in the MySQL Cluster Manager installation directory. If you open this file in a text editor, you can locate a series of set commands that would accomplish this task, similar to what is shown here in emphasized texts:

    2014-03-14 16:05:11.896: (message) [T0x1ad12a0 CMGR ]: Got new message mgr_import_configvalues {84880f7a 35 0}
    2014-03-14 16:05:11.896: (message) [T0x1ad12a0 CMGR ]: Got new message mgr_import_configvalues {84880f7a 36 0}
    2014-03-14 16:05:11.896: (message) [T0x1ad12a0 CMGR ]: Got new message mgr_import_configvalues {84880f7a 37 0}
    2014-03-14 16:05:13.698: (message) [T0x7f4fb80171a0 RECFG]: All utility process have finished
    2014-03-14 16:05:13.698: (message) [T0x7f4fb80171a0 RECFG]: Process started : /usr/local/mysql/bin/mysqld --no-defaults --help --verbose
    2014-03-14 16:05:13.698: (message) [T0x7f4fb80171a0 RECFG]: Spawning mysqld --nodefaults --help --verbose asynchronously
    2014-03-14 16:05:13.904: (message) [T0x7f4fb80171a0 RECFG]: Successfully pulled default configuration from mysqld 100
    2014-03-14 16:05:13.905: (warning) [T0x7f4fb80171a0 RECFG]: Failed to remove evsource!
    2014-03-14 16:05:15.719: (message) [T0x7f4fb80171a0 RECFG]: All utility process have finished
    2014-03-14 16:05:15.725: (message) [T0x7f4fb80171a0 RECFG]: Applying mysqld configuration to cluster...
    2014-03-14 16:05:16.186: (message) [T0x1ad12a0 CMGR ]: Got new message mgr_import_configvalues {84880f7a 38 0}
    2014-03-14 16:05:16.187: (message) [T0x1ad12a0 CMGR ]: Got new message x_trans {84880f7a 39 0}
    2014-03-14 16:05:16.286: (message) [T0x1ad12a0 CMGR ]: Got new message x_trans {84880f7a 40 0}
    2014-03-14 16:05:16.286: (message) [T0x7f4fb80171a0 RECFG]: The following will be applied to the current cluster config:
    set DataDir:ndb_mgmd:50="" newcluster
    set IndexMemory:ndbd:5=1073741824 newcluster
    set DataMemory:ndbd:5=1073741824 newcluster
    set DataDir:ndbd:5=/usr/local/mysql/mysql-cluster/data newcluster
    set ThreadConfig:ndbd:5="" newcluster
    set IndexMemory:ndbd:6=1073741824 newcluster
    set DataMemory:ndbd:6=1073741824 newcluster
    set DataDir:ndbd:6=/usr/local/mysql/mysql-cluster/data newcluster
    set ThreadConfig:ndbd:6="" newcluster
    set basedir:mysqld:100=/usr/local/mysql newcluster
    set character_sets_dir:mysqld:100=/usr/local/mysql/share/charsets newcluster
    set datadir:mysqld:100=/usr/local/mysql/data newcluster
    set general_log_file:mysqld:100=/usr/local/mysql/data/delta.log newcluster
    set lc_messages_dir:mysqld:100=/usr/local/mysql/share newcluster
    set log_error:mysqld:100=/usr/local/mysql/data/delta.err newcluster
    set ndb_connectstring:mysqld:100=alpha newcluster
    set ndb_mgmd_host:mysqld:100=alpha newcluster
    set optimizer_trace:mysqld:100=enabled=off,one_line=off newcluster
    set pid_file:mysqld:100=/usr/local/mysql/data/delta.pid newcluster
    set plugin_dir:mysqld:100=/usr/local/mysql/lib/plugin newcluster
    set report_port:mysqld:100=3306 newcluster
    set slow_query_log_file:mysqld:100=/usr/local/mysql/data/delta-slow.log newcluster
    set sql_mode:mysqld:100=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION newcluster

    Options used at the command line instead of in a configuration file to start a node of the standalone cluster are not imported into the target cluster by the import config command; moreover, they will cause one of the following to happen when the import config --dryrun is run:

    1. For some options, MySQL Cluster Manager will issue a warning that Option <param> may be removed on next restart of process <type><nodeid>, meaning that those options will not be imported into the target cluster, and thus will not be applied when those nodes are restarted after the import. Here are the lists of such options for each node type:

      When a warning is received for any of these options but you want its value to be included as part of the configuration for your target cluster, you will need to set the option value for your target cluster manually using the set command before you perform the final import cluster step.

    2. For some other options, while their values will also not be imported into the target cluster, unlike options described in (i) above, no warnings will be issued for them. Here are lists of such options for each node type:

    3. For options that belong to neither of the groups in (i) or (ii) above, having started the standalone cluster's nodes with them at the command line will cause the import config --dryrun command to fail with an error, complaining that the options are unsupported. You have to restart the wild cluster's nodes without those options, and then retry the import config --dryrun command.

    Assuming that the dry run was successful, you should now be able to import the wild cluster's configuration into newcluster, with the command and a result similar to what is shown here:

    mcm> import config newcluster;
    +------------------------------------------------------------------------------------------------------------------+
    | Command result                                                                                                   |
    +------------------------------------------------------------------------------------------------------------------+
    | Configuration imported successfully. Please manually verify plugin options, abstraction level and default values |
    +------------------------------------------------------------------------------------------------------------------+

    You should check the log from the dry run and the resulting configuration of newcluster carefully against the configuration of the wild cluster. If you find any inconsistencies, you must correct these in newcluster using the appropriate set commands afterwards.

Manual configuration import (MySQL Cluster Manager 1.3.0).  In MySQL Cluster Manager 1.3.0, which does not support the import config command, it is necessary to copy the wild cluster's configuration manually, using set commands in the mcm client (once you have obtained the values of any attributes that differ from their defaults). The remainder of this section applies primarily to MySQL Cluster Manager 1.3.0 and the process described here is generally not needed in MySQL Cluster Manager 1.3.1 and later.

MySQL Cluster global configuration data is stored in a file that is usually (but not always) named config.ini. Its location on a management node host is arbitrary (there is no default location for it), but if this is not already known, you can determine it by checking—for example, on a typical Linux system—the output of ps for the --config-file option value that the management node was started with, shown with emphasized text in the output:

shell> ps ax | grep ndb_mgmd
18851 ?        Ssl    0:00 ./ndb_mgmd --config-file=/var/lib/mysql-cluster/config.ini
18922 pts/4    S+     0:00 grep --color=auto ndb_mgmd

This file uses INI format to store global configuration information, and is thus easy to read, or to parse with a script. We start the setup of the target cluster' configuration by checking each section of this file in turn. The first section is repeated here:

[ndbd default]
DataMemory= 16G
IndexMemory= 12G
NoOfReplicas= 2

The [ndbd default] heading indicates that all attributes defined in this section apply to all cluster data nodes. We can set all three attributes listed in this section of the file for all data nodes in newcluster, using the set command shown here:

mcm> set DataMemory:ndbd=16G,IndexMemory:ndbd=12G,NoOfReplicas:ndbd=2 newcluster;
+-----------------------------------+
| Command result                    |
+-----------------------------------+
| Cluster reconfigured successfully |
+-----------------------------------+
1 row in set (0.36 sec)

You can verify that the desired changes have taken effect using the get command, as shown here:

mcm> get DataMemory:ndbd,IndexMemory:ndbd,NoOfReplicas:ndbd newcluster;
+--------------+-------+----------+---------+----------+---------+---------+---------+
| Name         | Value | Process1 | NodeId1 | Process2 | NodeId2 | Level   | Comment |
+--------------+-------+----------+---------+----------+---------+---------+---------+
| DataMemory   | 16G   | ndbd     | 5       |          |         | Process |         |
| IndexMemory  | 12G   | ndbd     | 5       |          |         | Process |         |
| NoOfReplicas | 2     | ndbd     | 5       |          |         | Process |         |
| DataMemory   | 16G   | ndbd     | 6       |          |         | Process |         |
| IndexMemory  | 12G   | ndbd     | 6       |          |         | Process |         |
| NoOfReplicas | 2     | ndbd     | 6       |          |         | Process |         |
+--------------+-------+----------+---------+----------+---------+---------+---------+
6 rows in set (0.07 sec)

The next section in the file is shown here:

[ndb_mgmd]
HostName=alpha
NodeId=1

This section of the file applies to the management node. We set its NodeId and HostName attributes previously, when we created newcluster. No further changes are required at this time.

The next two sections of the config.ini file, shown here, contain configuration values specific to each of the data nodes:

[ndbd]
NodeId=5
HostName=beta
DataDir=/var/lib/mysql-cluster

[ndbd]
NodeId=6
HostName=gamma
DataDir=/var/lib/mysql-cluster

As was the case for the management node, we already provided the correct node IDs and host names for the data nodes when we created newcluster, so only the DataDir attribute remains to be set. We can accomplish this by executing the following command in the mcm client:

mcm> set DataDir:ndbd:5=/var/lib/mysql-cluster,DataDir:ndbd:6=/var/lib/mysql-cluster \
            newcluster;
+-----------------------------------+
| Command result                    |
+-----------------------------------+
| Cluster reconfigured successfully |
+-----------------------------------+
1 row in set (0.42 sec)

You may have noticed that we could have set the DataDir attribute on the process level using the shorter and simpler command set DataDir:ndbd=/var/lib/mysql-cluster newcluster, but since this attribute was defined individually for each data node in the original configuration, we match this scope in the new configuration by setting this attribute for each ndbd instance instead. Once again, we check the result using the mcm client get command, as shown here:

mcm> get DataDir:ndbd newcluster;
+---------+------------------------+----------+---------+----------+---------+-------+---------+
| Name    | Value                  | Process1 | NodeId1 | Process2 | NodeId2 | Level | Comment |
+---------+------------------------+----------+---------+----------+---------+-------+---------+
| DataDir | /var/lib/mysql-cluster | ndbd     | 5       |          |         |       |         |
| DataDir | /var/lib/mysql-cluster | ndbd     | 6       |          |         |       |         |
+---------+------------------------+----------+---------+----------+---------+-------+---------+
2 rows in set (0.01 sec)

Configuration attributes for the SQL node are contained the next section of the file, shown here:

[mysqld]
NodeId=100
HostName=delta

The NodeId and HostName attributes were already set when we added the mysqld process to newcluster, so no additional set commands are required at this point. Keep in mind that there may be additional local configuration values for this mysqld that must be accounted for in the configuration we are creating for newcluster; we discuss how to determine these values on the SQL node later in this section.

The remaining section of the file, shown here, contains a section defining attributes for a free API node that is not required to connect from any particular host:

[ndbapi]
NodeId=101

We have already set the NodeId and there is no need for a HostName for a free process. There are no other attributes that need to be set for this node.

For more information about the MySQL config.ini global configuration file, see NDB Cluster Configuration Files, and NDB Cluster Configuration: Basic Example.

As mentioned earlier in this section, each mysqld process (SQL node) may have, in addition to any attributes set in config.ini, its own configuration data in the form of system variables which are specific to that mysqld. These can be set in two ways:

  • When the mysqld is started, by passing options to it on the command line when invoking it, by reading from an options file (usually named my.cnf, or my.ini on Windows), or both.

  • At runtime using the SQL SET statement.

Because the initial values of many options can be changed at runtime, it is recommended that—rather than attempt to read the my.cnf or my.ini file—you check values for all system variables on each SQL node live in the mysql client by examining the output of the SHOW VARIABLES statement, and execute set commands setting each of these values where it differs from the default for that variable on that SQL node.

The mcm client can execute a script file containing client commands. The contents of such a script, named my-commands.mcm, which contains all commands we executed to create and configure newcluster, are shown here:

create cluster --import --package=newpackage --processhosts=ndb_mgmd:50@alpha,ndbd:5@beta,ndbd:6@gamma  newcluster;
      
add process --processhosts=mysqld:100@delta,ndbapi:101@* newcluster;

set DataMemory:ndbd=16G,IndexMemory:ndbd=12G,NoOfReplicas:ndbd=2 newcluster;

set DataDir:ndbd:5=/var/lib/mysql-cluster,DataDir:ndbd:6=/var/lib/mysql-cluster newcluster;

You can run such a script by invoking the client from the command line with a redirection operator, like this:

shell> mcm < my-commands.mcm

The name of the script file is completely arbitrary. It must contain valid mcm client commands or comments only. (A comment is delimited by a # character, and extends from the point in the line where this is found to the end of the line.) Any valid mcm client command can be used in such a file. mcm must be able to read the file, but the file need not be executable, or readable by any other users.