MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0
This section describes how to set up the
daemon_memcached
plugin on a MySQL server.
Because the memcached daemon is tightly
integrated with the MySQL server to avoid network traffic and
minimize latency, you perform this process on each MySQL instance
that uses this feature.
Before setting up the daemon_memcached
plugin, consult Section 15.20.5, “Security Considerations for the InnoDB memcached Plugin” to
understand the security procedures required to prevent
unauthorized access.
The daemon_memcached
plugin is only
supported on Linux, Solaris, and macOS platforms. Other
operating systems are not supported.
When building MySQL from source, you must build with
-DWITH_INNODB_MEMCACHED=ON
.
This build option generates two shared libraries in the
MySQL plugin directory
(plugin_dir
) that are
required to run the daemon_memcached
plugin:
libmemcached.so
: the
memcached daemon plugin to MySQL.
innodb_engine.so
: an
InnoDB
API plugin to
memcached.
libevent
must be installed.
If you did not build MySQL from source, the
libevent
library is not included in
your installation. Use the installation method for your
operating system to install libevent
1.4.12 or later. For example, depending on the operating
system, you might use apt-get
,
yum
, or port
install
. For example, on Ubuntu Linux, use:
sudo apt-get install libevent-dev
If you installed MySQL from a source code release,
libevent
1.4.12 is bundled with the
package and is located at the top level of the MySQL
source code directory. If you use the bundled version of
libevent
, no action is required. If
you want to use a local system version of
libevent
, you must build MySQL with
the -DWITH_LIBEVENT
build
option set to system
or
yes
.
Configure the daemon_memcached
plugin so
it can interact with InnoDB
tables by
running the innodb_memcached_config.sql
configuration script, which is located in
.
This script installs the MYSQL_HOME
/shareinnodb_memcache
database with three required tables
(cache_policies
,
config_options
, and
containers
). It also installs the
demo_test
sample table in the
test
database.
mysql> source MYSQL_HOME
/share/innodb_memcached_config.sql
Running the innodb_memcached_config.sql
script is a one-time operation. The tables remain in place
if you later uninstall and re-install the
daemon_memcached
plugin.
mysql>USE innodb_memcache;
mysql>SHOW TABLES;
+---------------------------+ | Tables_in_innodb_memcache | +---------------------------+ | cache_policies | | config_options | | containers | +---------------------------+ mysql>USE test;
mysql>SHOW TABLES;
+----------------+ | Tables_in_test | +----------------+ | demo_test | +----------------+
Of these tables, the
innodb_memcache.containers
table is the
most important. Entries in the containers
table provide a mapping to InnoDB
table
columns. Each InnoDB
table used with the
daemon_memcached
plugin requires an entry
in the containers
table.
The innodb_memcached_config.sql
script
inserts a single entry in the containers
table that provides a mapping for the
demo_test
table. It also inserts a single
row of data into the demo_test
table.
This data allows you to immediately verify the installation
after the setup is completed.
mysql>SELECT * FROM innodb_memcache.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 mysql>SELECT * FROM test.demo_test;
+----+------------------+------+------+------+ | c1 | c2 | c3 | c4 | c5 | +----+------------------+------+------+------+ | AA | HELLO, HELLO | 8 | 0 | 0 | +----+------------------+------+------+------+
For more information about
innodb_memcache
tables and the
demo_test
sample table, see
Section 15.20.8, “InnoDB memcached Plugin Internals”.
Activate the daemon_memcached
plugin by
running the INSTALL PLUGIN
statement:
mysql> INSTALL PLUGIN daemon_memcached soname "libmemcached.so";
Once the plugin is installed, it is automatically activated each time the MySQL server is restarted.
To verify the daemon_memcached
plugin setup,
use a telnet session to issue
memcached commands. By default, the
memcached daemon listens on port 11211.
Retrieve data from the test.demo_test
table. The single row of data in the
demo_test
table has a key value of
AA
.
telnet localhost 11211
Trying 127.0.0.1... Connected to localhost. Escape character is '^]'.get AA
VALUE AA 8 12 HELLO, HELLO END
Insert data using a set
command.
set BB 10 0 16
GOODBYE, GOODBYE
STORED
where:
set
is the command to store a value
BB
is the key
10
is a flag for the operation;
ignored by memcached but may be used
by the client to indicate any type of information;
specify 0
if unused
0
is the expiration time (TTL);
specify 0
if unused
16
is the length of the supplied
value block in bytes
GOODBYE, GOODBYE
is the value that is
stored
Verify that the data inserted is stored in MySQL by
connecting to the MySQL server and querying the
test.demo_test
table.
mysql> SELECT * FROM test.demo_test;
+----+------------------+------+------+------+
| c1 | c2 | c3 | c4 | c5 |
+----+------------------+------+------+------+
| AA | HELLO, HELLO | 8 | 0 | 0 |
| BB | GOODBYE, GOODBYE | 10 | 1 | 0 |
+----+------------------+------+------+------+
Return to the telnet session and retrieve the data that you
inserted earlier using key BB
.
get BB
VALUE BB 10 16 GOODBYE, GOODBYE ENDquit
If you shut down the MySQL server, which also shuts off the
integrated memcached server, further attempts
to access the memcached data fail with a
connection error. Normally, the memcached
data also disappears at this point, and you would require
application logic to load the data back into memory when
memcached is restarted. However, the
InnoDB
memcached plugin
automates this process for you.
When you restart MySQL, get
operations once
again return the key-value pairs you stored in the earlier
memcached session. When a key is requested
and the associated value is not already in the memory cache, the
value is automatically queried from the MySQL
test.demo_test
table.
This example shows how to setup your own
InnoDB
table with the
daemon_memcached
plugin.
Create an InnoDB
table. The table must
have a key column with a unique index. The key column of the
city table is city_id
, which is defined
as the primary key. The table must also include columns for
flags
, cas
, and
expiry
values. There may be one or more
value columns. The city
table has three
value columns (name
,
state
, country
).
There is no special requirement with respect to column
names as along as a valid mapping is added to the
innodb_memcache.containers
table.
mysql>CREATE TABLE city (
city_id VARCHAR(32),
name VARCHAR(1024),
state VARCHAR(1024),
country VARCHAR(1024),
flags INT,
cas BIGINT UNSIGNED,
expiry INT,
primary key(city_id)
)ENGINE=InnoDB;
Add an entry to the
innodb_memcache.containers
table so that
the daemon_memcached
plugin knows how to
access the InnoDB
table. The entry must
satisfy the innodb_memcache.containers
table definition. For a description of each field, see
Section 15.20.8, “InnoDB memcached Plugin Internals”.
mysql> DESCRIBE innodb_memcache.containers;
+------------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+--------------+------+-----+---------+-------+
| name | varchar(50) | NO | PRI | NULL | |
| db_schema | varchar(250) | NO | | NULL | |
| db_table | varchar(250) | NO | | NULL | |
| key_columns | varchar(250) | NO | | NULL | |
| value_columns | varchar(250) | YES | | NULL | |
| flags | varchar(250) | NO | | 0 | |
| cas_column | varchar(250) | YES | | NULL | |
| expire_time_column | varchar(250) | YES | | NULL | |
| unique_idx_name_on_key | varchar(250) | NO | | NULL | |
+------------------------+--------------+------+-----+---------+-------+
The innodb_memcache.containers
table
entry for the city table is defined as:
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', 'test', 'city', 'city_id', 'name|state|country',
'flags','cas','expiry','PRIMARY');
default
is specified for the
containers.name
column to configure
the city
table as the default
InnoDB
table to be used with the
daemon_memcached
plugin.
Multiple InnoDB
table columns
(name
, state
,
country
) are mapped to
containers.value_columns
using a
“|” delimiter.
The flags
,
cas_column
, and
expire_time_column
fields of the
innodb_memcache.containers
table are
typically not significant in applications using the
daemon_memcached
plugin. However, a
designated InnoDB
table column is
required for each. When inserting data, specify
0
for these columns if they are
unused.
After updating the
innodb_memcache.containers
table, restart
the daemon_memcache
plugin to apply the
changes.
mysql>UNINSTALL PLUGIN daemon_memcached;
mysql>INSTALL PLUGIN daemon_memcached soname "libmemcached.so";
Using telnet, insert data into the city
table using a memcached
set
command.
telnet localhost 11211
Trying 127.0.0.1... Connected to localhost. Escape character is '^]'.set B 0 0 22
BANGALORE|BANGALORE|IN
STORED
Using MySQL, query the test.city
table to
verify that the data you inserted was stored.
mysql> SELECT * FROM test.city;
+---------+-----------+-----------+---------+-------+------+--------+
| city_id | name | state | country | flags | cas | expiry |
+---------+-----------+-----------+---------+-------+------+--------+
| B | BANGALORE | BANGALORE | IN | 0 | 3 | 0 |
+---------+-----------+-----------+---------+-------+------+--------+
Using MySQL, insert additional data into the
test.city
table.
mysql>INSERT INTO city VALUES ('C','CHENNAI','TAMIL NADU','IN', 0, 0 ,0);
mysql>INSERT INTO city VALUES ('D','DELHI','DELHI','IN', 0, 0, 0);
mysql>INSERT INTO city VALUES ('H','HYDERABAD','TELANGANA','IN', 0, 0, 0);
mysql>INSERT INTO city VALUES ('M','MUMBAI','MAHARASHTRA','IN', 0, 0, 0);
It is recommended that you specify a value of
0
for the flags
,
cas_column
, and
expire_time_column
fields if they are
unused.
Using telnet, issue a memcached
get
command to retrieve data you inserted
using MySQL.
get H
VALUE H 0 22
HYDERABAD|TELANGANA|IN
END
Traditional memcached
configuration options
may be specified in a MySQL configuration file or a
mysqld startup string, encoded in the
argument of the
daemon_memcached_option
configuration parameter. memcached
configuration options take effect when the plugin is loaded,
which occurs each time the MySQL server is started.
For example, to make memcached listen on port
11222 instead of the default port 11211, specify
-p11222
as an argument of the
daemon_memcached_option
configuration option:
mysqld .... --daemon_memcached_option="-p11222"
Other memcached options can be encoded in the
daemon_memcached_option
string.
For example, you can specify options to reduce the maximum
number of simultaneous connections, change the maximum memory
size for a key-value pair, or enable debugging messages for the
error log, and so on.
There are also configuration options specific to the
daemon_memcached
plugin. These include:
daemon_memcached_engine_lib_name
:
Specifies the shared library that implements the
InnoDB
memcached
plugin. The default setting is
innodb_engine.so
.
daemon_memcached_engine_lib_path
:
The path of the directory containing the shared library that
implements the InnoDB
memcached plugin. The default is NULL,
representing the plugin directory.
daemon_memcached_r_batch_size
:
Defines the batch commit size for read operations
(get
). It specifies the number of
memcached read operations after which a
commit occurs.
daemon_memcached_r_batch_size
is set to 1 by default so that every get
request accesses the most recently committed data in the
InnoDB
table, whether the data was
updated through memcached or by SQL. When
the value is greater than 1, the counter for read operations
is incremented with each get
call. A
flush_all
call resets both read and write
counters.
daemon_memcached_w_batch_size
:
Defines the batch commit size for write operations
(set
, replace
,
append
, prepend
,
incr
, decr
, and so
on).
daemon_memcached_w_batch_size
is set to 1 by default so that no uncommitted data is lost
in case of an outage, and so that SQL queries on the
underlying table access the most recent data. When the value
is greater than 1, the counter for write operations is
incremented for each add
,
set
, incr
,
decr
, and delete
call.
A flush_all
call resets both read and
write counters.
By default, you do not need to modify
daemon_memcached_engine_lib_name
or
daemon_memcached_engine_lib_path
.
You might configure these options if, for example, you want to
use a different storage engine for memcached
(such as the NDB memcached engine).
daemon_memcached
plugin configuration
parameters may be specified in the MySQL configuration file or
in a mysqld startup string. They take effect
when you load the daemon_memcached
plugin.
When making changes to daemon_memcached
plugin configuration, reload the plugin to apply the changes. To
do so, issue the following statements:
mysql>UNINSTALL PLUGIN daemon_memcached;
mysql>INSTALL PLUGIN daemon_memcached soname "libmemcached.so";
Configuration settings, required tables, and data are preserved when the plugin is restarted.
For additional information about enabling and disabling plugins, see Section 5.6.1, “Installing and Uninstalling Plugins”.