Automatic synchronization of Redis refreshes via MySQL

In the server development process, the general use of MySQL and other relational database as the ultimate storage engine, Redis can also be used as a key-value pairs of database, but in some real-world scenarios, especially relational structure is not suitable for using Redis directly as a database. These two guys can simply use "men and women, work not tired" to describe, together with using to do more. In this article we will make a reasonable match between the two and how the data between them is synchronized.

In general, Redis can be used as a cache layer for MySQL. Why does MySQL better have a cache layer? Imagine this scenario: in a multiplayer online game, leaderboards, friends, queues and other directly related data scenarios, if directly with the MySQL front, a large number of data requests may be tired of MySQL, even excessive requests will penetrate the database, resulting in the entire data service interruption, The bottleneck of database performance will be a hindrance to the development of the business; If you do data caching through Redis, you will greatly reduce the pressure on your query data. In this kind of shelf, when we have data query requirements in the business layer, first to the Redis cache query, if not found, and then to the MySQL database query, while the data to be updated to Redis; When we have changes in the business layer to insert data requirements, directly to the MySQL request, Update the Redis cache at the same time.

One of the key points in the above-mentioned shelves is that MySQL's crud is automatically updated to Redis, which needs to be done through a MySQL UDF. Specifically, we put the updated Redis logic into MySQL, that is, define a trigger trigger, listen to crud these operations, when the operation occurs, call the corresponding UDF function, remote write back to Redis, so the business logic only need to be responsible for updating MySQL on the line, The rest is given to the MySQL UDF to complete.

I. What is a UDF

UDF, the user-defined function, is the abbreviation for the Defined function. MySQL supports functions and also supports custom functions. UDFs are more efficient to perform than storage methods and support aggregation functions.

The UDF defines 5 api:xxx_init (), xxx_deinit (), xxx (), Xxx_add (), Xxx_clear (). The official documentation (HTTP://DEV.MYSQL.COM/DOC/REFMAN/5.7/EN/ADDING-UDF.HTML) gives a description of these APIs. The related structure is defined in the mysql_com.h, and it is mysql.h contained, only #include<mysql.h> can be used. The relationship between them and the order of execution can be expressed as follows:

1. XXX ()

This is the main function, 5 functions need at least xxx (), the results 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);

Types of SQL and mappings for C/A + + types:

SQL Type	C + + Type
STRING	char *
INTEGER	Long Long
REAL	Double

2. Xxx_init ()

The initialization of the XXX () main function, if defined, is used to check for initialization operations such as the number of parameters, type, allocated memory space, etc. of the incoming xxx (). The declaration of the function is as follows:

My_bool Xxx_init (Udf_init *initid, Udf_args *args, Char *message);

3. Xxx_deinit ()

The anti-initialization of the XXX () main function, if defined, is used to release the allocated memory space at initialization. The declaration of the function is as follows:

void Xxx_deinit (Udf_init *initid);

4. Xxx_add ()

Called repeatedly in the aggregation UDF, adding parameters to the aggregation 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 ()

Repeated calls in the aggregation UDF, resets the aggregation parameters, prepares 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);

Two. Basic use of UDF functions

Before you do this, you need to install the MySQL SDK:

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

We define one of the simplest UDF main functions:

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 one extern "C" My_bool simple_add_init (udf_init *initid, Udf_args *args, char *message) {13
   
    return 0;14}
   

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

extern "C" {...}

This is then compiled into a dynamic library. So:

-shared-fpic -i/usr/include/mysql-o simple_add.so simple.cpp

-shared means that the compilation and linking are using a globally shared class library;

-fpic Compiler output location-independent target code, suitable for dynamic library;

-i/usr/include/mysql indicates where the included header file Mysql.h is located.

After compiling the simple_add.so, use root to copy to/usr/lib64/mysql/plugin:

[email protected] mysql-redis-test]# CP simple_add.so/usr/lib64/mysql/plugin/

You can then create a function in MySQL to execute. Log in to MySQL and create the correlation function:

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

To test the UDF function:

Mysql> Select Simple_add (5); +-------------------+| Simple_add (5) |+-------------------+|                |+-------------------+1 Row in Set (0.00 sec)

As you can see, the UDF performs the addition correctly.

The syntax for creating a UDF function 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)

Three. Accessing Redis in UDF

As with the above practice, only the interface functions provided by Redis are called in the UDF. Redis has officially given Redis C + + Client (https://github.com/mrpi/redis-cplusplus-client), which encapsulates the basic operations of Redis.

Source is dependent on boost, need to install boost first:

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

Then download the Redis CPP client source code:

[[email protected] dev]# git clone https://github.com/mrpi/redis-cplusplus-client

The use of the Redisclient.h, Anet.h, Fmacros.h, anet.c these 4 files to the directory, began to write a UDF about Redis. We define redis_hset as the main function, connect Redis and call Hset Insert hash table, redis_hset_init as initialization, check the number and type of parameters.

 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 extern "C" char *redis_hset (udf_init *initid, Udf_args *args, Char *result, unsigned long *length, char *is_null, cha R *error) {12//Connect REDIS13 if (NULL = = m_client) {+ Const char* c_host = getenv ("R             Edis_host "); string host =" 127.0.0.1 "; + if (c_host) {# host = c_host;18 }19 m_client = new Redis::client (host); Args->args && Args->args[0] && args->args[1] && args->args[2])) {*is_nu ll = 1;24 return result;25}26 27//Call Hset Insert a hash table if (M_client->hset (args->arg         S[0], args->args[1], args->args[2])) {return result;30} else {31    *error = 1;32 return result;33}34} catch (const redis::redis_error& e) {retur n result;36}37}38, extern "C" My_bool redis_hset_init (udf_init *initid, Udf_args *args, char *message) { (3! = Args->arg_count) {Hset (Key, field, value) requires three parameters of strncpy (message, "Please input 3 args for:hset (' key ', ' field ', ' V         Alue '); ", mysql_errmsg_size); return-1;44}45 if (args->arg_type[0]! = String_result | | 46 ARGS-&GT;ARG_TYPE[1]! = String_result | | ARGS-&GT;ARG_TYPE[2]! = String_result) {48//Check parameter type strncpy (message, "args type Error:hset (' Key ', ' field ', ' value '); ", mysql_errmsg_size); return-1;51}52 args->arg_type[0] = String_resul t;54 args->arg_type[1] = string_result;55 args->arg_type[2] = string_result;56, initid->ptr = NULL; 0;59}

Compile Link:

[Email protected] mysql-redis-test]$ g++-shared-fpic-i/usr/include/mysql-lboost_serialization-lboost_system- Lboost_thread-o libmyredis.so anet.c Test.cpp

Compile-time need to add-lboost_serialization-lboost_system-lboost_thread, indicating the need to link three dynamic libraries: libboost_serialization.so, Libboost_ System.so, libboost_thread.so, or a missing function definition error will be reported at run time.

After compiling the libmyredis.so, copy it to the MySQL plug-in directory and raise the rights:

When you're done, log in to MySQL and create a correlation function test:

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

Delete the old UDF first, note the function name plus the inverse quotation mark ('). Call UDF test, return 0, execute success:

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

Open redis-cli and view results:

127.0.0.1:6379> hgetall zhxilin1) "id" 2) "09388334"

Four. Refresh Redis via MySQL Trigger

On the basis of the previous section, we want MySQL to automatically invoke the UDF when adding or removing changes, and also to use the MySQL trigger. Triggers can listen for basic operations such as INSERT, UPDATE, delete, and so on. 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 indicates the trigger time, value is after or before;

Trigger_event indicates the event triggered, the value is INSERT,UPDATE,DELETE and so on;

Trigger_statement represents the program body of a trigger, which can be either an SQL statement or a call to a UDF.

In Trigger_statement, if you have more than one SQL statement, you need to use begin ... End contains:

Begin[statement_list]end

Because the MySQL default end delimiter is a semicolon (;), if we are at begin ... A semicolon appears in end and is marked as finished, 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 delimiter, run out and then change the semicolon. For example, change to $:

Mysql> DELIMITER $

We begin to define a trigger that listens to the insert operation on the student table, and the student table was created in the previous article to see the previous article.

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 ()));      > 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 ()));          > END $

The trigger can be created by Show view, or drop Delete:

Mysql> SHOW TRIGGERS;

mysql> DROP TRIGGER tg_student;

Next we call an INSERT statement and observe the changes in Redis and MySQL data:

Mysql> INSERT into Student VALUES (' 09388165 ', ' Rose ', +, ' F ', ' ss3-205 '); Query OK, 1 row affected (0.27 sec)

Results of MySQL:

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)

Results of Redis:

127.0.0.1:6379> hgetall stu_09388165 1) "id" 2) "09388165" 3) "name" 4) "Rose" 5) "age" 6) "7" "gender" 8) "F" 9) " Department "Ten)" ss3-205 "

The results show that when MySQL inserts data, the UDF is called by the trigger, which automatically refreshes the Redis data. In addition, the call to MySQL Insert command, can be implemented through C + +, in order to implement the business logic in C + +, just call the mysql++ interface to implement MySQL database and Redis cache updates, this part of the article has been introduced.

Summarize

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

This article, starting with the most basic UDF, and then inserting data through a UDF connection to Redis, further describes the entire idea of automatically updating Redis data via MySQL trigger, achieving the goal of updating the MySQL database only in business code. In turn, Redis can automatically synchronize refreshes.

MySQL support for UDF functions and triggers makes it possible for Redis data to be synchronized with MySQL automatically. Of course, the UDF is run through the form of plug-ins in MySQL, and there is no excessive security intervention, once the plugin has a fatal crash, it is possible that MySQL will hang, so you need to write the UDF very carefully!

Automatic synchronization of Redis refreshes via MySQL