Saturday, April 27, 2013


Now that MySQL 5.6 has been out for a little while and Percona 5.6 is, at the time of writing this, in alpha, we will all start to look more into the NoSQL solutions related to MySQL. ( I will continue to watch and see what MariaDB does in this regard. ) But anyway... since developers and management continue to be curious about what NoSQL can offer....

This is just to give a high level overview of using Memcache as a NoSQL solution and keep the  MySQL & InnoDB database and data in tact.

While this blog is not the first on this topic, I hope it ties a few things together for some people.

Good reads and references are here:

# php --version

PHP 5.4.14 (cli) (built: Apr 27 2013 14:22:04)
Copyright (c) 1997-2013 The PHP Group
Zend Engine v2.4.0, Copyright (c) 1998-2013 Zend Technologies

Otherwise my php install also has this.... 

more /etc/php.d/memcache.ini

; ----- Options to use the memcache session handler
;  Use memcache as a session handler
;  Defines a comma separated of server urls to use for session storage

First activate the plugin...

mysql> install plugin daemon_memcached soname "";

Create a database so we do not just use the standard test db and demo_test used in all other examples. Just to show it is not locked to just that.

CREATE DATABASE nosql_mysql_innodb_memcache;
use nosql_mysql_innodb_memcache;

CREATE TABLE nosql_mysql (
   `demo_key` VARCHAR(32),
   `demo_value` VARCHAR(1024),
   `demo_flag` INT,
   `demo_cas` BIGINT UNSIGNED,
   `demo_expire` INT,
   primary key(demo_key)

use innodb_memcache;

INSERT INTO containers VALUES ('DEMO','nosql_mysql_innodb_memcache','nosql_mysql','demo_key','demo_value','demo_flag','demo_cas','demo_expire','PRIMARY');

select * from containers\G
*************************** 1. row ***************************
                  name: DEMO
             db_schema: nosql_mysql_innodb_memcache
              db_table: nosql_mysql
           key_columns: demo_key
         value_columns: demo_value
                 flags: demo_flag
            cas_column: demo_cas
    expire_time_column: demo_expire
unique_idx_name_on_key: PRIMARY

OK now lets just have some data in this table to prove that it runs like normal MySQL. 

mysql>use nosql_mysql_innodb_memcache;
mysql> insert into nosql_mysql VALUES ('key1','demo data','1',1,1);
Query OK, 1 row affected (0.04 sec)

select * from nosql_mysql  \G
*************************** 1. row ***************************
   demo_key: key1
 demo_value: demo data
  demo_flag: 1
   demo_cas: 1
demo_expire: 1
1 row in set (0.00 sec)

So let us see what we can do with PHP now... 

$memcache->connect('localhost', 11211) or die ("Could not connect");

$version = $memcache->getVersion();
echo "Server's version: ".$version."<br/>\n";

$memcache->set('key3', 'FROM PHP') or die ("Failed to save data at the server");
echo "Data from the cache:<br/>\n";
echo $memcache->get('key3');

Server's version: 5.6.10
Data from the cache:

Just for grins we can do the typical example we see and use telnet as well.

telnet 11211
Connected to
Escape character is '^]'.
set key2 1 0 11
Hello World
get key2
VALUE key2 1 11
Hello World
get key3
VALUE key3 0 8
OK let us see what we have in the MySQL database

set session TRANSACTION ISOLATION LEVEL read uncommitted;
SELECT @@GLOBAL.tx_isolation, @@tx_isolation \G
*************************** 1. row ***************************
@@tx_isolation: READ-UNCOMMITTED

> select * from nosql_mysql\G
*************************** 1. row ***************************
   demo_key: key1
 demo_value: demo data
  demo_flag: 1
   demo_cas: 1
demo_expire: 1
*************************** 2. row ***************************
   demo_key: key2
 demo_value: Hello World
  demo_flag: 1
   demo_cas: 2
demo_expire: 0
*************************** 3. row ***************************
   demo_key: key3
 demo_value: FROM PHP
  demo_flag: 0
   demo_cas: 2
demo_expire: 0

So with PHP and using MySQL InnoDB memcache we can see that we are able to address code directly and easily. 

Of course others have their own opinions ( but I think this is just the start of how we will be able to use NoSQL with MySQL.