MySQL memcached plug-in under the InnoDB engine

Installation

In order to make the article more complete, we choose to install MySQL from the source code, we need to note that the earlier version has a memory leak, so it is recommended to install the latest stable version, as of this time, the latest stable version is 5.6.13, we use this as an example to illustrate that the process is simple, as long as the activation of with_ Innodb_memcached can:

? 123456789101112131415 shell> groupadd mysql shell> useradd-r-g MySQL MySQL shell> tar zxvf mysql-5.6.13.tar.gz shell> CD mysql-5.6.13 shell> cmake. -dwith_innodb_memcached=on shell> makeshell> make installshell> cd/usr/local/mysqlshell> chown-r MySQL. Shell> chgrp-r MySQL. shell> scripts/mysql_install_db--user=mysql shell> chown-r root. shell> chown-r mysql data shell> bin/mysqld_safe--user=mysql & shell> CP support-files/mysql.server/etc/in It.d/mysql.server

After MySQL installation is complete, we can see innodb_engine.so and libmemcached.so in the plugin directory:

? 123456 mysql> SELECT @ @plugin_dir; +------------------------------+ | @ @plugin_dir | +------------------------------+ | /usr/local/mysql/lib/plugin/| +------------------------------+

You will also need to import the table structure required by the memcached plug-in:

? 1 mysql> Source/usr/local/mysql/share/innodb_memcached_config.sql

When you're ready, you can activate the memcached plugin (which, of course, can be disabled if needed):

mysql> INSTALL PLUGIN daemon_memcached soname "libmemcached.so"; mysql> UNINSTALL PLUGIN daemon_memcached;

Note: If you want to restart the plugin, you can first uninstall, and then install.

Memcached plug-in related configuration information is as follows, the specific introduction can refer to the official documents:

? 1234567891011 mysql> SHOW VARIABLES like '%memcached% '; +----------------------------------+------------------+ | variable_name | Value | +----------------------------------+------------------+ | Daemon_memcached_enable_binlog | OFF | | Daemon_memcached_engine_lib_name | innodb_engine.so | |         Daemon_memcached_engine_lib_path | | |         daemon_memcached_option | | | Daemon_memcached_r_batch_size | 1 | | Daemon_memcached_w_batch_size | 1 | +----------------------------------+------------------+

Note: Daemon_memcached_r_batch_size and daemon_memcached_w_batch_size, these two options have a large impact on performance, the simple point is to control the frequency of transaction commits, MySQL default value is 1, That is, each time the submission, which is mainly from the security considerations, we can adjust according to their own circumstances.

Almost, now that the memcached port should be ready, you can try it:

? 1 shell> echo "Stats" | NC localhost 11211

In other words, MySQL is already compatible with the Memcached protocol and can use the memcached command directly.

Configuration

In the installation step, we imported a script named Innodb_memcached_config.sql, which created a library (Innodb_memcache) three tables (Cache_policies, config_options, Containers):

? 123456789 mysql> Use Innodb_memcache mysql> SHOW TABLES; +---------------------------+ | Tables_in_innodb_memcache | +---------------------------+ | cache_policies | | config_options | | Containers | +---------------------------+

Cache_policies defines the cache policy, which includes the following choices:

Innodb_only: Use only InnoDB as the data store.

Cache-only: Use only the traditional memcached engine as back-end storage.

Caching: Both are used, if not found in the memcached, query InnoDB.

Config_options defines the separator symbol:

Separator:memcached only recognizes single values and uses this delimiter (|) to concatenate the values of multiple fields.

Table_map_delimiter: Use this delimiter (.) To confirm tables and keys, such as: @ @table. Key.

If we want to access a table through the Memcached protocol, we need to configure it in containers first:

Mysql> SELECT * from Containers\g *************************** 1.      Row *************************** name:aaa db_schema:test db_table:demo_test key_columns:c1 VALUE_COLUMNS:C2 flags:c3 cas_column:c4 expire_time_column:c5 unique_idx_name_on_key:primary

As the Demo_test table on the test database is already available, the value of C2 is queried by C1, and the table structure is as follows:

Mysql> DESC test.demo_test; +-------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------------+------+-----+---------+-------+ | C1 | varchar (32) | NO |     PRI |    | | | C2 | varchar (1024) |   YES | |    NULL | | | C3 | Int (11) |   YES | |    NULL | | | C4 | bigint (unsigned) |   YES | |    NULL | | | c5 | Int (11) |   YES | |    NULL | | +-------+---------------------+------+-----+---------+-------+

There is a row of data by default:

Mysql> SELECT * from Test.demo_test; +----+--------------+------+------+------+ | C1 | C2 | C3 | C4 | c5 | +----+--------------+------+------+------+ | AA |  Hello, hello |  8 |  0 | 0 | +----+--------------+------+------+------+

Let's use the Memcached protocol to access the look:

shell> echo "Get @ @aaa. AA "| NC localhost 11211 VALUE @ @aaa. AA 8 Hello, hello END

We can also first set the default access to the table, and then the subsequent query will only write the key name can be:

Shell> (echo "Get @ @aaa"; echo "Get AA") | NC localhost 11211 VALUE @ @aaa 0 test/demo_test endvalue AA 8 Hello, hello END

Although all my examples are executed through the command line, it's easy to change the way you write PHP.

Limit

Memcached plugin is very simple to use, but not everything is perfect, for example: When we configure the table, Containers table fields, in addition to Key_columns and Value_columns, other fields, such as: Flags,cas_ Column,expire_time_column, etc. must also be set, but many times, we can not find the appropriate field in the original table, at this time only corresponding to the new three fields, the taste is disgusting.

In addition, the containers table also has the following limitations:

The type of the Key_columns field must be char or varchar with a maximum length of 250 characters.

The type of the Value_columns field must be char or varchar or blob, not limited in length.

The type of the Cas_column field must be bigint.

The type of the Expiration_time_column field must be int.

The type of the flags field must be int.

Note: These restrictions may change as the MySQL version is updated, subject to availability.

Actual combat

Let's check the learning results with a user login example:

First create a user table in the test database:

Use ' test ' to CREATE TABLE ' users ' (' ID ' INT () UNSIGNED NOT NULL auto_increment, ' username ' VARCHAR (all) not NULL, ' Password ' varchar (+) NOT null, ' email ' varchar (+) NOT NULL, ' flags ' INT (TEN) UNSIGNED DEFAULT ' 0 ', ' cas_column ' B Igint UNSIGNED Default ' 0 ', ' expire_time_column ' INT (Ten) UNSIGNED default ' 0 ', PRIMARY key (' id '), UNIQUE KEY ' u ' Sername ' (' username ')) Engine=innodb;

Then add a few lines of test data:

INSERT into ' users ' (' username ', ' password ', ' email ') VALUES (' foo ', ' ffffffffffffffffffffffffffffffff ', ' [email Protected], (' Bar ', ' bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb ', ' [email protected] ');

Then configure this table in containers:

INSERT into Innodb_memcache.containers (name, Db_schema, db_table, Key_columns, value_columns, flags, Cas_column, exp Ire_time_column, Unique_idx_name_on_key) VALUES (' Default ', ' Test ', ' users ', ' username ', ' password|email ', ' flags ', ' Cas_column ', ' expire_time_column ', ' username ');

Here we define multiple fields (password and email) as value_columns, and use a vertical bar as a delimiter, actually using spaces, commas and the like, in the Innodb_ The following definitions of delimiters are found in the source code of the Config.c file and are not covered in the documentation:

static const char* Sep = ";, |\n";

Finally use the Memcached protocol to access, here we change a pattern, perform a mget operation:

shell> echo "get foo bar" | NC localhost 11211 VALUE foo 0 ffffffffffffffffffffffffffffffff| [Email protected] VALUE Bar 0 bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb| [Email protected] END

Now that I've set up multiple fields when I define value_columns, it's natural to return data from multiple fields, and they are separated by the separator field in the Innodb_memcache.config_options table. By default is a vertical line, if your field content contains a vertical line, then the default value will conflict, you can update the definition of separator, such as changing to three vertical bars and so on, need to be reminded that the changes do not forget to restart the memcached plugin.

Note: Because the table is named default when configured, you do not have to pass the table name at the time of the request. If default is not present, the names are ordered in alphabetical order, and the first is the default.

The. Codego.net Code provides

MySQL memcached plug-in under the InnoDB engine