1. 首页
  2. 文章中心
  3. 数据库

通过MySQL触发器同步MySQL数据到Redis中

104 阅读 0 评论
我是靠谱客的博主 孤独大树，最近开发中收集的这篇文章主要介绍通过MySQL触发器同步MySQL数据到Redis中，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

DROP FUNCTION IF EXISTS `redis_hset`;
DROP FUNCTION IF EXISTS `redis_hdel`;
DROP FUNCTION IF EXISTS `redis_set_addr`;
CREATE FUNCTION redis_hset RETURNS STRING SONAME 'sync-redis.so';
CREATE FUNCTION redis_hdel RETURNS STRING SONAME 'sync-redis.so';
CREATE FUNCTION redis_set_addr RETURNS STRING SONAME 'sync-redis.so';
select redis_set_addr("192.168.9.98:6379");

 

cpp so文件

#include <stdio.h>
#include <stdlib.h>
#include <syslog.h>

#include <mysql.h>

#include <acl_cpp/acl_cpp_init.hpp>
#include <acl_cpp/redis/redis.hpp>
#include <acl_cpp/redis/redis_client.hpp>
#include <acl_cpp/redis/redis_client_cluster.hpp>

extern char **environ; //导入这个全局变量

acl::redis *g_redis = NULL;
acl::redis_client *g_redisCli = NULL;
acl::redis_client_cluster *g_cluster = NULL;

char *g_redis_addr = NULL;
bool check_redis_conn()
{
    if (g_redis != NULL)
    {
        return true;
    }

    openlog("sync-redis-data", LOG_CONS | LOG_PID, LOG_LOCAL0);

    char *redisAddr = getenv("REDIS_ADDR");
    if (redisAddr == NULL)
    {
        if (g_redis_addr != NULL)
        {
            syslog(LOG_INFO, "mysql cmd line set redis addr: %s", g_redis_addr);
            redisAddr = g_redis_addr;
        }
        else
        {
            redisAddr = "127.0.0.1:6379";
        }

        syslog(LOG_INFO, "redis addr: %s", g_redis_addr);
    }

    const char *redisPasswd = getenv("REDIS_PASSWORD");
    if (redisPasswd == NULL)
    {
        syslog(LOG_INFO, "REDIS_PASSWORD environment variable is not set.");
    }

    int redisCluster = 0;
    //默认不设置则为单机
    const char *isCluster = getenv("REDIS_CLUSTER");
    if (isCluster != NULL)
    {
        redisCluster = atoi(isCluster);
    }

    //初始化redis集群连接
    int connTimeout = 30;
    int rwTimeout = 30;

    acl::acl_cpp_init();
    if (redisCluster == 1)
    {
        g_cluster = new acl::redis_client_cluster();
        // g_cluster->init(NULL, redisAddr, 100, connTimeout, rwTimeout);
        g_cluster->set(redisAddr, 100, connTimeout, rwTimeout);
        g_cluster->set_password("default", redisPasswd);
        g_redis = new acl::redis(g_cluster);
    }
    else
    {
        g_redisCli = new acl::redis_client(redisAddr, connTimeout, rwTimeout);
        g_redisCli->set_slice_request(false);
        g_redisCli->set_password(redisPasswd);
        g_redis = new acl::redis(g_redisCli);
    }

    syslog(LOG_INFO, "init redis conn success.");

    return true;
}

// 调用redis的hset命令
extern "C" char *redis_hset(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length, char *isNull, char *error)
{
    if (!check_redis_conn())
    {
        *error = 1;
        const char *msg = "connect redis error!";
        *length = strlen(msg) + 1;
        snprintf(result, *length, "%s", msg);
        syslog(LOG_ERR, msg);
        return result;
    }

    if (!(args->args && args->args[0] && args->args[1] && args->args[2]))
    {
        const char *msg = "Param is null.";
        *isNull = 1;
        *length = strlen(msg) + 1;
        snprintf(result, *length, msg);
        return result;
    }

    syslog(LOG_INFO, "hashkey: %s, field: %s, value: %sn", args->args[0], args->args[1], args->args[2]);

    g_redis->clear();
    if (g_redis->hset(args->args[0], args->args[1], args->args[2]) >= 0)
    {
        *length = 2;
        snprintf(result, 2, "0");
        syslog(LOG_INFO, "update key %s field %s success from redis.", args->args[0], args->args[1]);
        return result;
    }
    else
    {
        *error = 1;
        const char *msg = "redis hset error.";
        *length = strlen(msg) + 1;
        snprintf(result, *length, "%s", msg);
        syslog(LOG_ERR, "hset failed: key:%s,field:%s,value:%s. %s:%s", args->args[0], args->args[1], args->args[2], msg, g_redis->result_error());
        return result;
    }
}

// 调用redis的hset命令
extern "C" char *redis_hdel(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length, char *isNull, char *error)
{
    if (!check_redis_conn())
    {
        *error = 1;
        const char *msg = "connect redis error!";
        *length = strlen(msg) + 1;
        snprintf(result, *length, "%s", msg);
        syslog(LOG_ERR, msg);
        return result;
    }

    if (!(args->args && args->args[0] && args->args[1]))
    {
        const char *msg = "Param is null.";
        *isNull = 1;
        *length = strlen(msg) + 1;
        snprintf(result, *length, msg);
        return result;
    }

    syslog(LOG_INFO, "arg0:%s,arg1:%sn", args->args[0], args->args[1]);

    g_redis->clear();
    if (g_redis->hdel(args->args[0], args->args[1]) >= 0)
    {
        *length = 2;
        snprintf(result, 2, "0");
        syslog(LOG_INFO, "del field %s success from key of redis.", args->args[1], args->args[0]);
        return result;
    }
    else
    {
        *error = 1;
        const char *msg = "redis hset error.";
        *length = strlen(msg) + 1;
        snprintf(result, *length, "%s", msg);
        syslog(LOG_ERR, "hdel failed: key:%s,field:%s. %s:%s", args->args[0], args->args[1], msg, g_redis->result_error());
        return result;
    }
}

// 调用redis的hset命令
extern "C" char *redis_set_addr(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length, char *isNull, char *error)
{
    if (!(args->args && args->args[0]))
    {
        const char *msg = "Param is null.";
        *isNull = 1;
        *length = strlen(msg) + 1;
        snprintf(result, *length, msg);
        return result;
    }

    syslog(LOG_INFO, "set redis addr is: %sn", args->args[0]);

    g_redis_addr=new char[50];
    memset(g_redis_addr,0,50);
    strncpy(g_redis_addr, args->args[0], 49);
}

/*资源分配*/
extern "C" my_bool redis_hset_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
{
    if (3 != args->arg_count || args->arg_type[0] != STRING_RESULT || args->arg_type[1] != STRING_RESULT || args->arg_type[2] != STRING_RESULT)
    { // hset(key, field, value) 需要三个参数
        strncpy(message, "please input 3 args and must be string, such as: hset('key', 'feild', 'value');", MYSQL_ERRMSG_SIZE);
        return -1;
    }
    args->arg_type[0] = STRING_RESULT;
    args->arg_type[1] = STRING_RESULT;
    args->arg_type[2] = STRING_RESULT;

    initid->ptr = NULL;
    return 0;
}

/*资源分配*/
extern "C" my_bool redis_hdel_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
{
    if (2 != args->arg_count || args->arg_type[0] != STRING_RESULT || args->arg_type[1] != STRING_RESULT)
    { // hset(key, field, value) 需要三个参数
        strncpy(message, "please input 2 args and must be string, such as: hdel('key', 'feild');", MYSQL_ERRMSG_SIZE);
        return -1;
    }
    args->arg_type[0] = STRING_RESULT;
    args->arg_type[1] = STRING_RESULT;

    initid->ptr = NULL;
    return 0;
}

/*资源分配*/
extern "C" my_bool redis_set_addr_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
{
    if (1 != args->arg_count || args->arg_type[0] != STRING_RESULT) // || args->arg_type[1] != STRING_RESULT || args->arg_type[2] != STRING_RESULT)
    {
        strncpy(message, "please input 1 args and must be string, such as: redis_set_addr('redis_addr');", MYSQL_ERRMSG_SIZE);
        return -1;
    }
    args->arg_type[0] = STRING_RESULT;
    // args->arg_type[1] = STRING_RESULT;
    // args->arg_type[2] = STRING_RESULT;

    initid->ptr = NULL;
    return 0;
}

// // 测试
// int main()
// {
//     char isNull;
//     char message[128] = {0};
//     char result[128] = {0};
//     unsigned long length = 0;
//     UDF_ARGS args;
//     UDF_INIT initid;
//     args.arg_count = 3;
//     args.args = new char *[3];
//     args.args[0] = new char[16];
//     args.args[1] = new char[16];
//     args.args[2] = new char[16];
//     args.arg_type = new Item_result[3];
//     strcpy(args.args[0], "mykey");
//     strcpy(args.args[1], "myfeild");
//     strcpy(args.args[2], "myvalue");
//     redis_hset_init(&initid, &args, message);
//     redis_hset(&initid, &args, result, &length, &isNull, message);
//     printf("redis result: %sn", result);
//     if (args.arg_type)
//         delete args.arg_type;
//     if (args.args)
//         delete args.args;
//     return 0;
// }

 

///

#include <stdio.h>
#include <stdlib.h>
#include <syslog.h>

#include <mysql.h>

#include <acl_cpp/acl_cpp_init.hpp>
#include <acl_cpp/redis/redis.hpp>
#include <acl_cpp/redis/redis_client.hpp>
#include <acl_cpp/redis/redis_client_cluster.hpp>

extern char **environ; //导入这个全局变量

acl::redis *g_redis = NULL;
acl::redis_client *g_redisCli = NULL;
acl::redis_client_cluster *g_cluster = NULL;

bool check_redis_conn()
{
    if (g_redis != NULL)
    {
        return true;
    }

    openlog("sync-redis-data", LOG_CONS | LOG_PID, LOG_LOCAL0);

    char *redisAddr = getenv("REDIS_ADDR");
    if (redisAddr == NULL)
    {
        syslog(LOG_EMERG, "REDIS_HOST environment variable is not set. mysql data is not to sync redis");
        return false;
    }

    char *redisPasswd = getenv("REDIS_PASSWORD");
    if (redisPasswd == NULL)
    {
        syslog(LOG_INFO, "REDIS_PASSWORD environment variable is not set.");
    }

    int redisCluster = 0;
    //默认不设置则为单机
    char *isCluster = getenv("REDIS_CLUSTER");
    if (isCluster != NULL)
    {
        redisCluster = atoi(isCluster);
    }

    //初始化redis集群连接
    int connTimeout = 30;
    int rwTimeout = 30;

    // acl::string redisAddr = "127.0.0.1:6379";

    if (redisCluster == 1)
    {
        g_cluster = new acl::redis_client_cluster();
        // g_cluster->init(NULL, redisAddr, 100, connTimeout, rwTimeout);
        g_cluster->set(redisAddr, 100, connTimeout, rwTimeout);
        g_cluster->set_password("default", redisPasswd);
        g_redis = new acl::redis(g_cluster);
    }
    else
    {
        g_redisCli = new acl::redis_client(redisAddr, connTimeout, rwTimeout);
        g_redisCli->set_slice_request(false);
        char *pwd = NULL;
        g_redisCli->set_password(pwd);
        g_redis = new acl::redis(g_redisCli);
    }

    syslog(LOG_INFO, "init redis conn success.");

    return true;
}

// 调用redis的hset命令
char *redis_hset(char *result, unsigned long *length, char *isNull, char *error)
{
    if (!check_redis_conn())
    {
        *error = 1;
        const char *msg = "connect redis error!";
        *length = strlen(msg) + 1;
        snprintf(result, *length, "%s", msg);
        syslog(LOG_ERR, msg);
        return result;
    }

    int ret = 0;

    g_redis->clear();
    ret = g_redis->hset("testkey", "testfield", "testvalue");
    printf("hset return value: %dn", ret);

    g_redis->clear();
    ret = g_redis->hset("testkey", "testfield", "testvalue");
    printf("hset return value: %dn", ret);

    g_redis->clear();
    ret = g_redis->hset("testkey", "testfield", "testvalue");
    printf("hset return value: %dn", ret);

    g_redis->clear();
    if (g_redis->hset("testkey", "testfield", "testvalue") < 0)
    {
        *error = 1;
        const char *msg = "redis hset error.";
        *length = strlen(msg) + 1;
        snprintf(result, *length, "%s", msg);
        printf("redis hset exec error.%sn", g_redis->result_error());
        syslog(LOG_ERR, "%s:%s", msg, g_redis->result_error());
        return result;
    }
    else
    {
        *length = 2;
        snprintf(result, 2, "0");
        //syslog(LOG_INFO, "update key %s field %s success from redis.", args->args[0], args->args[1]);
        printf("hset success.n");
        return result;
    }
}

// 测试
int main()
{
    // acl::acl_cpp_init();

    // openlog("sync-redis-data", LOG_CONS | LOG_PID, LOG_LOCAL0);

    // char *redisAddr = getenv("REDIS_ADDR");
    // if (redisAddr == NULL)
    // {
    //     syslog(LOG_EMERG, "REDIS_HOST environment variable is not set. mysql data is not to sync redis");
    // }

    // char *redisPasswd = getenv("REDIS_PASSWORD");
    // if (redisPasswd == NULL)
    // {
    //     syslog(LOG_INFO, "REDIS_PASSWORD environment variable is not set.");
    // }

    // int redisCluster = 0;
    // //默认不设置则为单机
    // char *isCluster = getenv("REDIS_CLUSTER");
    // if (isCluster != NULL)
    // {
    //     redisCluster = atoi(isCluster);
    // }

    // //初始化redis集群连接
    // int connTimeout = 30;
    // int rwTimeout = 30;

    // redisAddr = "127.0.0.1:6379";

    // if (redisCluster == 1)
    // {
    //     g_cluster = new acl::redis_client_cluster();
    //     g_cluster->init(NULL, redisAddr, 100, connTimeout, rwTimeout);
    //     g_cluster->set_password("default", redisPasswd);
    //     g_redis = new acl::redis(g_cluster);
    // }
    // else
    // {
    //     g_redisCli = new acl::redis_client(redisAddr, connTimeout, rwTimeout);
    //     g_redisCli->set_slice_request(false);
    //     char *pwd = NULL;
    //     g_redisCli->set_password(pwd);
    //     g_redis = new acl::redis(g_redisCli);
    // }

    // if (g_redis->monitor() == false)
    // {
    //     printf("redis monitor error: %s, addr: %s",
    //            g_redis->result_error(), redisAddr);
    //     return false;
    // }
    // syslog(LOG_INFO, "init redis conn success.");

    // int conn_timeout = 10;
    // int rw_timeout = 10;

    // acl::acl_cpp_init();
    // // acl::log::stdout_open(true);

    // acl::string addr("127.0.0.1:6379");
    // acl::redis_client_cluster cluster;
    // cluster.set(addr.c_str(), 100, conn_timeout, rw_timeout);

    // acl::redis_client client(addr.c_str(), conn_timeout, rw_timeout);
    // client.set_slice_request(false);

    // acl::redis_hash redis;
    // int cluster_mode = 0;
    // if (cluster_mode)
    //     redis.set_cluster(&cluster, 100);
    // else
    //     redis.set_client(&client);

    // g_redis = &redis;

    char isNull;
    char message[128] = {0};
    char result[128] = {0};
    unsigned long length = 0;
    redis_hset(result, &length, &isNull, message);
    printf("redis result: %sn", result);

    return 0;
}

 

转载于:https://my.oschina.net/u/583029/blog/1808345

最后

以上就是孤独大树为你收集整理的通过MySQL触发器同步MySQL数据到Redis中的全部内容，希望文章能够帮你解决通过MySQL触发器同步MySQL数据到Redis中所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
点赞(69)

相关文章

MySql 数据库中查询日期函数的使用
MySql 数据库中查询日期函数的使用
sql server创建流水号 年月日+流水号
sql server创建流水号 年月日+流水号
【数据库】Mysql日期/时间相关函数汇总
【数据库】Mysql日期/时间相关函数汇总
Mysql学习之--Mysql二进制日志管理
Mysql学习之--Mysql二进制日志管理
通过MySQL触发器同步MySQL数据到Redis中
通过MySQL触发器同步MySQL数据到Redis中
精通mysql可以做什么_MySQL8技术- 学完这个MySQL就精通了
精通mysql可以做什么_MySQL8技术- 学完这个MySQL就精通了
mysql自定义编码集_mysql自定义业务编码
mysql自定义编码集_mysql自定义业务编码
oracle主键的设置方法
oracle主键的设置方法

评论列表共有 0 条评论

立即
投稿
返回
顶部