In this April MySQL Lab release, we’ll provide you a more robust and release-ready InnoDB Memcached Engine with a few enhancements. The most notable addition is the SASL support, which gives users the capability to protect their MySQL database from unauthenticated access through memcached clients. In this blog, I will walk you through steps of getting this option enabled.

Background Info:
SASL stands for “Simple Authentication and Security Layer”, which is a Standard for adding authentication support to connection-based protocols. Memcached added SASL support starting its 1.4.3 release. And here is a good article that gives you some background on why and how SASL is supported in Memcached.

For InnoDB Memcached, the “Memcached mapped” user table must be registered in the “container” “system table”. And memcached client(s) can only access such “registered” table.  Even though the DBA can add access restrictions on such table,  he/she has no control over who can access it through the memcached client(s). And this is exactly the reason we want to provide a means (in this case SASL) for DBA being able to have some control over who can access our InnoDB table(s).

In the following section, we will go through with you the steps to build, enable and test an SASL-enabled InnoDB Memcached plugin.

Steps to Build and Enable SASL in InnoDB Memcached Plugin:

By default, SASL-enabled InnoDB Memcached is not built-in (and included in the release package), since it relies on some SASL libraries to build SASL into Memcached Engine. So you will need to download the source and rebuild the InnoDB Memcached plugin after you download the SASL libraries. The detail is described in following sections:

1) First, you would need to get SASL development and utility libraries. For example, on Ubuntu, you can get these libraries through:

> sudo apt-get -f install libsasl2-2 sasl2-bin libsasl2-2 libsasl2-dev libsasl2-modules

2) Then build InnoDB Memcached Engine plugin (shared libraries) with SASL capability. This is done by adding ENABLE_MEMCACHED_SASL=1 to the cmake option. In addition, Memcached provides a simple plaintext passwords support, which is easier to use for testing, so we have support for that too. And this is enabled by setting the option ENABLE_MEMCACHED_SASL_PWDB=1.

So overall, we will need to add following three options to the cmake:

> cmake ... -DWITH_INNODB_MEMCACHED=1
  -DENABLE_MEMCACHED_SASL=1 -DENABLE_MEMCACHED_SASL_PWDB=1

3) The third step is to install the InnoDB Memcached Plugin as before. Please refer to my earlier blog posts on the procedures.

4) As mentioned in section 2), Memcached provides a simple plaintext password support through SASL, which will be used for this demo.  There was a good blog from Thond Norbye describes the steps, so you can follow the instruction there too. I will repeat the important steps here.

a) Create a user named “testname” and its password as “testpasswd” in a file:

> echo "testname:testpasswd:::::::" >/home/jy/memcached-sasl-db

b) Let memcached know about it by setting environment variable MEMCACHED_SASL_PWDB:

> export MEMCACHED_SASL_PWDB=/home/jy/memcached-sasl-db

c) Also tell memcached that it is a plaintext password:

> echo "mech_list: plain" > /home/jy/work2/msasl/clients/memcached.conf
> export SASL_CONF_PATH=/home/jy/work2/msasl/clients/memcached.conf

4) Then we are ready to reboot the server, and add a “daemon_memcached” option “-S”, to enable SASL:

> mysqld .. --daemon_memcached_option="-S"

5) Now we are done the setup. Let’s test it. To do so, you might need SASL-enabled client. I used a SASL-enabled libmemcached as described in Thond Norbye’s blog, and tested it accordingly:

> memcp --servers=localhost:11211 --binary  --username=testname --password=testpasswd myfile.txt
> memcat --servers=localhost:11211 --binary --username=testname --password=testpasswd myfile.txt

Without appropriate user name or password, above operation will be rejected by error message “memcache error AUTHENTICATION FAILURE”. Otherwise, the operation will be completed. You can also play with the plaintext password set in /home/jy/memcached-sasl-db to verify it.

There are other methods to test the SASL with memcahced. But the one described above is the most straightforward.

Other changes for InnoDB Memcached:

Besides the SASL support, there are a few changes in this release that worth mentioning:
1) We added a configuration option innodb_api_trx_level, so that user can control the transaction isolation level on the queries through InnoDB APIs, or in this case, the memcached.

In theory, for memcached, there is no such concept of “transactions”, so this is an extra property that we added on top of it, so that user has some level of control when issuing DMLs through the SQL interface. By default, it is set to “read uncommitted”.

2) Another option we added is innodb_api_enable_mdl, the “mdl” stands for “metadata locking”. This basically “locks” the table from the MySQL level, so that the mapped table cannot be dropped or altered by DDL through the SQL interfaces. Without the lock, the table can be dropped from MySQL layer, but will be kept in the InnoDB storage until memcached or any other user stops using it.

3) A configure option name change. To enable binlog, the configure variable name has changed from innodb_direct_access_enable_binlog to innodb_api_enable_binlog.

Summary:

In summary, this release provides you a more robust InnoDB Memcached Engine with SASL support. The steps to enable such support is fairly straightforward and almost identical to those you would do to enable SASL for a Memcached server. So if you are familiar with using SASL for memcached, then it would just some name flipping to build and enable it. And if you are not familiar with the operation, above steps also give you a quick start to use it.