MySQL数据同步到Redis - 黑帽联盟

官方插件 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

首先要动态库的正确性要不然注册函数就会报错
-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);

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);

至于触发器里面的内容不懂的可以去百度了这里不做解释

看测试结果
先情况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
mysql> select * from user_info;
+----+------+------+-------------+-------+
| id | NAME | age  | email    | addr  |
+----+------+------+-------------+-------+
|  1 | 111  | 26 | lcnblackhat@qq.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) "cnblackhat@qq.com"
7) "addr"
8) "1111 "
6.6.6.6:6379>