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；
![2024Mysql And Redis基础与进阶操作系列（13）作者——LJS[你个小黑子这都还学不会嘛？你是真爱粉嘛？真是的 ~；以后请别侮辱我家鸽鸽]](https://ucc.alicdn.com/pic/developer-ecology/56pfyutwi4tv4_a79819d7298c48188370fc3b8369ec48.png?x-oss-process=image/resize,h_160,m_lfit)
![2024Mysql And Redis基础与进阶操作系列（9）作者——LJS[含MySQL存储过程之局部、系统变量、参数传递、流程控制-判断/case具体详步骤；注意点及常见报错问题所对应的解决方法]](https://ucc.alicdn.com/pic/developer-ecology/56pfyutwi4tv4_c53df19098d2427391fe36eeb35f84c8.png?x-oss-process=image/resize,h_160,m_lfit)
