MySQL 與 Redis 同步實戰：基於 Gearman UDF 觸發器架構

MySQL 與 Redis 同步實戰：基於 Gearman UDF 觸發器架構

本文記錄在 Ubuntu Kylin 14.04 64bit 環境下，利用 Gearman、MySQL UDF 及觸發器實現 MySQL 數據異步同步至 Redis 的完整流程。

1. 環境準備與元件安裝

預先安裝：MySQL、Redis、PHP、lib_mysqludf_json.so、Gearman。

1.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

1.2 註冊 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";

1.3 指定 Gearman 服務地址

SELECT gman_servers_set('127.0.0.1:4730');

1.4 快速驗證 UDF 連通性

編寫簡單的 PHP Worker (reverse.php) 反轉字串：

<?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;
SELECT gman_do("reverse", password) AS test FROM Users;

觀察回傳結果，確認 MySQL 作為 Client 成功調用 Gearman Worker。

進階測試優先級：gman_do_high（高）、gman_do_background（背景低優先級，回傳作業號）。

2. 建立同步至 Redis 的 Worker

停止 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);
}

2.1 MySQL 測試同步

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

Redis 監控與查詢均確認數據已寫入。

3. 建立觸發器實現自動同步

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 ;

3.1 驗證觸發器生效

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

Redis 即時收到更新數據。

3.2 生產部署建議

Worker 以 nohup php syncToRedis.php & 方式在後台常駐運行。