Use Gearman to synchronize data from MySQL to Redis

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: core program of Gearman, which needs to be compiled and installed and run in the background as a daemon
• Gearman Client: it can be understood as the recipient of the task. 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, then, the execution result is immediately displayed to the user, and 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: Redis part of Resque
• Gearman Client: the Queue operation corresponding to the Resque
• Gearman Worker: Worker and Job corresponding to 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 "extension=gearman.so">/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 instance
To 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:

1. Task Name
2. Waiting for tasks in the queue
3. Running task
4. Running Worker Process

You can use watch for real-time monitoring:

watch -n 1"(echo status; sleep 0.1) | nc 127.0.0.1 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 passed in from the Client.Log content.

Synchronize data to Gearman through MySQL UDF + Trigger
The 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_json
The 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 https://github.com/mysqludf/lib_mysqludf_json/archive/master.zipunzip master.zipcd lib_mysqludf_json-master/rm lib_mysqludf_json.sogcc $(mysql_config --cflags)-shared -fPIC -o lib_mysqludf_json.so lib_mysqludf_json.c

You can see that the lib_mysqludf_json.so 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 lib_mysqludf_json.so file to the corresponding location:

cp lib_mysqludf_json.so /usr/lib/mysql/plugin/

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

CREATE FUNCTION json_object RETURNS STRING SONAME 'lib_mysqludf_json.so';

Install gearman-mysql-udf
The methods are almost the same:

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/make && make install

Log on to MySQL and run the statement to register the 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';

Finally, specify the information of the Gearman Server:

SELECT gman_servers_set('127.0.0.1:4730');

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(NEW.id 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 Redis
Redis, 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 "extension=redis.so">/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('127.0.0.1',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.

Install and test Redis in Ubuntu 14.04

Redis cluster details

Install Redis in Ubuntu 12.10 (graphic explanation) + Jedis to connect to Redis

Redis series-installation, deployment, and maintenance

Install Redis in CentOS 6.3

Learning notes on Redis installation and deployment

Redis. conf

Redis details: click here
Redis: click here

From: http://avnpc.com/pages/mysql-replication-to-redis-by-gearman%20

This article permanently updates the link address: