UDF是mysql的一个拓展接口,UDF(Userdefined function)用户自定义函数。应用场景:
- 网站使用mysql作为存储引擎,而redis作为缓存以减小查询请求穿透到mysql的数量,并将将缓存逻辑和业务逻辑分离,缓存层对业务逻辑提供服务透明,业务逻辑不用关心缓存逻辑,缓存逻辑也不用随业务变化而改动,其实就是把缓存更新的逻辑,放到mysql中去做。
- 这种方式的架构,当有查询请求的时候,我们可以在业务逻辑层控制:
读取- client读取redis,如果命中返回结果,如果没有命中转到2.
- client读取数据库,在数据库中没有查到,返回空;在数据库中查到了,返回查到的结果并更新Redis。
写入: - client修改/删除或者新增数据到MySQL。
- MySQL的触发器调用用户自定义的UDF。
- UDF把修改/删除或者新增的数据更新到redis中。
开发环境
$ uname -a
Linux ubuntu 4.18.0-13-generic #14-Ubuntu SMP Wed Dec 5 09:04:24 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
mysql> select version();
+-------------------------+
| version() |
+-------------------------+
| 5.7.24-0ubuntu0.18.10.1 |
+-------------------------+
一、UDF函数入门
- 编写函数文件,test_add.cpp如下
#include <mysql.h>
extern "C" long long testadd(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error)
{
int a = *((long long *)args->args[0]);
int b = *((long long *)args->args[1]);
return a + b;
}
extern "C" my_bool testadd_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
{
return 0;
}
编译:
$ sudo g++ -shared -fPIC -I /usr/include/mysql -o test_add.so test_add.cpp
-fPIC 代表编译后位置独立
-shared 代表生成的是动态链接库
- 插件部署
插件路径
mysql> show variables like '%plugin%';
+-------------------------------+------------------------+
| Variable_name | Value |
+-------------------------------+------------------------+
| default_authentication_plugin | mysql_native_password |
| plugin_dir | /usr/lib/mysql/plugin/ |
+-------------------------------+------------------------+
拷贝到mysql插件目录下:
$ cp test_add.so /usr/lib/mysql/plugin/
- 登录mysql,创建函数关联
mysql> create function testadd returns integer soname 'test_add.so';
Query OK, 0 rows affected (0.00 sec)
- 测试
mysql> select testadd(1,2);
+--------------+
| testadd(1,2) |
+--------------+
| 3 |
+--------------+
- 删除
先删除UDF函数
mysql> drop function testadd;
然后删除插件目录下的.so文件
$ rm -f /usr/lib/mysql/plugin/test_add.so
二、结合redis做缓存更新
1.redis准备
下载启动redis
https://blog.csdn.net/zhizhengguan/article/details/85047649
下载安装hiredis
hiredis可以通过C语言连接数据库
- 下载编译
$ git clone https://github.com/redis/hiredis
$ cd hiredis/
$ make
$ sudo make install # 把动态链接库拷贝到/usr/local/lib/中
- 测试是否成功安装hiredis
$ mkdir hiredistest
$ sudo gedit hiredistest.c
键入如下代码
#include <stdio.h>
#include <hiredis/hiredis.h>
int main()
{
redisContext *conn = redisConnect("127.0.0.1",6379);
if(conn != NULL && conn->err)
{
printf("connection error: %s\n",conn->errstr);
return 0;
}
redisReply *reply = (redisReply*)redisCommand(conn,"set foo 1234");
freeReplyObject(reply);
reply = redisCommand(conn,"get foo");
printf("%s\n",reply->str);
freeReplyObject(reply);
redisFree(conn);
return 0;
}
- 编译运行
$ gcc -o hiretest hiredistest.c -L/usr/local/lib/ -lhiredis
#-L 代表库文件的位置
#-l 代表扩展名称
$ ./hiretest
./hiretest: error while loading shared libraries: libhiredis.so.0.14: cannot open shared object file: No such file or directory 【动态库无法加载】
解决问题
$ sudo gedit /etc/ld.so.conf.d/usr-libs.conf
添加内容:/usr/local/lib
更新动态库配置:/sbin/ldconfig
$ ./hiretest
2. 用sql操作redis
用C/C++封装方法 :hset方法 和 hget方法
编写测试文件
#include <stdio.h>
#include <string.h>
#include <string>
#include "mysql.h"
#include <hiredis/hiredis.h>
using namespace std;
const char *redisHost="127.0.0.1";
int redisPort=6379;
extern "C" long long hset(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error)
{
redisContext* c = redisConnect(redisHost, redisPort);
char *tabName = (char *)args->args[0];
char *keyField = (char *)args->args[1];
char *valueField =(char *)args->args[2];
if ( c->err)
{
redisFree(c);
strcpy(error,"connect error");
return -1;
}
redisReply* r = (redisReply*)redisCommand(c, " hset %s %s %s",tabName,keyField,valueField);
freeReplyObject(r); //free redis command
redisFree(c); //free redis connect
return 1;
}
extern "C" my_bool hset_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
{
if(args->arg_count!=3){
strcpy(
message
, "Expected exactly three arguments"
);
return 1;
}
return 0;
}
extern "C" char *hget(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error)
{
redisContext* c = redisConnect(redisHost, redisPort);
char *tabName = (char *)args->args[0];
char *keyField = (char *)args->args[1];
if ( c->err)
{
redisFree(c);
strcpy(error,"connect error");
return NULL;
}
redisReply* r = (redisReply*)redisCommand(c, " hget %s %s ",tabName,keyField);
if ( r->type != REDIS_REPLY_STRING)
{
freeReplyObject(r);
redisFree(c);
return NULL;
}
string getStr=r->str;
char *getResult=const_cast<char*>(getStr.c_str());
// strcpy(getResult,getStr.c_str());
freeReplyObject(r); //free redis command
redisFree(c); //free redis connect
return getResult;
}
extern "C" my_bool hget_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
{
if(args->arg_count!=2){
strcpy(
message
, "hget needs tablename and keyfield"
);
return 1;
}
return 0;
}
- 编译链接
sudo g++ -shared -fPIC -o test.so test.cpp -I /usr/include/mysql/ -L /usr/local/lib/ -l hiredis
- 插件部署
$ sudo cp test.so /usr/lib/mysql/plugin/
- 登录mysql,创建函数关联
mysql> create function hget returns string soname 'test.so';
mysql> create function hset returns string soname 'test.so';
出现的错误:
ERROR 1126 (HY000): Can't open shared library 'test.so' (errno: 2 /usr/lib/mysql/plugin/test.so: undefined symbol: freeReplyObject)
解决:编译时-shared -fPIC -o 参数移至最前面:
sudo g++ -shared -fPIC -o test.so test.cpp -I /usr/include/mysql/ -L /usr/local/lib/ -l hiredis
错误2:ERROR 1126 (HY000): Can’t open shared library ‘test.so’ (errno: 11 libhiredis.so.0.14: cannot open shared object file: No such file or directory)
解决:
$ cd /usr/local/lib/
$ ls libh*
libhiredis.a libhiredis.so libhiredis.so.0.14
$ sudo cp /usr/local/lib/libhiredis.so.0.14 /usr/lib/
- 测试
登陆Mysql,操作redis
mysql> select hset('test','1','jack');
mysql> select hget('test','1');
登陆到redis
- 删除【并没有删除这这个插件】
先删除UDF函数
mysql> drop function hset;
mysql> drop function hget;
然后删除插件目录下的.so文件
$ rm -f /usr/lib/mysql/plugin/test.so
2.用触发器实现动态更新redis缓存
https://blog.csdn.net/socho/article/details/52292064
【未完】
参考:https://blog.csdn.net/socho/article/details/52292064
https://blog.csdn.net/qq_27385759/article/details/78981884
https://blog.csdn.net/txl13109187932/article/details/78885954
https://www.cnblogs.com/henryliublog/p/9170666.html
https://blog.csdn.net/achelloworld/article/details/41598389?utm_source=tuicool&utm_medium=referral
https://curl.haxx.se/download.html http插件
https://www.jianshu.com/p/b1b794e12013?utm_campaign=maleskine&utm_content=note&utm_medium=seo_notes&utm_source=recommendation