### 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]( https://www.digitalocean.com/community/tutorials/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](https://www.digitalocean.com/community/tutorials/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](https://dev.mysql.com/downloads/repo/apt/) 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:
“`command
wget https://dev.mysql.com/get/mysql-apt-config_0.3.5-1ubuntu14.04_all.deb
“`
Next, install it with `dpkg`:
“`command
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:
“`command
sudo apt-get update
“`
After that you are ready to install MySQL 5.6 on Ubuntu 14.04 with the command:
“`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:
“`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:
“`command
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`:
“`custom_prefix(mysql>)
CREATE DATABASE test;
“`
We’ll need this database later for our testing.
To exit the MySQL client type:
“`custom_prefix(mysql>)
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:
“`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:
“`command
mysql -u root
“`
or, if you set a password:
“`comand
mysql -u root -p
“`
and execute:
“`custom_prefix(mysql>)
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:
“`custom_prefix(mysql>)
install plugin daemon_memcached soname “libmemcached.so”;
“`
Exit the MySQL session:
“`custom_prefix(mysql>)
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:
“`command
sudo vim /etc/mysql/my.cnf
“`
Somewhere after the `[mysqld]` line add a new line containing:
“`
[label /etc/mysql/my.cnf]
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](http://dev.mysql.com/doc/refman/5.6/en/innodb-memcached-security.html).
To start the new listener process for the memcached plugin, restart the MySQL server with the command:
“`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`):
“`custom_prefix(mysql>)
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:
“`command
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:
“`command
mysql -u root test
“`
Or, if you have a MySQL password set:
“`command
mysql -u root test -p
“`
There should be already one row in the `demo_test` table:
“`custom_prefix(mysql>)
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:
“`custom_prefix(mysql>)
quit
“`
Now, let’s create a second record using the memcached NoSQL interface and telnet. Connect again to localhost on TCP port 11222:
“`command
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` or`mysql -u root test -p` and see the content of the `demo_test` table again with run the qyery:
“`custom_prefix(mysql>)
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:
“`custom_prefix(mysql>)
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](https://dev.mysql.com/doc/refman/5.6/en/innodb-memcached-internals.html).
## 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.
Source: DigitalOcean News