MySQL-to-redis data synchronization via Gearman

Known as one of the most popular open source database, MySQL is widely used in various scenarios, ApsaraDB RDS for MySQL of Alibaba Cloud provided critical relief for companies, especially small and medium-sized enterprises affected by novel coronavirus (COVID-19).

For data with very fast changes, if you also choose traditional static cache mode (memocached, File system, etc.) to display the data, it may be very expensive to cache access, and does not meet the needs, and Redis, a memory-based NoSQL database, is ideal for serving as a container for real-time data.

But often we have data reliability requirements, using MySQL as a data storage, not because of memory problems caused by data loss, but also can take advantage of the characteristics of the relational database to achieve a lot of functions.

So it would be natural to think of MySQL as the data storage engine, and Redis as the cache. This requirement has not yet seen a particularly sophisticated solution or tool, so this article will attempt to use the combination of Gearman+php+mysql UDF asynchronous implementation of MySQL to Redis data replication.

MySQL to Redis data replication scenario
Whether MySQL or Redis, itself with the mechanism of data synchronization, like the more commonly used MySQL Master/slave mode, is the slave-side analysis of Master Binlog to achieve, such data replication is actually an asynchronous process, Only when the server is in the same intranet, the asynchronous delay can almost be ignored.

So theoretically we can also analyze MySQL's Binlog file and insert data into Redis in the same way. However, this requires a very deep understanding of binlog files as well as MySQL, and because Binlog exists statement/row/mixedlevel many forms, it is very important to analyze binlog to achieve synchronization.

So here's a cheaper way to borrow the more mature MySQL UDF, put the MySQL data into Gearman first, and then sync the data to Redis with a PHP Gearman Worker that you write. There are many more processes than the analysis of Binlog, but the implementation costs are lower and easier to operate.
Installation and use of Gearman

Gearman is a distributed task-distribution framework. The design is simple and has been widely supported. A typical Gearman application consists of the following sections:

• Gearman Job Server:gearman Core program, need to compile and install and run in daemon form in the background
• Gearman Client: Can be understood as the task of the recipient, such as I want to perform a mail in the background to send a task, you can call a Gearman Client in the program and incoming message information, and then you can immediately display the results of the execution to the user, and the task itself will slowly run in the background.
• Gearman worker: The real performer of the task, generally need to write their own specific logic and run through the daemon process, Gearman Worker receives Gearman client to pass the task content, will be processed sequentially.

A similar background task processing project Resque has been described previously. The design of the two is actually very close, the simple analogy is:

• Gearman Job Server: Redis section for Resque
• Gearman Client: queue operation corresponding to Resque
• Gearman worker: Worker and job corresponding to Resque

The reason for choosing Gearman instead of Resque is that Gearman provides a more useful MySQL UDF with less work.

Installing Gearman and PHP Gearman extensions
The following are examples of Ubuntu12.04.

Apt-get install Gearman gearman-server Libgearman-dev      

Check the health of the Gearman:

/etc/init.  D/Gearman-Job-Server Status*is running      

Description Gearman has been installed successfully.
PHP Gearman extensions can be installed directly via the PECL

 
 
pecl install gearman
echo "extension=gearman.so">/etc/php5/conf.d/gearman.ini
service php5-fpm restart

But the actual test found that Ubuntu default installation of Gearman version is too low, directly run Pecl install Gearman will be error

Configure: error:1.1.  0or later required       

Therefore, the Gearman + PHP extension is recommended to be installed by compiling, here for a simple explanation, choose to install the old version of the extension:

PECL Install Gearman-1.0.  3   

Gearman + PHP Instance
To make it easier to understand the running flow of the post Gearman, here's a simple example of a Gearman instance, such as we're going to do a file-handling operation, first writing a Gearman client and naming client.php:

<?php
$client =newGearmanClient();
$client->addServer();
$client->doBackground(‘writeLog‘,‘Log content‘);
Echo ‘the file is already operating in the background ‘; 

Running this file is equivalent to impersonating the user requesting a Web page to return the processed information to the user:

PHP client.  PHP 

Take a look at the status of Gearman:

(; 0.1) | netcat127. 0.  0.14730     

You can see the output as

Writelog 100.   

Indicates that we have established a task named Writelog in Gearman, and there is a task waiting in the queue.
The above 4 columns represent the current Gearman's running state:

1. Task Name
2. Tasks in the wait queue
3. Tasks that are running
4. Worker processes that are running

You can use watch for real-time monitoring:

-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);}

The worker uses a while dead loop to implement the daemon, running

PHP worker.  PHP 

You can see that the Gearman status changes to:

Writelog 001  

While viewing the same directory under Gearman.log, the content should be the value passed in from the clientLog content.

Synchronizing data to Gearman via MySQL UDF + trigger
The best way for MySQL to interoperate with external programs is through MySQL UDF (MySQL user defined functions). In order for MySQL to pass data into Gearman, a combination of Lib_mysqludf_json and gearman-mysql-udf is used here.

Installing Lib_mysqludf_json
The reason for using Lib_mysqludf_json is that because Gearman only accepts strings as entry parameters, you can encode data in MySQL as a JSON string by Lib_mysqludf_json

 
apt-get install libmysqlclient-dev
wget https://github.com/mysqludf/lib_mysqludf_json/archive/master.zip
unzip master.zip
cd lib_mysqludf_json-master/
rm lib_mysqludf_json.so
gcc $(mysql_config --cflags)-shared -fPIC -o lib_mysqludf_json.so lib_mysqludf_json.c

You can see that the lib_mysqludf_json.so file was generated by recompiling, and you need to look at the MySQL plugin installation path:

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

Then copy the lib_mysqludf_json.so file to the corresponding location:

CP Lib_mysqludf_json.  /usr/lib/mysql/plugin/         

Finally, log in to the MySQL run statement to register the UDF function:

' lib_mysqludf_json.so ';

Installing GEARMAN-MYSQL-UDF
method is almost the same:

 
apt-get install libgearman-dev
wget https://launchpad.net/gearman-mysql-udf/trunk/0.6/+download/gearman-mysql-udf-0.6.tar.gz
tar -xzf gearman-mysql-udf-0.6.tar.gz
cd gearman-mysql-udf-0.6./configure --with-mysql=/usr/bin/mysql_config --libdir=/usr/lib/mysql/plugin/
make && make install

Log in to the MySQL run statement to register the UDF function:

' libgearman_mysql_udf.so ';' Libgearman_mysql_udf.so ';  

Finally, specify the information for the Gearman server:

SELECT gman_servers_set(' 127.0.0.1:4730 ');  

Data synchronization via MySQL triggers
The final synchronization of what data, the conditions of synchronization, or need to be based on the actual situation, such as I want to the data table of data in the synchronization of each update, then write 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 ;

Try updating a data in the database to see if the Gearman is in effect.

Gearman PHP worker asynchronously replicates MySQL data to Redis
Redis, as a hot, nosql cache solution without much introduction, is easy to install and use:

 
apt-get install redis-server 
pecl install redis
echo "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.  & 

In this way, the MySQL data is copied to Redis, and the test worker can be done almost instantaneously.

Ubuntu 14.04 Redis installation and simple test http://www.linuxidc.com/Linux/2014-05/101544.htm

Redis Cluster Detail Document Http://www.linuxidc.com/Linux/2013-09/90118.htm

Installation of Redis under Ubuntu 12.10 (graphic) + Jedis connection Redis http://www.linuxidc.com/Linux/2013-06/85816.htm

Redis Series-Installation and deployment Maintenance Chapter Http://www.linuxidc.com/Linux/2012-12/75627.htm

CentOS 6.3 Installation Redis http://www.linuxidc.com/Linux/2012-12/75314.htm

Redis Installation Deployment Learning Note http://www.linuxidc.com/Linux/2014-07/104306.htm

Redis configuration file redis.conf detailed http://www.linuxidc.com/Linux/2013-11/92524.htm

a detailed introduction to Redis : please click here
Redis's : please click here

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

This article permanently updates the link address : http://www.linuxidc.com/Linux/2015-01/111380.htm

MySQL-to-redis data synchronization via Gearman