---

You may need to perform a full installation of MemSQL as described in this guide if one of these conditions are met:

• The primary agent host does not have internet access
• Certain system limitations prevent users from having full access to hosts such as limitations on:
  • sudo
  • ssh
  • port access

The degree to which you may need to perform a manual installation will depend on the extent to which your system access is limited.

Follow the steps below to set up your MemSQL cluster:

1. Download MemSQL from memsql.com

Download latest version of MemSQL from memsql.com. This will download the MemSQL Ops installer which comes packaged as a compressed tar.gz.

2. Choose a host for the MemSQL Ops primary agent

You also need to choose the host for the MemSQL Ops primary agent. In a MemSQL Ops-deployed MemSQL cluster, every host machine must have a MemSQL Ops agent locally installed. Among the installed agents, the primary agent is the keeper of MemSQL Ops metadata. In a cluster, it is “followed” by the other MemSQL Ops agents. By default, the MemSQL Ops primary agent is on the same host as the MemSQL Database master aggregator, but this is not required.

3. Install MemSQL Ops on your chosen host for the primary agent

Within your chosen host for the MemSQL Ops primary agent, unpack the downloaded MemSQL Ops installer. This expands into the MemSQL Ops folder, memsql-ops-<version>.

tar xvf memsql-ops-<version>.tar.gz

Installing With Sudo Access

After unpacking the targz file, install MemSQL Ops with sudo privileges using the install.sh script. This will install MemSQL Ops on the primary host. Specifically, it will do the following:

• Setup the memsql user and group on the host
• Install memsql-ops in /var/lib/memsql-ops
• Set up MemSQL Ops to initialize at system startup
• Configure Linux system parameters that ensure optimal MemSQL database performance

By default, MemSQL Ops stores data in the following locations:

• MemSQL Ops data, including logs, is stored in /var/lib/memsql-ops/data. This directory can be changed at installation time by specifying the option --ops-datadir.
• MemSQL installation data is stored in /var/lib/memsql. This directory can be changed at installation time by specifying the option --memsql-installs-dir.

For instance, to save MemSQL Ops data in /path/to/memsql-ops-data and MemSQL installations in /path/to/memsql-installs, run:

sudo ./install.sh --ops-datadir /path/to/memsql-ops-data --memsql-installs-dir /path/to/memsql-installs

For more installation options, refer to the MemSQL Ops agent-deploy reference topic.

Installing Without Sudo Access

If you do not have sudo access on the machine, do the following:

1. Ensure the memsql-ops-<version> directory is in a directory you have access to. (required)
2. Set up memsql-ops to initialize at system startup (i.e. ensure /path/to/memsql-ops/memsql-ops start runs at system start). Refer to your distribution’s guides for more information on how to do this. (recommended)
3. Configure Linux settings for your host as described in the Installation Best Practices section (recommended)
4. Start the MemSQL Ops agent and web interface. This does not configure any system-wide permissions or settings. (required)

 /path/to/memsql-ops/memsql-ops start

5. Once MemSQL Ops is installed on the primary host, the MemSQL Ops web interface will be accessible on http://<primary_host_name>:9000. Point your browser to that URL and follow the steps to configure additional MemSQL Ops hosts and finish configuring your MemSQL cluster.

By default, MemSQL Ops will expose its web interface on port 9000. The web interface is the recommended way of installing MemSQL and MemSQL Ops, so if port 9000 is not available, restart MemSQL Ops on an exposed port if possible, as shown below:

/path/to/memsql-ops/memsql-ops stop
/path/to/memsql-ops/memsql-ops start --port NNN

4. Complete MemSQL Database deployment using the web UI or command line interface

With Internet and SSH Access to all hosts

MemSQL Ops will first ask if you would like to install the Enterprise or Community Edition.

If you choose the Enterprise Edition, MemSQL Ops will ask for a valid Enterprise license key. If you do not have one already, you can sign up for a new Enterprise license in the next screen.

Proper completion of the form generates an Enterprise license, which sends an email to the email address specified. The email will contain an Enterprise license key.

Community Edition users do not need to specify a license key.

MemSQL Ops will then ask if you would like to install MemSQL On Multiple Hosts or On a Single Host.

A multiple host installation sets up a MemSQL cluster across several hosts. A single host installation sets up an entire MemSQL cluster with one master aggregator and one leaf node in a single host.

Setting up an entire MemSQL cluster using the single host option is possible and recommended for quick functional testing. That said, it is not recommended for production environments where performance and high availability are critical. When configuring a MemSQL cluster in a single box, it is important to ensure port numbers of MemSQL aggregator and leaf nodes do not conflict.

If you choose the On Multiple Hosts option, you will see the Add Hosts screen, where you can input information about your cluster.

You will then see a screen that allows you to type in the hostnames, and the SSH access credentials - username, password and ssh private key. Through SSH, the primary MemSQL Ops agent will install itself into those other hosts.

Alternatively, you can use the memsql-ops command line interface to deploy MemSQL Ops to all other hosts in your cluster. The command below will use SSH to set the cluster up.

/path/to/memsql-ops/memsql-ops agent-deploy -h <SSH HOST> -P <SSH PORT> -u <SSH USER> -p <SSH PASSWORD> -i <SSH PRIVATE KEY>

If sudo is not accessible, you may be required to add --allow-no-sudo.

Finally, you will see a prompt that summarizes the hosts to be configured. You can edit the configuration to your liking. Once you are satisfied, click the Provision Hosts button. This will install the MemSQL Ops agent in each of the hosts.

MemSQL Ops will then download the MemSQL database binary, install it on each host, and set up the MemSQL cluster according to your desired configuration.

Without Internet Access

MemSQL Ops will first ask if you would like to install the Enterprise or Community Edition.

Add the MemSQL Database Binaries to Ops Manually

If internet access is not available on your primary agent or the web GUI is not available, the license and binary files must be downloaded manually.

If you select the Enterprise Edition, the MemSQL Ops web interface will prompt you for a valid license key. If you do not have one already, you can obtain a license key either through memsql.com/download or by contacting your MemSQL representative. You will then receive an email containing steps for how to download the MemSQL Database Enterprise Edition binary.

Download the MemSQL Database binary on a machine with internet access and copy it to your primary agent.

For Community Edition, the download link is: http://download.memsql.com/releases/latest/memsqlbin_amd64.tar.gz

For Enterprise Edition, the download link is: http://download.memsql.com/<LICENSE_KEY>/memsqlbin_amd64.tar.gz

Add the MemSQL binary to your primary agent using the file-add command:

memsql-ops file-add -t memsql /path/to/memsqlbin.tar.gz

Deploy MemSQL Ops to the remaining machines in your cluster

By default, MemSQL Ops uses SSH to deploy agents to new hosts in your cluster. If SSH is available, follow the steps in the With Internet and SSH Access to all hosts section above.

If SSH is not available between the hosts in your cluster, you must add each host to the MemSQL Ops cluster manually, following the steps below:

1. Install MemSQL Ops to each host in your cluster manually, by running install.sh in every single host.
2. Inform each of your new hosts about the location of the primary agent.

/path/to/memsql-ops/memsql-ops follow -h <primary_hostname> -P <primary_host_port>

3. After the MemSQL Ops agent is installed into each of the other hosts, you can add MemSQL nodes into those hosts.

From the web interface

• In the MemSQL Ops web interface, click on the “+” button, and select “Add MemSQL Nodes”:
• You will see a prompt that recommends a possible configuration (master aggregator, child aggregators, leaf nodes) for your cluster. Once you are satisfied with the configuration, click “Deploy MemSQL Cluster”.

From the command line

• From each host in your cluster, deploy a MemSQL node using MemSQL Ops

/path/to/memsql-ops/memsql-ops memsql-deploy --role {master,aggregator,leaf}

5. Test that MemSQL is deployed

To test that everything is set up correctly, connect to the master aggregator using any valid MySQL client driver.

Download a MySQL driver either through the MySQL website directly, or use your OS package manager to install the MySQL driver. For example, in Ubuntu:

sudo apt-get install mysql-client

When you have a MySQL client installed, you can then connect to the MemSQL database. For example:

mysql -u root -h <master_aggregator_hostname> -P 3306 --prompt="memsql> "

You can now start creating databases and querying MemSQL! Here’s a set of sample commands to get you started. You can copy-paste these directly into your MemSQL session.

-- create a new user
grant all on *.* to 'myuser'@'%';
-- create a new database
create database mymemsqldb;
show databases;
use mymemsqldb;
-- create a new rowstore table
create table mytable (id INT PRIMARY KEY, name varchar(10));
-- create a new columnstore table
create table mycolumnstoretable (id INT, groupid INT, name varchar(10), key (groupid) using clustered columnstore, shard key (id));
show tables;
describe mytable;
describe mycolumnstoretable;
-- perform data query operations
insert into mytable values (1, 'row');
select * from mytable;
insert into mycolumnstoretable values (1, 2, 'column');
select * from mycolumnstoretable;
select mytable.name, mycolumnstoretable.name from mytable inner join mycolumnstoretable on mytable.id = mycolumnstoretable.id;
-- determine which memsql version you are using
select @@memsql_version;