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, install mysql-udf "> Install gearman-mysql-udf?

1234567

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

Register UDF function:?

12345678910

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 the information for the Gearman server:?

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

?

12345678910

<?php $worker = new Gearmanworker (); $worker->addserver (); $worker->addfunction ( "my_reverse_function" while ($worker-> work ());  function my_reverse_function ($job) { &NBSP;&NBSP; return strrev ($ Job->workload ()); ?>

Enter the command PHP reverse.php to run.

Into MySQL, enter:

?

1	SELECTgman_do("reverse",‘abcdef‘) AS test FROMUsers; ---FROM Users也可以不带。

?

1	SELECTgman_do("reverse", password) AS test FROMUsers;

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:

?

1	SELECTgman_do_high("reverse", password) AS test FROMUsers; --高优先权

?

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

Create a Synctoredis job

Stop the front reverse worker and build a synctoredis.php

?

1234567891011121314151617181920212223242526

<?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:

?

1	SELECTgman_do("syncToRedis", json_object(user_id as user_id,password as password)) AS test FROMUsers;

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

Redis Query Results

2. Create a trigger?

123456

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:

?

12	insertinto Users values(‘8‘,‘new‘,‘3‘,‘hello‘);update Users set email=‘[email protected]‘ whereuser_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