redis作为mysql的加速器

---

1. redis 配合 mysql

• 数据访问流程:client -> app -> redis -> mysql -> redis -> client
• 客户端用 app 访问,先在 redis 里读数据,因为快,redis 没有才去 mysql 读,读完保存在redis 里,然后返回客户端,下次再读就快

server4：nginx和php

1. [root@server4 7008]# yum install -y psmisc-22.20-11.el7.x86_64
2. [root@server4 7008]# killall redis-server  # 关闭之前操作的redis-server进程
3. 安装nginx
tar zxf nginx-1.17.8.tar.gz
cd nginx-1.17.8
yum install pcre-devel -y
yum install zlib-devel -y
./configure --prefix=/usr/local/nginx
make && make install

cd /usr/local/nginx/
ln -s /usr/local/nginx/sbin/nginx /usr/local/sbin

[root@server4 nginx]# cd conf/
[root@server4 conf]# vim nginx.conf
2 user  nginx nginx;

 43         location / {
 44             root   html;
 45             index  index.php index.html index.htm;
 46         }

 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         }


useradd -u 900 nginx
nginx
netstat -antlpe  # 80端口开启，nginx成功

安装php
3. cd rhel7/
yum install -y *
4. [root@server4 rhel7]# systemctl start php-fpm
[root@server4 rhel7]# netstat -antlpe
Active Internet connections (servers and established)
tcp        0      0 127.0.0.1:9000          0.0.0.0:*               LISTEN      0          66449      12030/php-fpm: mast 


server2：redis
[root@server2 ~]# /etc/init.d/redis_6379 stop
Stopping ...
Redis stopped
[root@server2 ~]# vim /etc/redis/6379.conf   # 0.0.0.0
bind 0.0.0.0
[root@server2 ~]# /etc/init.d/redis_6379 start
Starting Redis server...

给server4一个测试页面：

[root@server4 rhel7]# cd /usr/local/nginx/html/
[root@server4 html]# ls
50x.html  index.html  test.php
[root@server4 html]# mv test.php index.php

server3：mysql

1. [root@server3 ~]# rpm -qa|grep mysql
mysql-community-libs-5.7.28-1.el7.x86_64
mysql-community-server-5.7.28-1.el7.x86_64
mha4mysql-node-0.58-0.el7.centos.noarch
mysql-community-common-5.7.28-1.el7.x86_64
mysql-community-client-5.7.28-1.el7.x86_64
mysql-community-libs-compat-5.7.28-1.el7.x86_64
[root@server3 ~]# rpm -e `rpm -qa|grep mysql` --nodeps  # 卸载安装的mysql
2. [root@server3 ~]# yum install maridb -y

3. [root@server3 ~]# yum install mariadb-server -y
4. [root@server3 ~]# systemctl start mariadb
5. [root@server3 ~]# mysql_secure_installation
6. [root@server3 ~]# mysql -uroot -pWestos+001
MariaDB [(none)]> create database test;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> grant all on test.* to redis@'%' identified by 'Westos+001';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

server4:修改index.php文件

[root@server4 html]# vim index.php 
$redis->connect('172.25.60.2',6379) or die ("could net connect redis server");

$connect = mysql_connect('172.25.60.3','redis','Westos+001');

server3:

[root@server3 ~]# ls
test.sql
[root@server3 ~]# mysql -pWestos+001 < test.sql   #导入数据

测试：
第一次从mysql中取数据
第二次直接从redis中取数据

问题：
server3：

[root@server3 ~]# mysql -uroot -pWestos+001
MariaDB [(none)]> use test;

MariaDB [test]> update test set name='westos' where id=1;
Query OK, 1 row affected (0.35 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)

[root@server2 ~]# redis-cli  # redis却没有数据更新
127.0.0.1:6379> get 1

2. 调度器：gearmand

server4:

[root@server4 html]# systemctl start gearmand
[root@server4 html]# systemctl status gearmand
● gearmand.service - Gearmand distributed job system
   Loaded: loaded (/usr/lib/systemd/system/gearmand.service; disabled; vendor preset: disabled)
   Active: active (running) since Thu 2020-03-12 16:35:28 CST; 5s ago
  Process: 12075 ExecStart=/usr/sbin/gearmand -d $OPTIONS (code=exited, status=0/SUCCESS)
 Main PID: 12076 (gearmand)
   CGroup: /system.slice/gearmand.service
           └─12076 /usr/sbin/gearmand -d

Mar 12 16:35:28 server4 systemd[1]: Starting Gearmand distributed jo....
Mar 12 16:35:28 server4 systemd[1]: Started Gearmand distributed job....
Hint: Some lines were ellipsized, use -l to show in full.

server3:

1. [root@server3 ~]# unzip lib_mysqludf_json-master.zip 
[root@server3 ~]# ls
lib_mysqludf_json-master  lib_mysqludf_json-master.zip  test.sql
2. [root@server3 ~]# yum install mariadb-devel -y
[root@server3 ~]# yum install gcc -y

3. [root@server3 ~]# cd lib_mysqludf_json-master
[root@server3 lib_mysqludf_json-master]# gcc $(mysql_config --cflags) -shared -fPIC -o lib_mysqludf_json.so lib_mysqludf_json.c

4. [root@server3 lib_mysqludf_json-master]# cp lib_mysqludf_json.so /usr/lib64/mysql/plugin/
5. [root@server3 ~]# mysql -uroot -p
Enter password: 

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

注册函数
MariaDB [(none)]> create function json_object returns string soname 'lib_mysqludf_json.so';
Query OK, 0 rows affected (0.00 sec)

安装gearman插件

1. [root@server3 ~]# tar zxf gearman-mysql-udf-0.6.tar.gz
[root@server3 ~]# cd gearman-mysql-udf-0.6
2. [root@server3 rhel7]# yum install libevent-devel-2.0.21-4.el7.x86_64.rpm libgearman-* -y
3. [root@server3 gearman-mysql-udf-0.6]# ./configure --libdir=/usr/lib64/mysql/plugin/ --with-mysql
4. [root@server3 gearman-mysql-udf-0.6]# make && make install
5. [root@server3 gearman-mysql-udf-0.6]# mysql -uroot -pWestos+001

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)

##指定 gman 服务信息
MariaDB [(none)]> SELECT gman_servers_set('172.25.60.4:4730');
+--------------------------------------+
| gman_servers_set('172.25.60.4:4730') |
+--------------------------------------+
| 172.25.60.4:4730                     |
+--------------------------------------+
1 row in set (0.00 sec)

编写 mysql 触发器

[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 -p < test.sql

编写 gman 的 worker 端

[root@server4 ~]# cp worker.php /usr/local/
[root@server4 ~]# cd /usr/local/
[root@server4 local]# vim worker.php 
$redis->connect('172.25.60.2', 6379);

后台运行 worker

[root@server4 local]# nohup php /usr/local/worker.php &> /dev/null &
[1] 12107

测试：

[root@server3 local]# mysql -uroot -pWestos+001
MariaDB [(none)]> use test;

MariaDB [test]> update test set name='westos' where id=2;