使用Redis做为MySQL的缓存

OS： Ubuntu 16.04.4 x64

更新并安装必要的工具

apt update && apt upgrade -y && apt dist-upgrade -y
apt autoremove
apt autoclean
apt 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 <stdio.h>
#include <stdlib.h>
#include <mysql.h>
#include <string.h>
#include <hiredis/hiredis.h>

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要定义三个触发器

查看redis

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 w3ckey
1) "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.****.net/gx_1983/article/details/79033502