IT博客汇
  • 首页
  • 精华
  • 技术
  • 设计
  • 资讯
  • 扯淡
  • 权利声明
  • 登录 注册

    关于mysql 同步reis 用redis做mysql的读写分离插件部署配置

    RuM发表于 2016-04-15 08:19:12
    love 0

    官方插件  https://github.com/dawnbreaks/mysql2redis
    安装插件 以及依赖
    // 安装apr + apr-util
    $ wget http://mirror.bit.edu.cn/apache//apr/apr-1.5.1.tar.gz
    $ tar zxvf apr-1.5.1.tar.gz
    $ ./configure --prefix=/usr/local/apr
    $ make
    $ make install

    $ wget http://mirror.bit.edu.cn/apache//apr/apr-util-1.5.4.tar.gz
    $ cd apr-util-1.5.4
    $ ./configure --with-apr=/usr/local/apr
    $ make
    $ make install

    // 安装hiredis, redis的C语言client
    $ git clone https://github.com/redis/hiredis
    $ cd hiredis
    $ make
    $ make install

    $ git clone https://github.com/dawnbreaks/mysql2redis.git
    $ cd mysql2redis
    $ make
    $ cp lib_mysqludf_redis_v2.so /usr/lib64/mysql/plugin/


    首先要动态库的正确性 要不然注册函数就会报错
    -bash-4.1# ldd /usr/local/mysql/lib/plugin/lib_mysqludf_redis_v2.so 
            Linux-vdso.so.1 =>  (0x00007fff98f5a000)
            libhiredis.so.0.13 => /usr/local/lib/libhiredis.so.0.13 (0x00007f255127e000)
            libapr-1.so.0 => /usr/lib64/libapr-1.so.0 (0x00007f2551051000)
            libaprutil-1.so.0 => /usr/lib64/libaprutil-1.so.0 (0x00007f2550e2d000)
            libc.so.6 => /lib64/libc.so.6 (0x00007f2550a99000)
            libuuid.so.1 => /lib64/libuuid.so.1 (0x00007f2550895000)
            libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007f255065d000)
            libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f2550440000)
            libexpat.so.1 => /lib64/libexpat.so.1 (0x00007f2550218000)
            libdb-4.7.so => /lib64/libdb-4.7.so (0x00007f254fea3000)
            /lib64/ld-linux-x86-64.so.2 (0x00000038c2800000)
            libfreebl3.so => /lib64/libfreebl3.so (0x00007f254fc2c000)
            libdl.so.2 => /lib64/libdl.so.2 (0x00007f254fa28000)
            
            保证里面没有错误的
    然后注册 mysq2redis UDF的函数

    CREATE FUNCTION redis_servers_set_v2 RETURNS int SONAME "lib_mysqludf_redis_v2.so";
    CREATE FUNCTION redis_command_v2 RETURNS int SONAME "lib_mysqludf_redis_v2.so";
    CREATE FUNCTION free_resources RETURNS int SONAME "lib_mysqludf_redis_v2.so";

    下面开始在数据库里面建表做测试
        我们先在tset里面新建一张表
        CREATE TABLE `user_info` (
      `id` int(100) NOT NULL,
      `NAME` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
      `age` varchar(11) COLLATE utf8_unicode_ci DEFAULT NULL,
      `email` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
      `addr` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
     
     然后新建 一个insert 跟upudate的触发器
     CREATE TRIGGER `trigger_INSERT` AFTER INSERT ON `user_info` FOR EACH ROW BEGIN
    set @ret=(select redis_servers_set_v2("6.6.6.6",6379)); 
    SET @ret1=(select redis_command_v2("hmset", concat("user_info:", id), 'name', name, 'age', age, 'email', email, 'addr', addr) from user_info where id = new.id);

    end;

    CREATE TRIGGER `trigger_update` AFTER UPDATE ON `user_info` FOR EACH ROW BEGIN
    SET @ret=(select redis_servers_set_v2("6.6.6.6",6379)); 
    SET @ret1=(select redis_command_v2("hmset", concat("user_info:", id), 'name', name, 'age', age, 'email', email, 'addr', addr) from user_info where id = old.id);

    end;

    至于触发器里面的内容不懂的可以去百度了这里不做解释
    看测试结果
    先情况redis 里面的数据
    6.6.6.6:6379> flushall
    OK
    6.6.6.6:6379> keys *
    (empty list or set)
    在数据库里面插入数据
    mysql> TRUNCATE table user_info;  # 清空表数据
    Query OK, 0 rows affected (0.04 sec)

    mysql> INSERT INTO `user_info` VALUES (1, '111', 26, 'lrm@163.com', '1111 ');


    进行查询  mysql
    mysql> select * from user_info;
    +----+------+------+-------------+-------+
    | id | NAME | age  | email       | addr  |
    +----+------+------+-------------+-------+
    |  1 | 111  | 26   | lrm@163.com | 1111  |
    +----+------+------+-------------+-------+
    1 row in set (0.00 sec)

    mysql> 
    进行查询 redis
    6.6.6.6:6379> keys *
    (empty list or set)
    6.6.6.6:6379> keys *
    1) "user_info:1"
    6.6.6.6:6379> hgetall user_info:1
    1) "name"
    2) "111"
    3) "age"
    4) "26"
    5) "email"
    6) "lrm@163.com"
    7) "addr"
    8) "1111 "
    6.6.6.6:6379> 

    到这里我们就配置完成了



沪ICP备19023445号-2号
友情链接