Mysql 5.7 memcached plugin

mysql 5.7 memcached plugin
通过在mysql进行写，memcached可以直接读取，适合在memcached加速读应用，不过限制多

https://yq.aliyun.com/articles/8282
mysql5.7 memcached


yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm -y
yum install libaio-devel -y
yum install ncurses* -y
yum install readline* -y
yum install zlib-dev* -y
yum install pam* -y
yum install valgrind* -y
yum install jemalloc* -y
yum install lrzsz -y 
yum install sysbench* -y
yum install python-pip -y
yum install keepalived -y
yum install libevent libevent-devel -y 
yum install zlib* -y


cmake . -DCMAKE_BUILD_TYPE=RelWithDebInfo -DBUILD_CONFIG=mysql_release -DFEATURE_SET=community -DWITH_EMBEDDED_SERVER=OFF \
-DCMAKE_INSTALL_PREFIX=/opt/mysql3306 \
-DMYSQL_DATADIR=/opt/mysql3306/data \
-DSYSCONFDIR=/opt/mysql3306/etc \
-DMYSQL_UNIX_ADDR=/opt/mysql3306/mysqld.sock \
-DMYSQL_USER=mysql \
-DMYSQL_TCP_PORT=3306 \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_MEMORY_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DEXTRA_CHARSETS=all \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DENABLED_LOCAL_INFILE=1 \
-DCMAKE_EXE_LINKER_FLAGS="-ljemalloc" \
-DWITH_SAFEMALLOC=OFF \
-DDOWNLOAD_BOOST=1 \
-DWITH_INNODB_MEMCACHED=ON \
-DWITH_BOOST=/tmp

source /opt/mysql3306/share/innodb_memcached_config.sql


测试：
创建测试表：


CREATE TABLE test.city (
city_id VARCHAR(32),
name VARCHAR(1024),
state VARCHAR(1024),
country VARCHAR(1024),
flags INT,
cas BIGINT UNSIGNED, 
expiry INT,
primary key(city_id)
) ENGINE=InnoDB;


INSERT INTO city VALUES ('C','CHENNAI','TAMIL NADU','IN', 0, 0 ,0);
INSERT INTO city VALUES ('D','DELHI','DELHI','IN', 0, 0, 0);
INSERT INTO city VALUES ('H','HYDERABAD','TELANGANA','IN', 0, 0, 0);
INSERT INTO city VALUES ('M','MUMBAI','MAHARASHTRA','IN', 0, 0, 0);



CREATE TABLE `users` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(15) NOT NULL,
  `password` VARCHAR(32) NOT NULL,
  `email` VARCHAR(50) NOT NULL,
  `flags` INT(10) UNSIGNED DEFAULT '0',
  `cas_column` BIGINT(20) UNSIGNED DEFAULT '0',
  `expire_time_column` INT(10) UNSIGNED DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB;




INSERT INTO `users` (`username`, `password`, `email`)
VALUES
('foo', 'ffffffffffffffffffffffffffffffff', 'foo@domain.com'),
('bar', 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb', 'bar@domain.com');



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', 'users', 'username', 'password|email',
  'flags', 'cas_column', 'expire_time_column', 'username'
);


配置表：
    
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 ('city', 'test', 'city', 'city_id', 'name|state|country', 'flags','cas','expiry','PRIMARY');


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', 'users', 'username', 'password|email',
    'flags', 'cas_column', 'expire_time_column', 'username'
  );




  
SELECT * FROM innodb_memcache.containers;

设置当前操作的表为test.users
get @@default


查询数据
get bar


设置当前操作的表为test.city
get @@city
get H




添加或更新表数据
set A 0 0 22
SHANGHAI|JIADING|IN




set E 0 0 22
HENAN|LUOSHAN|IN




echo "get @@city" | nc localhost 11211
echo "get A" | nc localhost 11211
echo "get foo" | nc localhost 11211




采用python对memcached进行读写
 pip install python-memcached


 [root@virtdb55 py]# more mem.py 
#!/usr/bin/env python
import memcache


pymem = memcache.Client(['192.168.2.61:11211'],debug=0)
pymem.get("@@city")
value = pymem.get("A")
print value
pymem.set("A","xiniang|wulumuqi|in")
value = pymem.get("A")
print value




[root@virtdb55 py]# more mem3.py 
#!/usr/bin/env python
import memcache


pymem = memcache.Client(['192.168.2.61:11211'],debug=0)
pymem.get("@@city")
for i in range(1,100):
    key = str(i)
    pymem.set(key,"hahaha|sbsbsbsb|in")
    value = pymem.get(key)
    print value


for  key in 'MPTU':
    pymem.delete(key)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/91975/viewspace-2125275/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/91975/viewspace-2125275/