How To Add NoSQL Queries to MySQL with memcached on Ubuntu 14.04

Introduction

The general idea of using memcached and its standalone server implementation with MySQL has been described in many fine articles such as the one How To Install and Use Memcache on Ubuntu 14.04. However, memcached as a standalone server works as an intermediary in front of the MySQL client access layer and manages information only in the memory without an option to store it persistently. This makes it suitable for tasks such as caching the results of duplicate MySQL queries. This saves resources and optimizes the performance of busy sites.

However, in this article we’ll be discussing something different. Memcached will be installed as a MySQL plugin and tightly integrated into MySQL. It will provide a NoSQL style access layer for managing information directly in regular MySQL InnoDB tables. This has numerous benefits as we’ll see later in the article.

Basic Understanding

To be able to follow this article you will need some basic understanding of what NoSQL and memcached are. Put simply, NoSQL works with information in the form of key-value(s) items. This obviously simpler approach than the standard SQL suggests better performance and scalability, which are especially sought after for working with large amounts of information (Big Data).

However, NoSQL’s good performance is not enough to replace the usual SQL. The simplicity of NoSQL makes it unsuitable for structured data with complex relations in it. Thus, NoSQL is not a replacement of SQL but rather an important addition to it.

As to memcached, it can be regarded as a popular implementation of NoSQL. It’s very fast and has excellent caching mechanisms as its name suggests. That’s why it makes a great choice for bringing NoSQL style to the traditional MySQL.

Some understanding of the memcached protocol is also needed. Memcached works with items which have the following parts:

• A key — Alphanumerical value which will be the key for accessing the value of the item.
• A value — Arbitrary data where the essential payload is kept.
• A flag — Usually a value used for setting up additional parameters related to the main value. For example, it could be a flag whether or not to use compression.
• An expiration time — Expiration time in seconds. Recall that memcached was initially designed with caching in mind.
• A CAS value — Unique identifier of each item.

Prerequisites

This guide has been tested on Ubuntu 14.04. The described installation and configuration would be similar on other OS or OS versions, but the commands and location of configuration files may vary.

You will need the following:

• Ubuntu 14.04 fresh install
• Non-root user with sudo privileges

All the commands in this tutorial should be run as a non-root user. If root access is required for the command, it will be preceded by sudo. If you don’t already have that set up, follow this tutorial: Initial Server Setup with Ubuntu 14.04.

Step 1 — Installing MySQL 5.6

The memcached plugin in MySQL is available in versions of MySQL above 5.6.6. This means that you cannot use the MySQL package (version 5.5) from the standard Ubuntu 14.04 repository. Instead, you’ll have to:

1. Add the MySQL official repository
2. Install the MySQL server, client, and libraries from it

First, go to the MySQL apt repository page and download the package that will add the MySQL repository to your Ubuntu 14.04 system. You can download the package directly on your Droplet:

wget https://dev.mysql.com/get/mysql-apt-config_0.3.5-1ubuntu14.04_all.deb

Next, install it with dpkg:

sudo dpkg -i mysql-apt-config_0.3.5-1ubuntu14.04_all.deb

When you run the above command, a text mode wizard appears with two questions in it:

• Which MySQL product do you wish to configure? Answer with Server.
• Which server version do you wish to receive? Answer with mysql-5.6.

Once you answer these two questions you’ll return to the first question about which product you wish to install. Answer with Apply, the bottom choice, to confirm your choices and exit the wizard.

Now that you have the new MySQL repo, you’ll have to update the apt cache, i.e. the information about the available packages for installation in Ubuntu. Thus, when you opt to install MySQL it will be retrieved from the new repository. To update the apt cache, run the command:

sudo apt-get update

After that you are ready to install MySQL 5.6 on Ubuntu 14.04 with the command:

sudo apt-get install mysql-server

Once you run the above command you’ll be asked to pick a MySQL root (administrator) password. For convenience, you may choose not to set a password at this point and when prompted just press ENTER. However, once you decide to turn this server in production, it’s recommended that you run the command sudo mysql_secure_installation to secure your MySQL installation and configure a root password.

When the installation process completes you will have MySQL server 5.6 installed along with its command line client and necessary libraries. You can verify it by starting the client with the command:

mysql -u root

If you set a password, you will need to use the following command and enter your MySQL root password when prompted:

mysql -u root -p

You should see:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.25 MySQL Community Server (GPL)
...

While still in the MySQL monitor (client terminal), create a new database called test:

CREATE DATABASE test;

We’ll need this database later for our testing.

To exit the MySQL client type:

quit

Finally, as a dependency for the memcached plugin, you will also need to install the development package for the asynchronous event notification library — libevent-dev. To make this happen run the command:

sudo apt-get install libevent-dev

Step 2 — Installing the memcached Plugin in MySQL

To prepare for the memcached plugin installation you first have to execute the queries found in the file /usr/share/mysql/innodb_memcached_config.sql. Start the MySQL client:

mysql -u root

or, if you set a password:

mysql -u root -p

and execute:

source /usr/share/mysql/innodb_memcached_config.sql;

This will create all the necessary settings for the plugin in the database innodb_memcache and also insert some example data in our newly created database test.

After that you can perform the installation of the memcached plugin from the MySQL terminal with the following command:

install plugin daemon_memcached soname "libmemcached.so";

Exit the MySQL session:

quit

This installs the memcached plugin which is found in the directory /usr/lib/mysql/plugin/ in Ubuntu 14.04. This file is available only in MySQL version 5.6 and up.

Once the installation is complete, you have to configure the memcached plugin listener. You will need it to connect to the memcached plugin. For this purpose, open the file /etc/mysql/my.cnf with your favorite editor like this:

sudo vim /etc/mysql/my.cnf

Somewhere after the [mysqld] line add a new line containing:

daemon_memcached_option="-p11222 -l 127.0.0.1"

The above configures the memcached plugin listener on port 11222 enabled only for the loopback IP 127.0.0.1. This means that only clients from the Droplet will be able to connect. If you omit the part about the IP (-l 127.0.0.1), the new listener will be freely accessible from everywhere, which is a serious security risk. If you are further concerned about the security of the memcached plugin please check its security documentation.

To start the new listener process for the memcached plugin, restart the MySQL server with the command:

sudo service mysql restart

Step 3 — Testing the memcached Plugin

To verify the installation is successful run the following MySQL command from the MySQL client (start the client with mysql -u root or mysql -u root -p):

show plugins;

If everything is fine, you should see in the output:

| daemon_memcached           | ACTIVE  | DAEMON             | libmemcached.so | GPL     |

If you don’t see this, make sure that you are using MySQL version 5.6 or up and that you have followed the installation instructions precisely.

You can also try to connect to the new memcached plugin interface with Telnet from your Droplet like this:

telnet localhost 11222

Upon success you should see output such as:

Connected to localhost.
Escape character is '^]'.

Now you can run a generic command such as stats, for statistics, to see how this connection works. To exit the prompt press simultaneously the combination of CTRL and ] on your keyboard. After that type quit to exit the Telnet client itself.

Telnet gives you simplest way to connect to the memcached plugin and to the MySQL data itself. It is good for testing, but when you decide to use it professionally you should use the readily available libraries for popular programming languages like PHP and Python.

Step 4 — Running NoSQL Queries in MySQL via memcached Plugin

If you go back to the installation part of the memcached plugin in this article, you will see that we executed the statements from the file /usr/share/mysql/innodb_memcached_config.sql. These statements created a new table demo_test in the test database. The demo_test table has the following columns in compliance with the memcached protocol:

• c1 implements the key field.
• c2 implements the value field.
• c3 implements the flag field.
• c4 implements the CAS field.
• c5 implements the expiration field.

The table demo_test will be the one we’ll be testing with. First, let’s open the database/table with the MySQL client with the following command:

mysql -u root test

Or, if you have a MySQL password set:

mysql -u root test -p

There should be already one row in the demo_test table:

SELECT * FROM demo_test;

The results should look like:

+-------------+--------------+------+------+------+
| c1          | c2           | c3   | c4   | c5   |
+-------------+--------------+------+------+------+
| AA          | HELLO, HELLO |    8 |    0 |    0 |
+-------------+--------------+------+------+------+
1 rows in set (0.00 sec)

Exit the MySQL session:

quit

Now, let’s create a second record using the memcached NoSQL interface and telnet. Connect again to localhost on TCP port 11222:

telnet localhost 11222

Then use the following syntax:

set [key] [flag] [expiration] [length in bytes]
[value]

Note that the value has to be on a new row. Also, for each record you have to specify the length in bytes for the value when working in the above manner.

As an example, let’s create a new item (database row) with key newkey, value 0 for flag, and value 0 for expiration (never to expire). The value will be 12 bytes in length.

set newkey 0 0 12
NewTestValue

Of course, you can also retrieve values via this NoSQL interface. This is done with the get command which is followed by the name of the key you want to retrieve. While still in the Telnet session, type:

get newkey

The result should be:

VALUE newkey 0 12
NewTestValue

The above set and get commands are valid for every memcached server. These were just a few simple examples how to insert and retrieve records in a NoSQL style.

Now let’s connect again to the MySQL client with the command mysql -u root test ormysql -u root test -p and see the content of the demo_test table again with run the qyery:

SELECT * FROM demo_test WHERE c1="newkey";

There you should see the newly created row like this:

+--------+--------------+------+------+------+
| c1     | c2           | c3   | c4   | c5   |
+--------+--------------+------+------+------+
| newkey | NewTestValue |    0 |    1 |    0 |
+--------+--------------+------+------+------+

By now you may wonder how the memcached plugin knows which database and table to connect to and how to map information to the table columns. The answer is in the database innodb_memcache and its table containers.

Execute this select statement:

select * from containers \G

You will see the following:

*************************** 1. row ***************************
                  name: aaa
             db_schema: test
              db_table: demo_test
           key_columns: c1
         value_columns: c2
                 flags: c3
            cas_column: c4
    expire_time_column: c5
unique_idx_name_on_key: PRIMARY
1 row in set (0.00 sec)

To learn more on how to create different mappings and find out advanced features of the memcached plugin please check out the memcached plugin internals page.

Benefits of Integrating MySQL with the memcached Plugin

The above information and examples outline a few important benefits of integrating MySQL with NoSQL through the memcached plugin:

• All your data (MySQL and NoSQL) can be kept in one place. You don’t have to install and maintain additional software for NoSQL data.
• Data persistence, recovery, and replication for NoSQL data is possible thanks to the powerful InnoDB storage engine.
• The incredibly fast memcached data access layer can be still used so that you can work with higher volumes of information compared to when working with the slower MySQL client.
• NoSQL data can be managed with MySQL interface and syntax. Thus you can include NoSQL data in more complex SQL queries such as left joins.

Conclusion

By the end of this article you should be acquainted with the new possibilities for working with NoSQL data provided by MySQL. This may not be an universal solution to replace dedicated NoSQL servers such as MongoDB, but it certainly has its merits.