MySQL与Redis数据库结合(二)——基于lnmp配置gearmand实现Redis和MySQL数据同步（lnmp+redis+mysql+gearmand）

你的名字 2023-07-18 06:00 14阅读 0赞

### 文章目录 ###

*  一、基于lnmp配置gearmand实现Redis和MySQL数据同步
 *   *  1.什么是Gearmand？
     *  2.部署过程
     *  3.测试

# 一、基于lnmp配置gearmand实现Redis和MySQL数据同步 #

我们本篇博客解决的就是：如何将mysql端修改的数据及时更新到rdis端，实现数据同步。

## 1.什么是Gearmand？ ##

Gearmand是一个用来把工作委派给其它机器、分布式的调用更适合做某项工作的机器、并发的做某项工作在多个调用间做负载均衡、或用来调用其它语言的函数的系统。简单来讲，就是客户端程序把请求提交给gearmand，gearmand会把请求转发给合适的worker来处理这个请求，最后还通过gearmand返回结果。

运行流程：  
`Client --> Job --> Worker`

*  Client 请求发起者，客户端程序可以是任何一种语言：C 、PHP 、Perl 、Python等。
 *  Job 请求调度者，负载协调把 Client 发出的请求转发给合适的Worker。
 *  Worker 请求处理者，处理 Job 分发来的请求，可以是任何一种语言

## 2.部署过程 ##

在server1中启动gearmand：

systemctl start gearmand
    systemctl status gearmand

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70]  
在server3中：

yum install unzip -y
    unzip lib_mysqludf_json-master.zip

安装 mariadb-devel：

yum install -y mariadb-devel.x86_64

编译模块：

yum install gcc -y
    cd lib_mysqludf_json-master
    gcc $(mysql_config --cflags) -shared -fPIC -o lib_mysqludf_json.so lib_mysqludf_json.c

将模块放到mysql插件目录:

cp lib_mysqludf_json-master/lib_mysqludf_json.so /usr/lib64/mysql/plugin/

登陆数据库,在数据库中查看：

[root@server3 lib_mysqludf_json-master]# mysql -uroot -predhat
    MariaDB [(none)]> show global variables like 'plugin_dir';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70 1]  
注册udf函数，并查看。

MariaDB [(none)]> CREATE FUNCTION json_object RETURNS STRING SONAME 'lib_mysqludf_json.so';
    MariaDB [(none)]> select * from mysql.func;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70 2]  
安装libgearman安装包，并安装管理gearman的分布式队列插件，进行编译和安装

[root@server3 ~]# yum install libgearman-* libevent-devel-2.0.21-4.el7.x86_64.rpm  -y
    [root@server3 ~]# tar zxf gearman-mysql-udf-0.6.tar.gz 
    [root@server3 ~]# cd gearman-mysql-udf-0.6
    [root@server3 gearman-mysql-udf-0.6]# ls
    [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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70 3]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70 4]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70 5]  
![在这里插入图片描述][2020032417191665.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70 6]  
再次注册两个udf函数

MariaDB [(none)]> CREATE FUNCTION gman_do_background RETURNS STRING SONAME 'libgearman_mysql_udf.so';
    
    MariaDB [(none)]> CREATE FUNCTION gman_servers_set RETURNS STRING SONAME 'libgearman_mysql_udf.so';
    MariaDB [(none)]> select * from mysql.func;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70 7]  
指定german服务信息：

MariaDB [(none)]> SELECT gman_servers_set('172.25.254.1:4730');

![在这里插入图片描述][20200324172121587.png]  
编写mysql触发器脚本test.sql，并导入test数据库

[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 ;
    
    [root@server3 ~]# mysql -predhat < test.sql 
    [root@server3 ~]# mysql -uroot -predhat

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70 8]  
![在这里插入图片描述][20200324172234258.png]  
查看触发器

MariaDB [(none)]> SHOW TRIGGERS FROM test;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70 9]  
在server1中：  
编写gman的worker端：开启gearmand服务并查看其端口号4730是否开启

[root@server1 ~]# systemctl start gearmand
    [root@server1 ~]# netstat -antlpe

cd /usr/local
    vim worker.php 
    
    <?php
    $worker = new GearmanWorker();
    $worker->addServer();
    $worker->addFunction('syncToRedis', 'syncToRedis');
     
    $redis = new Redis();
    $redis->connect('172.25.254.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);
    }
    ?>

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70 10]  
后台运行worker：

nohup php /usr/local/worker.php &> /dev/null &

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70 11]

## 3.测试 ##

修改数据库内容：

MariaDB [(none)]> use test;
    MariaDB [test]> update test set name='westos' where id=2;
    MariaDB [test]> select * from test;

![在这里插入图片描述][20200324172650556.png]  
在redis上查看：

redis-cli
    127.0.0.1:6379> get 2
    "westos"

![在这里插入图片描述][20200324172713906.png]  
在页面查看，数据同步：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70 12]  
此时，就实现了Redis和MySQL数据同步了。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70]: /images/20230528/0b3c0fce0add4f9eb65038f87b6c8f93.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70 1]: /images/20230528/4350f3db9d9b40b995f58a007b2a7849.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70 2]: /images/20230528/e162b45d080142b99a7dd04920f322de.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70 3]: /images/20230528/ad7c7bfab2274e749017bcee0d776176.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70 4]: /images/20230528/75921f5170ce43cd94600c586019a9b8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70 5]: /images/20230528/58c550abaa18453cb1efd2e1f4b1c460.png
[2020032417191665.png]: /images/20230528/07d7f5334c6c4a3d9f11b0c1852d69ea.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70 6]: /images/20230528/170cacf477064428bacaf7016a196a26.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70 7]: /images/20230528/06557ab4281f491280dfb5036eecc861.png
[20200324172121587.png]: /images/20230528/4db9e048d6d14c3c86c621304a54af81.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70 8]: /images/20230528/fdcafcd976f8483c8915ffe69773c548.png
[20200324172234258.png]: /images/20230528/ac071cf967fd4979a59169ccf296d6ce.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70 9]: /images/20230528/7e5047d0e1bd48a48b927f32c60d084b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70 10]: /images/20230528/460471aa9d1a4a4193026af99c0584a2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70 11]: /images/20230528/ce2ae9fed53341e9b8db94bc320407c3.png
[20200324172650556.png]: /images/20230528/bccb6c75462e4acb8d6be3275489f21e.png
[20200324172713906.png]: /images/20230528/60bfd4f84d0d45f1a591beea8f0c32f5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNjk2OQ_size_16_color_FFFFFF_t_70 12]: /images/20230528/b2ea0d9a973846f7ae785a2b35aa2c8f.png