Installing memcached plugin for MySQL5.6

This is Saito from the infrastructure team.
Lately, I've been really into awscli commands.

Now,memcached (pronunciation: mem-cash-deehave any of you ever used
memcached is a general-purpose distributed caching system.

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

memcached celebrated its 13th anniversary on March 22nd, 2016.
memcached will surely become a powerful ally for you.

*By the way, IRedislike

This article assumes you are connected to the server via SSH and
will guide you through installing the memcached plugin on MySQL 5.6.
(On AWS RDS, the memcached plugin can be easily installed as an option...)

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,innodb_memcached_config.sqlcheck where the
You can use a command like `locate` 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 procedure from here on, you'll run into problems.
That's because there's a bug that has been reported in MySQL 5.6.

on your MySQL servera database named "test"please check if there is
If not, please create it.

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

Enter the following.
If you run this without creating a database named "test," the setup 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 this bug bothers you,
please use MySQL 5.7 or create 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 also 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 check

memcached should have been installed successfully.
So, how do we verify this?
Its startup process is different from normal services, which is quite concerning.
Let's check which ports are open and whether the process is 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, memcached11211.uses port
The mysqld process appears to be running, and the port is open.
Let's use telnet to verify its operation.

# 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

While telnet is versatile,libmemcachedis also available, so let's try using that.

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

Additionally,memslapyou can view benchmarks using
If you want to learn more commands, try the following commands as well.

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

That's all for this time