[文章作者:张宴 本文版本:v1.0 最后修改:2010.09.08 转载请注明原文链接:http://blog.zyan.cc/mysql-udf-http/]

  Mysql-udf-http 是一款简单的MySQL用户自定义函数(UDF, User-Defined Functions),具有http_get()、http_post()、http_put()、http_delete()四个函数,可以在MySQL数据库中利用HTTP协议进行REST相关操作。

  项目网址http://code.google.com/p/mysql-udf-http/
  中文说明http://blog.zyan.cc/mysql-udf-http/
  使用环境:Linux操作系统,支持的MySQL版本:5.1.x 和 5.5.x。5.0.x未经测试。
  软件作者:张宴



  一、REST架构风格:

  REST(Representational State Transfer)是一种轻量级的Web Service架构风格,其实现和操作明显比SOAP和XML-RPC更为简洁,可以完全通过HTTP协议实现,还可以利用缓存Cache来提高响应速度,性能、效率和易用性上都优于SOAP协议。REST最早是由 Roy Thomas Fielding 博士2000年在论文《Architectural Styles and the Design of Network-based Software Architectures》中提出的,中文译文全文PDF点此下载。另外,有篇译文对REST做了一个简化说明。

  目前,REST架构风格的常见实现是基于HTTP协议及其四种基本方法(如POST、GET、PUT和DELETE)的。有人将HTTP协议的四种方法与CRUD原则相对应,CRUD原则对于资源只需要四种行为:Create(创建)、Read(读取)、Update(更新)和Delete(删除)就可以完成对其操作和处理。

  点击在新窗口中浏览此图片

  点击在新窗口中浏览此图片

  在Mysql-udf-http中,四个函数http_post()、http_get()、http_put()、http_delete()分别对应HTTP协议的POST、GET、PUT、DELETE四种方法。

  REST是一种架构风格,而不是协议或标准。HTTP协议“POST、GET、PUT、DELET”四种方法与CRUD原则“Create、Read、Update、Delete”四种行为的一一对应关系只是一种架构设计习惯,而不是规范。因此,POST方法也可以用来更新资源,PUT方法也可以用来创建资源,这就要看具体应用程序作者的定义了。例如Tokyo Tyrant除了支持Memcached协议外,还支持REST方式存取,PUT代表创建和更新,GET代表读取,DELETE代表删除(关于Tokyo Tyrant的安装使用请点击这儿)。

  目前国内外流行的Web 2.0应用API接口中,很多都支持REST架构风格。例如:新浪微博开放平台人人网API、Google OpenID、Flickr、Twitter、eBay、Facebook、Last.fm、del.icio.us、Yahoo Search、Amazon S3、Amazon EC2、Digg、Microsoft Bing、FriendFeed、PayPal、Foursquare,更多...

  当记录数成百上千万条时,通常采用 MySQL 分表减低数据库压力。但是,全部数据按点击数、精华、积分排序显示等功能,在MySQL 分表中则无法实现。编写 Mysql-udf-http 的最初目的,是为了在项目开发中,将 MySQL 各分表的数据自动同步到我们的 TCSQL 高速列表数据库,用来做列表查询、显示,内容页则根据ID直接查询各 MySQL 分表的内容。由于HTTP协议的通用性,通过 Mysql-udf-http 可以做更多的事情。

  通过Mysql-udf-http,你可以在MySQL中利用触发器,将MySQL的数据同步到支持REST的应用上。例如你有一个独立博客,你可以在文章表创建MySQL触发器,这样,在发表文章时,就可以将文章标题、URL自动同步到新浪微博、Twitter。你想用 Tokyo Tyrant 做缓存,也可以利用MySQL触发器在发生增、删、改时,将数据自动同步到 Tokyo Tyrant。详细配置方法本文第4节中会有介绍。



  二、Mysql-udf-http的安装与使用:

  1. 在Linux系统上安装Mysql-udf-http

  注意:“/usr/local/webserver/mysql/”是你的MySQL安装路径,如果你的MySQL安装路径不同,请自行修改。

ulimit -SHn 65535
wget http://curl.haxx.se/download/curl-7.21.1.tar.gz
tar zxvf curl-7.21.1.tar.gz
cd curl-7.21.1/
./configure --prefix=/usr
make && make install
cd ../

echo "/usr/local/webserver/mysql/lib/mysql/" > /etc/ld.so.conf.d/mysql.conf
/sbin/ldconfig
wget http://mysql-udf-http.googlecode.com/files/mysql-udf-http-1.0.tar.gz
tar zxvf mysql-udf-http-1.0.tar.gz
cd mysql-udf-http-1.0/
./configure --prefix=/usr/local/webserver/mysql --with-mysql=/usr/local/webserver/mysql/bin/mysql_config
make && make install
cd ../




  2. 通过命令行登陆进入MySQL

/usr/local/webserver/mysql/bin/mysql -S /tmp/mysql.sock




  3. 创建MySQL自定义函数

  mysql>


  4. 使用方法

  I. 函数描述:

  mysql>
  II. 示例 A:

  mysql>
  III. 示例

  通过MySQL触发器,利用mysql-udf-http和第三方UDF函数lib_mysqludf_json,自动同步数据到 Tokyo Tyrant。

  (1). 下载安装 lib_mysqludf_json 修改版:

  以下安装包适合32位Linux操作系统:
wget http://mysql-udf-http.googlecode.com/files/lib_mysqludf_json-i386.tar.gz
tar zxvf lib_mysqludf_json-i386.tar.gz
cd lib_mysqludf_json-i386/
# 如果你的MySQL安装路径不是/usr/local/webserver/mysql/,请修改以下路径。
cp -f lib_mysqludf_json.so /usr/local/webserver/mysql/lib/mysql/plugin/lib_mysqludf_json.so
cd ../


  以下安装包适合64位Linux操作系统:
wget http://mysql-udf-http.googlecode.com/files/lib_mysqludf_json-x86_64.tar.gz
tar zxvf lib_mysqludf_json-x86_64.tar.gz
cd lib_mysqludf_json-x86_64/
# 如果你的MySQL安装路径不是/usr/local/webserver/mysql/,请修改以下路径。
cp -f lib_mysqludf_json.so /usr/local/webserver/mysql/lib/mysql/plugin/lib_mysqludf_json.so
cd ../


  通过命令行登陆进入MySQL:
/usr/local/webserver/mysql/bin/mysql -S /tmp/mysql.sock


  mysql>  lib_mysqludf_json的详细用法请访问:http://www.mysqludf.org/lib_mysqludf_json/

  (2). 创建测试表

  mysql>
  (3). 为测试表创建触发器:

  mysql>
  (4). 将 MySQL 表和 Tokyo Tyrant 关联进行查询:

  mysql>


  5. 如何删除mysql-udf-http UDF函数:

  mysql>





技术大类 » 数据库技术 | 评论(107) | 引用(0) | 阅读(165509)
guoxliang Email
2011-11-28 16:01
问题重复一遍:OS:Red Hat Enterprise Linux AS release 4 (Nahant Update 3)MySql:5.1.58mysql> create function lib_mysqludf_json_info returns string soname 'lib_mysqludf_json.so'; ERROR 2013 (HY000): Lost connection to MySQL server during querymysql> 111128 14:01:58 mysqld_safe Number of processes running now: 0111128 14:01:58 mysqld_safe mysqld restarted下面是error log:Version: '5.1.58-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution111128 14:01:58 - mysqld got signal 8 ;This could be because you hit a bug. It is also possible that this binaryor one of the libraries it was linked against is corrupt, improperly built,or misconfigured. This error can also be caused by malfunctioning hardware.We will try our best to scrape up some info that will hopefully help diagnosethe problem, but since we have already crashed, something is definitely wrongand this may fail.key_buffer_size=402653184read_buffer_size=2097152max_used_connections=1max_threads=151threads_connected=1It is possible that mysqld could use up tokey_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1013272 Kbytes of memoryHope that's ok; if not, decrease some variables in the equation.Thread pointer: 0xa5f540Attempting backtrace. You can use the following information to find outwhere mysqld died. If you see no messages after this, something wentterribly wrong...stack_bottom = 0x40a81fc8 thread_stack 0x40000/home/work/server/mysql/libexec/mysqld(my_print_stacktrace+0x26) [0x75a786]/home/work/server/mysql/libexec/mysqld(handle_segfault+0x35d) [0x58ce4d]/lib64/tls/libpthread.so.0 [0x302b80c420]/lib64/ld-linux-x86-64.so.2 [0x302ad07487]/lib64/ld-linux-x86-64.so.2 [0x302ad0784e]/lib64/ld-linux-x86-64.so.2 [0x302ad08c20]/lib64/tls/libc.so.6 [0x302aff7068]/lib64/ld-linux-x86-64.so.2 [0x302ad0aa60]/lib64/tls/libc.so.6(_dl_open+0xaa) [0x302aff79fa]/lib64/libdl.so.2 [0x302b201054]/lib64/ld-linux-x86-64.so.2 [0x302ad0aa60]/lib64/libdl.so.2 [0x302b201552]/lib64/libdl.so.2(dlopen+0x32) [0x302b201092]/home/work/server/mysql/libexec/mysqld(mysql_create_function(THD*, st_udf_func*)+0x445) [0x6ad6b5]/home/work/server/mysql/libexec/mysqld(mysql_execute_command(THD*)+0x3cc1) [0x59f3d1]/home/work/server/mysql/libexec/mysqld(mysql_parse(THD*, char*, unsigned int, char const**)+0x1f3) [0x5a4003]/home/work/server/mysql/libexec/mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int)+0xdec) [0x5a4eec]/home/work/server/mysql/libexec/mysqld(do_command(THD*)+0xde) [0x5a58de]/home/work/server/mysql/libexec/mysqld(handle_one_connection+0x61d) [0x595b0d]/lib64/tls/libpthread.so.0 [0x302b80610a]/lib64/tls/libc.so.6(__clone+0x73) [0x302afc6003]Trying to get some variables.Some pointers may be invalid and cause the dump to abort.Query (0xabc760): create function lib_mysqludf_json_info returns string soname 'lib_mysqludf_json.so'Connection ID (thread ID): 1Status: NOT_KILLEDThe manual page at http://dev.mysql.com/doc/mysql/en/crashing.html containsinformation that should help you find out what is causing the crash.111128 14:01:58 mysqld_safe Number of processes running now: 0111128 14:01:58 mysqld_safe mysqld restarted111128 14:01:58 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.111128 14:01:58 [Note] Event Scheduler: Loaded 0 events111128 14:01:58 [Note] /home/work/server/mysql/libexec/mysqld: ready for connections.紧急!请各位大侠帮忙
guoxliang Email
2011-11-28 16:15
问题重复一遍:OS:Red Hat Enterprise Linux AS release 4 (Nahant Update 3)MySql:5.1.58mysql> create function lib_mysqludf_json_info returns string soname 'lib_mysqludf_json.so'; ERROR 2013 (HY000): Lost connection to MySQL server during querymysql> 111128 14:01:58 mysqld_safe Number of processes running now: 0111128 14:01:58 mysqld_safe mysqld restarted下面是error log:Version: '5.1.58-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution111128 14:01:58 - mysqld got signal 8 ;This could be because you hit a bug. It is also possible that this binaryor one of the libraries it was linked against is corrupt, improperly built,or misconfigured. This error can also be caused by malfunctioning hardware.We will try our best to scrape up some info that will hopefully help diagnosethe problem, but since we have already crashed, something is definitely wrongand this may fail.key_buffer_size=402653184read_buffer_size=2097152max_used_connections=1max_threads=151threads_connected=1It is possible that mysqld could use up tokey_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1013272 Kbytes of memoryHope that's ok; if not, decrease some variables in the equation.。。。紧急!请各位大侠帮忙
wedding520 Email Homepage
2011-12-27 15:51
ワイドネックライン、'V'ネックやボートネックラインは、一般にキャップスリーブとドレスのために選ばれている。ボートスタイルのネックラインと幅広いネックラインのためには、オフショルダーのキャップスリーブを追加することができます。オフショルダーのウエディングドレスは、数年以来、結婚式の摩耗で人気のパターンになっても流行で、今日でもあります。カラードレス 格安.のこのスタイルで素敵に見える模様のオフショルダーの袖に行く同じシースのサテンからステッチ/オフショルダーのキャップスリーブの上に薄いと飾ったときに裾フレアのビットとシースのサテンのイブニングドレスとスパゲッティネックラインがすごい見えます。
不懂就问
2011-12-28 12:06
1126 - Can't open shared library 'mysql-udf-http.so' (errno: 0 feature disabled)
折腾一天,配置的几台机器就一台出现这情况,郁闷啊,求教到底该怎么解决?
Walt123
2012-2-21 17:18
楼主内容赞一个 是否可以考虑做我们网站的特邀专家 数据库http://www.searchdatabase.com.cn/
Walt123
2012-2-21 17:18
楼主威武啊
ralph lauren uk Email Homepage
2012-3-3 13:36
Thinking silver and gold ornaments are a little bit ordinary? Why not try ralph lauren uk Jewelry. With charming colors and delicate shapes.Yesterday was my aunt and uncle's silver wedding anniversary. My uncle sent his wife a set of polo ralph lauren uk.
dr dre beats Email Homepage
2012-3-3 13:36
In particular, products from dr dre beats with leather design are fashionable, handmade,leading the wave of American pop.It with simple,durable and unique style to win consumers.Finished with the signature clasp, the classic and generous proportions and luxurious drapes make beats headphones so noble and famous.
beats by dre Homepage
2012-3-3 16:11
Different style bags from beats by dre are always eloquent statement outside a woman's attitude towards life and career. Thus, women's bag, must exchange the use of occasions, so they are not the same as showing different spatial and temporal rhythm of beauty.beats is universally renowned as a luxury brand. dre beats store offers customers the newest fashion collections which can make you different.
coach2outlet Email Homepage
2012-3-3 16:12
coach outlet handbags You can not just save your cash but also your time inside a convenient way to obtain the exact same bags product and do not be concerned the shippment of one's products.As our coach outlet online guarantees high quality for you, we have gained more customers'trust. The more they order, the more discounts they will enjoy.
Hogan Email Homepage
2012-5-10 17:50
In che possono posso individuare un fabbro uno dei più affidabili Greater London? Nel caso in cui si vogliono ottenere le aziende particolari del vostro fabbro Grande Londra quindi si sta andando in vista di un individuo Hogan che è totalmente certificata che è successo con fiducia. Nel caso in cui si va a vedere Locallocksmith24hour. corp. britannico sarete in grado di ottenere probabilmente il più efficace fabbro Grande Londra sta offrendo.
hogan280 Email
2012-5-10 18:04
Nella segnalazione del Rochdale "processo grooming" molto è stato fatto del fatto che gli uomini erano in gran parte pakistani, mentre Hogan le 47 vittime, anche se solo sette hanno fornito prove - erano tutti bianchi e classe operaia. Le vittime avevano un'età compresa tra 13 e 15 anni al momento dell'abuso, e la polizia li ha descritti come da "caotica" e "Consiglio tenuta" sfondi.
vivienne westwood sale Email Homepage
2012-6-11 23:42
<p>Westwood would be wired working in  Glossopdale, Derbyshire with your yourselves parents you're your shoemaker. she turned  <strong>vivienne westwood sale</strong>  upward with your staples yes fashion. you will be able would be  <strong>vivienne westwood bags</strong>  working in you the 1970's yet 1980's whilst she went to work you make jazz wear what to wear  <strong>Vivienne Westwood Jewellery</strong>  with your yourselves relationship with your  Malcolm McLaren.  ,Look more related about <strong>vivienne westwood sale</strong>:http://www.ukviviennewestwoodhandbags.com/ </br></p>
virginiag Email Homepage
2012-7-24 16:27
Birkenstock Sandals are become the best friends of more and more young people.Whether in the city or in the town,the comfortable and relax of Birkenstock Sandals always can bring the new and leisure way of life.So now pls also change the slipper,the Birkenstock Sandals is the new and best choice for you.Also you need choose the top quality of Birkenstock Sandals in this professional of Birkenstock Outlet online store.
分页: 4/6 第一页 上页 1 2 3 4 5 6 下页 最后页
发表评论
表情
emotemotemotemotemot
emotemotemotemotemot
emotemotemotemotemot
emotemotemotemotemot
emotemotemotemotemot
打开HTML
打开UBB
打开表情
隐藏
记住我
昵称   密码   游客无需密码
网址   电邮   [注册]