mysql 5.6安装innodb_MySQL 5.6 安装配置InnoDB memcached Plugin

准备工作, 安装libmemached包，提供一些memcat/cp/dump命令，方便测试。

# yum install libmemcached.x86_64 -y

1. Setup required tables.

mysql> source MYSQL_HOME/share/innodb_memcached_config.sql

Query OK, 1 row affected (0.00 sec)

Database changed

Query OK, 0 rows affected (0.04 sec)

Query OK, 0 rows affected (0.04 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed

Query OK, 0 rows affected (0.03 sec)

Query OK, 1 row affected (0.01 sec)

[root@localhost:test 11:58:56]> show databases;

+——————–+

| Database           |

+——————–+

| information_schema |

| db01               |

| innodb_memcache    |

| mysql              |

| performance_schema |

| test               |

+——————–+

6 rows in set (0.00 sec)

[root@localhost:innodb_memcache 12:04:01]> show tables;

+—————————+

| Tables_in_innodb_memcache |

+—————————+

| cache_policies            |

| config_options            |

| containers                |

+—————————+

3 rows in set (0.00 sec)

2. Install the daemon plugin

mysql> install plugin daemon_memcached soname “libmemcached.so”;

Uninstall the daemon plugin

mysql> uninstall plugin daemon_memcached;

3. Start memcache

a. Set variable daemon_memcached_option when mysql is running.

> set global variable daemon_memcached_option = ‘-p11222’

b. Set option daemon_memcached_option during mysql start.

$ mysqld …. –daemon_memcached_option=”-p11222″

c. Set daemon_memcached_option in my.cnf.

$ grep -i memcache /usr/local/mysql/my.cnf

daemon_memcached_option=”-p11222″

4. Verify memcache status.

a. Check listen port.

[mysql@slc4-ra0002pxe159 ~]$ netstat -nultpa|grep -i list

tcp        0      0 :::11222                    :::*                        LISTEN      681/mysqld

b. Check memcache contents.

$ export MEMCACHED_SERVERS=127.0.0.1:11222

$ touch memcache/mime.types

$ memcp memcache/mime.types

$ memcat memcache/mime.types

$ telnet 127.0.0.1 11222

Trying 127.0.0.1…

Connected to 127.0.0.1.

Escape character is ‘^]’.

set all 10 0 9

123456789

STORED

get all

VALUE all 10 9

123456789

END

quit

Connection closed by foreign host.

[root@localhost:innodb_memcache 12:24:09]> select *  from test.demo_test;

+————+———–+——+——+——+

| c1         | c2        | c3   | c4   | c5   |

+————+———–+——+——+——+

| all        | 123456789 |   10 |    2 |    0 |

| mime.types |           |    0 |    1 |    0 |

+————+———–+——+——+——+

2 rows in set (0.00 sec)

5. Tuning memcache.

[root@localhost:test 11:56:44]> show variables like ‘%memcache%’;

+———————————-+——————+

| Variable_name                    | Value            |

+———————————-+——————+

| daemon_memcached_enable_binlog   | OFF              |

| daemon_memcached_engine_lib_name | innodb_engine.so |

| daemon_memcached_engine_lib_path |                  |

| daemon_memcached_option          | -p11222          |

| daemon_memcached_r_batch_size    | 1                |

| daemon_memcached_w_batch_size    | 1                |

+———————————-+——————+

daemon_memcached_r_batch_size batch commit size for read operations (get). It specifies after how many memcached read operations the system automatically does a commit. By default, this is set to 1 so that every get request can access the very latest committed data in the InnoDB table, whether the data was updated through memcached or by SQL. When its value is greater than 1, the counter for read operations is incremented once for every get call. The flush_all call resets both the read and write counters.

daemon_memcached_w_batch_size batch commit for any write operations (set, replace, append, prepend, incr, decr, and so on) By default, this is set as 1, so that no uncommitted data is lost in case of an outage, and any SQL queries on the underlying table can access the very latest data. When its value is greater than 1, the counter for write operations is incremented once for every add, set, incr, decr, and delete call. The flush_all call resets both the read and write counters.