Reids作为MySQL的缓存服务器（Redis+Nginx+MySQL+PHP）
Redis做为数据库的缓存
实验环境：
server1 172.25.66.1 nginx + php
server2 172.25.66.2 redis 服务器
server3 172.25.66.3 mysql 数据库
1.部署Nginx
安装包：
nginx-1.15.7.tar.gz
1.源码编译安装nginx
#1.下载nginx源码包并解压
[[email protected] ~]# ls
nginx-1.15.7.tar.gz
[[email protected] ~]# tar zxf nginx-1.15.7.tar.gz
[[email protected] ~]# ls
nginx-1.15.7 nginx-1.15.7.tar.gz
#2.隐藏版本号
[[email protected] ~]# cd nginx-1.15.7
[[email protected] nginx-1.15.7]# vim src/core/nginx.h
#3.关闭debug日志
[[email protected] nginx-1.15.7]# vim auto/cc/gcc
#4.源码编译安装
[[email protected] nginx-1.15.7]# yum install -y gcc pcre-devel zlib-devel
[[email protected] nginx-1.15.7]# ./configure --prefix=/usr/local/nginx
[[email protected] nginx-1.15.7]# make && make install
2.启动nginx
#1.制作软连接，便于使用nginx命令
[[email protected] nginx-1.15.7]# ln -s /usr/local/nginx/sbin/nginx /sbin/
[[email protected] ~]# nginx
[[email protected] ~]# netstat -antlp
网页测试：
输入：172.25.66.1 即可访问nginx的默认发布页面
2.部署PHP
安装包：
libevent-devel-2.0.21-4.el7.x86_64.rpm
libgearman-1.1.12-18.el7.x86_64.rpm
libgearman-devel-1.1.12-18.el7.x86_64.rpm
libzip-0.10.1-8.el7.x86_64.rpm
openssl-1.0.2k-16.el7.x86_64.rpm
openssl-libs-1.0.2k-16.el7.x86_64.rpm
php-cli-5.4.16-46.el7.x86_64.rpm
php-common-5.4.16-46.el7.x86_64.rpm
php-fpm-5.4.16-46.el7.x86_64.rpm
php-mysql-5.4.16-46.el7.x86_64.rpm
php-pdo-5.4.16-46.el7.x86_64.rpm
php-pecl-gearman-1.1.2-1.el7.x86_64.rpm
php-pecl-igbinary-1.2.1-1.el7.x86_64.rpm
php-pecl-redis-2.2.8-1.el7.x86_64.rpm
php-process-5.4.16-46.el7.x86_64.rpm
php-xml-5.4.16-46.el7.x86_64.rpm
1.安装php
[[email protected] ~]# rm -rf nginx-1.15.7.tar.gz
#注意：lib*和openssl-*是php的依赖包
[[email protected] ~]# ls
libevent-devel-2.0.21-4.el7.x86_64.rpm
libgearman-1.1.12-18.el7.x86_64.rpm
libgearman-devel-1.1.12-18.el7.x86_64.rpm
libzip-0.10.1-8.el7.x86_64.rpm
nginx-1.15.7
openssl-1.0.2k-16.el7.x86_64.rpm
openssl-libs-1.0.2k-16.el7.x86_64.rpm
php-cli-5.4.16-46.el7.x86_64.rpm
php-common-5.4.16-46.el7.x86_64.rpm
php-fpm-5.4.16-46.el7.x86_64.rpm
php-mysql-5.4.16-46.el7.x86_64.rpm
php-pdo-5.4.16-46.el7.x86_64.rpm
php-pecl-gearman-1.1.2-1.el7.x86_64.rpm
php-pecl-igbinary-1.2.1-1.el7.x86_64.rpm
php-pecl-redis-2.2.8-1.el7.x86_64.rpm
php-process-5.4.16-46.el7.x86_64.rpm
php-xml-5.4.16-46.el7.x86_64.rpm
#1.查看系统是否已经安装openssl-devel
[[email protected] rhel7]# rpm -aq openssl-devel
openssl-devel-1.0.1e-60.el7.x86_64
#2.卸载openssl-devel；必须卸载，因为它对php的安装有影响
[[email protected] rhel7]# rpm -e openssl-devel
[[email protected] rhel7]# rpm -aq openssl-devel
#3.安装php
[[email protected] ~]# yum install -y *
2.开启php
[[email protected] conf]# systemctl start php-fpm
[[email protected] ~]# netstat -antlp
3.整合Nginx和PHP
1.更改nginx的配置文件
[[email protected] ~]# cd /usr/local/nginx/conf
#1.更改配置文件
[[email protected] conf]# vim nginx.conf
###################
location / {
root html;
index index.php index.html index.htm; #设定默认访问index.php（因为写在前边的优先级高）
}
location ~ \.php$ { #打开php模块
root html;
fastcgi_pass 127.0.0.1:9000;
fastcgi_index index.php;
#fastcgi_param SCRIPT_FILENAME /scripts$fastcgi_script_name;
include fastcgi.conf;
}
#2.检测语法
[[email protected] conf]# nginx -t
nginx: the configuration file /usr/local/nginx/conf/nginx.conf syntax is ok
nginx: configuration file /usr/local/nginx/conf/nginx.conf test is successful
#3.重新加载
[[email protected] conf]# nginx -s reload
[[email protected] conf]# cd /usr/local/nginx/html/
[[email protected] html]# ls
50x.html index.html
2.编写测试页面
[[email protected] html]# pwd
/usr/local/nginx/html/
#这是在网上下载的一个用php编写的发布页面
[[email protected] html]# vim index.php
###################
<?php
$redis = new Redis();
$redis->connect('127.0.0.1',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('127.0.0.1','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.66.1 发现无法访问redis服务器，因为还没有配置redis
4.部署Redis
1.下载redis并解压
#1.在官网上下载redis
[[email protected] ~]# ls
redis-5.0.3.tar.gz
#2.解压
[[email protected] ~]# tar zxf redis-5.0.3.tar.gz
[[email protected] ~]# ls
redis-5.0.3 redis-5.0.3.tar.gz
2.源码编译
[[email protected] ~]# cd redis-5.0.3
#1.安装依赖包
[[email protected] redis-5.0.3]# yum install -y gcc
#2.编译与安装
[[email protected] redis-5.0.3]# make && make install
3.执行脚本启动redis
[[email protected] redis-5.0.3]# ls
00-RELEASENOTES COPYING Makefile redis.conf runtest-sentinel tests
BUGS deps MANIFESTO runtest sentinel.conf utils
CONTRIBUTING INSTALL README.md runtest-cluster src
[[email protected] redis-5.0.3]# cd utils/
#直接回车即可
[[email protected] utils]# ./install_server.sh
#查看端口
[[email protected] ~]# netstat -antlp
4.更改配置文件
[[email protected] utils]# vim /etc/redis/6379.conf
#####################
bind 0.0.0.0 #监听所有地址
5.重启redis
[[email protected] ~]# systemctl restart redis_6379
测试：
[[email protected] redis]# redis-cli
#查看redis信息，必须是master
127.0.0.1:6379> info
5.部署数据库
由于源码编译mysql太麻烦，耗时过长，所以这里我们直接用mariadb做实验即可
1.安装mariadb-server
注意：如果你之前已经源码编译安装过mysql，必须先卸载，才能安装mariadb
#1.卸载mysql
[[email protected] ~]# rpm -aq | grep mysql
mysql-community-libs-5.7.24-1.el7.x86_64
mysql-community-server-5.7.24-1.el7.x86_64
mha4mysql-node-0.58-0.el7.centos.noarch
mysql-community-common-5.7.24-1.el7.x86_64
mysql-community-client-5.7.24-1.el7.x86_64
mysql-community-libs-compat-5.7.24-1.el7.x86_64
[[email protected] ~]# rpm -e `rpm -aq | grep mysql`
error: Failed dependencies:
libmysqlclient.so.18()(64bit) is needed by (installed) postfix-2:2.10.1-6.el7.x86_64
libmysqlclient.so.18()(64bit) is needed by (installed) perl-DBD-MySQL-4.023-5.el7.x86_64
libmysqlclient.so.18(libmysqlclient_18)(64bit) is needed by (installed) postfix-2:2.10.1-6.el7.x86_64
libmysqlclient.so.18(libmysqlclient_18)(64bit) is needed by (installed) perl-DBD-MySQL-4.023-5.el7.x86_64
[[email protected] ~]# rpm -e `rpm -aq | grep mysql` --nodeps
warning: /etc/my.cnf saved as /etc/my.cnf.rpmsave
[[email protected] ~]# rpm -aq | grep mysql
#2.删除数据
[[email protected] ~]# cd /var/lib/mysql/
[[email protected] mysql]# rm -rf *
[[email protected] mysql]# ls
#3.安装mariadb-server
[[email protected] ~]# yum install -y mariadb-server
2.开启数据库
[[email protected] ~]# systemctl start mariadb
[[email protected] ~]# netstat -antlp
3.初始化
[[email protected] ~]# mysql_secure_installation
4.创建redis用户并授权
[[email protected] ~]# mysql -uroot -pwestos
MariaDB [(none)]> show databases;
MariaDB [(none)]> grant all on test.* to [email protected]'%' identified by 'westos';
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> quit
5.批量插入数据
[[email protected] ~]# vim test.sql
#################
use test;
CREATE TABLE `test` (`id` int(7) NOT NULL AUTO_INCREMENT, `name` char(8) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `test` VALUES (1,'test1'),(2,'test2'),(3,'test3'),(4,'test4'),(5,'test5'),(6,'test6'),(7,'test7'),(8,'test8'),(9,'test9');
#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 ;
[[email protected] ~]# mysql -pwestos < test.sql
6.查看表数据
[[email protected] ~]# mysql -uroot -pwestos
MariaDB [(none)]> select * from test.test;
6.整合lnmp与redis
[[email protected] ~]# cd /usr/local/nginx/html/
[[email protected] html]# vim index.php
###################
$redis->connect('172.25.66.2',6379) or die ("could net connect redis server"); #连接redis
$connect = mysql_connect('172.25.66.3','redis','westos') #连接数据库
测试：
输入：172.25.66.1 即可通过访问redis缓存，进而访问数据库表信息
[[email protected] ~]# redis-cli
127.0.0.1:6379> get 1
"test1"
127.0.0.1:6379> get 2
"test2"
127.0.0.1:6379> get 3
"test3"
到这里，我们已经实现了 redis 作为 mysql 的缓存服务器
但我们发现了一个很严重的问题：
如果更新了mysql，而redis中仍然会有对应的 KEY，数据就不会更新，此时就会出现 mysql 和 redis数据不一致的情况。
1.更改数据库表信息
[[email protected] ~]# mysql -uroot -pwestos
MariaDB [(none)]> use test;
MariaDB [test]> update test set name='redhat' where id=1;
MariaDB [test]> select * from test;
2.刷新网页
网页访问，发现数据并没有更新
[[email protected] ~]# redis-cli
127.0.0.1:6379> get 1
"test1"
127.0.0.1:6379> quit
所以接下来就要通过 mysql 触发器，将改变的数据同步到redis中
解决方案：
配置gearman实现数据同步
Gearman简介：
Gearman 是一个支持分布式的任务分发框架:
Gearman Job Server: Gearman 核心程序,需要编译安装并以守护进程形式运行在后台。
Gearman Client:可以理解为任务的请求者。
Gearman Worker:任务的真正执行者,一般需要自己编写具体逻辑并通过守护进程方式运行，Gearman Worker 接收到 Gearman Client 传递的任务内容后,会按顺序处理。
大致流程：
下面要编写的 mysql 触发器,就相当于 Gearman 的客户端。修改表,插入表就相当于直接
下发任务。然后通过 lib_mysqludf_json UDF 库函数将关系数据映射为 JSON 格式,然后
在通过 gearman-mysql-udf 插件将任务加入到 Gearman 的任务队列中,最后通过
redis_worker.php,也就是 Gearman 的 worker 端来完成 redis 数据库的更新。
1.安装gearmand软件
安装包：
gearmand-1.1.12-18.el7.x86_64.rpm
#1.在官网上下载gearmand
[[email protected] ~]# ls
gearmand-1.1.12-18.el7.x86_64.rpm nginx-1.15.7
#2.安装gearmand
[[email protected] ~]# yum install -y gearmand-1.1.12-18.el7.x86_64.rpm
2.开启gearmand服务
[[email protected] ~]# systemctl start gearmand
[[email protected] ~]# netstat -antlp
3.安装lib_mysqludf_json
lib_mysqludf_json 库函数：将关系数据映射为 JSON 格式。通常,数据库中的数据映射为 JSON 格式,是通过程序来转换的。
安装包：
lib_mysqludf_json-master.zip
#1.在官网上下载lib_mysqludf_json
[[email protected] ~]# ls
gearman-mysql-udf-0.6.tar.gz lib_mysqludf_json-master.zip test.sql
[[email protected] ~]# unzip lib_mysqludf_json-master.zip
-bash: unzip: command not found
#2.安装解压工具
[[email protected] ~]# yum install -y unzip
#3.解压
[[email protected] ~]# unzip lib_mysqludf_json-master.zip
[[email protected] ~]# ls
gearman-mysql-udf-0.6.tar.gz lib_mysqludf_json-master.zip
lib_mysqludf_json-master test.sql
#4.安装依赖包
[[email protected] ~]# cd lib_mysqludf_json-master
[[email protected] lib_mysqludf_json-master]# yum install gcc mysql-devel -y
5.安装
[[email protected] lib_mysqludf_json-master]# gcc $(mysql_config --cflags) -shared -fPIC -o lib_mysqludf_json.so lib_mysqludf_json.c
[[email protected] lib_mysqludf_json-master]# ll
4.拷贝json模块
[[email protected] lib_mysqludf_json-master]# cp lib_mysqludf_json.so /usr/lib64/mysql/plugin/
[[email protected] lib_mysqludf_json-master]# cd /usr/lib64/mysql/plugin/
[[email protected] plugin]# ll lib_mysqludf_json.so
-rwxr-xr-x 1 root root 17440 Mar 2 14:24 lib_mysqludf_json.so
5.注册json函数
[[email protected] ~]# mysql -uroot -pwestos
#查看数据库的模块目录
MariaDB [(none)]> show global variables like 'plugin_dir';
#注册函数
MariaDB [(none)]> CREATE FUNCTION json_object RETURNS STRING SONAME
-> 'lib_mysqludf_json.so';
#查看函数
MariaDB [(none)]> select * from mysql.func;
MariaDB [(none)]> exit
6.安装gearman-mysql-udf
udf插件：是用来管理调用 Gearman 的分布式的队列
安装包：
gearman-mysql-udf-0.6.tar.gz
（1）下载并解压gearman-mysql-udf
[[email protected] ~]# ls
gearman-mysql-udf-0.6.tar.gz lib_mysqludf_json-master.zip
lib_mysqludf_json-master test.sql
[[email protected] ~]# tar zxf gearman-mysql-udf-0.6.tar.gz
[[email protected] ~]# ls
gearman-mysql-udf-0.6 lib_mysqludf_json-master test.sql
gearman-mysql-udf-0.6.tar.gz lib_mysqludf_json-master.zip
（2）源码编译
[[email protected] ~]# cd gearman-mysql-udf-0.6
#配置；有报错
[[email protected] gearman-mysql-udf-0.6]# ./configure --libdir=/usr/lib64/mysql/plugin/
依赖包：
libevent-devel-2.0.21-4.el7.x86_64.rpm
libgearman-1.1.12-18.el7.x86_64.rpm
libgearman-devel-1.1.12-18.el7.x86_64.rpm
1.在官网上下载依赖包
[[email protected] ~]# ls
gearman-mysql-udf-0.6 lib_mysqludf_json-master
gearman-mysql-udf-0.6.tar.gz lib_mysqludf_json-master.zip
libevent-devel-2.0.21-4.el7.x86_64.rpm test.sql
libgearman-1.1.12-18.el7.x86_64.rpm libgearman-devel-1.1.12-18.el7.x86_64.rpm
#2.安装依赖包
[[email protected] ~]# yum install -y libgearman-* libevent-*
#3.configure配置
[[email protected] ~]# cd gearman-mysql-udf-0.6
[[email protected] gearman-mysql-udf-0.6]# ./configure --libdir=/usr/lib64/mysql/plugin/
#4.编译与安装
[[email protected] gearman-mysql-udf-0.6]# make && make install
[[email protected] gearman-mysql-udf-0.6]# cd /usr/lib64/mysql/plugin/
[[email protected] plugin]# cd /usr/lib64/mysql/plugin/
[[email protected] plugin]# ll
7.注册udf函数
[[email protected] ~]# mysql -u root -pwestos
#注册函数
MariaDB [(none)]> CREATE FUNCTION gman_do_background RETURNS STRING SONAME
-> 'libgearman_mysql_udf.so';
MariaDB [(none)]> CREATE FUNCTION gman_servers_set RETURNS STRING SONAME
-> 'libgearman_mysql_udf.so';
#查看函数
MariaDB [(none)]> select * from mysql.func;
#指定gearman的服务信息
MariaDB [(none)]> SELECT gman_servers_set('172.25.66.1:4730');
8.编写数据库触发器（根据实际情况编写）
[[email protected] ~]# vim test.sql
#################
use test;
#CREATE TABLE `test` (`id` int(7) NOT NULL AUTO_INCREMENT, `name` char(8) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#INSERT INTO `test` VALUES (1,'test1'),(2,'test2'),(3,'test3'),(4,'test4'),(5,'test5'),(6,'test6'),(7,'test7'),(8,'test8'),(9,'test9');
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 ;
[[email protected] ~]# mysql -pwestos < test.sql
[[email protected] ~]# mysql -uroot -pwestos
#查看触发器
MariaDB [(none)]> show triggers from test;
9. 编写gearman的worker端
#1.编写worker（php语言）
[[email protected] ~]# cd /usr/local/
[[email protected] local]# vim worker.php
####################
<?php
$worker = new GearmanWorker();
$worker->addServer();
$worker->addFunction('syncToRedis', 'syncToRedis');
$redis = new Redis();
$redis->connect('172.25.66.2', 6379); #连接redis
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); #将id作KEY和name作VALUE分开存储,需要和前面写的 php 测试代码的存取一致。
}
?>
#2.后台运行worker；&> /dev/null 表示将输出信息导入垃圾箱，&表示打入后台
[[email protected] local]# nohup php /usr/local/worker.php &> /dev/null &
[1] 6186
测试：
1.更新数据库的表信息
[[email protected] ~]# mysql -uroot -pwestos
MariaDB [(none)]> use test;
MariaDB [test]> select * from test;
MariaDB [test]> update test set name='lily' where id=1;
MariaDB [test]> select * from test;
2.刷新网页
发现实现了数据同步，因为redis服务器端同步了数据库端的数据更新
[[email protected] ~]# redis-cli
127.0.0.1:6379> get 1
"lily"
127.0.0.1:6379> exit