How To Add NoSQL Queries to MySQL with memcached on Ubuntu 14.04
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.
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.
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:
- Add the MySQL official repository
- 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
- 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
- Which server version do you wish to receive? Answer with
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
- CREATE DATABASE test;
We’ll need this database later for our testing.
To exit the MySQL client type:
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
- 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
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:
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:
c1implements the key field.
c2implements the value field.
c3implements the flag field.
c4implements the CAS field.
c5implements the expiration field.
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
- 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:
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
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:
The result should be:
VALUE newkey 0 12 NewTestValue
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 or
mysql -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
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.
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.