Mysql与Redis的同步实践

一、测试环境在Ubuntu kylin 14.04 64bit

已经安装Mysql、Redis、php、lib_mysqludf_json.so、Gearman。 
点击这里查看测试数据库及表参考 
本文也有些基本操作，在之前文章里有介绍。

1、安装

mysql-udf">安装gearman-mysql-udf

?

1 2 3 4 5 6 7

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函数：

?

1 2 3 4 5 6 7 8 9 10

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服务器的信息：

?

1	SELECT gman_servers_set( '127.0.0.1:4730' );

使用示例：

参照http://blog.csdn.net/xundh/article/details/46287681 建立一个reverse.php的worker

?

1 2 3 4 5 6 7 8 9 10

<?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里，输入：

?

1	SELECT gman_do( "reverse" , 'abcdef' ) AS test FROM Users; ---FROM Users也可以不带。

?

1	SELECT gman_do( "reverse" , password ) AS test FROM Users;

可以看到输出结果，其中password列已经被reverse的worker处理，mysql这时充当client端：

还可以输入以下命令测试：

?

1	SELECT gman_do_high( "reverse" , password ) AS test FROM Users; --高优先权

?

1	SELECT gman_do_background( "reverse" , password ) AS test FROM Users; --后台低优先权，返回主机和作业号。

创建syncToRedis作业

停止前面的reverse worker，建立一个syncToRedis.php

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

<?php $worker = new GearmanWorker(); $worker->addServer(); $worker->addFunction( 'syncToRedis' , 'syncToRedis' );   $redis = new Redis(); $redis-> connect ( '127.0.0.1' , 6379); echo( "begin:\n" );   while($worker-> work ()); function syncToRedis($job) {          global $redis;          $workString = $job->workload();          $ work = json_decode($workString);      echo( 'get value:' );      echo($workString);      echo( "\n" );      echo( 'json_decode:' );      var_dump($ work );      echo( "\n" );          if(!isset($ work ->user_id)){                  return false ;          }          $redis-> set ($ work ->user_id, $workString); }

在mysql里测试一下：

?

1	SELECT gman_do( "syncToRedis" , json_object(user_id as user_id, password as password )) AS test FROM Users;

如果redis监控是打开的，可以看到redis已经收到了数据： 

redis查询结果 

2、建立触发器

?

1 2 3 4 5 6

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语句测试：

?

1 2	insert into Users values ( '8' , 'new' , '3' , 'hello' ); update Users set email= 'new8@qq.com' where user_id=8;

正常使用时，可以把worker使用&设置为后台任务： 
nohup php syncToReids.php &