Use mysql to automatically synchronize redis and mysql to synchronize redis

Use mysql to automatically synchronize redis and mysql to synchronize redis

During server development, MySQL and other relational databases are generally used as the final storage engine. Redis can also be used as a key-Value Pair database, but in some practical scenarios, in particular, relational structures are not suitable for using Redis directly as a database. These two guys can simply use "work with men and women, work is not tired" to describe, use together to get twice the result with half the effort. In this article, we will discuss how the two work properly together and how to synchronize data between them.

Generally, Redis can be used as the cache layer of MySQL. Why is MySQL better to have a cache layer? Imagine this scenario: In a multiplayer online game where rankings, friends, queues, and other directly related data are displayed, if you are directly in front of MySQL, A large number of data requests may make MySQL exhausted, or even excessive requests will penetrate into the database, leading to the interruption of the entire data service. The Bottleneck of database performance will limit business development; if you use Redis for data caching, the Data Query pressure will be greatly reduced. On this shelf, when we have data query requirements at the business layer, we first query them in the Redis cache. If we cannot find them, we can query them in the MySQL database, at the same time, we update the Retrieved Data to Redis. When we need to modify and insert data at the business layer, we directly send a request to MySQL and update the Redis cache.

One of the key points in the above architecture is that MySQL's CRUD is automatically updated to Redis after it occurs, which needs to be implemented through MySQL UDF. Specifically, we put the updated Redis logic in MySQL to define a Trigger and listen to CRUD operations. When an operation occurs, we call the corresponding UDF function, remote Write back to Redis, so the business logic only needs to update MySQL, and the rest is handed over to the MySQL UDF.

1. What is UDF?

UDF is short for User-Defined Function. MySQL supports functions and user-defined functions. Udfs have higher execution efficiency than storage methods and support clustering functions.

UDF defines five APIs: xxx_init (), xxx_deinit (), xxx (), xxx_add (), and xxx_clear (). The official documentation (http://dev.mysql.com/doc/refman/5.7/en/adding-udf.html) provides instructions on these Apis. The related struct is defined in mysql_com.h, which is also included by mysql. h. You only need to # include <mysql. h> for use. The relationship and execution sequence between them can be expressed as follows:

1. xxx ()

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

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

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 );

Ing between the SQL type and the C/C ++ type:

SQL Type	C/C ++ Type
STRING	Char *
INTEGER	Long
REAL	Double

2. xxx_init ()

Initialization of the xxx () main function. If defined, it is used to check the number, type, memory allocation, and other initialization operations of input xxx () parameters. The function declaration 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 it is defined, it is used to release the memory space allocated during initialization. The function declaration is as follows:

Void xxx_deinit (UDF_INIT * initid );

4. xxx_add ()

It is called repeatedly in the aggregate UDF to add parameters to the aggregate parameters. The function declaration is as follows:

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

5. xxx_clear ()

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

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

Ii. Basic usage of UDF Functions

Before that, install the mysql development kit:

[root@localhost zhxilin]# yum install mysql-devel -y

We define a simplest UDF main function:

 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 }

Since the interfaces provided by mysql are implemented in C, we need to add:

extern "C" { ... }

Then compile it into a dynamic library. so:

[zhxilin@localhost mysql-redis-test]$ g++ -shared -fPIC -I /usr/include/mysql -o simple_add.so simple.cpp

-Shared indicates that global shared class libraries are used for compilation and linking;

-FPIC compiler outputs location-independent target code, applicable to dynamic libraries;

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

Compile simple_add.so and use root to copy it to/usr/lib64/mysql/plugin:

[root@localhost mysql-redis-test]# cp simple_add.so /usr/lib64/mysql/plugin/

Then you can create a function in MySQL for execution. Log on to MySQL and create an association function:

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

Test the UDF function:

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

As you can see, the UDF correctly performs addition.

The syntax for creating a UDF is create function xxx RETURNS [INTEGER/STRING/REAL] soname' [so name] ';

The syntax for deleting a UDF is drop function simple_add;

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

3. Access Redis in UDF

Just like the above practice, you only need to call the interface functions provided by Redis In the UDF. Redis officially given Redis C ++ Client (https://github.com/mrpi/redis-cplusplus-client), encapsulates the basic operations of Redis.

The source code depends on boost. You need to install boost first:

[root@localhost dev]# yum install boost boost-devel

Then download the redis cpp client source code:

[root@localhost dev]# git clone https://github.com/mrpi/redis-cplusplus-client

During use, you need to test the redisclient. h, anet. h, fmacros. h, and anet. c files in the directory and write the UDF about Redis. We have defined redis_hset as the main function, connected to Redis, and called hset to insert a hash table. redis_hset_init is used as initialization to 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 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 Redis13 if (NULL = m_client) {14 const char * c_host = getenv ("REDIS_HOST "); 15 string host = "127.0.0.1"; 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 table 28 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, cha R * message) {40 if (3! = Args-> arg_count) {41 // hset (key, field, value) requires three parameters: 42 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 parameter type 49 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}

Compilation link:

[zhxilin@localhost mysql-redis-test]$ g++ -shared -fPIC -I /usr/include/mysql -lboost_serialization -lboost_system -lboost_thread -o libmyredis.so anet.c test.cpp

-Lboost_serialization-lboost_system-lboost_thread must be added during compilation, indicating that three dynamic libraries: libraries, libboost_system.so, and libboost_thread.so must be linked. Otherwise, a function definition error is reported during runtime.

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

[root@localhost mysql-redis-test]# cp libmyredis.so /usr/lib64/mysql/plugin/ & chmod 777 /usr/lib64/mysql/plugin/libmyredis.so 

After that, log on to MySQL and test the association function:

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 that the function name is enclosed in quotation marks (''). Call the UDF test and return 0. Execution successful:

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 the result:

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

4. Refresh Redis using the MySQL trigger

On the basis of the previous section, we want MySQL to automatically call udfs when adding, deleting, modifying, and querying data. We also need to use MySQL triggers. Triggers can listen to basic operations such as INSERT, UPDATE, and DELETE. In MySQL, the basic syntax for creating a trigger is as follows:

CREATE TRIGGER trigger_nametrigger_timetrigger_event ON table_nameFOR EACH ROWtrigger_statement

Trigger_time indicates the trigger time. The value isAFTEROrBEFORE;

Trigger_event indicates the trigger event. The value isINSERT,UPDATE,DELETEAnd so on;

Trigger_statement indicates the trigger program body, which can be an SQL statement or a UDF call.

In trigger_statement, if multiple SQL statements exist, you must include them with BEGIN... END:

BEGIN[statement_list]END

Because the default END Separator of MySQL is a semicolon (;), if we see a semicolon in BEGIN... END, it will be marked as an END. At this time, the trigger definition cannot be completed. There is a way to call the DELIMITER command to temporarily modify the end separator, and then change the semicolon after use. For example, change to $:

mysql> DELIMITER $

We started to define a trigger to listen for the insert operation on the Student table. The Student table was created in the previous article and can be viewed in 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(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 $

After the trigger is created, you can view the trigger through show or drop to delete it:

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', 19, 'F', 'SS3-205');Query OK, 1 row affected (0.27 sec)

MySQL results:

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)

Redis results:

127.0.0.1:6379> 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, the UDF is called through the trigger to automatically refresh Redis data. In addition, you can call the MySQL INSERT command through C ++ to implement the business logic of C ++, you only need to call the MySQL ++ interface to update the MySQL database and Redis cache. This part has been introduced in the previous article.

 

Summary

Through practice, we can see how close MySQL and Redis are to each other! Pai_^

This article describes how to use the basic UDF, connect to Redis to insert data through UDF, and further introduce the entire idea of automatically updating Redis data through MySQL Trigger, that is, only the MySQL database is updated in the Business Code, and Redis can automatically synchronize and refresh.

MySQL's support for UDF functions and triggers makes it possible to automatically synchronize Redis data with MySQL. Of course, udfs run in MySQL in the form of plug-ins after all, and there is not much security intervention. Once the plug-in experiences a fatal crash, MySQL may also crash, therefore, be cautious when writing udfs!

 

The above content from: http://www.cnblogs.com/zhxilin/archive/2016/09/30/5923671.html