Memsql Installation on different Port


#1

How can I Install or uninstall the memsql on different port rather than default port 3306. Our present test environment nodes are with the port 43306 and 43307 respectively. I tried installing memsql with memsql-deploy tool and it took the default 3306. Can you please let me know what need to be changed here.

Also I wanted to uninstall the the existing installing which is on 43306 port. Please let me know how can I get that work on different ports.


#2

See the documentation here. With memsql-deploy, you can specify a port using -P or --port. Otherwise, it defaults to port 3306, as you observed. With memsql-delete, you can uninstall the nodes on the wrong port.


#3

Thanks for the reply , the document here shows the memsql-ops memsql-deploy, but isn’t that memsql-ops deprecated from 6.7 as per this documentation here.

I am trying to use the memsql-deploy cluster-in-a-box --license [YOUR LICENSE KEY] as referred in this doc, where there is no option for -P or --port.


#4

Sorry about that! I didn’t realize that my search for memsql-deploy took me to MemSQL Ops.

Here is the documentation for memsql-deploy setup-cluster for MemSQL Tools, which has a memsql-port option to specify a port other than 3306.

To start over, I think that you could either use memsql-deploy uninstall or memsql-admin commands such as stop-node, remove-leaf, remove-aggregator, delete-node, add-node with the port option, add-aggregator, and add-leaf.

I hope that’s more helpful.


#5

Sorry I did not mention earlier regarding the hosts, I think that helps for multi hosts, my test env is with single host and for that I am using
memsql-deploy cluster-in-a-box to create the cluster ( This will install the latest released version of memsql-server on the local machine and deploy a master aggregator on port 3306 and a leaf node on port 3307). How can I install in different port in a single host?

Also I tried upgrading the existing one which is currently 5.7 version on different ports, but unfortunately i was not able to upgrade it memsql-deploy upgrade --> unable to determine package type: no hosts. Looks like even the upgrade looking at the port 3306 by default.


#6

I could be wrong, but I don’t think that using memsql-deploy cluster-in-a-box is a requirement to deploy to a single box. It just deploys a common configuration to make the process easier. However, if you’re deviating from that common configuration, by using the non-default ports for example, then I suspect that you just need to use memsql-deploy setup-cluster.

MemSQL Tools is more recent functionality (added with 6.7, I think), so I suspect that you need to upgrade to at least 6.7 using MemSQL Ops before you can start using MemSQL Tools for future upgrades. See the documentation on how to do that here.


#7

I tried doing with memsql-deploy setup-cluster, but that doesn’t seem to work for single host as it throws error saying both the master and leadf node IP’s are same.

$ memsql-deploy setup-cluster --license BGE5ZGM1ODU2NDAwZDQ1NGM4ZGNmZjBmZDEwYTBkMzY1AAAAAAAAAAAEAAAAAAAAAAwwNQIYJ/hpxFt9So1zMUt8iGcwvByNt1650kvmAhkA9Kuh+Yvmh6KkiVSJJ8JJlpfiftWFv/13AA== --master-host 10.190.2.26 --memsql-port 3307 --leaf-hosts 10.190.2.26 --memsql-port 3306 --password root --high-availability=false

Starting rollback
✓ Rollback succeeded
Duplicate host 10.193.5.26 detected. Only unique hosts are allowed

However this works with different hosts

memsql-deploy setup-cluster --license BGE5ZGM1ODU2NDAwZDQ1NGM4ZGNmZjBmZDEwYTBkMzY1AAAAAAAAAAAEAAAAAAAAAAwwNQIYJ/hpxFt9So1zMUt8iGcwvByNt1650kvmAhkA9Kuh+Yvmh6KkiVSJJ8JJlpfiftWFv/13AA== --master-host 10.190.2.26 --memsql-port 3307 --leaf-hosts 10.190.2.27 --memsql-port 3306 --password root --high-availability=false
root@10.190.2.26's password: 
root@10.190.2.27s password: 
✓ Registered hosts
memsql-deploy will perform the following actions:
  · Install memsql-server 6.7.18 on hosts
    - 10.190.2.26
    - 10.190.2.27
  · Deploy a master aggregator on 10.193.5.26:3306
  · Deploy a leaf node on 10.193.4.60:3306
  · Set MemSQL root password on all nodes

#8

Looking at the documentation again, it appears that you may only be able to use the memsql-port flag to specify one port for all nodes, which is why it won’t let you use one IP address as well. Perhaps review the host-file flag for setup-cluster. It appears that you should be able to specify different ports for each node that way.


#9

No luck using --host-file , still getting the same error message.

memsql-deploy setup-cluster --license BGE5ZGM1ODU2NDAwZDQ1NGM4ZGNmZjBmZDEwYTBkMzY1AAAAAAAAAAAEAAAAAAAAAAwwNQIYJ/hpxFt9So1zMUt8iGcwvByNt1650kvmAhkA9Kuh+Yvmh6KkiVSJJ8JJlpfiftWFv/13AA== --hosts-file hosts.ini --password root --high-availability=false

Starting rollback

✓ Rollback succeeded

Duplicate host 10.193.4.37 detected. Only unique hosts are allowed

#10

The simpler cluster-in-a-box and setup-cluster installations do not support custom ports on a single host unfortunately. You should follow the “Comprehensive” installation guide (https://docs.memsql.com/guides/latest/install-memsql/on-premises/multi-host/comprehensive/step-1/) and use the --port option to memsql-admin create-node. This installation guide works for both single host and multiple hosts.


#11

Thank you @jack, that really helps, I have tried to register the hosts and tried using memsql-deploy but with vagrant user we couldn’t use memsql tools, it doesn’t see the hosts or nodes. We have to login to root user and use the memsql tools. But thats not the case with memsql-ops, we were able to manage the nodes with vagrant user itself.

✓ Successfully registered host 127.0.0.1
±----------±-----------±------------±--------------+
| Host | Local Host | SSH address | Identity File |
±----------±-----------±------------±--------------+
| 127.0.0.1 | No | 127.0.0.1 | |
±----------±-----------±------------±--------------+
[17.1.0:vagrant@feature-114:2 ~]$ memsql-deploy install
vagrant@127.0.0.1’s password:
The target user vagrant on host 127.0.0.1 does not have the privileges to perform this action.
Please enter your password to proceed with sudo. (For details, see https://docs.memsql.com/toolbox-redir/sudo-prompt).
sudo password for vagrant@127.0.0.1:
could not achieve desired privilege level: error running command: "/usr/bin/ssh" "-oBatchMode=yes" "-q" "-tt" "-oControlPath=/tmp/memsql-toolbox123164112/1.socket" "127.0.0.1" "'timeout'" "'--foreground'" "'5'" "'sudo'" "'-S'" "'-k'" "'-v'": exit status 125


#12

It’s expected that installation operations like memsql-deploy install requires root/sudo in a regular Tools installation. However operations like memsql-admin list-nodes and memsql-admin restart-node should not require root/sudo normally. It’s possible that they do in your case because of permissions issues, since you registered an existing cluster that may have been installed under a different user, or something like that.

You can install Tools without root/sudo, see this answer:


#13

Getting the below error while adding the leaf node

[17.1.0:root@feature-114:0 vagrant]# memsql-admin list-nodes
root@127.0.0.1’s password:
±-----------±--------±----------±------±--------------±-------------±--------±---------------±-------------------+
| MemSQL ID | Role | Host | Port | Process State | Connectable? | Version | Recovery State | Availability Group |
±-----------±--------±----------±------±--------------±-------------±--------±---------------±-------------------+
| E61AB34DED | Master | 127.0.0.1 | 43306 | Running | True | 6.8.1 | Online | |
| 42A11FA966 | Unknown | 127.0.0.1 | 43307 | Running | True | 6.8.1 | Online | |
±-----------±--------±----------±------±--------------±-------------±--------±---------------±-------------------+
[17.1.0:root@feature-114:0 vagrant]# memsql-admin add-leaf --memsql-id 42A11FA9666B468704E9F716D757BAEEB4A6348D
root@127.0.0.1’s password:
Toolbox will perform the following actions on host 127.0.0.1:
· Run ‘memsqlctl add-leaf --host 127.0.0.1 --port 43307 --user root’

Would you like to continue? [y/N]: y
✘ Failed to run ‘memsqlctl add-leaf’
error running memsqlctl: error running command: "/usr/bin/ssh" "-oBatchMode=yes" "-q" "-tt" "-oControlPath=/tmp/memsql-toolbox769032315/1.socket" "127.0.0.1" "'memsqlctl'" "'--json'" "'--yes'" "'add-leaf'" "'--host'" "'127.0.0.1'" "'--port'" "'43307'" "'--user'" "'root'": exit status 1
stderr: tcgetattr: Inappropriate ioctl for device
Could not add leaf to cluster. Verify network connectivity between the node at 127.0.0.1:43307 and the master aggregator.
Please ensure that you have entered the correct password for the node.
If the node does not yet exist, run ‘memsql-admin create-node’ and then retry this command.


#14

@jack/@bvincent Any update on this, Installing new memsql or “registering” the existing nodes, both seems to be not working on a single host.


#15

I tried using below command, it worked fine. I was able to add the nodes with different ports.

memsqlctl add-leaf --host 127.0.0.1 --port 43307

But then for the upgrade the memsql-admin register-node throwing an error at

[17.1.0:root@feature-114:0 master-43306-MIa4c4bbc0]# memsql-admin register-node --host 127.0.0.1 --memsql-config /database/memsql/master-43306-MIa4c4bbc0/memsql.cnf --password “”

Toolbox is about to perform the following actions on host 127.0.0.1:

· Run ‘memsqlctl register-node --memsql-config /database/memsql/master-43306-MIa4c4bbc0/memsql.cnf --password ●●●●●●’

Would you like to continue? [y/N]: y

error running memsqlctl: error running command: "/usr/bin/memsqlctl" "--json" "--yes" "register-node" "--memsql-config" "/database/memsql/master-43306-MIa4c4bbc0/memsql.cnf" "--secure-password" "": exit status 1

stderr: Failed to verify that the directory tree at /database/memsql/master-43306-MIa4c4bbc0/plancache is owned by root: Path ‘/database/memsql/master-43306-MIa4c4bbc0/plancache/001’ uid: 32120, gid: 497 does not match target uid: 0, gid: 0

[17.1.0:root@feature-114:0 master-43306-MIa4c4bbc0]# chown memsql:memsql ./plancache/