Migrate Cluster

The following semi-automated process can be used to migrate from MemSQL Ops to SingleStore Tools.

Command output examples are provided. Note that, as they are representative samples, what is displayed on your screen may vary.

Note

Unless explicitly stated otherwise, perform all of the following steps on/from the Master Aggregator.

In the commands provided, replace all instances of content in angle brackets with the referenced substitute. Note that multiple instances may need to be replaced in a single command.

In some cases, a script may require non-password access to all hosts in the cluster.

Review Current Cluster

Use MemSQL Ops to validate the SingleStore cluster health. Confirm that all nodes are listed, online, healthy, and are running MemSQL 6.7 or later. Make sure that the nodes meet these requirements before proceeding with the migration.

memsql-ops memsql-list
 ID       Agent Id  Process State  Cluster State  Role        Host        Port  Version 
 44CDE71  A0c8a89   RUNNING        CONNECTED      MASTER      10.0.0.98   3306  x.x
 3FD3FCF  Aa5eab9   RUNNING        CONNECTED      AGGREGATOR  10.0.0.133  3306  x.x
 9C0841F  A04f65f   RUNNING        CONNECTED      LEAF        10.0.0.236  3306  x.x
 9A551DA  A5f0572   RUNNING        CONNECTED      LEAF        10.0.0.136  3306  x.x

Confirm Connectivity

Confirm that memsql-ops agent-ssh can be used to SSH (open a secure shell) into each host in the cluster. Although this command is used to SSH into hosts, the initial output of the command can be used to check whether SSH has been configured on all hosts. 

Note: After invoking the following interactive prompt, exit the command immediately by using Control-C without actually connecting to the hosts via SSH.

sudo memsql-ops agent-ssh
 Index  ID       Host        Port  Role      State   Version  Has Credential 
 1      Aeea1ec  10.0.0.98   9000  PRIMARY   ONLINE  x.x      No             
 2      A005a87  10.0.0.133  9000  FOLLOWER  ONLINE  x.x      Yes            
 3      Aa9367f  10.0.0.236  9000  FOLLOWER  ONLINE  x.x      Yes            
 4      Ad90aba  10.0.0.136  9000  FOLLOWER  ONLINE  x.x      Yes

In the output displayed above, only the main host where the Ops primary agent runs should reflectNo for Has Credential. For all other hosts, it is strongly recommended to set an SSH credential before proceeding with the migration. SingleStore Toolbox must SSH into each host to run commands. This means that if the SSH credentials are not set, you will be prompted for a username and a password for every host in the cluster, for every SingleStore Toolbox command that you run.

To configure ssh keys for each host, run the memsql-ops agent-set-credential command (see instructions).

Alternatively, use the following script to automate this process. While executing the script, make sure to provide the ssh keys where applicable.

memsql-ops agent-list -q | xargs -n 1 memsql-ops agent-set-credential -i </key-file/including/path>

Enable Manual Control

Enable manual control of the SingleStore cluster to prevent MemSQL Ops from interfering with the SingleStore Toolbox install.

memsql-ops cluster-manual-control --enable
The cluster is now under manual control. We will not automatically restart stopped nodes, update license files, etc.

Caution

MemSQL Ops will not automatically restart nodes that fail during the migration unless manual control of the cluster is enabled. While SingleStore Toolbox has been installed, no hosts or nodes are known to it yet. Hence, nodes will not be auto-restarted if they fail while the migration is underway.

Add a License

If you are currently using the Odin license, which uses an earlier format, update it to the Freya license with the latest format obtained from the Cloud Portal. To verify the format of the current license, use the command below:

memsql-ops license-list -j

The license marked as best is the one in current use, and the expected format of the license is <HASH>==. If the current license does not match the expected format, request a new license in the Cloud Portal and add it using the command below:

memsql-ops license-add --license-key <license>
Successfully added license.

Set root Password

Set the root password for all nodes in the cluster. Substitute the Master Aggregator’s password for <password> in the following command.

memsql-ops memsql-list -q | xargs -n 1 \
memsql-ops memsql-update-root-password --no-confirmation -p <password>

Generate Cluster Migration File

Run the following command to generate a cluster migration YAML file that will pass the cluster information from MemSQL Ops to SingleStore Toolbox. The cluster migration YAML file will be used in the command captured in the next step.

sudo memsql-ops migration-setup
Check succeeded: All nodes on MemSQL version x.x
Check succeeded: All nodes online and connected
Check succeeded: All nodes with supported license version
All checks passed. Migration configuration file written to /var/lib/memsql-ops/data/cache/migration_config.yml

Add root Password

Run the following command to add the root_password to the end of the cluster migration YAML file. Substitute the Master Aggregator’s password for <password>.

echo 'root_password: <password>' >> <cluster_file>

Deploy Migration File

Use the cluster migration YAML generated by the previous command.

sdb-deploy setup-cluster --cluster-file <cluster_file>

Confirm Registered Hosts

Use SingleStore Toolbox to confirm that all hosts have been registered.

sdb-toolbox-config list-hosts
+------------+------------+-------------+--------------------------+
|    Host    | Local Host | SSH address |       Identity File      |
+------------+------------+-------------+--------------------------+
| 10.0.0.98  | No         | 10.0.0.98   | /home/<user>/.ssh/id_rsa |
| 10.0.0.133 | No         | 10.0.0.133  | /home/<user>/.ssh/id_rsa |
| 10.0.0.136 | No         | 10.0.0.136  | /home/<user>/.ssh/id_rsa |
| 10.0.0.236 | No         | 10.0.0.236  | /home/<user>/.ssh/id_rsa |
+------------+------------+-------------+--------------------------+

Confirm Registered Nodes

Use SingleStore Toolbox to confirm that all nodes have been registered.

sdb-admin list-nodes
+------------+------------+------------+------+---------------+--------------+---------+----------------+--------------------+
| MemSQL ID  |    Role    |    Host    | Port | Process State | Connectable? | Version | Recovery State | Availability Group |
+------------+------------+------------+------+---------------+--------------+---------+----------------+--------------------+
| 0EAE75B920 | Master     | 10.0.0.98  | 3306 | Running       | True         | x.x     | Online         |                    |
| 35B6AA0317 | Aggregator | 10.0.0.133 | 3306 | Running       | True         | x.x     | Online         |                    |
| B964A18F18 | Leaf       | 10.0.0.136 | 3306 | Running       | True         | x.x     | Online         | 1                  |
| 1A8EDD42BB | Leaf       | 10.0.0.236 | 3306 | Running       | True         | x.x     | Online         | 2                  |
+------------+------------+------------+------+---------------+--------------+---------+----------------+--------------------+

Restart Nodes

Use SingleStore Toolbox to restart all nodes. This transfers cluster control to SingleStore Toolbox. After this step is completed, SingleStore Toolbox will manage the cluster, including automatically starting any offline nodes.

sdb-admin restart-node --all

For high-availability clusters, you may include the --online option to perform a rolling restart of the cluster, which allows the workload to continue while the nodes are restarted.

sdb-admin restart-node --all --online

Warning

While this step is underway, the node state may be registered incorrectly in SingleStore Toolbox (as displayed by sdb-admin list-nodes). You can check the actual cluster state with SHOW LEAVES or SHOW AGGREGATORS executed in a database connection to the Master Aggregator. When this step is completed, SingleStore Toolbox should show the node state accurately.

Confirm Node Restart

Confirm that all nodes have restarted.

sdb-admin list-nodes
+------------+------------+------------+------+---------------+--------------+---------+----------------+--------------------+
| MemSQL ID  |    Role    |    Host    | Port | Process State | Connectable? | Version | Recovery State | Availability Group |
+------------+------------+------------+------+---------------+--------------+---------+----------------+--------------------+
| D785AA80D7 | Master     | 10.0.0.98  | 3306 | Running       | True         | x.x     | Online         |                    |
| 9098564B01 | Aggregator | 10.0.0.133 | 3306 | Running       | True         | x.x     | Online         |                    |
| 2189856FC2 | Leaf       | 10.0.0.136 | 3306 | Running       | True         | x.x     | Online         | 2                  |
| 3094B68557 | Leaf       | 10.0.0.236 | 3306 | Running       | True         | x.x     | Online         | 1                  |
+------------+------------+------------+------+---------------+--------------+---------+----------------+--------------------+

Unmonitor Nodes

Once SingleStore Toolbox is managing the SingleStore cluster, the competing MemSQL Ops management system must be uninstalled.

Use MemSQL Ops to list all running nodes.

memsql-ops memsql-list
 ID       Agent Id  Process State  Cluster State  Role        Host        Port  Version 
 44CDE71  A0c8a89   RUNNING        CONNECTED      MASTER      10.0.0.98   3306  x.x
 3FD3FCF  Aa5eab9   RUNNING        CONNECTED      AGGREGATOR  10.0.0.133  3306  x.x
 9C0841F  A04f65f   RUNNING        CONNECTED      LEAF        10.0.0.236  3306  x.x
 9A551DA  A5f0572   RUNNING        CONNECTED      LEAF        10.0.0.136  3306  x.x

Use MemSQL Ops to stop monitoring each SingleStore node.

Use the IDs from the ID column in the MemSQL Ops output.

Caution

Do not remove the leaf nodes if prompted.

Main Host

Run the following command on the main host, which is usually the host of the Master Aggregator.

memsql-ops memsql-unmonitor 44CDE71
2019-07-02 16:30:23: J9fe705 [INFO] Stopping monitoring for MemSQL node ABECB9CFBB84C5369982BE10A85EC050D20486B0
2019-07-02 16:30:23: J9fe705 [INFO] No longer monitoring MemSQL node ABECB9CFBB84C5369982BE10A85EC050D20486B0

Unmonitor Remaining Nodes

Child Aggregator

memsql-ops memsql-unmonitor 3FD3FCF
2019-07-02 16:30:54: J6fb0f3 [INFO] Stopping monitoring for MemSQL node 4DF1BC43819C21D33B6D6AAE02C8851F40709FDF
2019-07-02 16:30:54: J6fb0f3 [INFO] No longer monitoring MemSQL node 4DF1BC43819C21D33B6D6AAE02C8851F40709FDF

Leaf Node 1

memsql-ops memsql-unmonitor 9C0841F
Would you additionally like to remove the leaf node from the SingleStore cluster? (You can specify this behavior with --remove-leaf.) [y/n] n
2019-07-02 16:31:22: J2eae2d [INFO] Stopping monitoring for MemSQL node AF1C3A169BFE4FD792163909BDB592C65D0E37AF
2019-07-02 16:31:22: J2eae2d [INFO] No longer monitoring MemSQL node AF1C3A169BFE4FD792163909BDB592C65D0E37AF

Note: If a leaf is removed unintentionally, connect to the database and run ADD LEAF to add it back to the cluster: For example, on the Master Aggregator: memsql> ADD LEAF 'root'@'<<leaf node IP>>':3307

Leaf Node 2

memsql-ops memsql-unmonitor 9A551DA
Would you additionally like to remove the leaf node from the SingleStore cluster? (You can specify this behavior with --remove-leaf.) [y/n] n
2019-07-02 16:32:01: J52843c [INFO] Stopping monitoring for MemSQL node 143B60B39072A9E9C16D2D8ACC16E6997286079E
2019-07-02 16:32:01: J52843c [INFO] No longer monitoring MemSQL node 143B60B39072A9E9C16D2D8ACC16E6997286079E

Note: If a leaf is removed unintentionally, connect to the database and run ADD LEAF to add it back to the cluster: For example, on the Master Aggregator: memsql> ADD LEAF 'root'@'<<leaf node IP>>':3307

Confirm Unmonitored Nodes

Use MemSQL Ops to confirm that all SingleStore nodes are no longer monitored.

memsql-ops memsql-list
No MemSQL nodes were found.

Remove MemSQL Ops

sudo memsql-ops agent-uninstall --uninstall-ops-only --all

The --uninstall-ops-only flag ensures that the MemSQL Ops agent will be uninstalled without deleting any node data.

Last modified: April 3, 2023

Was this article helpful?