MySQL to Redis replication

In system development, caching is often required to improve the ability of concurrent reads, which can be synchronized via the MySQL UDF and Hiredis.

Pre-title: Installed mysql5.6 and client , development environment WINDOW7 32, vs2013

1. Installing Redis and Hiredis

Msopentech/redis Project address on GitHub

The specific compilation installation method can be viewed

Http://www.cnblogs.com/raker/p/4368741.html

2. Download and install Mysql2redis
Https://github.com/dawnbreaks/mysql2redis

There's a problem here, because on GitHub, mysql2redis is a setup project written for the Linux system, so we can't use it directly here, we just need

The source files in the Src folder of the root directory are OK after decompression.

3. Deploy MySQL dynamic link library

　　

First open vs Create a new Win32 dynamic link library project, select the project, Project named MY_UDF (name casual)

Create a new source file and prepare the library:

1.mysql Library files (choose to install the development package when MySQL is installed, in the MySQL root directory)

SRC source files inside the 2.mysql2redis

3.hiredis Library file ( create a new Hiredis folder in source file):

• Create a new Lib folder under the Hiredis inside the Redis file downloaded in the first step above, locate the Hiredis.lib and Win32_interop.lib copy under the debug file compiled under the MSVs file .
• Copy the Hiredis folder in the Deps directory under Redis to Hiredis
• Finally copy the SRC folder under Redis, and then go into src to remove the other files from the Fmacros.h file and the Win32_interop folder.

4. Import each library in the project settings

PS: Remember to load 2 lib libraries

Add-on dependencies, input, properties--- Hiredis.lib;
#pragma comment (lib, "Hiredis.lib")
#pragma comment (lib, "Win32_interop.lib")

Then copy the compiled generated my_udf.dll to the plugin in the MySQL lib.
Execute the following statement loading method in MySQL

DROP FUNCTION IF EXISTSLib_mysqludf_redis_info; #删除DROP FUNCTION IF EXISTSRedis_command;CREATE FUNCTIONLib_mysqludf_redis_infoRETURNSSTRING SONAME'My_udf.dll'; #加载CREATE FUNCTIONRedis_commandRETURNS INTSONAME'My_udf.dll';

5. Create Table,trigger in MySQL
Table

CREATE  TABLE IF  not EXISTS' test '. ' My_test ' (' uid ' )int NULL, ' name 'VARCHAR( -) not NULL ,    ` Money`INT NULL ,    PRIMARY KEY(' uid '), ENGINE=InnoDB;

Trigger :

CREATE    /*!50017 definer = ' root ' @ '% '*/    TRIGGER' Tri_my_test_insert_redis ' beforeINSERT  on' My_test ' forEach ROWBEGIN    DECLAREDoneINT DEFAULT 999; DECLAREInseridINT DEFAULT 0; #DeclareInfoChar ; #SetInfo=CONCAT ("{",CAST(New.uid as CHAR), ":", New.uid, ",",CAST(New. Money  as CHAR), ":", New. Money,"}"); #SETInserid=LAST_INSERT_ID ()+1;

SELECT MAX (UID) +1 to Inserid from My_test;         SETDone=Redis_command ("127.0.0.1",6379, CONCAT ("Set User:: ", Inserid," ", CONCAT (" {uid: ", Inserid,", Name: ",CAST(New.name as CHAR),", Money: ", New. Money,"}")        )    ); IFDone> 0  Then#45001throw a custom exception termination operation SIGNAL SQLSTATE'45001' SETMessage_text="Redis error!"; END IF; END; $ $DELIMITER;

 insert  into  my_test (name,money ) values  ( " sally  , 1000 )  

< Span class= "Apple-converted-space" >< Span class= "Apple-converted-space" >

MySQL &NBSP;
&NBSP;

Redis's &NBSP;


Use MySQL UDF and trigger to ensure data consistency for MySQL and Redis, SIGNAL SQLState ' 45001 ' will roll back things when Redis fails. &NBSP;

Question: Hiredis to submit the data with space need to use

1 " SET key:%s%s ", myID, value);  

Then for Mysql2redis Redis_command is unable to work, this part can be a different command write function. such as Redis_command_set.

MySQL to Redis replication