Many of you have already heard about NoSQL databases and one of the the most used tool is Memcached, where you add a cache layer between the application and database. Since MySQL version 5.6, a new plugin is available to do the integration between MySQL and Memcached. On this article, we will learn how to install it on linux, and some basic configurations of it.

Pre-requirements:
Install libevent

Installation:
To install memcached support we will need to create a few tables responsible for MySQL and memcached integration. MySQL already includes the file which creates those tables (innodb_memcached_config.sql), you can find this file in a sub folder of your basedir. To discover where is your basedir, run the bellow command:

mysql> SHOW VARIABLES LIKE 'basedir';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| basedir       | /usr  |
+---------------+-------+
1 row in set (0.00 sec)

If you have installed MySQL via your distro repository, the path will be as follow:

$basedir/share/mysql/innodb_memcached_config.sql

In case you have used MySQL binaries, the path will be as follow:

$basedir/share/innodb_memcached_config.sql

Now we will run this .sql file. By default, the script creates a test table on test database, but on our tests we will use the memcached db:

mysql> CREATE DATABASE IF NOT EXISTS test;
Query OK, 1 row affected (0.00 sec)

mysql> source /usr/share/mysql/innodb_memcached_config.sql
Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.04 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.04 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.02 sec)

Database changed
Query OK, 0 rows affected (0.03 sec)

Query OK, 1 row affected (0.01 sec)

mysql>

Now let’s create our table to store memcached data:

mysql> CREATE DATABASE IF NOT EXISTS memcached;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE memcached.dados LIKE test.demo_test;
Query OK, 0 rows affected (0.02 sec)

mysql> UPDATE innodb_memcache.containers SET db_schema = 'memcached', db_table = 'dados' WHERE name = 'aaa' LIMIT 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> DROP DATABASE test;
Query OK, 1 row affected (0.07 sec)

Next step is to install memcached plugin on MySQL. To do it we will use INSTALL PLUGIN command:

mysql> INSTALL PLUGIN daemon_memcached soname "libmemcached.so";
Query OK, 0 rows affected (0.01 sec)

To verify if the plugin was successful installed, we can run the bellow command:

mysql> \! netstat -tunap | grep LIST | grep mysql
tcp        0      0 0.0.0.0:11211               0.0.0.0:*                   LISTEN      2032/mysqld         
tcp        0      0 :::11211                    :::*                        LISTEN      2032/mysqld         
tcp        0      0 :::3306                     :::*                        LISTEN      2032/mysqld         
mysql> 

Configuring and Using:

Now we will test memcached via some programming language, PHP in this case:

 [root@localhost memcache]# cat test1.php 
< ?php $m = new Memcached(); $m->addServer('localhost', 11211);

$m->set('key1', 'Testing memcached');
echo 'Value of key1 is:' . $m->get('key1') . "\n";
?>
[root@localhost memcache]# php test1.php 
Value of key1 is:Testing memcached
[root@localhost memcache]# 

Now, let’s see what is stored on MySQL?

mysql> SELECT * FROM memcached.dados;
+------+-------------------+------+------+------+
| c1   | c2                | c3   | c4   | c5   |
+------+-------------------+------+------+------+
| key1 | Testing memcached |    0 |   19 |    0 |
+------+-------------------+------+------+------+
1 row in set (0.00 sec)

mysql> 

What happens if we change an entry manually on MySQL?

mysql> UPDATE memcached.dados SET c2 = 'Entry modified  directly on MySQL';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0


[root@localhost memcache]# cat test2.php 
< ?php $m = new Memcached(); $m->addServer('localhost', 11211);

echo 'Value of key1 is:' . $m->get('key1') . "\n";
?>

[root@localhost memcache]# php test2.php 
Value of key1 is:Entry modified  directly on MySQL

[root@localhost memcache]# 

What about if we want to store the entries on a different MySQL table?
We just need to create a new table, add a new container and use the delimiter which is configured on config_options table from innodb_memcache database:

mysql> SELECT * FROM innodb_memcache.config_options WHERE name = 'table_map_delimiter';
+---------------------+-------+
| name                | value |
+---------------------+-------+
| table_map_delimiter | .     |
+---------------------+-------+
1 row in set (0.00 sec)

mysql> CREATE TABLE memcached.dados2 LIKE memcached.dados;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO innodb_memcache.containers (name, db_schema, db_table, key_columns, value_columns, flags, cas_column, expire_time_column, unique_idx_name_on_key) VALUES ('bbb', 'memcached', 'dados2', 'c1', 'c2', 'c3','c4','c5','PRIMARY');
Query OK, 1 row affected (0.01 sec)

We have created a new table named dados2 and added a new container to refer to that table, which we named as bbb, now we just need to use that as prefix on memcached:

 [root@localhost memcache]# cat test3.php 
< ?php $m = new Memcached(); $m->addServer('localhost', 11211);

$m->set('@@bbb.key1', 'Should be stored on dados2 table');
echo 'Value of bbb.key1 is:' . $m->get('@@bbb.key1') . "\n";
?>
[root@localhost memcache]# php test3.php 
Value of bbb.key1 is:Should be stored on dados2 table
[root@localhost memcache]# 


mysql> SELECT * FROM memcached.dados2;
+------+----------------------------------+------+------+------+
| c1   | c2                               | c3   | c4   | c5   |
+------+----------------------------------+------+------+------+
| key1 | Should be stored on dados2 table |    0 |   22 |    0 |
+------+----------------------------------+------+------+------+
1 row in set (0.00 sec)

We also can map the table to store values into separate fields on the table.
Verify that on the config_options table we already have a separator configured:

mysql> SELECT * FROM innodb_memcache.config_options WHERE name = 'separator';
+-----------+-------+
| name      | value |
+-----------+-------+
| separator | |     |
+-----------+-------+
1 row in set (0.00 sec)

This character we will use to store values into multiple columns. Let’s create a table and add it to a new container(we will specify the columns we want the values separated by comma ‘,’):

mysql> CREATE TABLE products (id varchar(128), name varchar(255), value varchar(15), c3 int, c4 bigint, c5 int, PRIMARY KEY(id));
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO innodb_memcache.containers (name, db_schema, db_table, key_columns, value_columns, flags, cas_column, expire_time_column, unique_idx_name_on_key) VALUES ('products', 'memcached', 'products', 'id', 'name,value', 'c3','c4','c5','PRIMARY');
Query OK, 1 row affected (0.00 sec)

Now, lets create an array of products and add them to memcached:

[root@localhost memcache]# cat test4.php 
< ?php $m = new Memcached(); $m->addServer('localhost', 11211);

$products = array(
array('1', 'TV', '1999,00'),
array('2', 'Hack', '399,00'),
array('3', 'Table', '599,00'),
array('4', 'Chair', '99,00')
);

foreach($products as $product)
{
	$key = '@@products.' . $product[0];
	$value = $product[1] . '|' . $product[2];
	$m->set($key, $value);
}

?>
[root@localhost memcache]# php test4.php 



mysql> SELECT * FROM memcached.products;
+----+----------+---------+------+------+------+
| id | nome     | valor   | c3   | c4   | c5   |
+----+----------+---------+------+------+------+
| 1  | TV       | 1999,00 |    0 |   23 |    0 |
| 2  | Hack     | 399,00  |    0 |   24 |    0 |
| 3  | Table    | 599,00  |    0 |   25 |    0 |
| 4  | Chair    | 99,00   |    0 |   26 |    0 |
+----+----------+---------+------+------+------+
4 rows in set (0.00 sec)

Server/Service restart:

Let’s see what happens if we have to restart MySQL service(Same apply to server reboot)?
Will the data remain available on memcached after it?

[root@localhost memcache]# service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
[root@localhost memcache]# cat test5.php 
< ?php $m = new Memcached(); $m->addServer('localhost', 11211);

echo 'Value of key1 is:' . $m->get('key1') . "\n";
?>

[root@localhost memcache]# php test5.php 
Value of key1 is:Entry modified  directly on MySQL

[root@localhost memcache]# 

That is it! Entries will remain available even after reboot’s/restart’s.

SELinux:

On environments where we have SELinux enabled, it can block memcached integration because MySQL is not allowed to listen on memcached port, where is a tip on how to allow it(I’m using CentOS as linux distro):

Search for entries containing mysqld and dinied keywords on /var/log/audit/audit.log , if you find any, type the bellow commands to create a new SELinux module to allow it:

type=AVC msg=audit(1421080542.433:31): avc:  denied  { name_bind } for  pid=1360 comm="mysqld" src=11211 scontext=unconfined_u:system_r:mysqld_t:s0 tcontext=system_u:object_r:memcache_port_t:s0 tclass=tcp_socket
type=SYSCALL msg=audit(1421080542.433:31): arch=c000003e syscall=49 success=no exit=-13 a0=2d a1=7f5fcc0409a0 a2=10 a3=7f5fe0ee275c items=0 ppid=1123 pid=1360 auid=0 uid=27 gid=27 euid=27 suid=27 fsuid=27 egid=27 sgid=27 fsgid=27 tty=pts0 ses=2 comm="mysqld" exe="/usr/sbin/mysqld" subj=unconfined_u:system_r:mysqld_t:s0 key=(null)


audit2why < /var/log/audit/audit.log

cd /root/
mkdir selinux-custom
cd selinux-custom
audit2allow -a -M mysql-memcache
semodule -i mysql-memcache.pp

Opcoes do memcached: In case you want to modify any memcached specific option, you can add the daemon_memcached_option to MySQL config file, for example, to change memcached port:

#no arquivo de configuração (normalmente my.cnf)
daemon_memcached_option="-p11222"

That is it, I hope you’ve learned how to install and configure MySQL and memcached plugin integration.