通过MySQL触发器UDF自动同步刷新Redis
将编译得到的动态库放入/usr/lib/mysql/plugin
- h_myredis_udf.so
- libmysql_redis_udf_2nd.so
- libmysql_redis_udf.so
- libmysql_json_udf.so
创建数据库, 数据表, 触发器, udf
#创建数据库
create database if not exists db_online_tv;
#创建数据表
create table if not exists db_online_tv.tb_slot_version
(
id int(32) unsigned auto_increment
primary key,
version int(32) unsigned not null comment '版本号',
content longtext not null comment '发布的内容',
publish_time datetime not null comment '发布时间',
use_version tinyint unsigned not null comment '使用的版本'
)
comment '在线视频发布表' default charset='utf8';
#创建触发器
Delimiter //
DROP TRIGGER IF EXISTS `db_online_tv`.`tr_slot_version_insert`//
CREATE TRIGGER `db_online_tv`.`tr_slot_version_insert` AFTER INSERT ON `db_online_tv`.`tb_slot_version`
FOR EACH ROW
BEGIN
SET @redis_host = '127.0.0.1';
SET @redis_port = 6379;
SET @redis_pwd = '';
SET @operator = 'HSET';
SET @h_key = 'pub_slot_online_tv_version';
SET @h_field = CONCAT(UNIX_TIMESTAMP(NEW.publish_time));
SET @value = json_object(NEW.version, NEW.content, UNIX_TIMESTAMP(NEW.publish_time), NEW.use_version);
SET @conn = redis_server_set(@redis_host, @redis_port, @redis_pass);
SET @ret = redis_exec(@operator, @h_key, @h_field, @value);
END
//
Delimiter ;
#定义udf
DROP FUNCTION IF EXISTS redis_server_set; CREATE FUNCTION redis_server_set RETURNS string SONAME "libmysql_redis_udf_2nd.so";
DROP FUNCTION IF EXISTS redis_exec; CREATE FUNCTION redis_exec RETURNS integer SONAME "libmysql_redis_udf_2nd.so";
导入数据测试触发mysql同步到redis
INSERT INTO db_online_tv.tb_slot_version (version, content, publish_time, use_version) VALUES (1482307790, '儿童天地', '2020-06-22 16:01:00', 1);
查看redis是否同步
./src/redis-cli --raw
127.0.0.1:6379> hgetall pub_slot_online_tv_version
1592812860
{"1482307790": "儿童天地", "1592812860": 1}
