本文详细介绍了如何在LNMP环境中利用Redis作为MySQL的缓存服务器。通过设置触发器和插件，当MySQL数据更新时，通过Gearman队列将更新推送到Redis，实现数据同步。实验涵盖了Nginx、PHP、Redis、MariaDB的配置和管理，以及MySQL触发器和 Gearman 的使用。
实验大致过程：
mysql–>update–>trigger(触发器）–>json_map–>mysql plugin(插件） -->gearman(server) -->worker(php-gearman/php-redis) --> redis
实验环境
| 主机 | 服务 |
|---|---|
| server1：172.25.21.1 | nginx服务器 |
| server2：172.25.21.2 | redis |
| server3：172.25.21.3 | mysql |
- server1的配置
注意：server1上如果之前做过mysqld实验会跟php版本冲突，所以要重新建立
1.解压nginx安装包
[root@server1 ~]# ls
nginx-1.16.0.tar.gz
[root@server1 ~]# tar zxf nginx-1.16.0.tar.gz
2.关闭debug日至
[root@server1 ~]# cd nginx-1.16.0
[root@server1 nginx-1.16.0]# ls
auto CHANGES.ru configure html man src
CHANGES conf contrib LICENSE README
[root@server1 nginx-1.16.0]# vim auto/cc/gcc
# debug
#CFLAGS="$CFLAGS -g"
3.安装能解决依赖性的包
[root@server1 nginx-1.16.0]# yum install -y gcc pcre-devel zlib-devel
4.编译并安装
[root@server1 nginx-1.16.0]# ./configure --prefix=/usr/local/nginx
[root@server1 nginx-1.16.0]# make && make install
5.编辑nginx配置文件
[root@server1 nginx-1.16.0]# cd /usr/local/nginx/conf/
[root@server1 conf]# vim nginx.conf
location / {
root html;
index index.php index.html index.htm;
}
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;
}
6.语法检测并开启nginx服务
[root@server1 conf]# ../sbin/nginx -t
[root@server1 conf]# ../sbin/nginx
7.安装如下安装包
[root@server1 ~]# cd rhel7/
[root@server1 rhel7]# ls
gearmand-1.1.12-18.el7.x86_64.rpm
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
[root@server1 rhel7]# yum install -y *
8.开启php并查看端口
[root@server1 rhel7]# systemctl start php-fpm
[root@server1 rhel7]# netstat -nltp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:80 0.0.0.0:* LISTEN 3622/nginx: master
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 790/sshd
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 894/master
tcp 0 0 127.0.0.1:9000 0.0.0.0:* LISTEN 3737/php-fpm: maste
tcp6 0 0 :::22 :::* LISTEN 790/sshd
tcp6 0 0 ::1:25 :::* LISTEN 894/master
9.将编写好的php测试页放在 /usr/local/nginx/html/目录下，给测试页重命名
[root@server1 rhel7]# cd /usr/local/nginx/html/
[root@server1 html]# ls
50x.html index.html test.php
[root@server1 html]# mv test.php index.php
[root@server1 html]# ls
50x.html index.html index.php
- server2的配置
注意：server2的redis配置在之前的实验已经做好，所以不需要在重新配置
1.开启server2的redis服务
[root@server2 ~]# /etc/init.d/redis_6379 start
Starting Redis server...
2.编辑配置文件 ，查看70行正确否，slave是否存在（不存在正常）,看最后一行是否有内容（有的话删除）
[root@server2 ~]# cd /etc/redis/
[root@server2 redis]# ls
6379.conf sentinel.conf
[root@server2 redis]# vim 6379.conf
3.重启redis服务
[root@server2 redis]# /etc/init.d/redis_6379 restart
4.测试redis是否为主
[root@server2 ~]# redis-cli #测试是否是主
127.0.0.1:6379> set name westos
OK
127.0.0.1:6379> get name
"westos"
127.0.0.1:6379> del name
(integer) 1
127.0.0.1:6379> get name
(nil)
127.0.0.1:6379> exit
- server3配置
注意：因为之前做过redis，mysql实验，所以先关闭服务，删除之前的mysql配置，保证实验环境纯净
1.关闭redis、 mysql服务，查看进程服务已关闭
[root@server3 ~]# /etc/init.d/redis_6379 stop
Stopping ...
Redis stopped
[root@server3 ~]# systemctl stop mysqld
[root@server3 ~]# ps ax
2.删除之前的mysql配置
[root@server3 ~]# rpm -qa | 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
[root@server3 ~]# rpm -e `rpm -qa | grep mysql` --nodeps
warning: /etc/my.cnf saved as /etc/my.cnf.rpmsave
3.安装mariadb-server,不需要复杂的密码方便实验
[root@server3 ~]# yum install -y mariadb-server
4.删除之前的mysql数据，开启mariadb服务
[root@server3 ~]# cd /var/lib/mysql/
[root@server3 mysql]# ls
auto.cnf ib_buffer_pool relay-log.info
binlog.000001 ibdata1 server3-relay-bin.000003
binlog.000002 ib_logfile0 server3-relay-bin.000004
binlog.000003 ib_logfile1 server3-relay-bin.index
binlog.index master.info server-cert.pem
ca-key.pem mysql server-key.pem
ca.pem performance_schema sys
client-cert.pem private_key.pem westos
client-key.pem public_key.pem
[root@server3 mysql]# rm -fr *
[root@server3 mysql]# systemctl start mariadb
5.安全初始化
[root@server3 mysql]# mysql_secure_installation
6.创建库，创建用户授权，刷新授权表
[root@server3 mysql]# mysql -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 ##创建库
-> ;
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)
7.在server1上修改默认发布php测试页面
[root@server1 html]# vim index.php
8.把编写好的测试库导入数据库，进入数据库查看
[root@server3 ~]# mysql -predhat < test.sql
[root@server3 ~]# mysql -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 13
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)]> 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]> show tables; ##查看表
+----------------+
| Tables_in_test |
+----------------+
| test |
+----------------+
1 row in set (0.00 sec)
MariaDB [test]> select * from test;
+----+-------+
| id | name |
+----+-------+
| 1 | test1 |
| 2 | test2 |
| 3 | test3 |
| 4 | test4 |
| 5 | test5 |
| 6 | test6 |
| 7 | test7 |
| 8 | test8 |
| 9 | test9 |
+----+-------+
9 rows in set (0.00 sec)
9.测试：浏览器中访问172.25.21.1
再刷新一次
10.如果在mysql端修改了数据
MariaDB [test]> select * from test;
+----+-------+
| id | name |
+----+-------+
| 1 | test1 |
| 2 | test2 |
| 3 | test3 |
| 4 | test4 |
| 5 | test5 |
| 6 | test6 |
| 7 | test7 |
| 8 | test8 |
| 9 | test9 |
+----+-------+
9 rows 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
MariaDB [test]> select * from test;
+----+--------+
| id | name |
+----+--------+
| 1 | westos |
| 2 | test2 |
| 3 | test3 |
| 4 | test4 |
| 5 | test5 |
| 6 | test6 |
| 7 | test7 |
| 8 | test8 |
| 9 | test9 |
+----+--------+
9 rows in set (0.00 sec)
在redis端查看数据没有改变
刷新浏览器，数据没有发生变化
11.在redis端也修改一个数据
刷新浏览器，数据发生改变，redis端修改的数据已经同步，但mysql端修改的数据却没有同步，说明数据是在redis端取得
- 那么如何实现将mysql端修改的数据及时更新到redis端呢?
- mysql端
1.解压并安装lib_mysqludf_json-master，进入解压目录下，查看目录下内容
[root@server3 ~]# ls
gearman-mysql-udf-0.6.tar.gz redis-5.0.3
lib_mysqludf_json-master.zip test.sql
[root@server3 ~]# yum install unzip -y
[root@server3 ~]# unzip lib_mysqludf_json-master.zip
[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
2.安装mariadb-devel服务
[root@server3 lib_mysqludf_json-master]# yum install mariadb-devel -y
3.安装gcc并用gcc进行编译模块
[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/
5.进入数据库，查看插件
[root@server3 ~]# mysql -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 15
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 global variables like 'plugin_dir';
+---------------+--------------------------+
| Variable_name | Value |
+---------------+--------------------------+
| plugin_dir | /usr/lib64/mysql/plugin/ |
+---------------+--------------------------+
1 row in set (0.00 sec)
6.注册udf函数，并查看函数
MariaDB [(none)]> CREATE FUNCTION json_object RETURNS STRING SONAME 'lib_mysqludf_json.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 |
+-------------+-----+----------------------+----------+
1 row in set (0.00 sec)
7.解压并安装插件管理gearman的分布式队列
[root@server3 ~]# tar zxf gearman-mysql-udf-0.6.tar.gz
[root@server3 ~]# yum install -y libevent-devel-2.0.21-4.el7.x86_64.rpm libgearman-*
8.进入解压目录下，编译安装gearman
[root@server3 ~]# cd gearman-mysql-udf-0.6
[root@server3 gearman-mysql-udf-0.6]# ls
aclocal.m4 config configure.ac m4 NEWS
AUTHORS config.h.in COPYING Makefile.am README
ChangeLog configure libgearman_mysql_udf Makefile.in
[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
9.注册udf函数，并查看函数
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)
10.指定german服务信息
MariaDB [(none)]> SELECT gman_servers_set('172.25.21.1:4730');
+--------------------------------------+
| gman_servers_set('172.25.21.1:4730') |
+--------------------------------------+
| 172.25.21.1:4730 |
+--------------------------------------+
1 row in set (0.00 sec)
11.编写mysql触发器
[root@server3 ~]# 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 ;
12.导入数据库,查看触发器
[root@server3 ~]# mysql -p < test.sql
Enter password:
show triggers from test
- worker端
1.打开gearmand并查看端口
[root@server1 rhel7]# systemctl start gearmand
[root@server1 rhel7]# netstat -ntlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:80 0.0.0.0:* LISTEN 3622/nginx: master
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 790/sshd
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 894/master
tcp 0 0 0.0.0.0:4730 0.0.0.0:* LISTEN 14197/gearmand
tcp 0 0 127.0.0.1:9000 0.0.0.0:* LISTEN 3737/php-fpm: maste
tcp6 0 0 :::22 :::* LISTEN 790/sshd
tcp6 0 0 ::1:25 :::* LISTEN 894/master
tcp6 0 0 :::4730 :::* LISTEN 14197/gearmand
2.编写german的worker测试页
[root@server1 ~]# vim worker.php
<?php
$worker = new GearmanWorker();
$worker->addServer();
$worker->addFunction('syncToRedis', 'syncToRedis');
$redis = new Redis();
$redis->connect('172.25.21.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);
}
?>
3.复制worker测试页到/usr/local下
[root@server1 ~]# cp worker.php /usr/local/
4.后台运行worker，查看进程
[root@server1 ~]# nohup php /usr/local/worker.php &> /dev/null &
[1] 14216
[root@server1 ~]# ps ax
- 测试：
1.在server3修改数据库信息
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]> select * from test;
+----+--------+
| id | name |
+----+--------+
| 1 | westos |
| 2 | test2 |
| 3 | test3 |
| 4 | test4 |
| 5 | test5 |
| 6 | test6 |
| 7 | test7 |
| 8 | test8 |
| 9 | test9 |
+----+--------+
9 rows in set (0.00 sec)
MariaDB [test]> update test set name='yun' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
2.在redis上查看，数据更新了
[root@server2 ~]# redis-cli
127.0.0.1:6379> get 1
"yun"
3.刷新浏览器，数据也更新了
扫一扫
545
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
