Automatically refresh Redis via MySQL

In the server-side development process, a relational database such as MySQL is generally used as the final storage engine. In fact, Redis can also be used as a key-value pair database, but in some practical scenarios, especially the relational structure is not suitable for use. Redis directly acts as a database. These two guys can simply be described as "male and female match, work is not tired", and they can be used together to get twice the result with half the effort. In this article, we will expand on how to match the two reasonably and how to synchronize the data between them.

Generally, Redis can be used as a caching layer for MySQL. Why is it better for MySQL to have a caching layer? Imagine a scenario like this: In a multiplayer online game, where there is a direct relationship between rankings, friendships, queues, etc., if you directly compete with MySQL, a large number of data requests may make MySQL exhausted or even excessive. The request will penetrate the database, causing the entire data service to be interrupted, and the bottleneck of database performance will hinder the development of the business; then, if you use Redis for data caching, it will greatly reduce the pressure of querying data. In this kind of shelf, when we have data query requirements in the business layer, we first query in the Redis cache. If we cannot find it, we then query in the MySQL database. At the same time, we update the data found in Redis; When the business layer needs to modify and insert data, it directly initiates a request to MySQL and updates the Redis cache at the same time.

In the above shelf, there is a key point, that is, MySQL's CRUD is automatically updated to Redis after it occurs, which needs to be implemented through MySQL UDF. Specifically, we put the logic of updating Redis into MySQL, that is, define a trigger Trigger to monitor these CRUD operations. When the operation occurs, call the corresponding UDF function and write back to Redis remotely, so the business logic only needs Responsible for updating MySQL is enough, and MySQL UDF will do the rest.

1. What is UDF

UDF, is the abbreviation of User Defined Function, user-defined function. MySQL supports functions and also supports custom functions. UDF has higher execution efficiency than storage methods and supports aggregate functions.

UDF defines 5 APIs: xxx_init(), xxx_deinit(), xxx(), xxx_add(), xxx_clear(). The official document ( gives a description of these APIs. The related structure is defined in mysql_com.h, which is included by mysql.h, so you only need to #include<mysql.h> to use it. The relationship between them and the order of execution can be represented by the following diagram:

1. xxx()

This is the main function. Five functions require at least xxx(). The result of the MySQL operation is returned here. The declaration of the function is as follows:

char *xxx(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length, char *is_null, char *error);

long long xxx(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error);

double xxx(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error);

The mapping of SQL types and C/C++ types:

SQL TypeC/C++ Type
STRINGchar *
INTEGERlong long

2. xxx_init()

The initialization of the main function of xxx(), if defined, is used to check the number of parameters passed in xxx(), the type, the allocation of memory space and other initialization operations. The declaration of the function is as follows:

my_bool xxx_init(UDF_INIT *initid, UDF_ARGS *args, char *message);

3. xxx_deinit()

The deinitialization of the xxx() main function, if defined, is used to release the memory space allocated during initialization. The declaration of the function is as follows:

void xxx_deinit(UDF_INIT *initid);

4. xxx_add()

Called repeatedly in the aggregate UDF, adding parameters to the aggregate parameters. The declaration of the function is as follows:

void xxx_add(UDF_INIT *initid, UDF_ARGS *args, char *is_null,char *error);

5. xxx_clear()

It is repeatedly called in the aggregate UDF to reset the aggregate parameters to prepare for the operation of the next row of data. The declaration of the function is as follows:

void xxx_clear(UDF_INIT *initid, char *is_null, char *error);

2. Basic use of UDF function

Before that, you need to install the mysql development kit:

[[email protected] zhxilin]# yum install mysql-devel -y

We define the simplest UDF main function:

Copy code

1 /*simple.cpp*/ 2 #include <mysql.h> 3  4 extern "C" long long simple_add(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error) 5 { 6     int a = *((long long *)args->args[0]); 7     int b = *((long long *)args->args[1]); 8     return a + b; 9 }10 11 extern "C" my_bool simple_add_init(UDF_INIT *initid, UDF_ARGS *args, char *message)12 {13     return 0;14 }

Copy code

Since the interface provided by mysql is implemented in C, we need to add when using it in C++:

extern "C" { ... }

Next, compile it into a dynamic library .so:

[[email protected] mysql-redis-test]$ g++ -shared -fPIC -I /usr/include/mysql -o simple.cpp

-shared means that the global shared class library is used when compiling and linking;

-fPIC compiler outputs position-independent object code, suitable for dynamic libraries;

-I /usr/include/mysql specifies the location of the included header file mysql.h.

After compiling, copy it to /usr/lib64/mysql/plugin as root:

[[email protected] mysql-redis-test]# cp /usr/lib64/mysql/plugin/

Then you can create a function to execute in MySQL. Log in to MySQL and create an associated function:

mysql> CREATE FUNCTION simple_add RETURNS INTEGER SONAME '';Query OK, 0 rows affected (0.04 sec)

Test UDF function:

Copy code

mysql> select simple_add(10, 5);+-------------------+| simple_add(10, 5) |+-------------------+|                15 |+-------------------+1 row in set (0.00 sec)

Copy code

As you can see, UDF performs the addition correctly.

The syntax for creating UDF functions is CREATE FUNCTION xxx RETURNS [INTEGER/STRING/REAL] SONAME'[so name]';

The syntax for deleting UDF functions is DROP FUNCTION simple_add;

mysql> DROP FUNCTION simple_add;Query OK, 0 rows affected (0.03 sec)

3. Access Redis in UDF

As with the above method, you only need to call the interface function provided by Redis in the UDF. Redis officially provides the Redis C++ Client ( , which encapsulates the basic operations of Redis.

The source code depends on boost, and boost needs to be installed first:

[[email protected] dev]# yum install boost boost-devel

Then download the redis cpp client source code:

[[email protected] dev]# git clone

When using it, you need to put the four files redisclient.h, anet.h, fmacros.h, anet.c into the directory, and start writing UDF about Redis. We define redis_hset as the main function, connect to Redis and call hset to insert the hash table, redis_hset_init as initialization, and check the number and types of parameters.

Copy code

1 /* test.cpp */ 2 #include <stdio.h> 3 #include <mysql.h> 4 #include "redisclient.h" 5 using namespace boost; 6 using namespace std; 7  8 static redis::client *m_client = NULL; 9 10 extern "C" char *redis_hset(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length, char *is_null, char *error) {11     try {12 // Connect to Redis13         if(NULL == m_client) {14             const char* c_host = getenv("REDIS_HOST");15             string host = "";16             if(c_host) {17                 host = c_host;18             }19             m_client = new redis::client(host);20         }        21 22         if(!(args->args && args->args[0] && args->args[1] && args->args[2])) {23             *is_null = 1;24             return result;25         }26 27 // Call hset to insert a hash table28         if(m_client->hset(args->args[0], args->args[1], args->args[2])) {29             return result;30         } else {31             *error = 1;32             return result;33         }34     } catch (const redis::redis_error& e) {35         return result;36     }37 }38 39 extern "C" my_bool redis_hset_init(UDF_INIT *initid, UDF_ARGS *args, char *message) {40     if (3 != args->arg_count) {41 // hset(key, field, value) requires three parameters42         strncpy(message, "Please input 3 args for: hset('key', 'field', 'value');", MYSQL_ERRMSG_SIZE);43         return -1;44     }45     if (args->arg_type[0] != STRING_RESULT  || 46         args->arg_type[1] != STRING_RESULT  || 47         args->arg_type[2] != STRING_RESULT) { 48 // Check the parameter type49         strncpy(message, "Args type error: hset('key', 'field', 'value');", MYSQL_ERRMSG_SIZE);50         return -1;51     }52 53     args->arg_type[0] = STRING_RESULT;54     args->arg_type[1] = STRING_RESULT;55     args->arg_type[2] = STRING_RESULT;56 57     initid->ptr = NULL;58     return 0;59 }

Copy code

Compile link:

[[email protected] mysql-redis-test]$ g++ -shared -fPIC -I /usr/include/mysql -lboost_serialization -lboost_system -lboost_thread -o anet.c test.cpp

You need to add -lboost_serialization -lboost_system -lboost_thread when compiling, which means that you need to link three dynamic libraries:,,, otherwise an error of missing function definition will be reported at runtime.

After compiling, copy it to the mysql plug-in directory and raise the rights:

[[email protected] mysql-redis-test]# cp /usr/lib64/mysql/plugin/ & chmod 777 /usr/lib64/mysql/plugin/

Log in to MySQL after completion, create an associated function to test it:

mysql> DROP FUNCTION IF EXISTS `redis_hset`;Query OK, 0 rows affected (0.16 sec)mysql> CREATE FUNCTION redis_hset RETURNS STRING SONAME '';Query OK, 0 rows affected (0.02 sec)

Delete the old UDF first, and pay attention to the backtick mark (``) in the function name. Call the UDF test, return 0, the execution is successful:

Copy code

mysql> SELECT redis_hset('zhxilin', 'id', '09388334');+-----------------------------------------+| redis_hset('zhxilin', 'id', '09388334') |+-----------------------------------------+| 0                                                     |+-----------------------------------------+1 row in set (0.00 sec)

Copy code

Open redis-cli and view the results:> HGETALL zhxilin1) "id"2) "09388334"

4. Refresh Redis via MySQL trigger

On the basis of the previous section, we want MySQL to automatically call UDF when adding, deleting, modifying, and checking, and we also need to use MySQL triggers. The trigger can monitor basic operations such as INSERT, UPDATE, and DELETE. In MySQL, the basic syntax for creating triggers is as follows:

CREATE TRIGGER trigger_nametrigger_timetrigger_event ON table_nameFOR EACH ROWtrigger_statement

trigger_time represents the trigger time, the value is AFTER or BEFORE ;

trigger_event represents the triggered event, the value is INSERT , UPDATE , DELETE, etc.;

trigger_statement represents the program body of the trigger, which can be a SQL statement or call UDF.

In trigger_statement, if there are multiple SQL statements, they need to be included with BEGIN...END:


Since MySQL's default end delimiter is a semicolon (;), if we have a semicolon in BEGIN...END, it will be marked as the end, and the trigger definition cannot be completed at this time. There is a way, you can call the DELIMITER command to temporarily modify the end separator, and then change the semicolon after use. For example, change to $:

mysql> DELIMITER $

We begin to define a trigger to monitor the insert operation on the Student table. The Student table was created in the previous article. You can view the previous article.

Copy code

mysql > DELIMITER $      > CREATE TRIGGER tg_student       > AFTER INSERT on Student       > FOR EACH ROW       > BEGIN      > SET @id = (SELECT redis_hset(CONCAT('stu_', new.Sid), 'id', CAST(new.Sid AS CHAR(8))));      > SET @name = (SELECT redis_hset(CONCAT('stu_', new.Sid), 'name', CAST(new.Sname AS CHAR(20))));      > Set @age = (SELECT redis_hset(CONCAT('stu_', new.Sid), 'age', CAST(new.Sage AS CHAR)));       > Set @gender = (SELECT redis_hset(CONCAT('stu_', new.Sid), 'gender', CAST(new.Sgen AS CHAR)));       > Set @dept = (SELECT redis_hset(CONCAT('stu_', new.Sid), 'department', CAST(new.Sdept AS CHAR(10))));          > END $

Copy code

After creating the trigger, you can view it through show, or delete it through drop:

mysql> SHOW TRIGGERS;mysql> DROP TRIGGER tg_student;

Next, we call an insert statement, and then observe the changes in Redis and MySQL data:

mysql> INSERT INTO Student VALUES('09388165', 'Rose', 19, 'F', 'SS3-205');Query OK, 1 row affected (0.27 sec)

MySQL results:

Copy code

mysql> SELECT * FROM Student;+----------+---------+------+------+---------+| Sid      | Sname   | Sage | Sgen | Sdept   |+----------+---------+------+------+---------+| 09388123 | Lucy    |   18 | F    | AS2-123 || 09388165 | Rose    |   19 | F    | SS3-205 || 09388308 | zhsuiy  |   19 | F    | MD8-208 || 09388318 | daemon  |   18 | M    | ZS4-630 || 09388321 | David   |   20 | M    | ZS4-731 || 09388334 | zhxilin |   20 | M    | ZS4-722 |+----------+---------+------+------+---------+6 rows in set (0.00 sec)

Copy code

Redis results:

Copy code> HGETALL stu_09388165 1) "id" 2) "09388165" 3) "name" 4) "Rose" 5) "age" 6) "19" 7) "gender" 8) "F" 9) "department"10) "SS3-205"

The above results show that when MySQL inserts data, UDF is called through a trigger to automatically refresh Redis data. In addition, the command to call MySQL insert can be implemented through C++, and then in the business logic of C++, the MySQL database and Redis cache update can be realized by simply calling the MySQL++ interface. This part of the content has been in the previous article. Introduced.

to sum up

Through practice, you can realize how much MySQL and Redis love each other! ^_^

This article talks about starting from the most basic UDF, then connecting to Redis through UDF to insert data, and then further introducing the whole idea of ​​automatically updating Redis data through MySQL Trigger, achieving a goal, that is, only updating the MySQL database in the business code. In turn, Redis can automatically refresh synchronously.

MySQL's support for UDF functions and triggers makes it possible to realize automatic synchronization of Redis data and MySQL. Of course, UDF runs in MySQL through plug-ins after all, and there is no excessive security intervention. Once the plug-in crashes fatally, MySQL may also hang, so you need to be very careful when writing UDF!