Outdated Version

You are viewing an older version of this section. View current production version.

SingleStore DB Studio WebSocket Proxy

MemSQL v7.1 introduced a WebSocket Proxy which provides a new method for communicating with a MemSQL 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 MemSQL 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 MemSQL 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.

    sdb-admin start-node --all
    

Option 2: SingleStore DB Toolbox

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

    For HTTPS:

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

    For HTTP:

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

Refer to the sdb-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 MemSQL logs. Refer to Trace Log for more information.

Use SingleStore DB Studio with the Proxy

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

Figure 1: Master Aggregator Host Architecture
image

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

This solution has a number of advantages:

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

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

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

Figure 2: Separate Host Architecture
image

Architectural Differences

There are some differences with this new SingleStore DB 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 singlestoredb-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 SingleStore DB Studio architecture, where SingleStore DB Studio is served over HTTPS and the the singlestoredb-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.

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

  • The MemSQL endpoint added to SingleStore DB Studio must use the same domain that’s configured in the MemSQL 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 SingleStore DB Studio

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

  1. Start the proxy.

  2. Edit the SingleStore DB 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 SingleStore DB Studio.

    Using systemd with package-based installations of SingleStore DB Studio:

    sudo systemctl restart singlestoredb-studio
    

    Using sudo with package-based installations of SingleStore DB Studio:

    sudo singlestoredb-studio &
    

    For non-sudo/tarball-based installations of SingleStore DB Studio:

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