Shulou(Shulou.com)06/03 Report--

MySQL-to-Redis data replication scheme

Both MySQL and Redis have a data synchronization mechanism. For example, the commonly used Master/Slave mode of MySQL is implemented by the server analyzing the binlog of Master. This kind of data replication is actually an asynchronous process, but when the servers are all in the same private network, the asynchronous delay is almost negligible.

So in theory, we can also analyze the binlog file of MySQL and insert the data into Redis in the same way. However, this requires a very in-depth understanding of binlog files and MySQL, and because there are many forms of Statement/Row/Mixedlevel in binlog, the workload of analyzing binlog synchronization is very large.

So here we choose a cheaper way to develop, borrow the mature MySQL UDF, put the MySQL data into Gearman first, and then synchronize the data to Redis through a PHP Gearman Worker written by yourself. It adds a lot of processes than the way of analyzing binlog, but it is cheaper to implement 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 includes the following parts:

Gearman Job Server:Gearman core program, 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 a task. For example, if I want to perform an email task in the background, I can call a Gearman Client in the program and pass in the email information, and then the execution result can be immediately displayed to the user, and the task itself will slowly run in the background.

Gearman Worker: the real executor of a task usually needs to write its own logic and run it through a daemon. When Gearman Worker receives the task content passed by Gearman Client, it will process it sequentially.

A similar background task processing project, Resque, has been introduced before. The designs of the two are actually very similar, and their simplicity can be compared as follows:

Gearman Job Server: the Redis part of the corresponding Resque

Gearman Client: Queue operation corresponding to Resque

Gearman Worker: Worker and Job corresponding to Resque

The reason for choosing Gearman over Resque here is that Gearman provides a more useful MySQL UDF with less effort.

1. Installation dependency

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, and 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 (when starting, create the gearmand.log log file under / var/log/. -l specify log file-d background run-L 0.0.0.0 bind to IPV4

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

5. Check whether the startup is successful.

Ps-ef | grep gearman

6. Check whether the installation is successful, and view the gearman version information

Gearmand-V

7. MySQL UDF + Trigger synchronizes data to Gearman (https://github.com/mysqludf)

Install lib_mysqludf_json (lib_mysqludf_json can output MySQL 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 configuration file of mysql. You can search find / usr-name mysql_config for where it is.

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 log in to MySQL and enter the command "show variables like'% plugin%'" to view the plugin location.)

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

Demonstrate lib_mysqludf_json featur

Log in to mysql

Mysql-uroot-h227.0.0.1-p

Register the UDF function

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"

/ / 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 error is resolved by using an alias 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_config

10. Install 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. Install libgearman-devel

Yum install libgearman-devel-y

If there is no yum feed, add an 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 and enter the command "show variables like'% plugin%'" to view the plugin location, the configuration file of mysql_config, and the path where the plug-in library is located, and the .so file will be generated in this path after compilation.)

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

Make & & make install

Demonstrate gearman-mysql-udf featur

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 registration method is similar, please refer to gearman-mysql-udf-0.6/README

/ / specify gearman job server address

SELECT gman_servers_set ('127.0.0.1pur4730')

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 object file: No such file or directory)

Indicates that the system cannot find the libgearman.so file. Generally, the so is in the / usr/local/lib directory. Modify the configuration file / etc/ld.so.conf and add the / usr/local/lib directory to it:

$cat / etc/ld.so.conf

Include ld.so.conf.d/.conf

/ usr/local/lib

$/ sbin/ldconfig-v | grep gearman

13. MySQL Trigger calls Gearman UDF to achieve synchronization

Create trigger

DELIMITER $$

CREATE TRIGGER test_data_to_redis AFTER UPDATE ON test FOR EACH ROW BEGIN

SET@ret=gman_do_background ('syncToRedis', json_object (NEW.id AS id, NEW.phone ASphone))

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 problem: this class uses java-gearman-service (address: https://launchpad.net/gearman-java) on the official website of gearman. The current version of release is 0.6.6. The java-gearman-servic.jar package includes gearman server, as well as client and work client API.

Problem: the config class is a configuration file class injected by spring. In worker.addFunction, it will be a problem if you pass through the properties of the config class and the properties are obtained from the configuration file. I don't know why, but death is OK's. This type connects to a 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 geramand service is enabled * 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"; / / job server listening port 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: IP address of jobserver Parameter 2: port that jobserver listens on * * jobserver receives the job of client and distributes it to register worker * * / GearmanServer server = gearman.createGearmanServer (EchoWorker.ECHO_HOST, EchoWorker.ECHO_PORT); / / create a worker GearmanWorker worker = gearman.createGearmanWorker () of Gearman; / / here comes the topic, create work node. Worker.setReconnectPeriod (2, TimeUnit.SECONDS); / / set timeout reconnection time worker.setMaximumConcurrency (5); / / maximum number of concurrency / / tell workers how to perform work (mainly implement GearmanFunction interface) worker.addFunction (EchoWorker.ECHO_FUNCTION_NAME, new EchoWorker ()); / / worker connection server worker.addServer (server) } @ Overridepublic byte [] work (String function, byte [] data, GearmanFunctionCallback callback) throws Exception {/ / work method implements the work method in the GearmanFunction interface. In this example, the string is reversed by if (data! = null) {String str = new String (data); System.out.println (str); StringBuffer sb = new StringBuffer (str); return sb.reverse (). ToString (). GetBytes () } else {return "did not receive data" .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 * * Parameter 1: IP address of jobserver * Parameter 2: the port on which jobserver listens * * jobserver receives the job of client And distribute it to register worker * * / GearmanServer server = gearman.createGearmanServer (EchoWorker.ECHO_HOST, EchoWorker.ECHO_PORT) / / tell the client that it can connect to the server client.addServer (server) when submitting work / * * submit work to job server * * Parameter 1: gearman function name * Parameter 2: data sent to job server and worker * * GearmanJobReturn returns job fever result * / GearmanJobReturn jobReturn = client.submitJob (EchoWorker.ECHO_FUNCTION_NAME ("Hello World!") .getBytes () / / traverse the job event until we hit the final file while (! jobReturn.isEOF ()) {/ / the next job event GearmanJobEvent event = jobReturn.poll () Switch (event.getEventType ()) {case GEARMAN_JOB_SUCCESS: / / job successfully executed System.out.println (new String (event.getData (); break Case GEARMAN_SUBMIT_FAIL: / / job submission failed case GEARMAN_JOB_FAIL: / / job execution failed System.err.println (event.getEventType () + ":" + new String (event.getData () Default:}} / / close gearman.shutdown ();}

}

Http://gearman.org/download/

Php scheme: https://www.tuicool.com/articles/B7Jjaa

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.