Use Gearman to synchronize data from MySQL to Redis

Source: Internet
Author: User

Use Gearman to synchronize data from MySQL to Redis
For data that frequently changes, if you still use the traditional static cache method (Memocached, File System, and so on) to display data, there may be a huge overhead in cache access, but Redis, a memory-based NoSQL database, is very suitable for serving as a container for real-time data. However, we often have data reliability requirements. Using MySQL as the data storage will not cause data loss due to memory problems. At the same time, we can use the features of relational databases to achieve many functions. Therefore, it is natural to think about whether MySQL can be used as the data storage engine, while Redis can be used as the Cache. This requirement has not yet seen a particularly mature solution or tool. Therefore, this article will try to use a combination of Gearman + PHP + MySQL UDF for asynchronous data replication from MySQL to Redis.
Data Replication solution from MySQL to Redis
Both MySQL and Redis have their own data synchronization mechanisms. For example, the commonly used MySQL Master/Slave mode is implemented by the Slave end to analyze the binlog of the Master, such data replication is actually an asynchronous process, but when the server is on the same intranet, the asynchronous latency is almost negligible.

In theory, we can use the same method to analyze the binlog file of MySQL and insert data into Redis. However, this requires a deep understanding of the binlog file and MySQL. At the same time, because binlog has multiple forms of Statement/Row/Mixedlevel, the workload for analyzing binlog synchronization is very large.

Therefore, we chose a lower development cost method. By using a mature MySQL UDF, we first put MySQL Data into Gearman, and then use a self-compiled PHP Gearman Worker, synchronize data to Redis. It adds a lot of processes than the binlog analysis method, but the implementation cost is lower and easier to operate.
Installation and Use of Gearman
Gearman is a distributed task distribution framework. The design is concise and widely supported. A typical Gearman application includes the following parts:

Gearman Job Server: the core program of Gearman. It needs to be compiled and installed and run in the form of a daemon on the background Gearman Client: it can be understood as the task receiver, for example, if I want to execute a mail sending task in the background, I can call a Gearman Client in the program and input the mail information, and then I can immediately display the execution result to the user, the task itself runs slowly in the background. Gearman Worker: The real executor of a task. Generally, you need to write the specific logic and run it in the daemon mode. After receiving the task content transmitted by Gearman Client, Gearman Worker will process it in order.


We have previously introduced a similar background task processing project Resque. The two are actually very similar in design, which can be analogous:

Gearman Job Server: Gearman Client for Redis of Resque: Gearman Worker for Queue operation of Resque: Worker and Job for Resque

Here, we chose Gearman instead of Resque because Gearman provides a relatively useful MySQL UDF with less workload.



Install Gearman and PHP Gearman extensions

The following uses Ubuntu12.04 as an example.

apt-get install gearman gearman-server libgearman-dev

Check the running status of Gearman:

/etc/init.d/gearman-job-server status* gearmand is running

It indicates that Gearman has been installed successfully.


PHP's Gearman extension can be directly installed through pecl

pecl install gearmanecho "">/etc/php5/conf.d/gearman.iniservice php5-fpm restart

However, we found that the default gearman version installed on ubuntu is too low. Running pecl install gearman directly reports an error.

configure: error: libgearman version 1.1.0or later required

Therefore, it is recommended that you install the Gearman + PHP extension by compiling it. Here, for a simple description, select to install the earlier version extension:

pecl install gearman-1.0.3

Gearman + PHP instanceTo make it easier to understand the subsequent running process of Gearman, we may explain it from a simplest Gearman instance. For example, we want to perform a file processing operation, first, write a Gearman Client and name it client. php:

<? Php $ client = newGearmanClient (); $ client-> addServer (); $ client-> doBackground ('writelog', 'Log content '); echo 'file operations in the backend ';

Running this file is equivalent to simulating a user request for a Web page and returning the processed information to the user:

php client.php

Check the Gearman status:

(echo status ; sleep 0.1)| netcat127.0.0.14730

The output is

writeLog        100.

It indicates that a task named writeLog has been created in Gearman, and one task is waiting in the queue.

The above four columns represent the running status of the current Gearman respectively:


Synchronize data to Gearman through MySQL UDF + TriggerThe best way for MySQL to communicate with external programs is through MySQL UDF (MySQL user defined functions. To allow MySQL to pass data into Gearman, lib_mysqludf_json and gearman-mysql-udf are used here.Install lib_mysqludf_jsonThe reason for using lib_mysqludf_json is that Gearman only accepts strings as entry parameters. You can use lib_mysqludf_json to encode MySQL Data as JSON strings.

apt-get install libmysqlclient-devwget master.zipcd lib_mysqludf_json-master/rm lib_mysqludf_json.sogcc $(mysql_config --cflags)-shared -fPIC -o lib_mysqludf_json.c

You can see that the file is re-compiled and generated. You need to check the MySQL plug-in installation path:

mysql -u root -pPASSWORD --execute="show variables like '%plugin%';"+---------------+------------------------+|Variable_name|Value|+---------------+------------------------+| plugin_dir    |/usr/lib/mysql/plugin/|+---------------+------------------------+

Copy the file to the corresponding location:

cp /usr/lib/mysql/plugin/

Finally, log on to MySQL and run the statement to register the UDF:


Install gearman-mysql-udf

The methods are almost the same:

apt-get install libgearman-devwget -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/make && make install

Log on to MySQL and run the statement to register the UDF:


Finally, specify the information of the Gearman Server:

SELECT gman_servers_set('');

Data synchronization using MySQL triggers

The data to be synchronized and the synchronization conditions must be determined based on the actual situation. For example, if I want to synchronize the data in the data table during each update, write the Trigger as follows:

DELIMITER $$CREATE TRIGGER datatoredis AFTER UPDATE ON data  FOR EACH ROW BEGIN    SET @ret=gman_do_background('syncToRedis', json_object( as`id`, NEW.volume as`volume`));END$$DELIMITER ;

Update a piece of data in the database to check whether Gearman has taken effect.


Gearman PHP Worker asynchronously copies MySQL Data to RedisRedis, as a popular NoSQL cache solution, does not need to be described too much. Its installation and use are also very simple:

apt-get install redis-server pecl install redisecho "">/etc/php5/conf.d/redis.ini

Then write a Gearman Worker: redis_worker.php

#!/usr/bin/env php<?$worker =newGearmanWorker();$worker->addServer();$worker->addFunction('syncToRedis','syncToRedis');$redis =newRedis();$redis->connect('',6379);while($worker->work());function syncToRedis($job){global $redis;        $workString = $job->workload();        $work = json_decode($workString);if(!isset($work->id)){returnfalse;}        $redis->set($work->id, $workString);}

Finally, you need to run the Worker in the background:

nohup php redis_worker.php &

By copying MySQL Data to Redis in this way, the Worker test can be completed instantly.

Task Name: The Worker process in which the task in the waiting queue is running

You can use watch for real-time monitoring:

watch -n 1"(echo status; sleep 0.1) | nc 4730"

Then we need to write a Gearman Worker named worker. php:

<?php$worker =newGearmanWorker();$worker->addServer();$worker->addFunction('writeLog','writeLog');while($worker->work());function writeLog($job){        $log = $job->workload();file_put_contents(__DIR__ .'/gearman.log', $log ."\n", FILE_APPEND | LOCK_EX);}

Worker uses a while endless loop to implement daemon and run

php worker.php

You can see that the Gearman status changes:

writeLog        001


View gearman. log in the same directory. The content should be the value Log content passed in from the Client.

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