Synchronization practices between Mysql and Redis and mysqlredis

Synchronization practices between Mysql and Redis and mysqlredis
I. testing environment in Ubuntu kylin 14.04 64bit

You have installed Mysql, Redis, php, lib_mysqludf_json.so, and Gearman.
Click here to view the test database and Table reference
Some basic operations are also described in this article.

1. Install and install 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

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

Specify the information of the Gearman Server:

SELECT gman_servers_set('127.0.0.1:4730');

Example:

Create a worker for reverse. php with reference to the 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());}?>

Run the php reverse. php Command.

To mysql, enter:

SELECT gman_do ("reverse", 'abcdef ') AS test FROM Users; --- FROM Users can also be left blank.

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

The output result is displayed. The password column is processed by the worker of the reverse. mysql acts as the client:

You can also enter the following command to test:

SELECT gman_do_high ("reverse", password) AS test FROM Users; -- high priority

SELECT gman_do_background ("reverse", password) AS test FROM Users; -- the background priority is low, and the host and job number are returned.

Create a syncToRedis job

Stop the previous reverse worker and create 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 in mysql:

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

If redis monitoring is enabled, you can see that redis has received the data:

Redis query results

2. Create a trigger

    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 ;

Run the SQL statement test:

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

In normal use, you can use and set worker as a background task:
Nohup php syncToReids. php &