Quick Start with Docker
As of MemSQL 6.7, MemSQL Ops is a deprecated tool. You are encouraged to use the new MemSQL management tools for future deployments. See the installation guides for more details on how to deploy and manage a cluster using the new management tools.
MemSQL runs on 64-bit Linux-based operating systems; however the easiest way to demonstrate how to use MemSQL on any major operating system is through a pre-configured Docker image. The
memsql/quickstart image is available for free and comes installed with MemSQL Ops, a single-node MemSQL cluster, and a simple benchmarking program.
Running MemSQL in a Docker container as described below is for prototyping and functional testing purposes only! If you wish to test out MemSQL for production deployments, see the on-premises quick start guide instead.
By downloading the
memsql/quickstart image, you agree to the MemSQL terms of service. Users who want to use MemSQL on Docker in production should contact MemSQL Support before proceeding. Get free enterprise support by downloading a MemSQL Enterprise trial today at memsql.com/download.
This guide assumes that Docker Community Edition (CE) has been installed on your machine. If you have not installed Docker CE on your machine, please refer to the Docker documentation for information on installing and setting up Docker.
In addition, make sure that Docker has access to at least four cores and 4GB of RAM on your machine. You can enable this in the Preferences dialog box in Docker CE. For more information on how to do this please consult Docker’s documentation.
Running the MemSQL Docker Image
The MemSQL docker image contains a master aggregator, which runs SQL queries across the cluster and aggregates the results, and a single leaf, which is responsible for storing and processing data. These two components form the most basic MemSQL cluster that you can run.
Before spinning up a MemSQL cluster, verify that your machine meets the minimum system requirements.
$ docker run --rm memsql/quickstart check-system
Now spin up the MemSQL cluster on your machine by running
docker run. The
memsql/quickstartimage will be downloaded first if it is not already on your machine. In order to connect to the web interface, you must expose port 9000 to your local machine, and to connect to the database, port 3306 must also be exposed.
$ docker run -d -p 3306:3306 -p 9000:9000 --name memsqlcontainer memsql/quickstart
Finally visit the MemSQL Ops web interface (requires Firefox or Google Chrome):
$ open "http://localhost:9000"
You should see the following:
Using Persistent Storage with your Container (Optional)
Starting up the MemSQL container without attaching a volume means all data will be lost when the container is destroyed or upgraded. If you wish to persist your data, do the following:
Create a data container that will store the data.
$ docker create --name memsqldata memsql/quickstart /bin/true
Run the MemSQL container, mounting the volume from the previous container.
$ docker run -d -p 3306:3306 -p 9000:9000 --name memsqlcontainer --volumes-from memsqldata memsql/quickstart
Connect to MemSQL and Create a Database
By exposing port 3306 when starting up the MemSQL container, MemSQL is available to connect to from your machine. You can then use the
memsql client within the container to perform cluster operations and run SQL queries against a database.
Connect to the container and open the
$ docker exec -it memsqlcontainer memsql
Run the following MemSQL Database commands to create a table and seed it with sample data:
memsql> create database test; memsql> use test; memsql> CREATE TABLE test_table (id BIGINT PRIMARY KEY AUTO_INCREMENT, c INT); memsql> INSERT INTO test_table (c) VALUES (1); memsql> INSERT INTO test_table (c) SELECT c*2 FROM test_table; memsql> INSERT INTO test_table (c) SELECT c*2 FROM test_table; memsql> INSERT INTO test_table (c) SELECT c*2 FROM test_table; memsql> INSERT INTO test_table (c) SELECT c*2 FROM test_table; memsql> INSERT INTO test_table (c) SELECT c*2 FROM test_table; memsql> SELECT SUM(c) FROM test_table; +--------+ | SUM(c) | +--------+ | 243 | +--------+ 1 row in set (0.00 sec)
Connect to MemSQL with Your Favorite Tools
In addition to running queries from within the container, you can also connect with your favorite tools like Sequel Pro, or use tools such as the MySQL client (if you have it installed) to do local development work.
$ mysql -h 127.0.0.1 -u root -P 3306 --prompt="memsql> "
–-prompt="memsql> " changes the prompt in the client program to say
memsql>. It is not required for using the MySQL client. Also, in this container, port 3306 corresponds to the master aggregator node and port 3307 corresponds to the leaf node. If you want to also access the leaf node through a SQL client interface, make sure to add
-p 3307:3307 to your
docker run command.
Upgrading the MemSQL Docker Image
To upgrade the
memsql/quickstart Docker image to the latest version, run
$ docker pull memsql/quickstart
If you have a running container, you can stop and restart it through the following commands. By restarting the container after upgrade you will lose any data stored in MemSQL, unless it is persisted outside the container as described earlier in this guide.
If you have any data that you’d like to save before upgrading, you can do a backup with
mysqldump or MemSQL backup functionality.
$ docker stop memsqlcontainer $ docker rm memsqlcontainer $ docker run -d -p 3306:3306 -p 9000:9000 --name memsqlcontainer [--volumes-from memsqldata] memsql/quickstart
The Docker image contains sample code in Python that demonstrates how MemSQL can handle high numbers of concurrent writes per second. To run it, use the following docker command:
$ docker exec -it memsqlcontainer simple-benchmark
After the benchmark starts, use the MemSQL Ops UI to observe the transactional throughput of this workload. You can access the UI by running
See Multi-Insert Examples for more concurrent benchmark examples.
Sample Query Workload
See How to Run Queries section for an example schema and queries to begin exploring the potential of MemSQL.
Removing the memsql/quickstart Image
When you are done testing with the
memsql/quickstart image, you can clean it up by running the following command:
$ docker stop memsqlcontainer && docker rm -v memsqlcontainer
and, if you have a data container called
memsqldata, you can remove it with the following command :
$ docker rm -v memsqldata