UDFs是User Defined Functions的缩写，表示MySQL的用户定义函数，应用程序可以利用这些函数从MySQL 5.0以上版本的数据库中访问Memcached写入或者获取的数据。此外，MySQL从5.1版本开始支持触发器，从而可以在触发器中使用UDFs直接更新Memcached的内容，这种方式降低了应用程序设计和编写的复杂性。下面简单介绍UDFs的安装和使用。 UDFs安装
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/bin/memcached
make
make install
4. 安装memcached_functions_mysql
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/memcache_mysql --with-mysql=/usr/local/mysql/bin/mysql_config --with-libmemcached=/usr/local/libmemcached
make
make install
DELIMITER //
DROP TRIGGER IF EXISTS url_mem_insert;
CREATE TRIGGER url_mem_insert
BEFORE INSERT ON urls
FOR EACH ROW BEGIN
set @mm = memc_set(NEW.id, NEW.url);
if @mm <> 0 then
insert into results(id) values(NEW.id);
end if;
END //
DELIMITER ;
DELIMITER //
DROP TRIGGER IF EXISTS url_mem_update;
CREATE TRIGGER url_mem_update
BEFORE UPDATE ON urls
FOR EACH ROW BEGIN
set @mm = memc_replace(OLD.id,NEW.url);
if @mm <> 0 then
insert into results(id) values(OLD.id);
end if;
END //
DELIMITER ;
DELIMITER //
DROP TRIGGER IF EXISTS url_mem_delete;
CREATE TRIGGER url_mem_delete
BEFORE DELETE ON urls
FOR EACH ROW BEGIN
set @mm = memc_delete(OLD.ID);
if @mm <> 0 then
insert into results(id) values(OLD.id);
end if;
END //
DELIMITER ;
3. 设置Memcached相关参数
设置UDFs操作Memcaced服务器的IP地址和端口。
mysql> SELECT memc_servers_set('192.168.11.52:11211');
+-----------------------------------------+
| memc_servers_set('192.168.11.52:11211') |
+-----------------------------------------+
| 0 |
+-----------------------------------------+
1 row in set (0.07 sec)
mysql>select memc_server_count();
+---------------------+
| memc_server_count() |
+---------------------+
| 1 |
+---------------------+
1 row in set (0.00 sec)
1>telnet 192.168.11.52 11211
Trying 192.168.11.52...
Connected to 192.168.11.52 (192.168.11.52).
Escape character is '^]'.
get 1
VALUE 1 0 22
http://www.test.com.cn
END
2）更新表urls里面的数据，然后查询Memcached中是否也进行了更新。
mysql>update test.urls set url='http://blog.test.com.cn' where id=1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select memc_replace('1','http://blog.test.com.cn');
+---------------------------------------------+
| memc_replace('1','http://blog.test.com.cn') |
+---------------------------------------------+
| 0 |
+---------------------------------------------+
1 row in set (0.00 sec)
mysql> select memc_get('1');
+-------------------------+
| memc_get('1') |
+-------------------------+
| http://blog.test.com.cn |
+-------------------------+
1 row in set (0.00 sec)
1>telnet 192.168.11.52 11211
Trying 192.168.11.52...
Connected to 192.168.11.52(192.168.11.52).
Escape character is '^]'.
get 1
VALUE 1 0 23
http://blog.test.com.cn
END
3）删除表urls中的数据，然后查看Memcached是否也将该数据删除了。
mysql>delete from test.urls where id=1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select memc_get('1');
+---------------+
| memc_get('1') |
+---------------+
| NULL |
+---------------+
1 row in set (0.00 sec)
1>telnet 192.168.11.52 11211
Trying 192.168.11.52...
Connected to 192.168.11.52 (192.168.11.52).
Escape character is '^]'.
get 1
END
数据库
发布于：2021-07-03 21:43
|
阅读数：351
|
评论：0
QQ好友和群
QQ空间
