Join the SingleStore Community Today
Get expert advice, develop skills, and connect with others.

MemSQL Studio WebSocket Proxy

MemSQL v7.1 introduced a WebSocket Proxy which provides a new method for communicating with a cluster. The WebSocket Proxy (or, simply, “the proxy”) has a small footprint, and typically a low overhead when running.

Each proxy instance opens:

  • A new Unix socket for communicating with the SingleStore DB engine
  • An HTTPS port for communicating with the outside world

Start the Proxy

There are three options available for starting the proxy. Before starting the proxy, keep the following points in mind:

  • As the https_proxy_port and http_proxy_port WebSocket variables are mutually exclusive, the proxy will fail to start if both are set, and you will be prompted to update the memsql.cnf file.

  • The https_proxy_port variable requires SSL to be enabled. For more information on enabling SSL, refer to SSL Secure Connections guide.

  • If multiple nodes have a WebSocket variable set, only the Master Aggregator’s proxy will be used, with the non-aggregator proxies remaining dormant. If a new aggregator is attached or promoted, it must have a WebSocket variable set in order to serve WebSocket traffic.

Option 1: Modify the memsql.cnf file

  1. On the Master Aggregator host of a cluster, add one of the following lines to the memsql.cnf file.

    • For HTTPS: https_proxy_port = <port>
    • For HTTP: http_proxy_port = <port>
  2. Start the cluster.

    memsql-admin start-node --all
    

Option 2: MemSQL Toolbox

  1. On the Master Aggregator host of a running cluster, use memsql-admin update config to set either the https_proxy_port or http_proxy_port variable.

    For HTTPS:

    memsql-admin update-config --key https_proxy_port --value <port> --all
    

    For HTTP:

    memsql-admin update-config --key http_proxy_port --value <port> --all
    

Refer to the memsql-admin update-config command reference for more information on how to update the configuration of the Master Aggregator.

Option 3: SQL

  1. Start the cluster and run the following in a database connection where you have superuser SUPER privileges.

    For HTTPS:

    SET GLOBAL https_proxy_port = <port>;
    

    For HTTP:

    SET GLOBAL http_proxy_port = <port>;
    
  2. Restart the proxy.

    RESTART PROXY;
    

This will allow the proxy to start even if no proxy port was specified in the memsql.cnf file. As setting both https_proxy_port and http_proxy_port is not supported, select the protocol that best fits your requirements.

Alert

In the case of conflicting variables, such as when both the https_proxy_port and http_proxy_port variables are set, the proxy will throw an error but not abort. This means that it is possible for both variables to be set at runtime, but RESTART PROXY will be disabled until this discrepancy is resolved. As these variables can be set coincidentally, there should be no expectation that the state of the global variable reflects the state of the proxy.

Change the Proxy Port

  1. To change a proxy’s port when it’s running, specify a new port and restart the proxy.

    For HTTPS:

    SET GLOBAL https_proxy_port = <port>;
    

    For HTTP:

    SET GLOBAL http_proxy_port = <port>;
    
  2. Restart the proxy. Note that RESTART PROXY still requires SUPER.

    RESTART PROXY;
    

By design, RESTART PROXY is designed to either fail, where nothing undesirable happens and an error is returned, or to work absolutely, where a proxy is running on the port specified by the global variable.

About the Proxy State

Regardless of how it was started, the proxy will not restart itself on a failure. This is intentional, because if the proxy terminates, it will require manual intervention to restart it. In this case, the user and/or the host’s administrator must verify that the host itself is stable and problem-free before issuing a RESTART PROXY.

If RESTART PROXY does not throw an error, the state of the proxy is reflected in the global variable at the time of the restart. Once started, it is the user’s responsibility to ensure that the proxy does not enter an unknown state.

The proxy log, websocket_proxy.log, can be found in the same directory as the other SingleStore DB logs. Refer to Trace Log for more information.

Use MemSQL Studio with the Proxy

Prior to MemSQL Studio v1.9.7, MemSQL Studio was required to be co-located on the cluster’s Master Aggregator host if secure connections to SingleStore DB were desired.

Figure 1: Master Aggregator Host Architecture
image

With the introduction of this WebSocket Proxy (or, simply, “the proxy”), MemSQL Studio can now connect to SingleStore DB via HTTPS on hosts other than the Master Aggregator.

This solution has a number of advantages:

  • Unlike the previous solution, MemSQL Studio can now accept users that are configured as REQUIRE SSL. Previously, the communication between MemSQL Studio and SingleStore DB was not over HTTPS, which necessitated the co-location requirement. As a consequence, MemSQL Studio no longer needs to be co-located with a cluster in order to provide secure communications.

  • Traffic no longer needs to be proxied through MemSQL Studio and can now go directly to the cluster.

As shown in the diagram below, MemSQL Studio can now connect directly to a cluster through the wss:// protocol.

Figure 2: Separate Host Architecture
image

Architectural Differences

There are some differences with this new MemSQL Studio architecture compared to the one it replaces:

  • The machine running the browser needs to have direct access to the Master Aggregator host. Previously, only the memsql-studio server required this access.

  • For situations where REQUIRE SSL is not mandatory, and if the additional configuration required to use a direct WebSocket connection becomes a bottleneck, it may be simpler to use the existing MemSQL Studio architecture, where MemSQL Studio is served over HTTPS and the the memsql-studio server is co-located with the Master Aggregator.

Prerequisites

The following requirements must be met in order to use a secure proxy connection from your browser.

  • MemSQL Studio must be served via HTTPS to establish and use a proxy connection via the wss:// protocol.

  • The SingleStore DB endpoint added to MemSQL Studio must use the same domain that’s configured in the SingleStore DB SSL certificate as expressed in the memsql.cnf file.

  • The port (the https_proxy_port or http_proxy_port value) must be exposed so that a browser can communicate over it.

  • The browser must be configured to trust the certificate. This can either be achieved by using a certificate created by a known CA, such as letsencrypt.org, or by an internal enterprise CA that may be pre-installed on a company computer. Refer to How to add a trusted CA certificate to Chrome and Firefox for more information.

Configure MemSQL Studio

Use the following steps to configure MemSQL Studio to use the WebSocket proxy to connect to your cluster.

  1. Start the proxy.

  2. Edit the MemSQL Studio state file and add the following lines to the cluster definition:

    • websocket = true
    • websocketSSL = true or false
      • If using https_proxy_port: true
      • If using http_proxy_port: false
    • port = <port>
      • If websocket is true, the WebSocket port
  3. Restart MemSQL Studio.

    Using systemd with package-based installations of MemSQL Studio:

    sudo systemctl restart memsql-studio
    

    Using sudo with package-based installations of MemSQL Studio:

    sudo memsql-studio &
    

    For non-sudo/tarball-based installations of MemSQL Studio:

    cd memsql-studio-<version>
    
    nohup ./memsql-studio > studio.stdout 2> studio.stderr < /dev/null &
    
  4. Log into MemSQL Studio and connect to the cluster that you configured to use WebSockets.