Install memcached plugin on MySQL5.6

This is Saito from the infrastructure team.
Lately I've been addicted to awscli commands.

Well, have you ever used
memcached ( pronunciation: mem-cash-dee memcached is a general purpose distributed cache system.

Used to reduce DB load and speed up dynamic apps by caching data and objects in RAM

March 22, 2016 marks the 13th year since memcached was introduced.
memcached will surely become a powerful ally for you.

*By the way, I like Redis

we will install the memcached plugin on MySQL5.6
assuming that you are connecting to the server via ssh (With AWS RDS, the memcached plugin can be installed as an option, so it's easy...)

Install MySQL version 5.6

First, let's install the MySQL repository.

wget http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm -P /usr/local/src cd /usr/local/src yum -y install mysql-community-release -el6-5.noarch.rpm

Next, install MySQL server. Various tuning (/etc/my.cnf) will be omitted.

yum -y install mysql-community-server

Start MySQL

/etc/init.d/mysqld start

I think everything is fine up to this point. Once started, [OK] will be displayed.

Prepare the necessary files

Now, check where
innodb_memcached_config.sql When searching, it is a good idea to use the locate command.

$ locate innodb_memcached_config.sql /usr/share/mysql/innodb_memcached_config.sql

there was. This time we need this .sql file.

Note

Now, if you make a mistake in the steps from here, you'll get hooked.
This is because there is a bug reported in MySQL 5.6.

check if there is
a DB named test on your MySQL server If you don't have one, be sure to make one.

mysql> show databases; mysql> create database test;

If a DB named test is not prepared here, an error will occur later.

Import DB and tables

Then on the MySQL console

mysql> source /usr/share/mysql/innodb_memcached_config.sql

or on the shell

$ mysql < /usr/share/mysql/innodb_memcached_config.sql

Let's enter.
If you run it without creating a DB named test, the configuration will fail.
(You will need to edit the sql file for recovery)

$ mysql < /usr/share/mysql/innodb_memcached_config.sql

If I run the command below, it will also fail, but if I run it again after creating testDB, it will be fixed.

mysql> source /usr/share/mysql/innodb_memcached_config.sql

The bugs reported in MySQL 5.6 are as follows.

ERROR 1049 (42000) at line 86: Unknown database 'test'
[9 Jun 2014 13:20] Daniel Price
Fixed as of the upcoming 5.7.5 release, and here's the changelog entry:

The "innodb_memcached_config.sql" configuration script would fail after
running the "mysql_secure_installation" script, which removes the MySQL
"test" database. The "innodb_memcached_config.sql" script now creates the
"test" database if it does not exist.

Thank you for the bug report.Reference
: Bug #72678 InnoDB Memcached Plugin Configuration - test database

If you are concerned about this bug, please use MySQL5.7 or
create testDB first. Otherwise, the memcached process will not start.

Now, when I look at the DB again, I see that

mysql> show databases; +--------------------+ | Database | +-------------------- -+ | information_schema | | innodb_memcache | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec)

innodb_memcache has been added.
Let's also check the table.

mysql> use innodb_memcache; mysql> show tables; +---------------------------+ | Tables_in_innodb_memcache | +------- --------------------+ | cache_policies | | config_options | | containers | +------------------- --------+ mysql> select * from innodb_memcache.containersG *************************** 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 | +----+------------------- +------+------+------+

The test database also contains memcached test tables.

Installing memcached plugin

Sorry to have kept you waiting. Now it's time to install the plugin.

mysql> install plugin daemon_memcached soname "libmemcached.so"; mysql> show plugins; +--------------------------+--- -------+--------------------+-----------------+--- ------+ | Name | Status | Type | Library | License | +----------------------------+-- --------+--------------------+-----------------+-- -------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | ... | INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_TABLESPACES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_SYS_DATAFILES | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | | daemon_memcached | ACTIVE | DAEMON | libmemcached.so | GPL | +---- ------------------------+----------+------------- ------+-----------------+---------+

It seems to have been installed successfully. This completes the plugin installation.

Operation confirmation

memcached should have been successfully installed.
So how can we check?
I am very concerned because the startup is different from normal services.
Check which ports are open and if there are processes.

First, let's check whether the port is open.

# netstat -lnpt | egrep "(Proto|mysql)" Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0 0.0.0.0:11211 0.0.0.0:* LISTEN 531/mysqld tcp 0 0 : ::3306 :::* LISTEN 531/mysqld tcp 0 0 :::11211 :::* LISTEN 531/mysqld

Normally memcached uses port
11211 The mysqld process seems to be running and the ports are open.
Let's check the operation using telnet.

# yum -y install telnet $ telnet localhost 11211 Trying 127.0.0.1... Connected to localhost. Escape character is '^]'. get foo VALUE foo 0 2 hi END stats STAT pid 8861 (etc)

It certainly seems to be working.

Telnet is versatile, but libmemcached is also available, so let's try using it.

# yum -y install libmemcached # export MEMCACHED_SERVERS=127.0.0.1:11211 # memcat AA HELLO, HELLO

You can
also memslap If you want to know more commands, try the following commands.

memcat #read memrm #delete memstat #information memcp #copy memflush #delete

That's all for now.