redis 作为 mysql的缓存服务器(读写分离)

redis 作为 mysql的缓存服务器(读写分离)

环境准备

IP	主机名	服务
192.168.131.132	redis	redis
192.168.131.133	lnmp	mysql+nginx+php

[root@localhost ~]# cat /etc/redhat-release
CentOS Linux release 7.6.1810 (Core)
[root@localhost ~]# sestatus
SELinux status:                 disabled
[root@localhost ~]# systemctl status firewalld.service
● firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
   Active: inactive (dead)
     Docs: man:firewalld(1)
[root@localhost ~]# iptables -nL
Chain INPUT (policy ACCEPT)
target     prot opt source               destination   

Chain FORWARD (policy ACCEPT)
target     prot opt source               destination   

Chain OUTPUT (policy ACCEPT)
target     prot opt source               destination
[root@localhost ~]# hostnamectl set-hostname --static redis
[root@localhost ~]# hostnamectl set-hostname --static lnmp

部署nginx

[root@lnmp ~]# yum -y install epel-release.noarch
[root@lnmp ~]# yum -y install nginx \
php php-fpm php-cli php-common php-gd \
php-mbstring php-mysql php-pdo php-devel \
php-xmlrpc php-xml php-bcmath php-dba php-enchant
[root@lnmp ~]# nginx -v
nginx version: nginx/1.16.1
[root@lnmp ~]# php -v
PHP 5.4.16 (cli) (built: Apr  1 2020 04:07:17)
Copyright (c) 1997-2013 The PHP Group
Zend Engine v2.4.0, Copyright (c) 1998-2013 Zend Technologies

# 配置nginx
[root@lnmp ~]# cp /etc/nginx/nginx.conf{,.bak}
[root@lnmp ~]# vim /etc/nginx/nginx.conf
    server {
        listen       80;
        server_name  localhost;
        root         /usr/share/nginx/html;

        # Load configuration files for the default server block.
        include /etc/nginx/default.d/*.conf;

        location / {
        }
  
        location ~ \.php$ {    # 配置php
        root /usr/share/nginx/html;
        fastcgi_pass 127.0.0.1:9000;
        fastcgi_index index.php;
        fastcgi_param SCRIPT_FILENAME /usr/share/nginx/html/$fastcgi_script_name;
        include fastcgi_params;
        }

        error_page 404 /404.html;
            location = /40x.html {
        }

        error_page 500 502 503 504 /50x.html;
            location = /50x.html {
        }
    }
[root@lnmp ~]# nginx -t
nginx: the configuration file /etc/nginx/nginx.conf syntax is ok
nginx: configuration file /etc/nginx/nginx.conf test is successful
[root@lnmp ~]# systemctl enable nginx.service --now     # 立即启动nginx，并开机自启

# 配置php-fpm
[root@lnmp ~]# sed -i 's/apache/nginx/g' /etc/php-fpm.d/www.conf
[root@lnmp ~]# systemctl enable php-fpm.service --now     # 立即启动php-fpm，并开机自启
[root@lnmp ~]# [root@mysql html]# cat index.php
<?php
    phpinfo();
?>
# 浏览器访问192.168.131.133/index.php 可以访问到php的信息，就ok了

部署mysql

[root@lnmp ~]# wget https://repo.mysql.com/mysql57-community-release-el7-11.noarch.rpm
[root@lnmp ~]# yum -y install mysql57-community-release-el7-11.noarch.rpm
[root@lnmp ~]# yum -y install yum-utils   # 安装yum管理工具
[root@lnmp ~]# yum-config-manager --disable mysql80-community   # 禁用8.0版本
[root@lnmp ~]# yum-config-manager --enable mysql57-community    # 启用5.7版本
[root@lnmp ~]# yum repolist enabled | grep mysql    # 检查一下，确保只有一个版本
mysql-connectors-community/x86_64       MySQL Connectors Community           165
mysql-tools-community/x86_64            MySQL Tools Community                115
mysql57-community/x86_64                MySQL 5.7 Community Server           444
[root@lnmp ~]# yum -y install mysql-community-server mysql
[root@lnmp ~]# systemctl enable mysqld --now   # 设为开机自启，并立即启动
[root@lnmp ~]# grep "temporary password" /var/log/mysqld.log
2020-07-30T12:36:18.820133Z 1 [Note] A temporary password is generated for root@localhost: 3j3Otwv%=uD*
[root@lnmp ~]# mysql -uroot -p'3j3Otwv%=uD*'
mysql> alter user 'root'@'localhost' identified by 'Test123.com';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all privileges on *.* to 'root'@'192.168.131.%' identified by 'Test123.com';
Query OK, 0 rows affected, 1 warning (0.02 sec)

部署redis

[root@redis ~]# yum -y install gcc make
[root@redis ~]# wget http://download.redis.io/releases/redis-4.0.9.tar.gz
[root@redis ~]# tar xf redis-4.0.9.tar.gz -C /usr/local/
[root@redis ~]# cd /usr/local/
[root@redis local]# ln -s redis-6.0.5/ redis
[root@redis local]# cd redis
[root@redis redis]# make && make install
[root@redis redis]# cp redis.conf{,.bak}
[root@redis redis]# vim redis.conf
bind 192.168.131.133    # 只接收192.168.131.133的请求
protected-mode no       # 是否开启保护模式，默认开启。要是配置里没有指定bind和密码。开启该参数后，redis只会本地进行访问
daemonize yes           # 后台运行，默认no
[root@redis redis]# ./src/redis-server /usr/local/redis/redis.conf
49183:C 09 Aug 15:03:11.798 # oO0OoO0OoO0Oo Redis is starting oO0OoO0OoO0Oo
49183:C 09 Aug 15:03:11.799 # Redis version=4.0.9, bits=64, commit=00000000, modified=0, pid=49183, just started
49183:C 09 Aug 15:03:11.799 # Configuration loaded
[root@redis redis]# ss -nltp | grep 6379
LISTEN     0      128    192.168.131.133:6379                     *:*                   users:(("redis-server",pid=49184,fd=6))

# lnmp服务器上配置redis的php插件
[root@lnmp ~]# [root@lnmp ~]# wget https://github.com/phpredis/phpredis/archive/4.0.2.tar.gz
[root@lnmp ~]# tar xf 4.0.2.tar.gz -C /usr/local/
[root@lnmp ~]# cd /usr/local/phpredis-4.0.2/
[root@lnmp phpredis-4.0.2]# phpize
Configuring for:
PHP Api Version:         20100412
Zend Module Api No:      20100525
Zend Extension Api No:   220100525
[root@lnmp phpredis-4.0.2]# ./configure --with-php-config=/usr/bin/php-config
[root@lnmp phpredis-4.0.2]# make && make install
[root@lnmp ~]# cp /etc/php.ini{,.bak}
[root@lnmp ~]# vim /etc/php.ini   # 如果没有  extension=redis.so  就加上这一行
[redis]
extension=redis.so
[root@lnmp ~]# systemctl restart php-fpm.service
# 浏览器访问192.168.131.132/index.php 可以访问到redis的php版本信息，就ok了

实现redis作为mysql的缓存服务器

[root@lnmp ~]# mysql -uroot -p
Enter password:
mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> use test;
Database changed
mysql> CREATE TABLE `test` (`id` int(7) NOT NULL AUTO_INCREMENT, `name` char(8) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO `test` VALUES (1,'test01'),(2,'test02'),(3,'test03'),(4,'test04'),(5,'test05'),(6,'test06'),(7,'test07'),(8,'test08'),(9,'test09');
Query OK, 9 rows affected (0.04 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> select * from test.test;
+----+--------+
| id | name   |
+----+--------+
|  1 | test01 |
|  2 | test02 |
|  3 | test03 |
|  4 | test04 |
|  5 | test05 |
|  6 | test06 |
|  7 | test07 |
|  8 | test08 |
|  9 | test09 |
+----+--------+
9 rows in set (0.00 sec)

编写php测试代码

[root@lnmp ~]# vim /usr/share/nginx/html/test.php
<?php
        $redis = new redis();
        $redis->connect('192.168.131.132',6379) or die ("could net connect redis server");
        $query = "select * from test limit 8";
        // 为了简单一点，这里就读取了8条数据
        for ($key = 1; $key < 9; $key++)
        {
                if (!$redis->get($key))
                {
                        $connect = mysql_connect('192.168.131.133','root','Test123.com');
                        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 < 9; $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>";
        }
?>

浏览器访问：192.168.131.133/test.php
可以获取到mysql内插入的内容就是成功了
到这里，我们已经实现了redis作为mysql的缓存服务器，但是如果更新了mysql，redis中仍然会有对应的KEY，数据就不会更新，此时就会出现mysql和redis数据不一致的情况。所以接下来就要通过mysql触发器将改变的数据同步到redis中。

通过gearman实现同步

Gearman是一个支持分布式的任务分发框架：
- Gearman Job Server：'Gearman核心程序，需要编译安装并以守护进程形式运行在后台。
- Gearman Client：'可以理解为任务的请求者。
- Gearman Worker：'任务的真正执行者，一般需要自己编写具体逻辑并通过守护进程方式运行，Gearman Worker接收到Gearman Client传递的任务内容后，会按顺序处理。

安装gearmand

[root@lnmp~]# yum -y install gearmand libgearman-devel
[root@lnmp ~]# systemctl enable gearmand.service --now

安装gearman的php扩展

[root@lnmp ~]# wget http://pecl.php.net/get/gearman-1.1.2.tgz
[root@lnmp ~]# tar xf gearman-1.1.2.tgz -C /usr/local/
[root@lnmp ~]# cd /usr/local/gearman-1.1.2/
[root@lnmp gearman-1.1.2]# phpize
Configuring for:
PHP Api Version:         20100412
Zend Module Api No:      20100525
Zend Extension Api No:   220100525
[root@lnmp gearman-1.1.2]# ./configure --with-php-config=/usr/bin/php-config
[root@lnmp gearman-1.1.2]# make && make install
[root@lnmp ~]# vim /etc/php.ini
[gearman]
extension = gearman.so
[root@lnmp ~]# systemctl restart php-fpm.service
# 浏览器访问：http://192.168.131.133/index.php 可以获取到gearman的信息即可

安装lib_mysqludf_json

[root@lnmp ~]# wget https://mirrors.tuna.tsinghua.edu.cn/mysql/downloads/MySQL-5.7/mysql-community-devel-5.7.31-1.el7.x86_64.rpm
[root@lnmp ~]# yum -y localinstall mysql-community-devel-5.7.31-1.el7.x86_64.rpm
[root@lnmp ~]# wget https://github.com/mysqludf/lib_mysqludf_json/archive/master.zip
[root@lnmp ~]# mv master.zip /usr/local/
[root@lnmp ~]# cd /usr/local/
[root@lnmp ~]# unzip master.zip
Archive:  master.zip
37f851c808c4161beb4d5e535771dc0c59c82de6
   creating: lib_mysqludf_json-master/
  inflating: lib_mysqludf_json-master/README.md
  inflating: lib_mysqludf_json-master/lib_mysqludf_json.c
  inflating: lib_mysqludf_json-master/lib_mysqludf_json.html
  inflating: lib_mysqludf_json-master/lib_mysqludf_json.so
  inflating: lib_mysqludf_json-master/lib_mysqludf_json.sql
[root@lnmp local]# cd lib_mysqludf_json-master/
[root@lnmp lib_mysqludf_json-master]# gcc $(mysql_config --cflags) -shared -fPIC -o lib_mysqludf_json.so lib_mysqludf_json.c
[root@lnmp ~]# mysql -uroot -p
Enter password:
mysql> show global variables like 'plugin_dir';
+---------------+--------------------------+
| Variable_name | Value                    |
+---------------+--------------------------+
| plugin_dir    | /usr/lib64/mysql/plugin/ |
+---------------+--------------------------+
1 row in set (0.06 sec)
[root@lnmp ~]# cp /usr/local/lib_mysqludf_json-master/lib_mysqludf_json.so /usr/lib64/mysql/plugin/  # 将插件copy到上面mysql获取到的目录下
[root@lnmp ~]# mysql -uroot -p
Enter password:
mysql> CREATE FUNCTION json_object RETURNS STRING SONAME 'lib_mysqludf_json.so';
Query OK, 0 rows affected (0.00 sec)

安装gearman-mysql-udf

[root@lnmp ~]# wget https://launchpad.net/gearman-mysql-udf/trunk/0.6/+download/gearman-mysql-udf-0.6.tar.gz
[root@lnmp ~]# tar xf gearman-mysql-udf-0.6.tar.gz
[root@lnmp ~]# cd gearman-mysql-udf-0.6/
[root@lnmp gearman-mysql-udf-0.6]# ./configure --with-mysql=/usr/bin/mysql_config --libdir=/usr/lib64/mysql/plugin/     # libdir的路径，要改为自己mysql里面查询出来的路径
[root@lnmp gearman-mysql-udf-0.6]# make && make install
# 注册UDF函数
[root@lnmp ~]# mysql -uroot -p
Enter password:
mysql> CREATE FUNCTION gman_do_background RETURNS STRING SONAME 'libgearman_mysql_udf.so';
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE FUNCTION gman_servers_set RETURNS STRING SONAME 'libgearman_mysql_udf.so';
Query OK, 0 rows affected (0.00 sec)

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 |
+--------------------+-----+-------------------------+----------+
2 rows in set (0.00 sec)

编写mysql触发器

# 根据实际情况编写
[root@lnmp ~]# mysql -uroot -p
Enter password:
mysql> use test;
Database changed
mysql> DELIMITER $$
mysql> 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 $$
mysql> DELIMITER;

编写gearman的worker端

[root@lnmp ~]# vim /usr/share/nginx/html/mysql.php
<?php
$worker = new GearmanWorker();
$worker->addServer();
$worker->addFunction('syncToRedis', 'syncToRedis');

$redis = new redis();
$redis->connect('192.168.131.132', 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@lnmp ~]# nohup php /usr/share/nginx/html/mysql.php &
# $redis->set($work->id, $work->name);这条语句就是将id作KEY和name作VALUE分开存储，需要和前面写的php测试代码的存取一致。

更新mysql中的数据

[root@lnmp ~]# mysql -uroot -p
Enter password:
mysql>set @RECV= 1;
mysql>select @RECV;
l>use mytest;
mysql> update test set name = 'ssss' where id = 1;
mysql> select @RECV；