Home » linux » 通过mysql触发器操作memcached

通过mysql触发器操作memcached

Posted on zydyh
in linux, mysql
on 7月 4, 2017
No Comments.

mysql5.0以上版本可以利用用户的自定义函数（即UDFs），来访问memcache的写入或者读取
mysql5.1版本开始支持触发器，可以在触发器中使用UDFs直接修改memcached中的数据，摆脱依靠程序要更新缓存。下面记录了实现这个功能的安装步骤

系统环境 centos6.9
1.安装 mysql5.6.36 和 memcached 1.4.37
我这边为了方便，直接使用oneinstack安装了mysql和memcached，具体方法参考：oneinstack
oneinstack一直在更新，比较稳定

2.安装libmemcached-0.34
Memcached Functions for MySQL Latest version is 1.1 只兼容0.34版本： https://launchpad.net/memcached-udfs/trunk

1
2
3
4
5
6
7
8

cd /tmp
mkdir -p /usr/local/libmemcached
wget https://launchpad.net/libmemcached/1.0/0.34/+download/libmemcached-0.34.tar.gz
tar zxvf libmemcached-0.34.tar.gz
cd libmemcached-0.34
./configure --prefix=/usr/local/libmemcached --with-memcached=/usr/local/webserver/memcached/bin/memcached
make
make install

3.安装memcached_functions_mysql

1
2
3
4
5
6
7

cd /tmp
wget https://launchpad.net/memcached-udfs/trunk/1.1/+download/memcached_functions_mysql-1.1.tar.gz
tar xvf memcached_functions_mysql-1.1.tar.gz
cd memcached_functions_mysql-1.1
./configure --prefix=/usr/local/memcached_functions_mysql --with-mysql=/usr/local/webserver/mysql/bin/mysql_config --with-libmemcached=/usr/local/libmemcached
make
make install

4.将库文件拷贝至mysql的安装目录下

1
2
3

cp /usr/local/memcached_functions_mysql/lib/libmemcached_functions_mysql.* /usr/local/webserver/mysql/lib/plugin
echo "/usr/local/lib" >> /etc/ld.so.conf
ldconfig

5.导入memcached的自定义函数

1 2	MySQL [(none)]> source /tmp/memcached_functions_mysql-1.1/sql/install_functions.sql Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

检查是否成功

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

安装基本已经完成，接下来就测试用触发器操作memcached

1.创建表
新建两张表：domain_name和error，其中error是错误的记录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

create database t1;
use t1
drop table if exists domain_name;
CREATE TABLE `domain_name` (
`id` int(10) NOT NULL,
`url` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
);
drop table if exists error;
CREATE TABLE `error` (
`id` int(10) NOT NULL,
`error` varchar(255) NOT NULL DEFAULT 'error',
`time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
);

2.建立3个触发器（trigger）

当向domain_name表中插入数据时，对Memcached执行set操作。trigger的代码如下：

1
2
3
4
5
6
7
8
9
10
11

DELIMITER //
DROP TRIGGER IF EXISTS url_mem_insert;
CREATE TRIGGER url_mem_insert
BEFORE INSERT ON domain_name
FOR EACH ROW BEGIN
set @mm = memc_set(NEW.id, NEW.url);
if @mm <> 0 then
insert into error(id) values(NEW.id);
end if;
END //
DELIMITER ;

当对domain_name表中的数据进行更新时，对Memcached执行replace操作。trigger代码如下：

1
2
3
4
5
6
7
8
9
10
11

DELIMITER //
DROP TRIGGER IF EXISTS url_mem_update;
CREATE TRIGGER url_mem_update
BEFORE UPDATE ON domain_name
FOR EACH ROW BEGIN
set @mm = memc_replace(OLD.id,NEW.url);
if @mm <> 0 then
insert into error(id) values(OLD.id);
end if;
END //
DELIMITER ;

当对domain_name表中的数据进行删除操作时，对Memcached执行delete操作。trigger代码如下：

1
2
3
4
5
6
7
8
9
10
11

DELIMITER //
DROP TRIGGER IF EXISTS url_mem_delete;
CREATE TRIGGER url_mem_delete
BEFORE DELETE ON domain_name
FOR EACH ROW BEGIN
set @mm = memc_delete(OLD.ID);
if @mm <> 0 then
insert into error(id) values(OLD.id);
end if;
END //
DELIMITER ;

3.设置memcache的参数（trigger）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

MySQL [(none)]> SELECT memc_servers_set('192.168.0.99:11211');
+----------------------------------------+
| memc_servers_set('192.168.0.99:11211') |
+----------------------------------------+
| 0 |
+----------------------------------------+
1 row in set (0.00 sec)

MySQL [(none)]> select memc_server_count();
+---------------------+
| memc_server_count() |
+---------------------+
| 1 |
+---------------------+
1 row in set (0.01 sec)

MySQL [(none)]> select memc_servers_behavior_set('MEMCACHED_BEHAVIOR_NO_BLOCK','1');
+--------------------------------------------------------------+
| memc_servers_behavior_set('MEMCACHED_BEHAVIOR_NO_BLOCK','1') |
+--------------------------------------------------------------+
| 0 |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL [(none)]> select memc_servers_behavior_set('MEMCACHED_BEHAVIOR_TCP_NODELAY','1');
+-----------------------------------------------------------------+
| memc_servers_behavior_set('MEMCACHED_BEHAVIOR_TCP_NODELAY','1') |
+-----------------------------------------------------------------+
| 0 |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)

简单的测试：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

MySQL [t1]> insert into domain_name (id,url) values (1, 'http://www.zzz.com');
Query OK, 1 row affected (0.01 sec)
MySQL [t1]> select memc_get('1');
+--------------------+
| memc_get('1') |
+--------------------+
| http://www.zzz.com |
+--------------------+
1 row in set (0.01 sec)

telnet 192.168.0.99 11211
Trying 192.168.0.99...
Connected to 192.168.0.99.
Escape character is '^]'.
get 1
VALUE 1 0 18
http://www.zzz.com
END

修改数据

1
2
3
4
5
6
7
8
9
10
11

MySQL [t1]> update t1.domain_name set url='http://www.xxx.com' where id=1;
MySQL [t1]> Query OK, 1 row affected, 1 warning (0.00 sec)

telnet 192.168.0.99 11211
Trying 192.168.0.99...
Connected to 192.168.0.99.
Escape character is '^]'.
get 1
VALUE 1 0 18
http://www.xxx.com
END

删除数据：

1
2
3
4
5
6
7
8
9

MySQL [t1]> delete from t1.domain_name where id=1;
Query OK, 1 row affected (0.00 sec)

telnet 192.168.0.99 11211
Trying 192.168.0.99...
Connected to 192.168.0.99.
Escape character is '^]'.
get 1
END