mysql到redis的复制

系统开发中时常会需要缓存来提升并发读的能力，这时可以通过mysql的UDF和hiredis来进行同步

前题：安装了mysql5.5和client

1、安装mysql2redis

git clone https://github.com/jackeylu/mysql2redis.git
cd mysql2redis
./install.sh

2、安装hiredis

git clone http://github.com/redis/hiredis
cd hiredis
make && make install

3、安装mysql json udf

git clone https://github.com/mysqludf/lib_mysqludf_json.git
cd lib_mysqludf_json
gcc $(/usr/local/mariamysql/bin/mysql_config --cflags) -shared -fPIC -o lib_mysqludf_json.so lib_mysqludf_json.c

然后将lib_mysqludf_json.so拷贝到mysql的plugin
在mysql里执行lib_mysqludf_json.sql

4、在mysql里创建table，trigger

CREATE  TABLE IF NOT EXISTS `test`.`t_users` (
  `user_name` VARCHAR(50) NOT NULL ,
  `nick_name` VARCHAR(100) NOT NULL ,
  `password` VARCHAR(32) NOT NULL ,
  `age` INT NULL ,
  `create_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
  PRIMARY KEY (`user_name`) ,
  UNIQUE INDEX `un_t_users_nick_name` (`nick_name` ASC) )
ENGINE = InnoDB;

DELIMITER |
create trigger tri_users_redis_json BEFORE INSERT on t_users
  For EACH ROW 
BEGIN
DECLARE done INT DEFAULT 999;

set done = redis_command("127.0.0.1",6379,concat("set user::",cast(NEW.user_name as CHAR)," ",json_object(NEW.user_name as userName,NEW.nick_name as nickName,NEW.age as age)));

if done>0 then
  SIGNAL sqlstate '45001' set message_text = "Redis error! ";
end if ;

END;
|
DELIMITER ;

insert into t_users(user_name,nick_name,password,age,create_time) values('Sally','雪莉','123456',25,CURRENT_TIMESTAMP)

mysql的
[img]http://dl.iteye.com/upload/attachment/0083/6230/d7b13ab9-157e-3085-813a-0205dca9faf9.png[/img]

redis的
[img]http://dl.iteye.com/upload/attachment/0083/6240/dcc870d2-ef83-3cbc-bd04-b8d01a92979b.png[/img]

使用mysql的udf和trigger可以保证mysql和redis的数据一致性，SIGNAL sqlstate '45001'会在redis失败时回滚事物。

问题:hiredis里递交带空格数据需要这样使用

reply = redisCommand(context, "SET key:%s %s", myid, value);

那么对于mysql2redis的redis_command是无法工作的，这部分可以为不同命令单写函数。如redis_command_set。

为了得到更好的灵活性，现在在使用gearman的UDF client，php的worker联合工作。