Using MySQL Memcached API: Installation, New Features, and Practical Examples

Memcached is a distributed, high‑speed key‑value cache that resides in memory and is commonly used to accelerate websites or databases. It is especially suitable for frequently accessed data, low‑security data, and small tables that are updated often, such as user status or inventory tables.

MySQL Memcached API

Starting with MySQL 5.6, the memcached functionality is provided as a plugin called the memcached API, allowing cache data to be stored as InnoDB tables on disk. This solves persistence issues, enables direct SQL access to cached data, and removes the need for a separate memcached installation.

MySQL 5.7 further optimizes the API, reportedly achieving up to 1 million QPS in read‑only scenarios. MySQL 8.0 adds two new features: batch retrieval of multiple keys and range queries on keys.

Demonstration Steps

1. Import metadata

(localhost:ytt)<mysql>\. /usr/share/mysql-8.0/innodb_memcached_config.sql
Query OK, 1 row affected (0.05 sec)

2. Install the plugin

(localhost:test)<mysql>INSTALL PLUGIN daemon_memcached soname "libmemcached.so";
Query OK, 0 rows affected (0.08 sec)

Verify installation:

(localhost:information_schema)<mysql>SELECT * FROM information_schema.plugins WHERE plugin_name='daemon_memcached'\G
*************************** 1. row ***************************
PLUGIN_NAME: daemon_memcached
PLUGIN_VERSION: 1.0
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: DAEMON
PLUGIN_TYPE_VERSION: 80020.0
PLUGIN_LIBRARY: libmemcached.so
PLUGIN_LIBRARY_VERSION: 1.10
PLUGIN_AUTHOR: Oracle Corporation
PLUGIN_DESCRIPTION: Memcached Daemon
PLUGIN_LICENSE: GPL
LOAD_OPTION: ON
1 row in set (0.00 sec)

3. Create a sample table

(localhost:ytt)<mysql>CREATE TABLE t1 (
  id SERIAL PRIMARY KEY,
  r1 INT,
  r2 INT,
  r3 VARCHAR(20),
  flags INT,
  cas BIGINT UNSIGNED,
  expiry INT);
Query OK, 0 rows affected (0.05 sec)

Insert example rows (shown in the original output) and then configure the memcached container to map the table:

(localhost:ytt)<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 ('default','ytt','t1','id','r1|r2|r3','flags','cas','expiry','primary');
Query OK, 1 row affected (0.01 sec)

4. Read data via the plugin

After restarting the plugin, MySQL 5.7 can only retrieve a single key per GET command, requiring repeated calls for multiple keys. Example using telnet:

telnet localhost 11211
get 1
VALUE 1 0 23
2|9|2040-01-20 07:29:47
END
... (subsequent single‑key gets) ...

Attempting a multi‑key GET 1 2 3 4 5 6 returns an error: "We temporarily don't support multiple get option."

In MySQL 8.0, the API supports batch retrieval:

telnet localhost 11222
get 1 2 3 4 5 6
VALUE 1 0 23
2|9|2040-01-20 07:29:47
VALUE 2 0 23
7|1|2037-12-25 22:43:52
... (other values) ...
END

Range queries are also possible, e.g., GET @<=6 to fetch keys ≤ 6, GET @>10 for keys > 10, or GET @>10@<20 for the interval (10, 20). MySQL 8.0 currently supports only a single range per request.

Summary

The article introduces the MySQL Memcached API, outlines its use cases, walks through installation and configuration steps, and demonstrates the new batch‑get and range‑get features introduced in MySQL 8.0, providing practical code snippets for readers.