使用MySQL UDFs来调用gearman分布式任务分发系统

  • A+
所属分类:Gearman

当向表插入数据的时候,触发执行某些任务

安装gearman参加:http://www.ttlsa.com/html/663.html
一.安装gearman-mysql-udf
# apt-get install libmysql++-dev
# wget https://launchpad.net/gearman-mysql-udf/trunk/0.6/+download/gearman-mysql-udf-0.6.tar.gz
# tar zxvf gearman-mysql-udf-0.6.tar.gz
# ./configure  --with-mysql=/usr/bin/mysql_config --libdir=/usr/lib/mysql/plugin
# make
# make install
# mysql
[codesyntax lang="sql"]
mysql> CREATE FUNCTION gman_do RETURNS STRING
SONAME "libgearman_mysql_udf.so";
mysql> CREATE FUNCTION gman_do_high RETURNS STRING
SONAME "libgearman_mysql_udf.so";
mysql> CREATE FUNCTION gman_do_low RETURNS STRING
SONAME "libgearman_mysql_udf.so";
mysql> CREATE FUNCTION gman_do_background RETURNS STRING
SONAME "libgearman_mysql_udf.so";
mysql> CREATE FUNCTION gman_do_high_background RETURNS STRING
SONAME "libgearman_mysql_udf.so";
mysql> CREATE FUNCTION gman_do_low_background RETURNS STRING
SONAME "libgearman_mysql_udf.so";
mysql> CREATE AGGREGATE FUNCTION gman_sum RETURNS INTEGER
SONAME "libgearman_mysql_udf.so";
mysql> CREATE FUNCTION gman_servers_set RETURNS STRING
SONAME "libgearman_mysql_udf.so";
mysql> SELECT gman_servers_set("192.168.1.60:4730,192.168.1.60:4731") as gman_servers;  //设置gearman server
+-------------------------------------+
| gman_servers                        |
+-------------------------------------+
| 192.168.1.60:4730,192.168.1.60:4731 |
+-------------------------------------+
mysql> create table udf_test(
-> id int unsigned auto_increment primary key,
-> val varchar(20) not null);  //新建表
mysql> create trigger sendmail before insert on udf_test for each row set @return=gman_do_background('MAIL','undef');  //创建触发器,当向表udf_test插入数据时候,执行任务。
[/codesyntax]
# perl -MCPAN -e shell
cpan> install Gearman::Worker  //安装Gearman::Worker模块
cpan> install Mail::SendEasy   //安装Mail::SendEasy模块
# vi WORKER_SENDMAIL.pl   //创建worker任务
[codesyntax lang="perl"]
use strict;
use Mail::SendEasy ;
use v5.10;
use Gearman::Worker;

my $worker=new Gearman::Worker;
$worker->job_servers('192.168.1.60:4730');
$worker->register_function(MAIL=>\&sendmail);
$worker->work while 1;

sub sendmail{
my $job=shift;
my $date=localtime;

my $mail = new Mail::SendEasy(
smtp => 'smtp.ttlsa.com' ,
user => 'service@ttlsa.com' ,
pass => '******',
);

print "$date\n";
my $status = $mail->send(
from    => 'service@ttlsa.com' ,
from_title => 'ttlsa' ,
to      => 'xxxxx@qq.com' ,
subject => "MAIL Test $date" ,
msg     => "$date" ,
html    => "<b>test $date</b>" ,
);
if (!$status) { print $mail->error ;}
}
[/codesyntax]
# perl WORKER_SENDMAIL.pl &
# perl WORKER_SENDMAIL.pl &  //在后台运行两个worker

二.测试
1.向udf_test表插入数据
mysql> insert into udf_test value ('','a');
2.查看是否收到邮件

gearman-mysql-udf

三.Gearman server信息
# telnet 192.168.1.60 4730
Trying 192.168.1.60...
Connected to 192.168.1.60.
Escape character is '^]'.
status
MAIL    0       0       2
说明:第一列worker名称;第二列jobs执行数量;第三列jobs队列数量;第四列可用worker数量

weinxin
微信公众号
扫一扫关注运维生存时间公众号,获取最新技术文章~
默北

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

目前评论:6   其中:访客  5   博主  0   引用   1

    • 骑行318 9

      请问博主俺在俺装gearman-mysql-udf插件(版本是0.6)配置总是报错
      俺的配置参数:./configure –enable-tcmalloc –with-mysql=/usr/local/mysql5.6.11/bin/mysql_config –libdir=/usr/local/mysql5.6.11/lib/plugin/
      configure: error: At least version 0.33 of libgearman is required for gearman-mysql-udf
      俺yum安装了libgearman-devel,只能安装0.28版本的
      后来换成0.5版本的插件同样报错
      configure: error: At least version 0.10 of libgearman is required for gearman-mysql-udf
      俺先安装gearman1.1.19,成功也能工作
      找不到是什么原因一直configure失败,博主知道这是什么原因吗?感激不尽

        • 默北 默北 6

          @骑行318 版本过低

            • 骑行318 9

              @默北 libgearman-devel-0.28-3.fc17.x86_64俺yum安装的版本0.28
              按理说gearman-mysql-udf换成0.5版本的就可以安装了阿,
              它还是提示configure: error: At least version 0.10 of libgearman is required for gearman-mysql-udf,俺的系统是fedora64位的机器,都折腾几天啦,都每搞定,请问能手动改configure的libgearman库位置吗?

              • 骑行318 9

                @默北 问题搞定了,就是没弄明白–with-libgearman-prefix设置这个参数无用,最后看config.log发现没有gearman.h文件,ln -s libgearman-1.0 /usr/include,这才安装成功

          • 来自外部的引用: 1

            • MySQL触发器应用