前言：
在实际项目中，MySQL数据库服务器有时会位于另外一台主机，需要通过网络来访问数据库；即使应用程序与MySQL数据库在同一个主机中，访问MySQL也涉及到磁盘IO操作（MySQL也有一些数据预读技术，能够减少磁盘IO读写，此部分后续继续研究），总之，直接从MySQL中读取数据不如直接从内存中读取数据来的效率高。为了提高数据库访问效率，人们采用了各种各样的方法，其中方法之一就是使用一个给予内存的缓存系统放置在数据库和应用程序之间。在查找数据的时候，首先从内存中查找，如果找到则使用，如果没有找到，那么再真正访问数据库。这种方法在一些场景下（例如：频繁查找相同数据）能够提高系统的整体效率。
本文的主要目的即介绍上文说的这样一种方法，采用redis nosql数据库作为Mysql数据库的缓存，在查找的时候，首先查找redis缓存，如果找到则返回结果；如果在redis中没有找到，那么查找Mysql数据库，找到的花则返回结果并且更新redis；如果没有找到则返回空。对于写入的情况，直接写入mysql数据库，mysql数据库通过触发器及UDF机制自动把变更的内容更新到redis中。
一、实验环境
| server1 | nginx+php | 172.25.75.1 |
|---|---|---|
| server2 | redis | 172.25.75.2 |
| server3 | mysql | 172.25.75.3 |
selinux状态为disabled 关闭防火墙
二、在server1上部署nginx+php（这里nginx只作为前端服务器，版本要求不高）
1、nginx的搭建
获得压缩包，解压：
[root@server1 ~]# ls
nginx-1.16.0.tar.gz
[root@server1 ~]# tar zxf nginx-1.16.0.tar.gz
[root@server1 ~]# ls
nginx-1.16.0 nginx-1.16.0.tar.gz
关闭debug日志，编译安装
[root@server1 nginx-1.16.0]# yum install gcc pcre-devel zlib-devel -y
[root@server1 nginx-1.16.0]# vim auto/cc/gcc
171 # debug
172 #CFLAGS="$CFLAGS -g"
[root@server1 nginx-1.16.0]# ./configure --prefix=/usr/local/nginx --with-threads --with-file-aio
[root@server1 nginx-1.16.0]# make && make install
2、作nginx的基本配置
[root@server1 conf]# pwd
/usr/local/nginx/conf
[root@server1 conf]# vim nginx.conf
2 user nginx nginx;
3 worker_processes 2;
65 location ~ \.php$ {
66 root html;
67 fastcgi_pass 127.0.0.1:9000;
68 fastcgi_index index.php;
69 #fastcgi_param SCRIPT_FILENAME /scripts$fastcgi_script_name;
70 include fastcgi.conf;
71 }
添加nginx用户，并启动nginx服务：
[root@server1 conf]# useradd nginx
[root@server1 conf]# ../sbin/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
[root@server1 conf]# ../sbin/nginx
[root@server1 conf]# netstat -tnlp | grep nginx
tcp 0 0 0.0.0.0:80 0.0.0.0:* LISTEN 5534/nginx: master
3、php的搭建
获得php安装资源：
安装：
[root@server1 rhel7]# yum install * -y
启动：
[root@server1 rhel7]# systemctl start php-fpm
[root@server1 rhel7]# systemctl status php-fpm
三、在server2上搭建redis服务
具体搭建过程可以参考：redis数据库哨兵模式实现主从故障切换
注意：这里需要搭建的redis服务是master，不能为slave
四、在server3上搭建mariadb服务
1、下载
[root@server3 ~]# yum install -y mariadb-server
2、启动，初始化
[root@server3 ~]# systemctl start mariadb
[root@server3 ~]# mysql_secure_installation #安全初始化
3、进入mariadb数据库，创建test库，授权
[root@server3 ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 5.5.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
MariaDB [(none)]> create database test; #创建库
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> grant all on test.* to redis@'%' identified by 'redhat'; #授权
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> flush privileges; #刷新授权表
Query OK, 0 rows affected (0.00 sec)
五、配置server1的nginx服务，写入读写策略
更改配置文件：
[root@server1 html]# pwd
/usr/local/nginx/html
[root@server1 html]# vim ../conf/nginx.conf
43 location / {
44 root html;
45 index index.php index.html index.htm;
46 }
编写php动态脚本，写入策略
[root@server1 html]# vim index.php
<?php
$redis = new Redis();
$redis->connect('172.25.75.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.75.3','redis','redhat');
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>";
}
?>
[root@server1 html]# ../sbin/nginx -s reload
六、获得一个写好的数据库，导入server3的数据库中
[root@server3 ~]# ls
test.sql
[root@server3 ~]# cat 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 ;
[root@server3 ~]# mysql -predhat <test.sql
查看数据库test库的内容：
七、客户端测试
我们在客户端的浏览器访问：172.25.75.1
可以看到首次访问，访问的是server3数据库里面的数据，我们刷新一下：
第二次访问后，数据来自于server2部署的redis中，redis缓存了server3数据库的数据！！！
那么，如果我们的数据库更新了，我们的用户访问时的内容来自哪里呢？能否同步更新呢？
更新server3数据库数据：
MariaDB [(none)]> use test;
MariaDB [test]> select * from test;
MariaDB [test]> update test set name='redhat' where id=1;
MariaDB [test]> select * from test;
客户端再次访问：
数据仍没有更新：看来我们的redis库和mariadb库没有做到同步呢。
八、触发器实现redis和mysql数据同步
我们解决的问题是：
在server3上更新数据库的信息，但是redis不能同步到。
（1）在server1上获得gearmand资源，安装，打开
[root@server1 rhel7]# ls
gearmand-1.1.12-18.el7.x86_64.rpm php-fpm-5.4.16-46.el7.x86_64.rpm
libevent-devel-2.0.21-4.el7.x86_64.rpm php-mysql-5.4.16-46.el7.x86_64.rpm
libgearman-1.1.12-18.el7.x86_64.rpm php-pdo-5.4.16-46.el7.x86_64.rpm
libgearman-devel-1.1.12-18.el7.x86_64.rpm php-pecl-gearman-1.1.2-1.el7.x86_64.rpm
libzip-0.10.1-8.el7.x86_64.rpm php-pecl-igbinary-1.2.1-1.el7.x86_64.rpm
openssl-1.0.2k-16.el7.x86_64.rpm php-pecl-redis-2.2.8-1.el7.x86_64.rpm
openssl-libs-1.0.2k-16.el7.x86_64.rpm php-process-5.4.16-46.el7.x86_64.rpm
php-cli-5.4.16-46.el7.x86_64.rpm php-xml-5.4.16-46.el7.x86_64.rpm
php-common-5.4.16-46.el7.x86_64.rpm
[root@server1 rhel7]# systemctl start gearmand #gearmand-1.1.12-18.el7.x86_64.rpm此包已经安装，所以此处打开就可以
[root@server1 rhel7]# systemctl status gearmand
（2）在server3的mariadb端进行资源获取,解压。安装mariadb-devel开发包
[root@server3 ~]# ls
lib_mysqludf_json-master.zip test.sql
[root@server3 ~]# yum install unzip -y
[root@server3 ~]# unzip lib_mysqludf_json-master.zip
[root@server3 ~]# yum install mariadb-devel -y
（3）进入解压的目录下，用gcc进行编译模块
[root@server3 ~]# ls
lib_mysqludf_json-master lib_mysqludf_json-master.zip test.sql
[root@server3 ~]# cd lib_mysqludf_json-master
[root@server3 lib_mysqludf_json-master]# ls
lib_mysqludf_json.c lib_mysqludf_json.so README.md
lib_mysqludf_json.html lib_mysqludf_json.sql
[root@server3 lib_mysqludf_json-master]# yum install gcc -y
[root@server3 lib_mysqludf_json-master]# gcc $(mysql_config --cflags) -shared -fPIC -o lib_mysqludf_json.so lib_mysqludf_json.c
（4）将模块放到mysql插件目录
[root@server3 lib_mysqludf_json-master]# cp lib_mysqludf_json.so /usr/lib64/mysql/plugin/
[root@server3 lib_mysqludf_json-master]# ll /usr/lib64/mysql/plugin/
total 5036
-rwxr-xr-x 1 root root 11632 Sep 21 2016 adt_null.so
-rwxr-xr-x 1 root root 7336 Sep 21 2016 auth_0x0100.so
-rwxr-xr-x 1 root root 11536 Sep 21 2016 auth_pam.so
-rwxr-xr-x 1 root root 7568 Sep 21 2016 auth_socket.so
-rwxr-xr-x 1 root root 11720 Sep 21 2016 auth_test_plugin.so
-rw-r--r-- 1 root root 227 Sep 12 2016 daemon_example.ini
-rwxr-xr-x 1 root root 7504 Sep 21 2016 dialog_examples.so
-rwxr-xr-x. 1 root root 11296 Sep 21 2016 dialog.so
-rwxr-xr-x 1 root root 1644520 Sep 21 2016 ha_innodb.so
-rwxr-xr-x 1 root root 169568 Sep 21 2016 handlersocket.so
-rwxr-xr-x 1 root root 106248 Sep 21 2016 ha_sphinx.so
-rwxr-xr-x 1 root root 11720 Sep 21 2016 libdaemon_example.so
-rwxr-xr-x 1 root root 17440 May 13 16:21 lib_mysqludf_json.so
-rwxr-xr-x 1 root root 12208 Sep 21 2016 mypluglib.so
-rwxr-xr-x. 1 root root 7184 Sep 21 2016 mysql_clear_password.so
-rwxr-xr-x 1 root root 7168 Sep 21 2016 qa_auth_client.so
-rwxr-xr-x 1 root root 11568 Sep 21 2016 qa_auth_interface.so
-rwxr-xr-x 1 root root 7336 Sep 21 2016 qa_auth_server.so
-rwxr-xr-x 1 root root 11736 Sep 21 2016 query_cache_info.so
-rwxr-xr-x 1 root root 41336 Sep 21 2016 semisync_master.so
-rwxr-xr-x 1 root root 15984 Sep 21 2016 semisync_slave.so
-rwxr-xr-x 1 root root 61184 Sep 21 2016 server_audit.so
-rwxr-xr-x 1 root root 2922944 Sep 21 2016 sphinx.so
-rwxr-xr-x 1 root root 12152 Sep 21 2016 sql_errlog.so
（5）在server3上进入数据库，注册udf函数。
[root@server3 lib_mysqludf_json-master]# mysql -uroot -p
MariaDB [(none)]> show global variables like 'plugin_dir';
MariaDB [(none)]> CREATE FUNCTION json_object RETURNS STRING SONAME 'lib_mysqludf_json.so';
（6）在server3上获取资源，安装插件管理gearman的分布式队列
首先安装libgearman
[root@server3 ~]# ls
gearman-mysql-udf-0.6 libgearman-devel-1.1.12-18.el7.x86_64.rpm
gearman-mysql-udf-0.6.tar.gz lib_mysqludf_json-master
libevent-devel-2.0.21-4.el7.x86_64.rpm lib_mysqludf_json-master.zip
libgearman-1.1.12-18.el7.x86_64.rpm test.sql
[root@server3 ~]# yum install -y libgearman-* libevent-devel-2.0.21-4.el7.x86_64.rpm
编译安装gearman插件
[root@server3 ~]# ls
gearman-mysql-udf-0.6.tar.gz lib_mysqludf_json-master.zip
lib_mysqludf_json-master test.sql
[root@server3 ~]# tar zxf gearman-mysql-udf-0.6.tar.gz
[root@server3 ~]# ls
gearman-mysql-udf-0.6 lib_mysqludf_json-master test.sql
gearman-mysql-udf-0.6.tar.gz lib_mysqludf_json-master.zip
[root@server3 ~]# cd gearman-mysql-udf-0.6
[root@server3 gearman-mysql-udf-0.6]# ls
aclocal.m4 ChangeLog config.h.in configure.ac libgearman_mysql_udf Makefile.am NEWS
AUTHORS config configure COPYING m4 Makefile.in README
[root@server3 gearman-mysql-udf-0.6]# ./configure --libdir=/usr/lib64/mysql/plugin/ --with-mysql
[root@server3 gearman-mysql-udf-0.6]# make && make install
（7）server3上注册udf函数，指定german服务信息
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 16
Server version: 5.5.52-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> CREATE FUNCTION gman_do_background RETURNS STRING SONAME 'libgearman_mysql_udf.so';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> CREATE FUNCTION gman_servers_set RETURNS STRING SONAME 'libgearman_mysql_udf.so';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> 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)
MariaDB [(none)]> SELECT gman_servers_set('172.25.75.1:4730');
+--------------------------------------+
| gman_servers_set('172.25.75.1:4730') |
+--------------------------------------+
| 172.25.75.1:4730 |
+--------------------------------------+
1 row in set (0.00 sec)
（8）编写mysql触发器
（9）导入数据库,查看触发器
[root@server3 ~]# mysql -predhat <test.sql
[root@server3 ~]# mysql -uroot -predhat
MariaDB [(none)]> SHOW TRIGGERS FROM test;
（10）编写german的worker端，server1。后台运行
[root@server1 ~]# vim /usr/local/worker.php
<?php
$worker = new GearmanWorker();
$worker->addServer();
$worker->addFunction('syncToRedis', 'syncToRedis');
$redis = new Redis();
$redis->connect('172.25.75.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：
[root@server1 ~]# nohup php /usr/local/worker.php &> /dev/null &
[1] 5756
九、测试
在server3修改数据库内容：
MariaDB [(none)]> use test;
MariaDB [test]> update test set name='Linux' where id=2;
在server2上的redis上查看：
[root@server2 ~]# redis-cli
127.0.0.1:6379> get 2
"Linux"
可以看到缓存redis已经同步了！
最后，我们再浏览器上测试：
浏览器上可以看到数据库更改后的内容哈，触发器实现redis和mysql数据同步成功！！！
扫一扫
366
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
