Setup a MemSQL Cluster in Debian 8

MemSQL is a distributed database that perform its transactions in memory, although the user can deliberately choose to store things on disk should they wish. This results in extremely low latency and high throughput and is perfect for real-time systems.

MemSQL Introduction Video

For this tutorial, I am going to configure two Debian 8 virtual machines using virtualbox consisting of an aggregator node that has just 4GiB of RAM and a leaf node that has over 8GiB of RAM. The leaf nodes store all of the data in memory, and the aggregator acts as a gatekeeper that takes queries from clients and pulls the relevant information from the relevant leaf nodes. This kind of architecture is the most common in distributed systems. If you are just getting started, you can install the aggregator and the leaf node in one go on the same server, but this tutorial is aimed at those who would like to configure a cluster.

Every machine in the cluster requires at least 4 vCPUs and 4 GiB of RAM.

Steps

Download the installation files on every node in your cluster.

wget http://download.memsql.com/memsql-ops-4.1.9/memsql-ops-4.1.9.tar.gz

By the time you read this, there may be a later version, so use the download that this page will give you.

Extract the files, navigate to within the extracted directory and run the installer.

tar --extract --gzip --file memsql-ops*
cd memsql-ops*
sudo ./install.sh

You should see the following output:

Installing MemSQL Ops in /var/lib/memsql-ops
Creating a memsql user
Setting up permissions
Adding stuart to the memsql group
Creating an init script so that MemSQL Ops runs on startup
Creating a symlink to MemSQL Ops at /usr/bin/memsql-ops

Successfully installed MemSQL Ops!

--------------------------------------------------------------------------------

You can install MemSQL across multiple machines
using the MemSQL Ops Web UI.

For quick testing, you can also install a simple MemSQL
deployment on this host.

Do you want to install MemSQL on this host only? [y/N]

If you are installing a cluster as we are, then answer with N. If you are setting up just a single server that will act as both aggregator and leaf node, then answer with 'Y'.

Navigate to the aggregator's IP/hostname in your browser with the port 9000. For example:

http://memsql-aggregator.programster.org:9000

You should be shown a page like follows:

Click next and select "Community Edition".

Then select "On Multiple Hosts".

Click cancel on the setup page. We are not going to plug our sudo/root SSH access details into this software. Instead we are going to manually configure the leaf nodes to "follow" the aggregator. Details of which can be found in the offline install manual.

SSH into the leaf node after installation and execute:

memsql-ops follow -h [aggregator hostname or IP]

You will now see the node's IP in your aggregators web GUI.

Now that all of your leaf nodes have been added and set to follow the aggregator, click the plus symbol in the web GUI and select "Add MemSQL Nodes"

You should see all of your nodes listed with their roles (master aggregator and leaf). If you are happy with the cluster, click the checkboxes and click the "DEPLOY MEMSQL CLUSTER" button.

You will see a progress log of the cluster setting up. Once it has finished, it will show you the command you need to execute in order to connect to your cluster. For example, mine was:

mysql -u root -h memsql-aggregator.programster.org -P 3306 --prompt="memsql> ";

Conclusion

You now have a MemSQL cluster configured. Next time we will cover setting up redundancy for automatic failover and securing your cluster.

References

• MemSQL Docs
• DigitalOcean - How to Install MemSQL on Ubuntu 14.04