As Calvin mentioned in “NoSQLto InnoDB with Memcached“, we just released a “technologypreview” of the feature that makes memcached a MySQL Daemon Plugin.And this “technology preview” release demonstrates how user can goaround SQL Optimizer and Query Processing and directly interactwith InnoDB Storage Engine through InnoDB APIs. Here, we would liketo walk with you step by step to see how to get the memcachedDaemon Plugin set up and get it running.</p>


If you would just like to get a brief introduction on thesetup steps, there is a “README-innodb_memcached” in themysql-5.6.2-labs-innodb-memcached package. This is a moreelaborated description on these steps.


1) Prerequisite:服务需求

Currently, the Memcached Daemon Plugin prototype is onlysupported on Linux platform. And as a prerequisite, you must havelibevent installed, since it is required by memcached.


If you have the source code release, then there is a libevent1.4.3 included in the package (plugin/innodb_memcached/libevent).You can go to the directory and do autoconf, ./configure, make andmake install to make the libevent installed.

如果需要你可以去plugin/innodb_memcached/libevent来安装libevent1.4.3,只需配置./configure 然后make &makeinstall

2) Build the server编译服务

Assuming you would like to build the server yourself(otherwise, you can just skip this section), once libevent isinstalled, you can just build the MySQL server as usual.


Our source code is in the “plugin/innodb_memcached”directory. As part of server build, it will generate two sharedlibraries:

1) this is the memcached daemon plugin toMySQL

2) this is an InnoDB API plugin tomemcached


1) 这个是mysql内的memcached插件

2) 这个是innodb调用memcached 的api插件

Make sure above two shared libraries are put in the MySQLplugin directory. You can find MySQL plugin directory by doing“select @@plugin_dir”:


mysql> select @@plugin_dir;
|@@plugin_dir |
| /home/jy/work2/mysql-5.6-memcached-labs/lib/plugin |
1 row in set (0.00 sec)

3) Install configuration tables:安装创建表

Next, the memcached plugin needs a bit configuration to knowhow to interact with InnoDB table. We have a configuration scriptin “scripts/innodb_memcached_config.sql”. You can just install thenecessary configure tables by running “mysql< scripts/innodb_memcached_config.sql”. If you donot like to know the detail of these configuration tables, you canjust skip this section.


This configure script installs 3 tables needed by the InnoDBMemcached. These tables are created in a dedicated database“innodb_memcache”. We will go over these three tablesin a bit more detail:


1) “containers” – This table is the most important table for“Memcached – InnoDB mapping”. It describes the table used to storethe memcached values. Currently, we can only map memcached to onetable at a time. So essentially, there will be only one row in thetable. In the future, we would consider making this configurationmore flexible and dynamic, or user can map memcached operations tomultiple tables.

1)container 数据存储表,这个表用于memcached -innodb映射,这里将保存所有memcached的映射相关值,目前这个表只有一个,且数据只有一行。未来我们会考虑将这个配置的更灵活,比如用户可以根据自定规则将值存储到多个指定表。

The mapping is done through specifying corresponding columnvalues in the table:

  • “db_schema” and “db_table” columns describe the database andtable name for storing the memcached value.
  • “key_columns” describes the column (single column) name forthe column being used as “key” for the memcached operation
  • “value_columns” describes the columns (can be multiple) usedas “values” for the memcached operation. User can specify multiplecolumns by separating them by comma (such as “col1, col2″etc.)
  • “unique_idx_name_on_key” is the name of the index on the“key” column. It must be a unique index. It can be primary orsecondary.


  • db_schema anddb_table行用来说明存储memcached的值,table是指保存数据的表,而schema是存储数据库名
  • key_column 表内哪列用于key标识
  • value_columns 表内哪列用于保存key对应的值
  • unique_idx_name_on_key这个是key_column指定的数据表内的key的索引名,必须是唯一索引,当然可以使用主键或二级索引
  • </ul>

    Above 5 column values (table name, key column, value columnand index) must be supplied. Otherwise, the setup willfail.

    Following are optional values, however, to fully comply withmemcached protocol, you will need these column values suppliedtoo.

    • “flags” describes the columns used as “flag” for memcached. Italso used as “column specifier” for some operations (such as incr,prepend) if memcached “value” is mapped to multiple columns. So theoperation would be done on specified column. For example, if youhave mapped value to 3 columns, and only want the “increment”operation performed on one of these columns, you can use flags tospecify which column will be used for these operations.
    • “cas_column” and “exp_column” are used specifically to storethe “cas” and “exp” value of memcached.


    • flag表示类似memcached的flag,他的用途用来表示某“列”比方说当memcached映射的值包含多列。比如我们有三列,我们只想incr操作其中的一列,你可以通过flags指定哪行进行此操作
    • case_column以及exp_column是用来保存cas以及exp设置(这俩你知道的……查下cas以及超时)

      </l I></ul>

      2. Table “cache_policies” specifies whether we’ll use InnoDBas the data store of memcached (innodb_only) or use memcached’s“default engine” as the backstore (cache-only) or both (caching).In the last case, only if the default engine operation fails, theoperation will be forwarded to InnoDB (for example, we cannot finda key in the memory, then it will search InnoDB).


      3) Table “config_options”, currently, we only support oneconfig option through this table. It is the “separator” used toseparate values of a long string into smaller values for multiplecolumns values. For example, if you defined “col1, col2″ as valuecolumns. And you define “|” as separate, you could issue followingcommand in memcached to insert values into col1 and col2respectively:

      set keyx 10 0 19


      So “valuecol1x” will send to col1 and valuecoly will send tocol2.


      set keyx 10 0 19



      4) Example tables

      Finally, as part of the configuration script, we created a“demo_test” in the “test” database as an example. It also allowsthe Daemon Memcached to work out of box, and no need to for anyadditional configurations.

      As you would notice, this “demo_test” table has more columnsthan needed, so it would need the entries in the “container” tableto tell which column is used for what purpose as describedabove.


      最后,在我们的配置脚本内,他会自动建立一个 demo_test在test数据库内。



      4) Install the Daemon Plugin(安装守护插件)

      The final step would be installing the daemon plugin. It isthe same as installing any other MySQL plugin:


      mysql> install plugin daemon_memcached soname“”;

      If you have any memcached specific configure parameters,although it takes effect when the plugin is installed, you wouldneed to specify it during MySQL server boot timeor enter them in the MySQL configure files.


      For example, if you would like the memcached to listen onport “11222″ instead of the default port “11211″, then you wouldneed to add “-p11222″ to MySQL system configure variable“daemon_memcached_option”:


      mysqld …. –loose-daemon_memcached_option=”-p11222″.

      Of course, you can add other memcached command line optionsto “daemon_memcached_option” string.

      The other configure options are:

      1) daemon_memcached_engine_lib_name (default“”)指定默认存储引擎so文件

      2) daemon_memcached_engine_lib_path (default NULL, the plugindirectory).指定memcached插件lib地址


      By default, you do not need to set/change anything with thesetwo configure option. We have above two configure options becausethey allow you to load any other storage engine for memcached (suchas NDB memcached engine). This opens door for more interestingexploration.


      3) daemon_memcached_r_batch_size, batch commit size for readoperation (get operations. It specifies after how many ops we willdo a commit. By default, this is set as a very large number,1048576.


      4) daemon_memcached_w_batch_size, batch commit for any writeoperations (set, replace, append, prepend, incr, decr etc.) Bydefault, this is set as 32.


      Again, please note that you will have these configurationparameter in your MySQL configure file or MySQL boot command line.And they will take effect when you load the memcachedplugin.


      5) Start to play:

      Now you have everything set up, you can start toplay:


      telnet 11211


      set a11 10 0 9
      get a11
      VALUE a11 0 9

      You can access the InnoDB table (“demo_test”) through thestandard SQL interfaces. However, there are some catches:

      1) If you would like to take a look at what’s in the“demo_test” table, please remember we had batched the commits (32ops by default) by default. So you will need to do “readuncommitted” select to find the just inserted rows:


      mysql> set session TRANSACTION ISOLATIONLEVEL
      -> read uncommitted;
      Query OK, 0 rows affected (0.00 sec)

      mysql> select * from demo_test;
      | cx |cy |c1 |cz |c2 | ca |CB |c3 |cu |c4 |C5 |
      | NULL | NULL | a11 | NULL | 123456789 | NULL |NULL | 10 | NULL| 3 | NULL|
      1 row in set (0.00 sec)

      2) The InnoDB table would be IS (shared intention) or IX(exclusive intentional) locked for all operations in a transaction.So unless you change “daemon_memcached_r_batch_size” and“daemon_memcached_w_batch_size” to small number (like 1), the tableis most likely intentionally locked between each operations. So youcannot do any DDL on the table.



      you have everything setup. And you can directly interactwith InnoDB storage engine through Memcached interfaces. Inaddition, as you might notice while going through this extended“README”, we still have a lot interesting options open forexploration and enhancement. This is the beginningof opening InnoDB to the outside world, and NoSQLis a perfect place for it to play.