Java enables MySQL-to-redis data synchronization via Gearman (asynchronous 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.

1. Installation dependent

Yum install-y boost-devel gperf libevent-devel libuuid-devel

Yum Install Mysql-devel-y

2. Download Gearman

wget https://launchpad.net/gearmand/1.2/1.1.12/+download/gearmand-1.1.12.tar.gz

3, compile and install, specify the link path of mysqlclient

TAR-ZXVF gearmand-1.1.12.tar.gz

CD gearmand-1.1.12

./configure

Make && make install

4. Start the Gearmand server (at startup, create the Gearmand.log log file under/var/log/. -l Specifies log file-D background run-L 0.0.0.0 bound to IPV4

Gearmand-l 0.0.0.0-l/var/log/gearmand.log-d

5. Check whether the start is successful

Ps-ef | grep Gearman

6. See if the installation is successful, view Gearman version information

Gearmand-v

7. MySQL UDF + trigger sync data to Gearman (HTTPS://GITHUB.COM/MYSQLUDF)

Install Lib_mysqludf_json (Lib_mysqludf_json can output MySQL table data in JSON data format)

wget Https://github.com/mysqludf/lib_mysqludf_json/archive/master.zip

Unzip Master.zip

CD lib_mysqludf_json-master/

RM-RF lib_mysqludf_json.so

8, compile Mysql_config This is the MySQL configuration file, you can find/usr-name mysql_config search under what location

GCC $ (/usr/local/mysql/bin/mysql_config--cflags)-shared-fpic-o lib_mysqludf_json.so lib_mysqludf_json.c

9. Copy lib_mysqludf_json.so to MySQL's plugin directory

(You can login to MySQL, enter the command "show variables like '%plugin% '" View plugin location)

CP lib_mysqludf_json.so/usr/local/mysql/lib/plugin/

Demo Lib_mysqludf_json Features

Log in to MySQL

Mysql-uroot-h127.0.0.1-p

Registering UDF functions

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

CREATE FUNCTION json_array RETURNS STRING SONAME "lib_mysqludf_json.so";

CREATE FUNCTION json_members RETURNS STRING SONAME "lib_mysqludf_json.so";

CREATE FUNCTION json_values RETURNS STRING SONAME "lib_mysqludf_json.so";

The Json_array|json_members|json_values function is registered in the same way as Json_object.

Select Json_object (Id,file_save_type,base_dir) as Sys_file_save_config from Sys_file_save_config;

ERROR 1123 (HY000): Can ' t initialize function ' Json_object '; Invalid JSON member Name-name cannot be empty

The above errors are resolved by using aliases for each member name:

Select Json_object (id as ID, file_save_type as filesavetype,app_id as AppID) as Sys_file_save_config from Sys_file_save_co Nfig;

10, Installation gearman-mysql-udf (HTTPS://LAUNCHPAD.NET/GEARMAN-MYSQL-UDF)

wget https://launchpad.net/gearman-mysql-udf/trunk/0.6/+download/gearman-mysql-udf-0.6.tar.gz

Tar zxvf gearman-mysql-udf-0.6.tar.gz

CD gearman-mysql-udf-0.6

11, Installation Libgearman-devel

Yum Install Libgearman-devel-y

If there is no Yum source, add Epel.repo yum source

[Epel]

Name=extra Packages for Enterprise Linux 6-$basearch

#baseurl =http://download.fedoraproject.org/pub/epel/6/$basearch

Mirrorlist=https://mirrors.fedoraproject.org/metalink?repo=epel-6&arch= $basearch

Failovermethod=priority

Enabled=1

Gpgcheck=1

Gpgkey=file:///etc/pki/rpm-gpg/rpm-gpg-key-epel-6

[Epel-debuginfo]

Name=extra Packages for Enterprise Linux 6-$basearch-debug

#baseurl =http://download.fedoraproject.org/pub/epel/6/$basearch/debug

Mirrorlist=https://mirrors.fedoraproject.org/metalink?repo=epel-debug-6&arch= $basearch

Failovermethod=priority

Enabled=0

Gpgkey=file:///etc/pki/rpm-gpg/rpm-gpg-key-epel-6

Gpgcheck=1

[Epel-source]

Name=extra Packages for Enterprise Linux 6-$basearch-source

#baseurl =http://download.fedoraproject.org/pub/epel/6/srpms

Mirrorlist=https://mirrors.fedoraproject.org/metalink?repo=epel-source-6&arch= $basearch

Failovermethod=priority

Enabled=0

Gpgkey=file:///etc/pki/rpm-gpg/rpm-gpg-key-epel-6

Gpgcheck=1

12. Compile and install

(You can log in to MySQL, enter the command "show variables like '%plugin% '" to see plugin location, mysql_config configuration file, and the path of the plugin library, which will generate. So files on this path after compilation)

./configure--with-mysql=/usr/local/mysql/bin/mysql_config--libdir=/usr/local/mysql/lib/plugin/

Make && make install

Demo GEARMAN-MYSQL-UDF Features

Mysql-uroot-p

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_high_background RETURNS STRING SONAME "libgearman_mysql_udf.so";

CREATE FUNCTION gman_do_low_background RETURNS STRING SONAME "libgearman_mysql_udf.so";

CREATE FUNCTION gman_sum RETURNS STRING SONAME "libgearman_mysql_udf.so";

function Gman_do|gman_do_high|gman_do_low|gman_do_high_background|gman_do_low_background|gman_sum is registered in a similar way, Please refer to Gearman-mysql-udf-0.6/readme

Specify Gearman Job server address

SELECT gman_servers_set (' 127.0.0.1:4730 ');

If an exception message appears:

ERROR 1126 (HY000): Can ' t open shared library ' libgearman_mysql_udf.so ' (errno:11 libgearman.so.8:cannot open Shared obj ECT file:no such file or directory)

Indicates that the system cannot find the libgearman.so file, general so is in the/usr/local/lib directory, modify the configuration file/etc/ld.so.conf, add the/usr/local/lib directory:

$ cat/etc/ld.so.conf

Include ld.so.conf.d/*.conf

/usr/local/lib

$/sbin/ldconfig-v | grep gearman*

13. MySQL Trigger Call Gearman UDF for synchronization

Create a Trigger

DELIMITER $$

CREATE TRIGGER Test_data_to_redis after UPDATE on test for each ROW BEGIN

[Email protected]=gman_do_background (' Synctoredis ', Json_object (new.id as ' id ', new.phone as ' phone ')];

end$$;

DELIMITER $$

CREATE TRIGGER Test_data_to_redis2 after INSERT on test

For each ROW BEGIN

SET @ret =gman_do_background (' SyncToRedis2 ', Json_object (new.id as ' id ', new.phone as ' phone '));

end$$

DELIMITER;

DELIMITER $$

CREATE TRIGGER Test_data_to_redis3 before DELETE on test

For each ROW BEGIN

SET @ret =gman_do_background (' SyncToRedis3 ', Json_object (old.id as ' id ', old.phone as ' phone '));

end$$

DELIMITER;

Description and questions: This class is based on the Java-gearman-service (address: Https://launchpad.net/gearman-java) of the Gearman website and the release version is now 0.6.6. The Java-gearman-servic.jar package, which includes the Gearman server, also includes the client and work Agent APIs.

Problem: The Config class for spring is injected into the configuration file class, in Worker.addfunction, if the properties of the Config class are passed, and the properties are from the configuration file, there is a problem. Do not know why, write Dead is OK. This class connects to the remote Gearman job server.

The jar package needs to be added to the local jar repository:

MVN install:install-file-dfile=c:\software\java-gearman-service-0.6.6.jar-dgroupid=org.gearman.jgs-dartifactid= Java-gearman-service-dversion=0.6.6-dpackaging=jar

Import Java.util.concurrent.TimeUnit;

Import Org.gearman.Gearman;

Import org.gearman.GearmanFunction;

Import Org.gearman.GearmanFunctionCallback;

Import Org.gearman.GearmanServer;

Import Org.gearman.GearmanWorker;

/**

* *echo_host = "192.168.125.131" is the host address where Gearman is installed and the Geramand service is turned on

*int echo_port = 4730 default port is 4730

*

* @author Administrator

*

*/

public class Echoworker implements Gearmanfunction {

Function name

public static final String echo_function_name = "Synctoredis";

Job server Address

public static final String echo_host = "192.168.1.245";

The port that the job server listens on

public static final int echo_port = 4730;

public static void Main (string[] args) {

Create a Gearman instance

Gearman Gearman = Gearman.creategearman ();

/*

* Create a JobServer

*

* Parameter 1:job Server IP address Parameter 2:job server listening port

*

* Job server receives the job of the client and sends it to the registered worker

*

*/

Gearmanserver Server = Gearman.creategearmanserver (Echoworker.echo_host, Echoworker.echo_port);

Create a Gearman worker

Gearmanworker worker = Gearman.creategearmanworker (); Here's the point, create a work node.

Worker.setreconnectperiod (2, timeunit.seconds); Set time-out to re-connect

Worker.setmaximumconcurrency (5); Maximum number of concurrent

Tell the worker how to perform the work (Gearmanfunction interface is mainly implemented)

Worker.addfunction (Echoworker.echo_function_name, New Echoworker ());

Worker Connection Server

Worker.addserver (server);

}

@Override

Public byte[] Work (String function, byte[] data, Gearmanfunctioncallback callback) throws Exception {

The work method implements the work method in the Gearmanfunction interface, and the inverse of the string in this example

if (data! = NULL) {

String str = new string (data);

System.out.println (str);

StringBuffer sb = new StringBuffer (str);

Return Sb.reverse (). toString (). GetBytes ();

} else {

Return "Data not received". GetBytes ();

}

}

}

Import Org.gearman.Gearman;

Import org.gearman.GearmanClient;

Import org.gearman.GearmanJobEvent;

Import Org.gearman.GearmanJobReturn;

Import Org.gearman.GearmanServer;

public class Echoclient {

public static void Main (String ... args) throws Interruptedexception {

Create a Gearman instance

Gearman Gearman = Gearman.creategearman ();

Create a Gearman client

Gearmanclient client = Gearman.creategearmanclient ();

/*

* Create a JobServer

*

* IP address of Parameter 1:job server

* Parameter 2:job Server listening port

*

*job server receives the client's job and sends it to the registered worker

*

*/

Gearmanserver Server = Gearman.creategearmanserver (

Echoworker.echo_host, Echoworker.echo_port);

Tells the client that it can connect to the server when it commits the work

Client.addserver (server);

/*

* Submit work to Job server

*

* Parameter 1:gearman function name

* Parameter 2: Data sent to the job server and worker

*

* Gearmanjobreturn return Job fever results

*/

Gearmanjobreturn Jobreturn = Client.submitjob (

Echoworker.echo_function_name, ("Hello world!"). GetBytes ());

Traverse job events until we hit the final file

while (!jobreturn.iseof ()) {

Next Job Event

Gearmanjobevent event = Jobreturn.poll ();

Switch (Event.geteventtype ()) {

Case gearman_job_success://job Successful execution

System.out.println (New String (Event.getdata ()));

Break

Case Gearman_submit_fail://job commit Failed

Case Gearman_job_fail://job execution failed

System.err.println (Event.geteventtype () + ":"

+ New String (Event.getdata ()));

Default

}

}

Shut down

Gearman.shutdown ();

}

}

http://gearman.org/download/

PHP Scenario: Https://www.tuicool.com/articles/B7Jjaa

More Good Articles to share

Java enables MySQL-to-redis data synchronization via Gearman (asynchronous replication)