使用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.csdn.net/gx_1983/article/details/79033502