一、测试环境在Ubuntu kylin 14.04 64bit
已经安装MySQL、Redis、php、lib_mysqludf_json.so、Gearman。
点击这里查看测试数据库及表参考
本文也有些基本操作，在之前文章里有介绍。
1、安装
安装gearman-mysql-udf
apt-get install libgearman-dev
wget https://launchpad.net/gearman-mysql-udf/trunk/0.6/+download/gearman-mysql-udf-0.6.tar.gz
tar -xzf gearman-mysql-udf-0.6.tar.gz
cd gearman-mysql-udf-0.6
./configure --with-mysql=/usr/bin/mysql_config --libdir=/usr/lib/mysql/plugin/
make
sudo make install
注册UDF函数：
CREATE FUNCTION gman_do_background RETURNS STRING SONAME 'libgearman_mysql_udf.so';
CREATE FUNCTION gman_servers_set RETURNS STRING SONAME 'libgearman_mysql_udf.so';
CREATE FUNCTION gman_do RETURNS STRING SONAME "libgearman_mysql_udf.so";
CREATE FUNCTION gman_do_high RETURNS STRING SONAME "libgearman_mysql_udf.so";
CREATE FUNCTION gman_do_low RETURNS STRING SONAME "libgearman_mysql_udf.so";
CREATE FUNCTION gman_do_background RETURNS STRING SONAME "libgearman_mysql_udf.so";
CREATE FUNCTION gman_do_high_background RETURNS STRING SONAME "libgearman_mysql_udf.so";
CREATE FUNCTION gman_do_low_background RETURNS STRING SONAME "libgearman_mysql_udf.so";
CREATE AGGREGATE FUNCTION gman_sum RETURNS INTEGER SONAME "libgearman_mysql_udf.so";
CREATE FUNCTION gman_servers_set RETURNS STRING SONAME "libgearman_mysql_udf.so";
指定Gearman服务器的信息：
SELECT gman_servers_set('127.0.0.1:4730');
使用示例：
参照http://blog.csdn.net/xundh/article/details/46287681 建立一个reverse.php的worker
<?php
$worker= new GearmanWorker();
$worker->addServer();
$worker->addFunction("reverse", "my_reverse_function");
while ($worker->work());
function my_reverse_function($job){
return strrev($job->workload());
}
?>
输入命令php reverse.php运行。
到mysql里，输入：
SELECT gman_do("reverse",'abcdef') AS test FROM Users; ---FROM Users也可以不带。
SELECT gman_do("reverse", password) AS test FROM Users;
可以看到输出结果，其中password列已经被reverse的worker处理，mysql这时充当client端：
还可以输入以下命令测试：
SELECT gman_do_high("reverse", password) AS test FROM Users; --高优先权
SELECT gman_do_background("reverse", password) AS test FROM Users; --后台低优先权，返回主机和作业号。
创建syncToRedis作业
停止前面的reverse worker，建立一个syncToRedis.php
<code class="language-php hljs has-numbering" style="display: block; padding: 0px; color: inherit; box-sizing: border-box; font-family: 'Source Code Pro', monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal; background: transparent;"> <span class="hljs-preprocessor" style="color: rgb(68, 68, 68); box-sizing: border-box;"><?php</span>
ini_set(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'default_socket_timeout'</span>, -<span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span>); <span class="hljs-comment" style="color: rgb(136, 0, 0); box-sizing: border-box;">//很重要，否则可能报RedisException with message read error on connection ...</span>
<span class="hljs-variable" style="color: rgb(102, 0, 102); box-sizing: border-box;">$worker</span> = <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">new</span> GearmanWorker();
<span class="hljs-variable" style="color: rgb(102, 0, 102); box-sizing: border-box;">$worker</span>->addServer();
<span class="hljs-variable" style="color: rgb(102, 0, 102); box-sizing: border-box;">$worker</span>->addFunction(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'syncToRedis'</span>, <span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'syncToRedis'</span>);
<span class="hljs-variable" style="color: rgb(102, 0, 102); box-sizing: border-box;">$redis</span> = <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">new</span> Redis();
<span class="hljs-variable" style="color: rgb(102, 0, 102); box-sizing: border-box;">$redis</span>->connect(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'127.0.0.1'</span>, <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">6379</span>);
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">echo</span>(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"begin:\n"</span>);
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">while</span>(<span class="hljs-variable" style="color: rgb(102, 0, 102); box-sizing: border-box;">$worker</span>->work());
<span class="hljs-function" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">function</span> <span class="hljs-title" style="box-sizing: border-box;">syncToRedis</span><span class="hljs-params" style="color: rgb(102, 0, 102); box-sizing: border-box;">(<span class="hljs-variable" style="box-sizing: border-box;">$job</span>)</span>
{</span>
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">global</span> <span class="hljs-variable" style="color: rgb(102, 0, 102); box-sizing: border-box;">$redis</span>;
<span class="hljs-variable" style="color: rgb(102, 0, 102); box-sizing: border-box;">$workString</span> = <span class="hljs-variable" style="color: rgb(102, 0, 102); box-sizing: border-box;">$job</span>->workload();
<span class="hljs-variable" style="color: rgb(102, 0, 102); box-sizing: border-box;">$work</span> = json_decode(<span class="hljs-variable" style="color: rgb(102, 0, 102); box-sizing: border-box;">$workString</span>);
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">echo</span>(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'get value:'</span>);
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">echo</span>(<span class="hljs-variable" style="color: rgb(102, 0, 102); box-sizing: border-box;">$workString</span>);
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">echo</span>(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"\n"</span>);
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">echo</span>(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">'json_decode:'</span>);
var_dump(<span class="hljs-variable" style="color: rgb(102, 0, 102); box-sizing: border-box;">$work</span>);
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">echo</span>(<span class="hljs-string" style="color: rgb(0, 136, 0); box-sizing: border-box;">"\n"</span>);
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">if</span>(!<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">isset</span>(<span class="hljs-variable" style="color: rgb(102, 0, 102); box-sizing: border-box;">$work</span>->user_id)){
<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">return</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">false</span>;
}
<span class="hljs-variable" style="color: rgb(102, 0, 102); box-sizing: border-box;">$redis</span>->set(<span class="hljs-variable" style="color: rgb(102, 0, 102); box-sizing: border-box;">$work</span>->user_id, <span class="hljs-variable" style="color: rgb(102, 0, 102); box-sizing: border-box;">$workString</span>);
}</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li><li style="box-sizing: border-box; padding: 0px 5px;">19</li><li style="box-sizing: border-box; padding: 0px 5px;">20</li><li style="box-sizing: border-box; padding: 0px 5px;">21</li><li style="box-sizing: border-box; padding: 0px 5px;">22</li><li style="box-sizing: border-box; padding: 0px 5px;">23</li><li style="box-sizing: border-box; padding: 0px 5px;">24</li><li style="box-sizing: border-box; padding: 0px 5px;">25</li><li style="box-sizing: border-box; padding: 0px 5px;">26</li><li style="box-sizing: border-box; padding: 0px 5px;">27</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right-width: 1px; border-right-style: solid; border-right-color: rgb(221, 221, 221); list-style: none; text-align: right; background-color: rgb(238, 238, 238);"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li><li style="box-sizing: border-box; padding: 0px 5px;">9</li><li style="box-sizing: border-box; padding: 0px 5px;">10</li><li style="box-sizing: border-box; padding: 0px 5px;">11</li><li style="box-sizing: border-box; padding: 0px 5px;">12</li><li style="box-sizing: border-box; padding: 0px 5px;">13</li><li style="box-sizing: border-box; padding: 0px 5px;">14</li><li style="box-sizing: border-box; padding: 0px 5px;">15</li><li style="box-sizing: border-box; padding: 0px 5px;">16</li><li style="box-sizing: border-box; padding: 0px 5px;">17</li><li style="box-sizing: border-box; padding: 0px 5px;">18</li><li style="box-sizing: border-box; padding: 0px 5px;">19</li><li style="box-sizing: border-box; padding: 0px 5px;">20</li><li style="box-sizing: border-box; padding: 0px 5px;">21</li><li style="box-sizing: border-box; padding: 0px 5px;">22</li><li style="box-sizing: border-box; padding: 0px 5px;">23</li><li style="box-sizing: border-box; padding: 0px 5px;">24</li><li style="box-sizing: border-box; padding: 0px 5px;">25</li><li style="box-sizing: border-box; padding: 0px 5px;">26</li><li style="box-sizing: border-box; padding: 0px 5px;">27</li></ul>
在mysql里测试一下：
SELECT gman_do("syncToRedis", json_object(user_id as user_id,password as password)) AS test FROM Users;
如果redis监控是打开的，可以看到redis已经收到了数据：
redis查询结果
2、建立触发器
DELIMITER $$
CREATE TRIGGER datatoredis AFTER UPDATE ON Users
FOR EACH ROW BEGIN
SET @ret=gman_do_background('syncToRedis', json_object(NEW.user_id as `user_id`, NEW.email as `email`,NEW.display_name as `display_name`,NEW.password as `password`));
END$$
DELIMITER ;
执行SQL语句测试：
insert into Users values('8','new','3','hello');
update Users set email='new8@qq.com' where user_id=8;
正常使用时，可以把worker使用&设置为后台任务：
nohup php syncToReids.php &
其它问题
有的时候，命令行执行php语句时可能会提示下面的错误，
[root@iZ230296jm2Z redis]# php syncToRedis.php PHP Fatal error: Class ‘Redis’ not found in /home/wwwroot/default/youai/php/redis/syncToRedis.php on line 6
处理方法：
运行php命令和apache或ngnix可能使用了不同的php.ini，即使在php.ini里看到加载了redis，还需要在php命令行使用php.ini里也增加Redis模块。
查看ngnix使用的php.ini位置使用
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
