Redis与mysql同步之路

【java】gearman进行Mysql到Redis的复制

一.整体思路说明

以mysql数据为主，写操作（insert，update，delete）交于mysql,读操作交于redis。当数据库数据发生改变时，通过Mysql触发器（insert,update,delete）异步调用gearman的UDF提交一个job给job server（可以由PHP，也可以用java），当执行job的时候去更新redis，达到redis与mysql数据是同步的。

二、环境 mysql，redis，gearman，Centos 64位

三、简介

1.redis简介

Redis是一个开源的K-V内存数据库，它的key可以是string/set/hash/list/...，因为是基于内存的，所在访问速度相当快。

2.gearman简介

Gearman是一个开源的Map/Reduce分布式计算框架，具有丰富的client sdk，而且它支持MySQL UDF。

四、环境搭建（mysql，redis，gearman，Centos 64位）

1.redis的环境安装

linux安装redis详解

2.gearman的环境搭建

//安装依赖$ yum install -y boost-devel gperf libevent-devel libuuid-devel//下载gearman  2015-12-10来看都是最新版本$ wget https://launchpad.net/gearmand/1.2/1.1.12/+download/gearmand-1.1.12.tar.gz$ tar zxvf gearmand-1.1.12.tar.gz//编译安装，指定mysqlclient的链接路径 进入gearmand-1.1.12

$ cd gearmand-1.1.12$ ./configure$ make$ make install//启动gearmand服务端 （启动之时，在/var/log/下创建gearmand.log日志文件。-l 指定日志文件  -d后台运行 -L 0.0.0.0 绑定到IPV4）$ /usr/local/sbin/gearmand -L 0.0.0.0 -l /var/log/gearmand.log -d//查看是否启动成功[root@iZ94uyronrjZ mysql]# ps -ef | grep gearmanroot     31142     1  0 17:06 ?        00:00:00 /usr/local/sbin/gearmand -L 0.0.0.0 -l /znwx/logs/gearmand/gearmand.log -droot     31174 30507  0 17:40 pts/0    00:00:00 grep gearman//查看是否安装成功，查看gearman版本$ /usr/local/sbin/gearmand -V

安装问题解决：执行./configure(前提已安装boost)

configure:error :could not find boost

解决方法

$ yum install gcc-c++ 

3.MySQL UDF + Trigger同步数据到Gearman

3.1安装lib_mysqludf_json

//安装mysql的开发包，否则找不到mysql_config文件$ yum install -y  mysql-devel//下载lib_mysqludf_json（地址：https://github.com/mysqludf）$ cd lib_mysqludf_json先删除 lib_mysqludf_json.so//编译 mysql_config 这是mysql的配置文件，可以 find /usr -name mysql_config 搜索下在什么位置$ gcc $(/usr/local/mysql/bin/mysql_config  --cflags) -shared -fPIC -o lib_mysqludf_json.so lib_mysqludf_json.c//拷贝lib_mysqludf_json.so到MySQL的plugin目录//可以登陆MySQL，输入命令"show variables like '%plugin%'"查看plugin位置$ cp lib_mysqludf_json.so /usr/lib64/mysql/plugin///演示lib_mysqludf_json功能$ mysql -uname -hhost -ppwd//首先注册UDF函数mysql> CREATE FUNCTION json_object RETURNS STRING        SONAME "lib_mysqludf_json.so";//json_array|json_members|json_values函数注册方式与json_object一样.mysql> use test;mysql> select * from user_list;+------+----------+| NAME | PASSWORD |+------+----------+| troy | pwd      |+------+----------+mysql> select json_object(name,password) as user from user_list;+----------------------------------+| user                             |+----------------------------------+| {"name":"troy","password":"pwd"} |+----------------------------------+

3.2 安装gearman-mysql-udf

//下载 2015年12月10日已知最新版本$ wget https://launchpad.net/gearman-mysql-udf/trunk/0.6/+download/gearman-mysql-udf-0.6.tar.gz$ tar zxvf gearman-mysql-udf-0.6.tar.gz$ cd gearman-mysql-udf-0.6//安装libgearman-devel$ yum install libgearman-devel//编译安装//可以登陆MySQL，输入命令"show variables like '%plugin%'"查看plugin位置， mysql_config的配置文件，以及插件库所在路径，编译之后会在此路径生成.so文件./configure --with-mysql=/usr/local/mysql/bin/mysql_config --libdir=/usr/local/mysql/lib/plugin/$ make && make install//登录MySQL注册UDF函数mysql> CREATE FUNCTION gman_do_background RETURNS STRING       SONAME "libgearman_mysql_udf.so";mysql> CREATE FUNCTION gman_servers_set RETURNS STRING       SONAME "libgearman_mysql_udf.so";//函数gman_do|gman_do_high|gman_do_low|gman_do_high_background|gman_do_low_background|gman_sum注册方式类似，请参考gearman-mysql-udf-0.6/README//指定gearman job server地址mysql> SELECT gman_servers_set('127.0.0.1:4730');

如果出现异常信息：ERROR 1126 (HY000): Can't open shared library 'libgearman_mysql_udf.so' (errno: 11 libgearman.so.8: cannot open shared object file: No such file or directory)表示系统找不到 libgearman.so 文件，一般so都在/usr/local/lib目录下，修改配置文件/etc/ld.so.conf，将/usr/local/lib目录加入进去即可:操作如下

$ cat /etc/ld.so.confinclude ld.so.conf.d/*.conf/usr/local/lib$ /sbin/ldconfig -v | grep gearman*

3.3 MySQL Trigger调用Gearman UDF实现同步（user_list表添加id字段）

DELIMITER $$CREATE TRIGGER user_list_data_to_redis AFTER UPDATE ON user_list  FOR EACH ROW BEGIN    SET @ret=gman_do_background('MySQLToRedis', json_object(New.id as 'id',NEW.name as 'name', NEW.password as 'password'));   END$$DELIMITER ;

说明：mysql只支持单事件的触发，insert与delete需另外配置

五、java实现job server 和worker或者单worker

说明以及问题：此类采用了gearman官网的java-gearman-service（地址：https://code.google.com/p/java-gearman-service/），目前release版本是0.6.6。CSDN下载地址

还需要的redis的ja，采用maven配置。本次演示采用本地主程序测试.

<dependency>      <groupId>redis.clients</groupId>      <artifactId>jedis</artifactId>      <version>2.1.0</version>  </dependency> 

废话不多说，直接上java代码

package com.yuanjun.webSocket.redis;import java.util.concurrent.TimeUnit;import net.sf.json.JSONObject;import org.gearman.Gearman;import org.gearman.GearmanFunction;import org.gearman.GearmanFunctionCallback;import org.gearman.GearmanServer;import org.gearman.GearmanWorker;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import org.springframework.stereotype.Component;import redis.clients.jedis.Jedis;import redis.clients.jedis.JedisPool;import com.yuanjun.webSocket.bean.Demo;/**  * mysql 同步到 redis 的工具类。连接远程gearman job server  * 实现原理   * mysql_udf  >>>>>  gearman job server>>>> gearm worker(本类) >>>>> redis  * @author yuanjun 2017年11月17日  * 说明：Config配置采用手动配置，非配置文件的方式，只作为环境搭建的实验 */  @Component  public class MysqlToRedisWorker implements GearmanFunction{      private static final Logger log = LoggerFactory.getLogger(MysqlToRedisWorker.class);      //Gearman UDF的方法名      public static final String ECHO_FUNCTION_NAME = "MySQLToRedis";      //Gearman服务的ip地址     public static final String EchoHost = "47.94.208.171";    //Gearman的默认端口    public static final int EchoPort = 4730;            /**      * 连接其他jobserver用的。      */      public void startWorker() {          Gearman gearman = Gearman.createGearman();  //创建gearman对象，无论是client，worker都是由这个对象产生的          log.info("MysqlToRedisWorkder connection:"+EchoHost+":"+EchoPort+",function:"+MysqlToRedisWorker.ECHO_FUNCTION_NAME);          GearmanServer server = gearman.createGearmanServer(EchoHost, EchoPort);  //创建gearman server，主要是server地址和端口            GearmanWorker worker = gearman.createGearmanWorker();  //正题来了，创建work节点。          worker.setReconnectPeriod(2, TimeUnit.SECONDS);  //设置超时重连时间          worker.setMaximumConcurrency(5);  //最大并发数            worker.addFunction(ECHO_FUNCTION_NAME, this);  //添加function方法          worker.addServer(server);  //将work添加到server中          log.info("MysqlToRedisWorkder is started!!!!");      }      /**     * 具体的job实现，实现redis的更新操作       */    public byte[] work(String func, byte[] data, GearmanFunctionCallback callback)              throws Exception {                    log.info("收到mysql的数据：：："+new String(data));        String jsonStr = new String(data);        //将json格式字符串转为object        JSONObject jsonobject = JSONObject.fromObject(jsonStr);        Demo user= (Demo)JSONObject.toBean(jsonobject,Demo.class);         //Jedis的连接池        JedisPool jedisPool = RedisPoolUtil.getJedisPool();        Jedis jedis  = jedisPool.getResource();        //采用hash结构插入        jedis.hset("user_"+user.getId(), "name", user.getName());        jedis.hset("user_"+user.getId(), "password", user.getPassword());        //释放资源        RedisPoolUtil.release(jedisPool, jedis);        return data;      }}   

package com.yuanjun.webSocket.bean;/** * 对于的实体类 * @author Administrator * */public class Demo {private int id ;//是否删除private boolean isdelete;private String name;private String password;public Demo(){}public Demo(int id, boolean isdelete,String name,String password){this.id = id ;this.isdelete = isdelete;this.name = name;this.password  = password;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getPassword() {return password;}public void setPassword(String password) {this.password = password;}public int getId() {return id;}public void setId(int id) {this.id = id;}public boolean isIsdelete() {return isdelete;}public void setIsdelete(boolean isdelete) {this.isdelete = isdelete;}@Overridepublic String toString() {return "Demo [id=" + id + ", isdelete=" + isdelete + ", name=" + name+ ", password=" + password + "]";}}

package com.yuanjun.webSocket.redis;import redis.clients.jedis.Jedis;import redis.clients.jedis.JedisPool;import redis.clients.jedis.JedisPoolConfig;/** * redis 连接池工具类 * @author Administrator * */public class RedisPoolUtil { //Redis服务器IP    private static String ADDR = "47.94.208.171";        //Redis的端口号    private static int PORT = 6379;        //访问密码    private static String AUTH = "123456";        //可用连接实例的最大数目，默认值为8；    //如果赋值为-1，则表示不限制；如果pool已经分配了maxActive个jedis实例，则此时pool的状态为exhausted(耗尽)。    private static int MAX_ACTIVE = 1024;        //控制一个pool最多有多少个状态为idle(空闲的)的jedis实例，默认值也是8。    private static int MAX_IDLE = 200;        //等待可用连接的最大时间，单位毫秒，默认值为-1，表示永不超时。如果超过等待时间，则直接抛出JedisConnectionException；    private static int MAX_WAIT = 10000;        private static int TIMEOUT = 10000;        //在borrow一个jedis实例时，是否提前进行validate操作；如果为true，则得到的jedis实例均是可用的；    private static boolean TEST_ON_BORROW = true;private static volatile JedisPool jedisPool = null;private RedisPoolUtil(){}public static JedisPool getJedisPool(){if(null ==jedisPool){synchronized (JedisPool.class) {if(null == jedisPool){JedisPoolConfig poolConfig = new JedisPoolConfig();poolConfig.setMaxActive(MAX_ACTIVE);poolConfig.setMaxIdle(MAX_IDLE);poolConfig.setMaxWait(MAX_WAIT);poolConfig.setTestOnBorrow(TEST_ON_BORROW);jedisPool = new JedisPool(poolConfig, ADDR, PORT, TIMEOUT, AUTH);}}}return jedisPool;}public static void release(JedisPool jedisPool,Jedis jedis){if(null!=jedis){jedisPool.returnResource(jedis);}}}

package com.yuanjun.webSocket.Test;import com.yuanjun.webSocket.redis.MysqlToRedisWorker;/** * 演示测试 * @author Administrator * */public class Test {public static void main(String[] args) {MysqlToRedisWorker mysql = new MysqlToRedisWorker();mysql.startWorker();}}

六、测试结果展示