Redis 作 mysql 的缓存服务器

实验环境：
server1：lnmp平台
server2：redis
server3：mysql

---

在servre1上搭建lnmp平台

lnmp的源码编译安装

配置redis 作为 mysql 的缓存服务器

查看php支持哪些模块

php -m 

我们需要php支持mysql和redis，安装redis模块

yum install unzip
unzip phpredis-master.zip 
cd phpredis-master
phpize
./configure 
make
make install

查看安装的模块

cd /usr/lib64/php/modules/
ls

将模块加入php

	cd /etc/php.d
	cp mysql.ini redis.ini
	vim redis.ini 
	extension=redis.so

	/etc/init.d/php-fpm reload

查看是否安装成功

 php -m | grep redis

在server3中安装数据库

yum install -y mysql-server
 /etc/init.d/mysqld start

授予redis用户权限

grant all on test.* to redis@'%' identified by 'westos';

创建test库test表，写入数据方便测试

在server1上编辑测试页面

vim /usr/share/nginx/html/index.php
  <?php
        $redis = new Redis();
        $redis->connect('172.25.31.2',6379) or die ("could net connect redis server");
  #      $query = "select * from test limit 9";
        $query = "select * from test";
        for ($key = 1; $key < 10; $key++)
        {
                if (!$redis->get($key))
                {
                        $connect = mysql_connect('172.25.31.3','redis','westos');
                        mysql_select_db(test);
                        $result = mysql_query($query);
                        //如果没有找到$key,就将该查询sql的结果缓存到redis
                        while ($row = mysql_fetch_assoc($result))
                        {
                                $redis->set($row['id'],$row['name']);
                        }
                        $myserver = 'mysql';
                        break;
                }
                else
                {
                        $myserver = "redis";
                        $data[$key] = $redis->get($key);
                }
        }
 

        echo $myserver;
        echo "<br>";
        for ($key = 1; $key < 10; $key++)
        {
                echo "number is <b><font color=#FF0000>$key</font></b>";
 
                echo "<br>";
 
                echo "name is <b><font color=#FF0000>$data[$key]</font></b>";
 
                echo "<br>";
        }
?>

在浏览器中访问172.25.31.1

到这里,我们已经实现了 redis 作为 mysql 的缓存服务器,但是如果更新了 mysql,redis中仍然会有对应的 KEY,数据就不会更新,此时就会出现 mysql 和 redis 数据不一致的情况。所以接下来就要通过 mysql 触发器将改变的数据同步到 redis 中。

配置 gearman 实现数据同步

Gearman 是一个支持分布式的任务分发框架:
Gearman Job Server: Gearman 核心程序,需要编译安装并以守护进程形式运行在后台。
Gearman Client:可以理解为任务的请求者。
Gearman Worker:任务的真正执行者,一般需要自己编写具体逻辑并通过守护进程方式运行,Gearman Worker 接收到 Gearman Client 传递的任务内容后,会按顺序处理。

server1:
安装 gearman 软件包:

 yum install gearmand-1.1.8-2.el6.x86_64.rpm libgearman-* libevent-*
service gearmand start #启动服务
netstat -antlp |grep gearmand

安装 php 的 gearman 扩展

tar zxf gearman-1.1.2.tgz 
cd gearman-1.1.2
phpize
./configure 
make && make install
cd /etc/php.d/
cp redis.ini gearman.ini
vim gearman.ini 
extension=gearman.so

/etc/init.d/php-fpm reload
php -m | grep gearman

编写 gearman 的 worker 端

vim /usr/local/worker.php
<?php
$worker = new GearmanWorker();
$worker->addServer();
$worker->addFunction('syncToRedis', 'syncToRedis');

$redis = new Redis();
$redis->connect('172.25.31.2', 6379);

while($worker->work());
function syncToRedis($job)
{
        global $redis;
        $workString = $job->workload();
        $work = json_decode($workString);
        if(!isset($work->id)){
                return false;
        }
        $redis->set($work->id, $work->name);
}
?>

后台运行 worker

nohup php /usr/local/worker.php &

server3:

安装 lib_mysqludf_json
lib_mysqludf_json UDF 库函数将关系数据映射为 JSON 格式。通常,数据库中的数据映
射为 JSON 格式,是通过程序来转换的

unzip lib_mysqludf_json-master.zip
cd lib_mysqludf_json-master
yum install -y gcc mysql-devel
gcc $(mysql_config --cflags) -shared -fPIC -o lib_mysqludf_json.so lib_mysqludf_json.c
cp lib_mysqludf_json.so /usr/lib64/mysql/plugin/

查看 mysql 的模块目录

mysql> show global variables like 'plugin_dir';
+---------------+-------------------------+
| Variable_name | Value                   |
+---------------+-------------------------+
| plugin_dir    | /usr/lib64/mysql/plugin |
+---------------+-------------------------+
1 row in set (0.00 sec)

注册 UDF 函数

mysql> CREATE FUNCTION json_object RETURNS STRING SONAME
    -> 'lib_mysqludf_json.so';
Query OK, 0 rows affected (0.00 sec)

查看函数

mysql> select * from mysql.func;
+-------------+-----+----------------------+----------+
| name        | ret | dl                   | type     |
+-------------+-----+----------------------+----------+
| json_object |   0 | lib_mysqludf_json.so | function |
+-------------+-----+----------------------+----------+
1 row in set (0.00 sec)

安装 gearman-mysql-udf
这个插件是用来管理调用 Gearman 的分布式的队列。

tar zxf gearman-mysql-udf-0.6.tar.gz
cd gearman-mysql-udf-0.6
yum install gearmand-1.1.8-2.el6.x86_64.rpm libgearman-* libevent-*
cd gearman-mysql-udf-0.6
./configure --libdir=/usr/lib64/mysql/plugin/
make && make install

注册 UDF 函数

mysql> CREATE FUNCTION gman_do_background RETURNS STRING SONAME
    -> 'libgearman_mysql_udf.so';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FUNCTION gman_servers_set RETURNS STRING SONAME
    -> 'libgearman_mysql_udf.so';
Query OK, 0 rows affected (0.00 sec)

查看函数

mysql> select * from mysql.func;
+--------------------+-----+-------------------------+----------+
| name               | ret | dl                      | type     |
+--------------------+-----+-------------------------+----------+
| json_object        |   0 | lib_mysqludf_json.so    | function |
| gman_do_background |   0 | libgearman_mysql_udf.so | function |
| gman_servers_set   |   0 | libgearman_mysql_udf.so | function |
+--------------------+-----+-------------------------+----------+
3 rows in set (0.00 sec)

指定 gearman 的服务信息

mysql> SELECT gman_servers_set('172.25.31.1:4730');
+--------------------------------------+
| gman_servers_set('172.25.31.1:4730') |
+--------------------------------------+
| 172.25.31.1:4730                     |
+--------------------------------------+
1 row in set (0.00 sec)

编写 mysql 触发器

vim test.sql 
DELIMITER $$
CREATE TRIGGER datatoredis AFTER UPDATE ON test FOR EACH ROW BEGIN
    SET @RECV=gman_do_background('syncToRedis', json_object(NEW.id as `id`, NEW.name as `name`));
  END$$
DELIMITER ;

将触发器导入数据库

mysql < test.sql

查看触发器

mysql> SHOW TRIGGERS FROM test;
+-------------+--------+-------+----------------------------------------------------------------------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+
| Trigger     | Event  | Table | Statement                                                                                                      | Timing | Created | sql_mode | Definer        | character_set_client | collation_connection | Database Collation |
+-------------+--------+-------+----------------------------------------------------------------------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+
| datatoredis | UPDATE | test  | BEGIN
    SET @RECV=gman_do_background('syncToRedis', json_object(NEW.id as `id`, NEW.name as `name`)); 
  END | AFTER  | NULL    |          | root@localhost | latin1               | latin1_swedish_ci    | latin1_swedish_ci  |
+-------------+--------+-------+----------------------------------------------------------------------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

测试

在数据库中更新数据

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update test set name='redhat';
Query OK, 9 rows affected (0.09 sec)
Rows matched: 9  Changed: 9  Warnings: 0

mysql> select * from test;
+----+--------+
| id | name   |
+----+--------+
|  1 | redhat |
|  2 | redhat |
|  3 | redhat |
|  4 | redhat |
|  5 | redhat |
|  6 | redhat |
|  7 | redhat |
|  8 | redhat |
|  9 | redhat |
+----+--------+
9 rows in set (0.00 sec)

在浏览器中访问172.25.31.1