Migrate Cluster min read


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

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

Info

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.

Change to the /home/<user>/memsql/memsql-toolbox-<version> directory first before running all commands.

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

Review Current Cluster

Use MemSQL Ops to view the MemSQL cluster.

/home/<user>/memsql/memsql-ops-<version>/memsql-ops/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 secure shell (ssh) into each host in the cluster.

/home/<user>/memsql/memsql-ops-<version>/memsql-ops/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

If Has Credential is No for any host, use memsql-ops agent-set-credential to configure ssh keys for each host. Alternatively, use the following script to automate this process.

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

End: Seeing Has Credential is No?

Enable Manual Control

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

/home/<user>/memsql/memsql-ops-<version>/memsql-ops/memsql-ops cluster-manual-control --enable
****
The cluster is now under manual control. We will not automatically restart stopped nodes, update license files, etc.
Alert

MemSQL Ops will not automatically restart nodes if they fail while the migration is underway. While MemSQL Toolbox has been installed, no hosts or nodes are known to it.

Add a MemSQL License

A MemSQL license from the MemSQL Customer Portal is required before you can proceed with the migration process.

/home/<user>/memsql/memsql-ops-<version>/memsql-ops/memsql-ops license-add --license-key <license>
****
Successfully added license.

Generate Cluster Migration File

Run this command to generate a cluster migration YAML file for use in the next command.

/home/<user>/memsql/memsql-ops-<version>/memsql-ops/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 /home/<user>/memsql/memsql-ops-<version>/memsql-ops/data/cache/migration_config.yml

Modify Migration File

At the bottom of the YAML file:

  1. Change memsql_server_version to memsql_server_file_path: /home/<user>/memsql/memsql-server-<version>.tar.gz

  2. Change package_type: rpm to package_type: tar

    These two lines should then resemble:

    memsql_server_file_path: /home/<user>/memsql/memsql-server-<version>.tar.gz
    package_type: tar
    

Deploy Migration File

Use the cluster migration YAML generated by the previous command.

./memsql-deploy setup-cluster --cluster-file <cluster_file>
✓ Connection successful to 10.0.0.136     
✓ Connection successful to 10.0.0.133
✓ Connection successful to 10.0.0.236
memsql-deploy will perform the following actions:
  · Register Hosts
    - Host: 10.0.0.98
      + Localhost: true
    - Host: 10.0.0.136
      + Localhost: false
      + Port: 22
      + Username: <user>
    - Host: 10.0.0.133
      + Localhost: false
      + Port: 22
      + Username: <user>
    - Host: 10.0.0.236
      + Localhost: false
      + Port: 22
      + Username: <user>
  · Install MemSQL Server
    - Install memsql-server x.x on 10.0.0.98
    - Install memsql-server x.x on 10.0.0.136
    - Install memsql-server x.x on 10.0.0.133
    - Install memsql-server x.x on 10.0.0.236
  · Register Node
    - On Host: 10.0.0.136
      + Run 'memsqlctl register-node --memsql-config /var/lib/memsql/leaf-3306-MI2fc1915e/memsql.cnf --datadir /var/lib/memsql/leaf-3306-MI2fc1915e/data --plancachedir /var/lib/memsql/leaf-3306-MI2fc1915e/plancache --tracelogsdir /var/lib/memsql/leaf-3306-MI2fc1915e/tracelogs --port 3306'
    - On Host: 10.0.0.133
      + Run 'memsqlctl register-node --memsql-config /var/lib/memsql/child-3306-MIb4668da6/memsql.cnf --datadir /var/lib/memsql/child-3306-MIb4668da6/data --plancachedir /var/lib/memsql/child-3306-MIb4668da6/plancache --tracelogsdir /var/lib/memsql/child-3306-MIb4668da6/tracelogs --port 3306'
    - On Host: 10.0.0.236
      + Run 'memsqlctl register-node --memsql-config /var/lib/memsql/leaf-3306-MId88e2bc0/memsql.cnf --datadir /var/lib/memsql/leaf-3306-MId88e2bc0/data --plancachedir /var/lib/memsql/leaf-3306-MId88e2bc0/plancache --tracelogsdir /var/lib/memsql/leaf-3306-MId88e2bc0/tracelogs --port 3306'
    - On Host: 10.0.0.98
      + Run 'memsqlctl register-node --memsql-config /var/lib/memsql/master-3306-MI436853b1/memsql.cnf --datadir /var/lib/memsql/master-3306-MI436853b1/data --plancachedir /var/lib/memsql/master-3306-MI436853b1/plancache --tracelogsdir /var/lib/memsql/master-3306-MI436853b1/tracelogs --port 3306'
  · After executing the above actions the cluster is going to be in the following state:
    - 4 Registered nodes

Would you like to continue? [y/N]: y
✓ Registered hosts  
✓ Installed memsql-server<version> on host 10.0.0.236 (1/4)
✓ Installed memsql-server<version> on host 10.0.0.98 (2/4)
✓ Installed memsql-server<version> on host 10.0.0.136 (3/4)
✓ Installed memsql-server<version> on host 10.0.0.133 (4/4)
✓ Successfully installed on 4 hosts
✓ Successfully registered nodes
✓ No Nodes to Create
✓ No Leaves to add
✓ No Aggregators to add
The Final Cluster State
Hosts
+----------------+------------+---------------------------+---------------+
|      Host      | Local Host |        SSH address        | Identity File |
+----------------+------------+---------------------------+---------------+
| 10.0.0.98      | Yes        |                           |               |
| 10.0.0.133     | No         | <user>@10.0.0.133:22      |               |
| 10.0.0.136     | No         | <user>@10.0.0.136:22      |               |
| 10.0.0.236     | No         | <user>@10.0.0.236:22      |               |
+----------------+------------+---------------------------+---------------+
Nodes
+------------+------------+----------------+------+---------------+--------------+---------+----------------+--------------------+--------------+
| MemSQL ID  |    Role    |      Host      | Port | Process State | Connectable? | Version | Recovery State | Availability Group | Bind Address |
+------------+------------+----------------+------+---------------+--------------+---------+----------------+--------------------+--------------+
| 6C1E8E88A6 | Master     | 10.0.0.98      | 3306 | Running       | True         | x.x     | Online         |                    | 0.0.0.0      |
| BE9E87CCE3 | Aggregator | 10.0.0.133     | 3306 | Running       | True         | x.x     | Online         |                    | 0.0.0.0      |
| 8419072D05 | Leaf       | 10.0.0.136     | 3306 | Running       | True         | x.x     | Online         | 2                  | 0.0.0.0      |
| 36B777507F | Leaf       | 10.0.0.236     | 3306 | Running       | True         | x.x     | Online         | 1                  | 0.0.0.0      |
+------------+------------+----------------+------+---------------+--------------+---------+----------------+--------------------+--------------+

End: See Deploy Cluster Migration File Output

Confirm Registered Hosts

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

./memsql-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 MemSQL Toolbox to confirm that all nodes have been registered.

./memsql-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 MemSQL Toolbox to restart all nodes.

./memsql-admin restart-node --all --online
Toolbox is about to perform the following actions:
  · Restart all nodes in the cluster

Would you like to continue? [y/N]: y
✓ Stopped Master node on 10.0.0.98 (1/1)
✓ Successfully stopped Master node on 1 host
✓ Stopped Master node
✓ Stopped Aggregator nodes on 10.0.0.133 (1/1)
✓ Successfully stopped Aggregator nodes on 1 host
✓ Stopped Aggregator node
✓ Stopped Leaf nodes on 10.0.0.136 (1/2)
✓ Stopped Leaf nodes on 10.0.0.236 (2/2)
✓ Successfully stopped Leaf nodes on 2 hosts
✓ Stopped Leaf nodes
✓ Started Leaf nodes on 10.0.0.236 (1/2)
✓ Started Leaf nodes on 10.0.0.136 (2/2)
✓ Successfully started Leaf nodes on 2 hosts
✓ Successfully connected to Leaf nodes         
✓ Started Aggregator nodes on 10.0.0.133 (1/1)
✓ Successfully started Aggregator nodes on 1 host
✓ Successfully connected to Aggregator node         
✓ Started Master node on 10.0.0.98 (1/1)
✓ Successfully started Master node on 1 host
✓ Successfully connected to Master node         
Operation completed successfully

End: See Restart Nodes Output

Confirm Node Restart

Confirm that all nodes have restarted.

./memsql-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 MemSQL Toolbox is managing the MemSQL cluster, the competing MemSQL Ops management system must be uninstalled.

Use MemSQL Ops to list all running nodes.

/home/<user>/memsql/memsql-ops-<version>/memsql-ops/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 MemSQL node.

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

Alert

Do not remove the leaf nodes if prompted.

Master Aggregator

/home/<user>/memsql/memsql-ops-<version>/memsql-ops/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

Child Aggregator

/home/<user>/memsql/memsql-ops-<version>/memsql-ops/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

/home/<user>/memsql/memsql-ops-<version>/memsql-ops/memsql-ops memsql-unmonitor 9C0841F
****
Would you additionally like to remove the leaf node from the MemSQL 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

/home/<user>/memsql/memsql-ops-<version>/memsql-ops/memsql-ops memsql-unmonitor 9A551DA
****
Would you additionally like to remove the leaf node from the MemSQL 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

End: Unmonitor Remaining Nodes

Confirm Unmonitored Nodes

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

/home/<user>/memsql/memsql-ops-<version>/memsql-ops/memsql-ops memsql-list
****
No MemSQL nodes were found.

Remove MemSQL Ops

/home/<user>/memsql/memsql-ops-<version>/memsql-ops/memsql-ops agent-uninstall --uninstall-ops-only --all
This will completely delete all MemSQL Ops agents in the cluster, including this one. This operation will delete all MemSQL Ops data in the cluster and cannot be undone.
If you want to delete all MemSQL Ops agents, please type the word DELETE: DELETE
2019-09-06 13:58:23: J91ad35 [INFO] Deleting MemSQL Ops agent Ae9f037643ccf4b5fb3b0c03f2dd0da58
2019-09-06 13:58:23: J4cc070 [INFO] Deleting MemSQL Ops agent Ae2838b5fd2164d3a8691ad71227beee3
2019-09-06 13:58:23: Jfb011f [INFO] Deleting MemSQL Ops agent Afea25a7c3e184241acdf5296f429298d
2019-09-06 13:58:27: J4cc070 [INFO] Successfully deleted MemSQL Ops agent Ae2838b5fd2164d3a8691ad71227beee3
2019-09-06 13:58:27: Jfb011f [INFO] Successfully deleted MemSQL Ops agent Afea25a7c3e184241acdf5296f429298d
2019-09-06 13:58:27: J91ad35 [INFO] Successfully deleted MemSQL Ops agent Ae9f037643ccf4b5fb3b0c03f2dd0da58
WARNING: An unmonitored MemSQL node may still exist on this machine.  It had host 172.16.212.165 and port 3306 the last time MemSQL Ops connected to it.
Stopping MemSQL Ops
Waiting up to 60 seconds for a clean exit.
Done.
Successfully uninstalled MemSQL Ops

End: See Remove MemSQL Ops Output