Monday Oct 03, 2011

InnoDB Memcached with Binlog Capability

Note: this article was originally published on on Oct 3, 2011 by Jimmy Yang.

In our earlier Labs release, we presented a MySQL NoSQL solution through InnoDB Memcached Daemon Plugin (see earlier Calvin’s and my posts). That earlier release allows the Memcached Plugin directly interacting with InnoDB, completely bypassing MySQL optimizer and QP layers. It differs with another popular MySQL NoSQL solution, HandlerSocket, by skipping even the Handler API layer and directly access the InnoDB through InnoDB APIs. Thus, in theory, it would be simpler and more efficient.

However, there is one major functionality we did not implement in the first release: the replication (binlog) capability. And this important feature is being done in the latest labs release (mysql-5.6.4-labs-innodb-memcached), in which all memcached commands are now supported for binlogging.

In the following sections, we will quick walk you through the prototype and show how to use the binlog capability.

Set up the InnoDB Memcached:

Our earlier post (Get started with InnoDB Memcached Daemon plugin) details the step to set up the InnoDB Memcached. We will go very briefly on some important steps here:

  • Copy the plugins to the plugin directory

Find out your plugin directory:

        mysql> select @@plugin_dir;


       After you download and unzipped mysql-5.6.4-labs-innodb-memcached-linux2.6-i686.tar.gz,

in the lib/plugin directory, you will find two plugin share lib:



Copy them to your plugin directory:

       shell > cp mysql-5.6.4-labs-innodb-memcached-linux2.6-i686/lib/plugin/ /home/jy/work2/mysql-5.6-memcached-labs/lib/plugin

       shell > cp mysql-5.6.4-labs-innodb-memcached-linux2.6-i686/lib/plugin/ /home/jy/work2/mysql-5.6-memcached-labs/lib/plugin

  • Install the configuration tables:

In the scripts/ directory, find the configuration scripts: innodb_memcached_config.sql, and install it:

      mysql < scripts/nnodb_memcached_config.sql

  • Install the Daemon Plugin

Now install the daemon plugin:

       mysql> install plugin daemon_memcached soname ‘’;

      Query OK, 0 rows affected (0.05 sec)

Enable InnoDB Memcached binlog with “innodb_direct_access_enable_binlog”:

  • One new system configuration option – “innodb_direct_access_enable_binlog” is added to enable the InnoDB Memcached binlog. This configure option is a Server Boot-time option, so you must turn it on at server start up time. In addition, you must enable MySQL binlog with the –log-bin option. You can add these option to your server configure file my.cnf or just add these at command line.

mysqld …. –log-bin –innodb_direct_access_enable_binlog=1

  • Use mysqldump to create a master data snapshot, and sync it to slave.

master shell > mysqldump –all-databases –lock-all-tables > dbdump.db

       slave shell > mysql < dbdump.db

  • In Master, call “show master status” to obtain Master Binary Log Coordinates

       mysql> show master status;

File Position Binlog_Do_DB Binlog_Ignore_DB
0 114

1 row in set (0.00 sec)

  • In Slave, use “change master to” to set up slave with above coordinates:

         mysql> CHANGE MASTER TO




                MASTER_PORT = 13000,



  • Then start the slave:

mysql>start slave;

If the errolog prints following info, then the slave is ready for the replication:

111002 18:47:15 [Note] Slave I/O thread: connected to master ‘root@localhost:13000′,replication started in log ’0.000001′ at position 114

Test with memcached telnet interfaces:

Now let’s test the server with above replication setup. We use the memcached telnet interface for demonstration purpose. And we also connect to the master and slave server through SQL interfaces, and query the target table to verify the result.

In our configuration setup sql, one example table “demo_test” is created in the “test” database to take the memcached command by default. And we will use this default table for the demonstrations:

  • Use set to insert a record, key “test1”, value “t1” and flag is “10”:

       telnet 11211


       Connected to

       Escape character is ‘^]’.

       set test1 10 0 2



         In the master server, you can see the row is inserted (c1 maps to the key, c2 maps to value, c3 is the flag, and c4 is the cas, c5 is the expiration):

         mysql> select * from demo_test;

c1 c2 c3 c4 c5
test1 t1 10 2 0

        1 row in set (0.00 sec)

         In the slave server, you will see the same record is inserted by replication:

       mysql> select * from demo_test;

c1 c2 c3 c4 c5
test1 t1 10 2 0

      1 row in set (0.00 sec)

  • Use set command to update the key “test1” to a new value “new”:

Connected to

         Escape character is ‘^]’.

         set test1 10 0 3



From the slave server, the update is replicated (notice the cas value also updated):

         mysql> select * from demo_test;

c1 c2 c3 c4 c5
test1 new 10 3 0

  1 row in set (0.00 sec)

  • Delete the record with delete command:

Connected to

         Escape character is ‘^]’.

         delete test1


The delete is replicated to the slave, the record in slave is also deleted:

  mysql> select * from demo_test;

         Empty set (0.00 sec)

  • Truncated the table with the “flush all” command

First insert two records:

Connected to

         Escape character is ‘^]’

         set test2 10 0 5



         set test3 10 0 6



In the slave server, there two records replicated:

mysql> select * from demo_test;

c1 c2 c3 c4 c5
test2 again 10 5 0
test3 again1 10 6 0

2 rows in set (0.00 sec)

Call “flush_all” in telnet interface to truncate the table:

         Connected to

         Escape character is ‘^]’.



Then check the slave server, the truncate table is replicated:

   mysql> select * from demo_test;

         Empty set (0.00 sec)

In short, all memcached commands are supported in terms of replication. And we would not enumerate each of them here.

Notes for the InnoDB Memcached Binlog:

Binlog Format:

  • Most Memcached operations are mapped to DMLs (insert/delete/update), and since there is no actually “statement” comes through memcached, all memcached commands (except for flush_all) use Row Based (RBR) logging. This is independent of any server binlog_format stetting.
  • The Memcached “flush_all” command is mapped to “truncate table” command. Since “DDL” type commands can only use statement-based logging, this “flush_all” command is replicated with a “hand-crafted” “truncate table” statement.


  • The transaction concept is not well defined when coming to Memcached. We use “daemon_memcached_r_batch_size” and “daemon_memcached_w_batch_size” to control the read and write “transaction” batch size for performance consideration. However, for replication, each command is replicated right after successful completion of each command.
  • In this release, we adjusted the default value of “daemon_memcached_w_batch_size” from 32 to 1, so each Memcached “write” operation are directly committed (it used to commit every 32 operations). This is to avoid any confusion that records shows up in slave server while they can’t be seen from master server without turning on “set transaction isolation level read uncommitted” to view uncommitted records. The side effect of this reduced is certain degradation of performance.

Other minor updates:

  • The memcached used has upgraded to the latest 1.6.0 (beta 1). And the libevent used for memcached has been updated to version 1.4.12.
  • InnoDB memcached now supports on Linux, Solaris and OSX platforms.


In this release, we added the binlog support for InnoDB Memcached Daemon Plugins. This is an important feature to allow any updates through the memcached interface being able to be replicated for backup and high availability. More importantly, this demonstrate the capability of InnoDB APIs,  which not only support direct access to the InnoDB storage, but also being able to backup  any of such operations.


This is the InnoDB team blog.


« October 2011 »