Install memcached plugin on MySQL5.6

I'm Saito from the Infrastructure team.
I've been hooked on the awscli command lately.

So, have you ever used
memcached ( pronounce: mem-cash-dee Memcached is a general-purpose distributed caching system.

It is used to reduce DB load and speed up dynamic apps by caching data and objects in RAM

Memcached has been around for 13 years since its launch on March 22, 2016. We
believe that memcached will be a powerful tool for you.

*By the way, I like Redis

In this article, we will assume that you are connected to the server via SSH and
install the memcached plugin on MySQL 5.6.
(The memcached plugin is an option on AWS RDS, so it's easy to install.)

Install MySQL version 5.6

First, 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 the MySQL server. We will not go into details about tuning (/etc/my.cnf)

yum -y install mysql-community-server

Start MySQL

/etc/init.d/mysqld start

So far, everything should be fine. When you start it, [OK] will be displayed

Prepare the necessary files

Now, please check where " innodb_memcached_config.sql " is located.
You can use the locate command to find it.

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

There it is. This time we need this .sql file

Note

Now, if you make a mistake in the steps from here, you will get stuck.
There is a bug reported in MySQL 5.6.

please make sure that you have
a DB named test on your MySQL server If not, please make sure to create one.

mysql> show databases; mysql> create database test;

If a database 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 in the shell

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

run
this without creating a DB named test, the configuration will fail.
(You will need to edit the sql file to recover.)

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

The command below will also fail, but if you run it again after creating the 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 MySQL 5.7 or create a testDB first. Otherwise, the memcached process will not start.

Now, if we take a look at the DB again,

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 check the tables.

mysql> use innodb_memcache; mysql> show tables; +---------------------------+ | Tables_in_innodb_memcache | +--------------------------+ | cache_policies | | config_options | 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 the memcached test table

Installing the memcached plugin

Sorry for the wait. It's time to install the plugin

mysql> install plugin daemon_memcached soname "libmemcached.so"; mysql> show plugins; +---------------------------------------+----------+--------------------+-----------------+---------+ | Name | Status | Type | Library | License | 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 | GPL | | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | | daemon_memcached | ACTIVE | DAEMON | libmemcached.so | GPL | +---------------------------+----------+----------------------+-----+---------+

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

Operation confirmation

Memcached should have been installed successfully.
So how do we check it?
It starts differently from normal services, so we're curious.
Let's check which ports are open and whether there are any processes running.

First, check if 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 appears to be running and the port is open.
Let's use telnet to check if it's working:

# 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 #all erase

That's all for this time