Mysql 與Redis的同步實踐，mysqlredis同步

Mysql 與Redis的同步實踐，mysqlredis同步
一、測試環境在Ubuntu kylin 14.04 64bit

已經安裝Mysql、Redis、php、lib_mysqludf_json.so、Gearman。
點擊這裡查看測試資料庫及表參考
本文也有些基本操作，在之前文章裡有介紹。

1、安裝安裝gearman-mysql-udf

apt-get install libgearman-devwget https://launchpad.net/gearman-mysql-udf/trunk/0.6/+download/gearman-mysql-udf-0.6.tar.gztar -xzf gearman-mysql-udf-0.6.tar.gzcd gearman-mysql-udf-0.6./configure --with-mysql=/usr/bin/mysql_config --libdir=/usr/lib/mysql/plugin/makesudo 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

<?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裡測試一下：

    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 &