运维实战 Redis与Mysql的业务衔接
Redis与MqSQL业务衔接
需要准备3台服务器
Server1开启redisServer2安装php和nginx模拟同步信息用的数据用的ClientServer3安装mariadb充当mysql
业务逻辑是这样的, 写入mysql的数据通过Server2同步给redis
Server2上的操作
##编译安装nginx的过程略过
##安装php-fpm
[root@Server2 ~]# cd rhel7/
[root@Server2 rhel7]# yum install -y php-* libgearman-* libevent-*
[root@Server2 rhel7]# systemctl start php-fpm.service
##查看端口情况
[root@Server2 rhel7]# netstat -antulp ##查看是否开启相关端口
tcp 0 0 127.0.0.1:9000 0.0.0.0:* LISTEN 4121/php-fpm: maste
打开nginx配置文件中的对于php的相关支持
# pass the PHP scripts to FastCGI server listening on 127.0.0.1:9000
#
location ~ \.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;
}
Server1上安装Redis并保持为MASTER
Server3上安装Mariadb
[root@Server2 ~]# cd rhel7
[root@Server2 rhel7]# ll test.php
-rw-r--r-- 1 root root 1369 Apr 16 10:34 test.php
##复制测试文件到nginx默认发布目录
##并修改其内容
[root@Server2 rhel7]# cp test.php /usr/local/nginx/html/
[root@Server2 rhel7]# cd /usr/local/nginx/html/
[root@Server2 html]# vim test.php
<?php
$redis = new Redis();
$redis->connect('172.25.5.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('172.25.5.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;
}
在Server3上授权远程用户登录
[root@Server3 local]# mysql
MariaDB [(none)]> show databases;
MariaDB [(none)]> grant all on test.* to redis@'%' identified by 'westos';
##拷贝测试数据到Server3并导入数据库
[root@Server2 rhel7]# scp test.sql Server3:
[root@Server3 ~]# mysql < test.sql
进行简单测试
root@Server3 ~]# mysql
MariaDB [(none)]> use test
MariaDB [test]> show tables;
MariaDB [test]> select * from test;
[root@Server1 ~]# redis-cli
127.0.0.1:6379> get 1
"test1"
127.0.0.1:6379> get 2
"test2"
发现可以正确取到信息.
不足与问题
- 第一次访问时, 由于
Redis中没有缓存, 看不到数据库中的值 - 刷新页面后能够看到
- 但是当更改
MySQL内容后并不能反映在Redis上,这显然是不满足业务需求的 - 必须删除
Redis中已经存储的值, 再次刷新页面才能将新内容写入
实现自动同步
这里我们需要借助Gearman来进行数据同步.
Gearman是一个支持分布式的任务分发框架,分为Gearman Job Server, Gearman Job Server, Gearman Worker三个组成部分
工作流程
- 编写
MySQL触发器, 当出现插入修改操作时创建任务 - 通过
lib_mysqludf_json-udf库函数将关系数据映射为JSON格式 - 通过
gearman-mysql-udf插件将任务加入Gearman的队列中 - 通过
redis_worker.php完成数据库的更新
Server3操作
unzip lib_mysqludf_json-master.zip
cd lib_mysqludf_json-master/
yum install -y mysql-devel gcc
$$生成so文件
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> show global variables like 'plugin_dir';
+---------------+-------------------------+
| Variable_name | Value |
+---------------+-------------------------+
| plugin_dir | /usr/lib64/mysql/plugin |
+---------------+-------------------------+
##注册添加该函数
mysql> CREATE FUNCTION json_object RETURNS STRING SONAME
'lib_mysqludf_json.so';
##查看函数内容
mysql> select * from mysql.func;
+--------------------+-----+-------------------------+----------+
| name | ret | dl | type |
+--------------------+-----+-------------------------+----------+
| json_object | 0 | lib_mysqludf_json.so | function |
+--------------------+-----+-------------------------+----------+
##编译安装gearman-mysql-udf
yum install -y libgearman-* libevent-devel-2.0.21-4.el7.x86_64.rpm
tar zxf gearman-mysql-udf-0.6.tar.gz
cd gearman-mysql-udf-0.6
./configure --libdir=/usr/lib64/mysql/plugin/
make
make install
##注册libgearman_mysql_udf函数
mysql> CREATE FUNCTION gman_do_background RETURNS STRING SONAME
'libgearman_mysql_udf.so';
mysql> CREATE FUNCTION gman_servers_set RETURNS STRING SONAME
'libgearman_mysql_udf.so';
##查看函数情况
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 |
+--------------------+-----+-------------------------+----------+
##指定Gearman的Server端
mysql> SELECT gman_servers_set('172.25.5.2:4730');
需要注意的是
Gearman的Server端只负责接受MqSQL更改情况,不负责处理- 处理的流程交给
Worker进程来处理
在Server2上配置Gearman的Worker端
[root@Server2 rhel7]# yum install -y gearmand-1.1.12-18.el7.x86_64.rpm
[root@Server2 rhel7]# systemctl start gearmand.service
[root@Server2 rhel7]# netstat -antlp | grep 4730
tcp 0 0 0.0.0.0:4730 0.0.0.0:* LISTEN 19099/gearmand
tcp6 0 0 :::4730 :::* LISTEN 19099/gearmand
[root@Server2 rhel7]# vim worker.php
<?php
$worker = new GearmanWorker();
$worker->addServer();
$worker->addFunction('syncToRedis', 'syncToRedis');
$redis = new Redis();
$redis->connect('172.25.5.1', 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);
}
?>
[root@Server2 rhel7]# php -m | grep redis
redis
[root@Server2 rhel7]# nohup php worker.php &
在Server3上配置MySQL触发器
[root@Server3 ~]# vim test.sql
use test;
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 < test.sql
[root@Server3 ~]# mysql
MariaDB [(none)]> SHOW TRIGGERS FROM test;
MariaDB [(none)]> use test
MariaDB [test]> update test set name='NeuWings' where id=1;
此时在Server1上可以看到变化了
[root@Server1 ~]# redis-cli
127.0.0.1:6379> get 1
"NeuWings"
同样的,网页端也可以同步更新数据.
部分操作流程
MariaDB [test]> 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 | utf8 | utf8_general_ci | latin1_swedish_ci |
+-------------+--------+-------+----------------------------------------------------------------------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)
MariaDB [test]> update test set name = 'Test1' where id =1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [test]> SELECT gman_servers_set('172.25.5.2:4730');
+-------------------------------------+
| gman_servers_set('172.25.5.2:4730') |
+-------------------------------------+
| 172.25.5.2:4730 |
+-------------------------------------+
1 row in set (0.00 sec)
MariaDB [test]> exit
Bye
[root@Server4 ~]# systemctl restart mariadb.service
[root@Server4 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> update test set name = 'Test' where id =1;
ERROR 1046 (3D000): No database selected
MariaDB [(none)]> 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
MariaDB [test]> update test set name = 'Test' where id =1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [test]> SELECT gman_servers_set('172.25.5.2:4730');
+-------------------------------------+
| gman_servers_set('172.25.5.2:4730') |
+-------------------------------------+
| 172.25.5.2:4730 |
+-------------------------------------+
1 row in set (0.00 sec)
MariaDB [test]> 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 | utf8 | utf8_general_ci | latin1_swedish_ci |
+-------------+--------+-------+----------------------------------------------------------------------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)
MariaDB [test]> 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 [test]> SELECT gman_servers_set('172.25.5.2:4730');
+-------------------------------------+
| gman_servers_set('172.25.5.2:4730') |
+-------------------------------------+
| 172.25.5.2:4730 |
+-------------------------------------+
1 row in set (0.00 sec)
MariaDB [test]> update test set name = 'westos' where id =1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
1326
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
