Persistent Key-Value store with Mysql Memcached plugin

Memcached server provides a very fast key-value store where data is stored in memory. This can serve as a caching mechanism for web pages and database objects for fast retrieval by the application layer so that responses can be sent quickly. But the only problem with Memcached is, it is a volatile storage engine as it stores the data in memory. In the event of a system restart or Memcached crash, all data in it's storage is lost. This makes Memcached unsuitable for usage beyond just a caching engine like for storing sessions or document data. Mysql Memcached plugin can be an answer to this problem of volatile storage in Memcached.

Mysql Memcached plugin utilizes Memcached protocol for interacting with application which makes it possible for applications to take advantage of this Mysql plugin without any changes to code incase they were using Memcached server earlier. Mysql implements this plugin on top of it's InnoDB storage engine which provides the persistent layer to this otherwise volatile storage engine. Mysql Memcached plugin runs of the same port as Memcached and accepts all Memcached commands. When you store data through this plugin, Mysql stores it through it's InnoDB engine. So incase your server restarts or Memcached crashes then data is not lost and is available back to the application when the plugin is restarted.

The only downside of using this plugin is, it is not as fast as Memcached which uses memory for storage since this plugin has to deal with disk IO on a periodic basis to persist and retrieve the data. Memcached whereas has extremely fast access to data stored in memory.

Now let's see how to install this plugin on Mysql.

IMPORTANT: Donot install this plugin on your main Mysql server. Only install this plugin on a separate dedicated Mysql server.

Run the below command to prepare the Memcached plugin.

mysql -u root -p < /usr/share/mysql/innodb_memcached_config.sql

Now login to Mysql and execute the below statements. This will create a persistent cache store for your application. Replace the placeholders enclosed in <> with your values.

INSTALL PLUGIN daemon_memcached soname "libmemcached.so";

CREATE DATABASE <name of database>;
USE <name of database>;
CREATE TABLE cache (item_key VARCHAR(1024) NOT NULL, item_data MEDIUMBLOB, flags INT, cas BIGINT UNSIGNED, expiry INT,PRIMARY KEY(item_key));

CREATE USER <database user> IDENTIFIED BY '<password>';
GRANT SELECT ON <name of database>.* TO <database user>;

USE innodb_memcache;
DELETE FROM containers;
INSERT INTO containers (name, db_schema, db_table, key_columns, value_columns, flags, cas_column, expire_time_column, unique_idx_name_on_key) VALUES ('default', '<name of database>', 'cache', 'item_key', 'item_data', 'flags', 'cas', 'expiry', 'PRIMARY');
COMMIT;

UNINSTALL PLUGIN daemon_memcached;
INSTALL PLUGIN daemon_memcached soname "libmemcached.so";

You now have Memcached plugin started and is ready to accept Memcached connections on TCP port 11211. Enjoy your new persistent Memcached store.

You can read more on this on the Mysql website - http://dev.mysql.com/doc/refman/5.6/en/innodb-memcached-setup.html