MySQL Memcached UDF安装注意事项

长平狐 发布于 2012/08/22 16:34
阅读 635
收藏 0
近期测试MySQL Memcached UDF在几个环境下分别进行了安装,安装中碰到了很多的问题,总结一下。安装的过程真是折腾死了由于安装由几部分组成,各个部分的版本都有冲突,而且还有不向下兼容的情况,所以频频安装出错。
 
1 介绍
mysql memcached UDF 其实就是通过libmemcached来使用memcache的一系列函数,通过这些函数,你能 对memcache进行get, set, cas, append, prepend, delete, increment, decrement objects操作,如果我们通过mysql trigger来使用这些函数,那么就能通过mysql更好的,更自动的管理memcache!
2 安装
1)安装libevent+memcache
什么是 memcached ?顾名思义,就是内存缓存。按照其官网的介绍:memcached 是高性能、分布式的内存对象缓存系统,意图用来通过减轻数据库的负载来提高动态 WEB 应用的速度。读写内存的速度显然要比读写文件系统或数据库系统要快得多,将动态数据缓存在内存中,显然要比其他缓存,有效得多。添加内存能带来的性能提升,对于服务器来说,要比其他方式有效得多。对于大中型网站,部署应用 memcached 将收到很好的效果。
Debian 系直接 $ sudo apt-get install memcached 都搞定,不然还需要分别安装libevent与memcached
只是编译安装时注意64位的要增加64位选项  $ ./configure --enable-64bit 将编译 64 位的版本。
2)安装libmemcached
 
$ tar xvzf  libmemcached-0.34.tar.gz  
$ cd libmemcached-0.34
$  ./configure --prefix=/usr/local/libmemcached34 --with-memcached=/usr/bin/memcached
sudo make
sudo make install
$ sudo chmod 777 /etc/ld.so.conf
$ echo "/usr/local/libmemcached34" >> /etc/ld.so.conf
$ ldconfig
不要用libmemcached-0.42.tar.gz,会遇到 servers.c:263:28: error: ‘memcached_st’ has no member named ‘hosts’servers.c:264:28: error: ‘memcached_st’ has no member named ‘hosts’但是libmemcached-0.34到0.37都没有这个问题
这里要使用0.34 不然会遇到 ERROR 1126 (HY000) at line 38: Can't open shared library 'libmemcached_functions_mysql.so' (errno: 0 /usr/local/mysql/lib/plugin/libmemcached_functions_mysql.so: undefined symbol: memcached_string_append) 
 
3)安装memcached_functions_mysql 
wget http://download.tangent.org/memcached_functions_mysql-0.9.tar.gz
$ tar zxvf memcached_functions_mysql-0.9.tar.gz
cd memcached_functions_mysql-0.9
./configure --prefix=/usr/local/memcache_mysql --with-mysql=/usr/local/mysql/bin/mysql_config --with-libmemcached=/usr/local/libmemcached34
如果缺少pkg-config安装一下
sudo apt-get install pkg-config
sudo make
sudo make install
4)拷贝lib文件到mysql的plugin下面
$ sudo cp /usr/local/memcache_mysql/lib/libmemcached_functions*   /usr/local/mysql/lib/mysql/plugin 
5)添加memcache UDF 函数
mysql> source sql/install_functions.sql
这样我们就可以使用mysql memcached UDF 了,我们可以通过下面语句查看是否已经正常安装
mysql> select * from mysql.func;
+------------------------------+-----+---------------------------------+----------+
| name | ret | dl | type |
+------------------------------+-----+---------------------------------+----------+
| memc_add | 2 | libmemcached_functions_mysql.so | function |
| memc_add_by_key | 2 | libmemcached_functions_mysql.so | function |
| memc_servers_set | 2 | libmemcached_functions_mysql.so | function |
32 rows in set (0.00 sec)

添加trigger,就是向memcache内insert,update,deletel等,参照:
1)memcached_functions_mysql-0.9/sql 目录下的trigger_fun.sql
2)或者文档:http://dev.mysql.com/doc/refman/5.1/en/ha-memcached-interfaces-mysqludf.html
如果懒得看就看我后边的例子
3 测试
mysql>  select memc_servers_set('127.0.0.1:11211')
注意:如果mysql restart,需要重新运行这句以建立与memcached之间的关系
 
mysql> 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> 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)
注意:设置MEMCACHED_BEHAVIOR_NO_BLOCK为打开状态,这样在memcached出现问题时(不能连接时)
数据继续插入到mysql中,报错提示,如果不设置此值,如果memcached失败,mysql需要等到timeout
才可以插入到表中。
 
mysql> use test;
Database changed
 
mysql> create table xxd (id int, value varchar(100));    
 
mysql> create trigger xxdmmci after insert on xxd for each row set @tmp = memc_set(NEW.id, NEW.value);
 
mysql> create trigger xxdmmcu after update on xxd for each row set @tmp = memc_set(NEW.id, NEW.value);
 
mysql> create trigger xxdmmcd before delete on xxd for each row set @tmp = memc_delete(OLD.id);
 
mysql> insert into xxd values(1, 'xxd'),(2,'xxd79'),(3, 'buro79xxd');         
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
 
mysql> select memc_get('2');
+----------------------------+
| memc_get('2')              |
+----------------------------+
| xxd79 |
+----------------------------+
1 row in set (0.00 sec)
 
mysql> update xxd set value='xxd_new' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> select memc_get('1');
+----------------------------+
| memc_get('1')              |
+----------------------------+
| xxd_new |
+----------------------------+
1 row in set (0.00 sec)
 
mysql> delete from xxd where id=1;
Query OK, 1 row affected (0.00 sec)
 
mysql> select memc_get('1');
+----------------------------+
| memc_get('1')              |
+----------------------------+
| NULL |
+----------------------------+
1 row in set (0.00 sec)
 
mysql> exit
 
$ telnet 127.0.0.1 11211
Trying 127.0.0.1...
Connected to 127.0.0.1.
Escape character is '^]'.
get 1
END
get 2
VALUE 2 0 5
xxd79
END
get 3
VALUE 3 0 9
buro79xxd
END
quit
Connection closed by foreign host.
 
--EOF--

原文链接:http://www.cnblogs.com/buro79xxd/archive/2011/04/20/2022594.html
加载中
0
王一丁z
王一丁z
现在用memcache  udf的多吗?
返回顶部
顶部