本文共 3128 字，大约阅读时间需要 10 分钟。

OS： Ubuntu 16.04.4 x64

更新并安装必要的工具

apt update && apt upgrade -y && apt dist-upgrade -yapt autoremoveapt autocleanapt install gcc redis-server mysql-server libmysqld-dev libhiredis-dev -y

验证redis

127.0.0.1:6379> hgetall w3ckey(empty list or set)127.0.0.1:6379>

创建mysql数据

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

创建UDF动态库 mysqlUDFdemo.c

#include 
   
    #include 
    
     #include 
     
      #include 
      
       #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);                return 1;        }        const char * 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;}
       
      
     
    
   

编译

gcc -shared -fPIC -I /usr/include/mysql -o udfgx.so mysqlUDFdemo.c /usr/lib/x86_64-linux-gnu/libhiredis.a

拷贝so文件到mysql plugin

cp udfgx.so  /usr/lib/mysql/plugin/

配置UDF和trigger

use mysqlRedis;drop function if exists gxupdate;create function gxupdate returns INTEGER soname "udfgx.so";drop trigger if exists insert_redis;drop trigger if exists update_redis;drop trigger if exists delete_redis;delimiter |create trigger insert_redis    after insert on test    for each row    begin       declare ret int;       select gxupdate(1, NEW.id, NEW.name, NEW.age, NEW.description) into @ret;#必须加into @ret，否则返回错误ERROR 1415 (0A000) #at line 6: Not allowed to return a result set from a trigger#insert只有NEW变量。#update有NEW和OLD变量。#delete只有OLD变量。end|create trigger update_redis    after update on test    for each row    begin        declare ret int;        select gxupdate(1, NEW.id, NEW.name, NEW.age, NEW.description) into @ret;    end|create trigger delete_redis    after delete on test    for each row    begin        declare ret int;        select gxupdate(0, OLD.id, OLD.name, OLD.age, OLD.description) into @ret;    end|delimiter ;

注意insert，update，delete要定义三个触发器

127.0.0.1:6379> hgetall w3ckey(empty list or set)127.0.0.1:6379>

插入数据

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

查看redis

127.0.0.1:6379> hgetall w3ckey1) "name"2) "ggglwlop"3) "description"4) "ddddgdg"5) "likes"6) "27"7) "visitors"8) "23"127.0.0.1:6379>

mysql的改动已经缓存到了redis中

这里有一个github上不错的项目：

https://github.com/liukelin/canal_mysql_nosql_sync

参考：

https://blog.csdn.net/gx_1983/article/details/79033502