Mysql Synchronization Practice with Redis

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.