mysql2redis

by dawnbreaks

dawnbreaks / mysql2redis

A high performance mysql udf to sync the newly modified/inserted data from mysql to redis cache.

202 Stars 91 Forks Last release: Not found 27 Commits 0 Releases

Available items

No Items, yet!

The developer of this repository has not created any items for sale yet. Need a bug fixed? Help with integration? A different license? Create a request here:

mysql2redis

Redis udf to sync newly modified data from mysql to redis cache.

When you use redis to cache data only(not persistent), you will face the issue that how to sync the newly modified data from mysql to the redis cache? This project aims to provide a high performance and stable redis udf to solve this issue.

Dependencies

please download the dependencies below and compile/install it properly :

  • apr-1.4.6(http://apr.apache.org/download.cgi)
  • apr-util-1.5.2(http://apr.apache.org/download.cgi)
  • hiredis(https://github.com/redis/hiredis)
  • libmysqludfjson(https://github.com/mysqludf/libmysqludfjson)

Compile

run make from the src dir directly.

  make

Install redis udf

please make sure that the libmysqludfredisv2.so has been put into the mysql plugin dir. By the way, you can examine where is the mysql plugin dir by run ''' mysqlconfig --plugindir '''. and then connect to your mysql server, run the following command to install the the redis udf. ```sql DROP FUNCTION IF EXISTS redisserverssetv2; DROP FUNCTION IF EXISTS rediscommandv2; DROP FUNCTION IF EXISTS freeresources;

CREATE FUNCTION redisserverssetv2 RETURNS int SONAME "libmysqludfredisv2.so"; CREATE FUNCTION rediscommandv2 RETURNS int SONAME "libmysqludfredisv2.so"; CREATE FUNCTION freeresources RETURNS int SONAME "libmysqludfredis_v2.so"; ```

Test redis udf

connect to your mysql server, run the following command to test the the redis udf. ```sql select rediscommandv2("lpush","crmInboxEvents11",jsonobject(jsonmembers("op","insert","value","valuettt")));

select redisserverssetv2("192.168.0.118",6379); select rediscommandv2("lpush","crmInboxEvents11",jsonobject(json_members("op","insert","value","valuettt")));

select rediscommandv2("hset","hkey","hfield",jsonobject(jsonmembers("op","insert","value","valuettt")));

select freeresources(); select redisserverssetv2("192.168.0.118",6379); ```

What's more

you should create a trigger which will lpush the newly modified data to redis list juste as the following example:

sql
DELIMITER $$
CREATE TRIGGER insert_trigger AFTER INSERT ON email_folder
  FOR EACH ROW BEGIN
    SET @ret=
         redis_command_v2("lpush","crmInboxEvents",
                              json_object
                              (
                                    json_members
                                    (
                                            "op",
                                            "insert",
                                            "value",
                                            json_object
                                            (
                                                NEW.Id as "id",NEW.type as "type",
                                                NEW.mailserver_id as "mailserverId",NEW.sender as "sender",
                                                NEW.sender_name as "senderName",NEW.recevier as "recevier",
                                                NEW.replyto as "replyto",NEW.bbemails as "bbemails",
                                                NEW.ccemails as "ccemails",NEW.subject as "subject"
                                            )
                                        )
                                )
                          );
  END$$
DELIMITER ;

Finally

Now all update events for that mysql table has been transfer to a redis queue. You need to start a thread(or process) to handle these update events and update the redis cache properly.

For example, you can start a java process to poll the redis queue , and pop the events, then handle it and update the redis cache properly.

Performance test

In my poor labtop, 10,000 row data were synced into redis in 0.4 second.

======== Please feel free to contact me([email protected]) if you have any questions.

We use cookies. If you continue to browse the site, you agree to the use of cookies. For more information on our use of cookies please see our Privacy Policy.