Redis as a cache for MySQL

Introduction to using Redis as a cache for MySQL

• In the actual project, the MySQL database server is sometimes located in another host, need to access the database through the network, even if the application and the MySQL database in the same host, access to MySQL also involves disk IO operations (MySQL also has some data pre-reading technology, can reduce disk IO read and write , this section continues the study), in short, reading data directly from MySQL is not as efficient as reading data directly from memory. In order to improve the efficiency of database access, a variety of methods have been adopted, one of which is to use a memory-giving cache system between the database and the application. When looking for data, first from the memory, if found to use, if not found, then real access to the database. This method can improve the overall efficiency of the system in some scenarios, such as finding the same data frequently.
• The main purpose of this article is to introduce a method described above, using Redis NoSQL database as the cache of MySQL database, when looking for the Redis cache first, if found to return results, if not found in Redis, then find the MySQL database, The found flower returns the result and updates the Redis, or null if not found. For writes, the MySQL database is written directly to the MySQL database, and the content of the changes is automatically updated to Redis through a trigger and UDF mechanism.

Block diagram

1. Read steps:
   1. The client reads Redis if the hit returns a result if no hit goes to 2.
   2. The client reads the database, does not find it in the database, returns NULL, is found in the database, returns the results found, and updates the Redis.
2. Write steps:
   1. Client modifies/deletes or adds data to MySQL.
   2. The MySQL trigger invokes the user-defined UDF.
   3. The UDF updates the modified/deleted or new data to Redis.

Code Implementation Software Requirements

• Redis server is a C library associated with client installation, Redis programming.
• MySQL server installation, Mysql-devel package installation, this package contains the C language API package that operates the MySQL database.
-

Implement Step 1. Install and verify Redis

127.0.0.1:6379> hgetall w3ckey(empty list or set) #最开始在reids中没有w3ckey的K-V对。127.0.0.1:6379>

2. Install MySQL database server 2.1 The script to create the MySQL database is as follows

drop database if exists mysqlRedis;create database mysqlRedis;use mysqlRedis;create table test1( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(64), age INT, description VARCHAR(1000), primary key(id));

2.2 Creating a dynamic library for UDF use

#include <stdio.h>#include <stdlib.h>#include <mysql.h>#include <string.h>#include int Gxupdate (Udf_init * initid, Udf_args * ARGS,char * is_null,char * error) {Rediscontext * c = redisconnect ("127.0.0.1",6379);if (c->err) {redisfree (c);Return1; }Constchar * Command1 ="Hmset W3ckey name%s Description%s likes%d visitors%d"; Redisreply * r = (redisreply *) Rediscommand (c, Command1, args->args[2], Args->args[4], * (int *) Args->args[1], * (int *) args->args[ 3]); if (r = = NULL) {return 1;} if (! ( (R->type = = redis_reply_status) && (strcasecmp (r->str,  "OK") = = 0)") {Freereplyobject (R); Redisfree (c); return 1;} freereplyobject (R); return 0;} My_bool gxupdate_init (Udf_init * initid, Udf_args * ARGS, char * message) {return 0;}            

Compile as a dynamic library:

-shared -fPIC -I /usr/include/mysql -o udfgx.so mysqlUDFdemo.c /usr/local/lib/libhiredis.a

After compiling, copy the dynamic library udfgx.so to the/usr/lib64/mysql/plugin/folder and modify the user's corresponding permissions.

2.3 Configure UDFs with trigger.

Use Mysqlredis;Drop functionIfexists gxupdate;Create function Gxupdate returnsINTEGER Soname"Udfredis.so";DropTriggerIfexists Insert_redis;DropTriggerIfexists Update_redis;DropTriggerIfExists Delete_redis;delimiter |CreateTrigger Insert_redisAfterInsertOn Test1ForeachRowBeginDECLARE RETintSelect Gxupdate (1, New.id, New.name, New.age, new.description)into @ret; #必须加into @ret, otherwise error 1415 (0a000) #at line 6:not allowed to return a resultSetFrom atrigger#Insert has only a new variable. #Update has new and old variables. #Delete Has only the old variable.end|CreateTrigger Update_redisAfterUpdateOn Test1ForeachRowBeginDECLARE RETintSelect Gxupdate (1, New.id, New.name, New.age, new.description) into @ret; end| Create trigger Delete_redis after delete on test1 for each      row begin Declare r ET int; Select Gxupdate (0, Old.id, Old.name, Old.age, old.description) into @ret; end|delimiter;                 

Note that when creating UDFs in MySQL, insert, update, and delete cannot be written as a trigger and can only be defined as three triggers respectively.

Test View Redis

[[email protected]_24_16_centos mysql_redis]# redis-cli127.0.0.1:6379> hgetall w3ckey(empty list or set)127.0.0.1:6379> 

No key w3ckey corresponds to value in Redis.

Insert MySQL

into test1 (name, age, description) values ("ggglwlop", 23, "ddddgdg");Query OK, 1 row affected (0.02 sec)mysql> 

Insert MySQL.

View Redis

127.0.< Span class= "Hljs-number" >0.1:6379> hgetall w3ckey1)  "name" 2)  "Ggglwlop" 3)  "description" 4) " DDDDGDG "5"  "likes" 6 )  "7"  "visitors" 8) " 127.0. 0.1:6379>           

MySQL has the corresponding data, indicating that MySQL through the triger+udf way to update the changes to Redis.

Useful Links

Http://blog. csdn. net/socho/article/details/52292064Https://www. cnblogs. com/linuxbug/p/4950626.htmlHttps://www. cnblogs. com/tommy-huang/p/4703514.html Redis Architecture Design when using Redis as the MySQL cache.Http://blog. csdn. net/shikaiwencn/article/details/51792059 the Redis kv relationship needs to be flexibly designed according to actual needs.Https://www. cnblogs. com/bruceleeliya/archive/2009/05/23/linux-c-mysql. HTML uses MySQL's C API to access MySQL. https://www. 2cto.com/database/201110/108925.html #mysql UDF. https://www. Cnblogs. com/linuxbug/p/4950626.html #udf使用的一个例子. https://www. Jianshu.com/p/4381a38403a1http://blog. csdn. net/socho/article/ details/52292064               

Redis as a cache for MySQL