结构图如下:
上面提到的add.incr等操作完成依赖MySQL Server不需要人为干预.这样带来的好处主要是:
1、存储引擎自动做Memcached到数据文件的持久化.
2、数据自动加载Memcached
3、Memcached消耗CPU不多,内存占用可以控制.
4、因为InnoDB的一致性,不需要担心Memcached的事务性操作
其它请看官方文档
下面来安装Memcached For MySQL插件
mysql> select @@plugin_dir;
+---------------------------------+
| @@plugin_dir |
+---------------------------------+
| /usr/local/mysql5.6/lib/plugin/ |
+---------------------------------+
1 row in set (0.00 sec)
需要innodb_engine.so、libmemcached.so这两个文件
[mysql@localhost ~]$ ls /usr/local/mysql5.6/lib/plugin/
adt_null.so auth_socket.so daemon_example.ini innodb_engine.so libmemcached.so qa_auth_client.so qa_auth_server.so semisync_slave.so
auth.so auth_test_plugin.so debug libdaemon_example.so mypluglib.so qa_auth_interface.so semisync_master.so validate_password.so
[mysql@localhost ~]$
创建Memcached需要的表结构
mysql> source /usr/local/mysql5.6/share/innodb_memcached_config.sql
Query OK, 1 row affected (0.00 sec)
Database changed
Query OK, 0 rows affected (0.05 sec)
Query OK, 0 rows affected (0.04 sec)
Query OK, 0 rows affected (0.03 sec)
........
........
........
........
激活Memcached插件
mysql> INSTALL PLUGIN daemon_memcached soname "libmemcached.so";
Query OK, 0 rows affected (0.03 sec)
如果卸载执行下面的命令
mysql> UNINSTALL PLUGIN daemon_memcached;
查看Memcached相关的参数配置
mysql> SHOW VARIABLES LIKE '%memcached%';
+----------------------------------+------------------+
| Variable_name | Value |
+----------------------------------+------------------+
| daemon_memcached_enable_binlog | OFF |
| daemon_memcached_engine_lib_name | innodb_engine.so |
| daemon_memcached_engine_lib_path | |
| daemon_memcached_option | |
| daemon_memcached_r_batch_size | 1 |
| daemon_memcached_w_batch_size | 1 |
+----------------------------------+------------------+
daemon_memcached_enable_binlog此参数不支持动态修改需要重启MySQL
注意:参数daemon_memcached_r_batch_size、daemon_memcached_w_batch_size,这两个参数对性能影响较大,表示控制事物读写的频率,默认是1.
刚才执行的innodb_memcached_config.sql脚本会产生innodb_memcache库和三个表cache_policies, config_options, containers.
mysql> use innodb_memcache
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql>
mysql> show tables;
+---------------------------+
| Tables_in_innodb_memcache |
+---------------------------+
| cache_policies |
| config_options |
| containers |
+---------------------------+
3 rows in set (0.00 sec)
mysql> desc cache_policies;
+---------------+-------------------------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------------------------------------------------+------+-----+---------+-------+
| policy_name | varchar(40) | NO | PRI | NULL | |
| get_policy | enum('innodb_only','cache_only','caching','disabled') | NO | | NULL | |
| set_policy | enum('innodb_only','cache_only','caching','disabled') | NO | | NULL | |
| delete_policy | enum('innodb_only','cache_only','caching','disabled') | NO | | NULL | |
| flush_policy | enum('innodb_only','cache_only','caching','disabled') | NO | | NULL | |
+---------------+-------------------------------------------------------+------+-----+---------+-------+
5 rows in set (0.09 sec)
mysql> desc config_options;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(50) | NO | PRI | NULL | |
| value | varchar(50) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> desc 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 | |
+------------------------+--------------+------+-----+---------+-------+
9 rows in set (0.00 sec)
以上这三张表分别存放cache 策略、配置和容器信息
如何用Memcached访问表呢..那么就拿demo_test举例,首先MySQL已经帮助我们添加demo_test的信息了.
mysql> desc demo_test;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| c1 | varchar(32) | NO | PRI | | |
| c2 | varchar(1024) | YES | | NULL | |
| c3 | int(11) | YES | | NULL | |
| c4 | bigint(20) unsigned | YES | | NULL | |
| c5 | int(11) | YES | | NULL | |
+-------+---------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> select * from demo_test;
+----+--------------+------+------+------+
| c1 | c2 | c3 | c4 | c5 |
+----+--------------+------+------+------+
| AA | HELLO, HELLO | 8 | 0 | 0 |
+----+--------------+------+------+------+
1 row in set (0.00 sec)
mysql> select * from containers;
+------+-----------+-----------+-------------+---------------+-------+------------+--------------------+------------------------+
| name | db_schema | db_table | key_columns | value_columns | flags | cas_column | expire_time_column | unique_idx_name_on_key |
+------+-----------+-----------+-------------+---------------+-------+------------+--------------------+------------------------+
| aaa | test | demo_test | c1 | c2 | c3 | c4 | c5 | PRIMARY |
+------+-----------+-----------+-------------+---------------+-------+------------+--------------------+------------------------+
1 row in set (0.00 sec)
现在我们用Memcached来查看一下
[mysql@localhost ~]$ echo "get @@aaa.AA" | nc localhost 11211
VALUE @@aaa.AA 8 12
HELLO, HELLO
END
[mysql@localhost ~]$ echo "set @@aaa"; echo "get AA" | nc localhost 11211
set @@aaa
VALUE AA 8 12
HELLO, HELLO
END
此时set、get可以正常使用了.
现在我们来实际测试一下
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| demo_test |
+----------------+
1 row in set (0.00 sec)
mysql> create table emp (id int unsigned not null auto_increment,user varchar(20) not null,city varchar(20) not null,email varchar(20),work varchar(20),flags int unsigned default '0',cas_column bigint unsigned default '0', expire_time_column int unsigned default '0', primary key(id),unique key (user));
Query OK, 0 rows affected (0.28 sec)
mysql> insert into emp (user,city,email,work) values ('efg','beijing','456@abc.com','farmer');
Query OK, 1 rows affected (0.00 sec)
mysql> insert into emp (user,city,email,work) values ('edc','shenzhen','789@abc.com','teacher');
Query OK, 1 rows affected (0.00 sec)
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','emp','user','city|email|work','flags', 'cas_column', 'expire_time_column', 'user');
Query OK, 1 row affected (0.11 sec)
mysql> select * from containers;
+---------+-----------+-----------+-------------+------------------+-------+------------+--------------------+------------------------+
| name | db_schema | db_table | key_columns | value_columns | flags | cas_column | expire_time_column | unique_idx_name_on_key |
+---------+-----------+-----------+-------------+------------------+-------+------------+--------------------+------------------------+
| aaa | test | demo_test | c1 | c2 | c3 | c4 | c5 | PRIMARY |
| default | test | emp | user | city|email|email | flags | cas_column | expire_time_column | user |
+---------+-----------+-----------+-------------+------------------+-------+------------+--------------------+------------------------+
2 rows in set (0.00 sec)
现在策略已经设置完毕,现在我们通过Memcached查看一下
[mysql@localhost ~]$ echo "get efg"| nc 127.0.0.1 11211
VALUE efg 0 26
beijing|456@abc.com|farmer
END
[mysql@localhost ~]$ echo "get edc"| nc 127.0.0.1 11211
VALUE edc 0 25
shenzhen|789@abc.com|teacher
END
现在来看看从Memcached写入数据的效果
[mysql@localhost ~]$ telnet 127.0.0.1 11211
Trying 127.0.0.1...
Connected to 127.0.0.1.
Escape character is '^]'.
get @@aaa.AA
VALUE @@aaa.AA 8 12
HELLO, HELLO
END
set test 0 0 4
ss
STORED
get test
VALUE test 0 4
ss
END
进入MySQL查看刚才的数据
mysql> select * from test.demo_test\G
*************************** 1. row ***************************
c1: AA
c2: HELLO, HELLO
c3: 8
c4: 0
c5: 0
为什么没有呢?刚才我们明明test的啊.嘿嘿,这是由于我们没有设置transcaction级别,之后就才能正常显示了.
sql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
在看看
mysql> select * from test.demo_test\G
*************************** 1. row ***************************
c1: AA
c2: HELLO, HELLO
c3: 8
c4: 0
c5: 0
*************************** 2. row ***************************
c1: test
c2: ss
c3: 0
c4: 30
c5: 0
2 rows in set (0.00 sec)
OK了..刚才我们的刚才显示正常.在配置过程中有一张表我们需要注意.这就是cache_policies表.首先我们看看它的建表语句.
mysql> show create table cache_policies\G
*************************** 1. row ***************************
Table: cache_policies
Create Table: CREATE TABLE `cache_policies` (
`policy_name` varchar(40) NOT NULL,
`get_policy` enum('innodb_only','cache_only','caching','disabled') NOT NULL,
`set_policy` enum('innodb_only','cache_only','caching','disabled') NOT NULL,
`delete_policy` enum('innodb_only','cache_only','caching','disabled') NOT NULL,
`flush_policy` enum('innodb_only','cache_only','caching','disabled') NOT NULL,
PRIMARY KEY (`policy_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
这些策略都有四个选项:innodb_only, cache_only, caching 和disabled.分表代表的意思如下:
innodb_only:数据直接存储到innodb engine.
cache_only:跟传统的memcache server一样，数据存在memory中.
caching:memory中缓存,如果没有,再向innodb engine寻找数据
disabled就不解释了
结尾:在实验的过程中碰到不少壁,比如添加自定义表的时候，containers表的字段,除key_columns和value_columns外，其它的字段，如：flags，cas_column，expire_time_column也要设定,如果原表没有,那么就要新建了.这个比较恶心.个人感觉这还是一个不怎么成熟的产品同时还要颇多的限制,但是随着MySQL版本更新会迅速改进.不管怎样MySQL5.6引入Memcached支持NoSQL可以看到对NoSQL的强烈需求.今天先到此吧.^_^