nodejs mysql 异步_Gearman + Nodejs + MySQL UDF异步实现 MySQL 到 Redis 的数据同步

1, 环境

CentOS, MySQL, Redis, Nodejs

Redis是一个开源的K-V内存数据库，它的key可以是string/set/hash/list/...，因为是基于内存的，所在访问速度相当快。

Gearman是一个开源的Map/Reduce分布式计算框架，具有丰富的client sdk，而且它支持MySQL UDF。

Gearman工作图

Gearman调用流程

Gearman集群

从图中可以看出貌似Gearman的集群功能比较弱，Job Server之间没啥关系好像。

4, MySQL - Redis配合使用方案

首先我们以MySQL数据为主，将insert/update/delete交给MySQL，而select交给redis；

当有数据发生变化时，通过MySQL Trigger实时异步调用Gearman的UDF提交一个job给Job Server，当job执行的时候会去更新redis；从而保证redis与MySQL中的数据是同步的。

4, 软件安装

安装gearman

//安装依赖

$ yum install -y boost-devel gperf libevent-devel libuuid-devel

//下载gearman

$ wget https://launchpad.net/gearmand/1.2/1.1.12/+download/gearmand-1.1.12.tar.gz

$ tar zxvf gearmand-1.1.12.tar.gz

//编译安装，指定mysqlclient的链接路径

$ ./configure LDFLAGS=-L/usr/lib64/mysql/

$ make

$ make install

//启动gearmand服务端

$ /usr/local/sbin/gearmand -l /var/log/gearmand.log -d

//查看是否安装成功，查看gearman版本

$ /usr/local/sbin/gearmand -V

gearman client

gearman client有很多种：PHP, Perl, Python, Ruby, Nodejs, Go, Java, C#...，我们选择GearmaNode(Nodejs库)作为实验用的gearman client

Nodejs的安装省略

GearmaNode的安装及测试

安装GearmaNode

//安装GearmaNode

$ npm install gearmanode

启动Gearman Job Server

//确保gearman的job server已经启动, job server默认监听4730端口

$ netstat -nplt | grep 4730

//如果没有，则启动job server(-d表示启动, -l指定log的位置)

$ /usr/local/sbin/gearmand -l /var/log/gearmand.log -d

worker.js (Gearman Job Worker)

var gearmanode = require('gearmanode');

var worker = gearmanode.worker();

//对job client传递过来的字符串执行reverse倒序操作

worker.addFunction('reverse', function (job) {

job.sendWorkData(job.payload); // mirror input as partial result

job.workComplete(job.payload.toString().split("").reverse().join(""));

});

//启动job worker

$ node worker.js

client.js (Gearman Job Client)

var gearmanode = require('gearmanode');

var client = gearmanode.client();

//提交job

var job = client.submitJob('reverse', 'hello world!');

job.on('workData', function(data) {

console.log('WORK_DATA >>> ' + data);

});

//结果回调函数

job.on('complete', function() {

console.log('RESULT >>> ' + job.response);

client.close();

});

//执行client.js

$ node client.js

//查看控制台打印输出

WORK_DATA >>> hello world!

RESULT >>> !dlrow olleh

可以看到gearman安装成功，运行正常。

5, MySQL UDF + Trigger同步数据到Gearman

安装lib_mysqludf_json

lib_mysqludf_json可以把MySQL表的数据以json数据格式输出

//下载lib_mysqludf_json

$ git clone https://github.com/mysqludf/lib_mysqludf_json.git

$ cd lib_mysqludf_json

//编译

$ gcc $(mysql_config --cflags) -shared -fPIC -o lib_mysqludf_json.so lib_mysqludf_json.c

//拷贝lib_mysqludf_json.so到MySQL的plugin目录

//可以登陆MySQL，输入命令"show variables like '%plugin%'"查看plugin位置

$ cp lib_mysqludf_json.so /usr/lib64/mysql/plugin/

//演示lib_mysqludf_json功能

$ mysql -uname -hhost -ppwd

//首先注册UDF函数

mysql> CREATE FUNCTION json_object RETURNS STRING

SONAME "lib_mysqludf_json.so";

//json_array|json_members|json_values函数注册方式与json_object一样.

mysql> use test;

mysql> select * from user_list;

+------+----------+

| NAME | PASSWORD |

+------+----------+

| troy | pwd |

+------+----------+

mysql> select json_object(name,password) as user from user_list;

+----------------------------------+

| user |

+----------------------------------+

| {"name":"troy","password":"pwd"} |

+----------------------------------+

安装gearman-mysql-udf

//下载

$ 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

$ cd gearman-mysql-udf-0.6

//安装libgearman-devel

$ yum install libgearman-devel

//编译安装

$ ./configure --with-mysql=/usr/bin/mysql_config --libdir=/usr/lib64/mysql/plugin/

$ make && make install

//登录MySQL注册UDF函数

mysql> CREATE FUNCTION gman_do_background RETURNS STRING

SONAME "libgearman_mysql_udf.so";

mysql> CREATE FUNCTION gman_servers_set RETURNS STRING

SONAME "libgearman_mysql_udf.so";

//函数gman_do|gman_do_high|gman_do_low|gman_do_high_background|gman_do_low_background|gman_sum注册方式类似，请参考gearman-mysql-udf-0.6/README

//指定gearman job server地址

mysql> SELECT gman_servers_set('127.0.0.1:4730');

如果出现异常信息：

ERROR 1126 (HY000): Can't open shared library 'libgearman_mysql_udf.so' (errno: 11 libgearman.so.8: cannot open shared object file: No such file or directory)

表示系统找不到 libgearman.so 文件，一般so都在/usr/local/lib目录下，修改配置文件/etc/ld.so.conf，将/usr/local/lib目录加入进去即可:

$ cat /etc/ld.so.conf

include ld.so.conf.d/*.conf

/usr/local/lib

$ /sbin/ldconfig -v | grep gearman*

MySQL Trigger调用Gearman UDF实现同步

DELIMITER $$

CREATE TRIGGER user_list_data_to_redis AFTER UPDATE ON user_list

FOR EACH ROW BEGIN

SET @ret=gman_do_background('syncToRedis', json_object(NEW.name as 'name', NEW.password as 'password'));

END$$

DELIMITER ;

Gearman Nodejs Worker将数据异步复制到redis

redis的安装步骤省略

启动redis-server

$ redis-server

安装redis的Nodejs client:

npm install hiredis redis

redis-worker.js (Gearman Nodejs worker)

var redis = require("redis");

var gearmanode = require('gearmanode');

var worker = gearmanode.worker();

//添加gearman函数syncToRedis

//当MySQL表记录更改时，此函数会被调用

worker.addFunction('syncToRedis', function (job) {

job.sendWorkData(job.payload);

console.log("-------job.payload: " + job.payload.toString());

//将字符串转换成json object, 然后调用更新redis

updateRedis(eval('(' + job.payload.toString() + ')'));

job.workComplete("Successed!");

});

//些函数只是简单的将MySQL表中的一行的记录按单个字段更新到redis中。可根据实际情况自行扩展

function updateRedis(json)

{

var client = redis.createClient();

client.on("error", function (err) {

console.log("Error " + err);

});

for(var key in json)

{

client.set(key, json[key], redis.print);

}

client.quit();

}

启动worder:

node redis-worker.js

更新MySQL

mysql> select * from user_list;

+------+----------+

| NAME | PASSWORD |

+------+----------+

| troy | jane |

+------+----------+

mysql> update user_list set name='hello', password='world';

redis-worker控制台输出：

-------job.payload: {"name":"hello","password":"world"}

查看redis数据

$ redis-cli

127.0.0.1:6379> keys *

1) "password"

2) "name"

127.0.0.1:6379> get name

"hello"

127.0.0.1:6379> get password

"world"

可以看到只要MySQL中user_list表的数据被update了，redis中的数据也会被更新。

6, 参考资料