Mysql Synchronization Practice with Redis

First, test environment in Ubuntu Kylin 14.04 64bit

MySQL, Redis, PHP, lib_mysqludf_json.so, Gearman have been installed.
Click here to view the test database and table reference
This article also has some basic operations, which are described in the previous article.

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

To register the UDF function:

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";

Specify information for the Gearman server:

SELECT gman_servers_set(‘127.0.0.1:4730‘);

Examples of Use:

Establish a reverse.php worker with reference to http://blog.csdn.net/xundh/article/details/46287681

<?php$worker= new GearmanWorker();$worker->addServer();$worker->addFunction("reverse", "my_reverse_function");while ($worker->work());function my_reverse_function($job){  return strrev($job->workload());}?>

Enter the command PHP reverse.php to run.

Into MySQL, enter:

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

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

You can see the output, where the password column has been processed by the reverse worker, and MySQL acts as the client side:

You can also enter the following command test:

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

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

Create a Synctoredis job

Stop the front reverse worker and build a 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);}

Test it in MySQL:

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

If Redis monitoring is turned on, you can see that Redis has received the data:

Redis Query Results

2. Setting up triggers

    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 ;

Execute the SQL statement test:

insert into Users values(‘8‘,‘new‘,‘3‘,‘hello‘);update Users set email=‘[email protected]‘ where user_id=8;

When working normally, you can set the worker to use & as a background task:
Nohup PHP synctoreids.php &

Mysql Synchronization Practice with Redis