mysql 觸發器 Varnish:高速http緩衝 varnish cdn 推送平台

標籤：mysql 觸發器 varnish:高速http緩衝 varnish cdn 推送平台

sendfile(資料直接從 kernal的buffer出去)適合小檔案
Aio(nginx)全非同步(與事件驅動io的區別是：資料會到達mem才會通知，其實也不需要通知 可以直接返回給用戶端) 支援：mmap event drive（處理大檔案）

上一回說到，在資料庫修改後，redis裡面的資料不能做到自動更新，必須手動刪除更改過的資料 根據以上問題我們做出如下改進：
所以接下來就要通過 mysql 觸發器將改變的資料同步到 redis 中

配置 gearman 實現資料同步
Gearman 是一個支援分布式的任務分發架構:
Gearman Job Server: Gearman 核心程式,需要編譯安裝並以守護進程形式運行在後台。
Gearman Client:可以理解為任務的要求者。
Gearman Worker:任務的真正執行者,一般需要自己編寫具體邏輯並通過守護進程方式
運行,Gearman Worker 接收到 GearmanClient 傳遞的任務內容後,會按順序處理。

大致流程:下面要編寫的 mysql 觸發器,就相當於 Gearman 的用戶端。修改表,插入表就相當於直接
下發任務。然後通過 lib_mysqludf_json UDF 庫函數將關係資料對應為 JSON 格式,然後
在通過 gearman-mysql-udf 外掛程式將任務加入到Gearman 的任務隊列中,最後通過
redis_worker.php,也就是 Gearman 的worker 端來完成 redis 資料庫的更新。

dd1:
cd /root/redis
安裝gearman軟體包：
yum install gearmand-1.1.8-2.el6.x86_64.rpm libgearman-1.1.8-2.el6.x86_64.rpm
/etc/init.d/php-fpm start
netstat -antlpe
650) this.width=650;" src="https://s1.51cto.com/wyfs02/M00/8F/82/wKiom1jiVOTR-yC8AAEb2IV4LEs326.png-wh_500x0-wm_3-wmp_4-s_1420401243.png" title="1.png" alt="wKiom1jiVOTR-yC8AAEb2IV4LEs326.png-wh_50" />
dd1:安裝php的gearman擴充
tar zxf gearman-1.1.2.tgz
cd gearman-1.1.2
phpize
rpm -ivh libgearman-devel-1.1.8-2.el6.x86_64.rpmlibevent-devel-1.4.13-4.el6.x86_64.rpm libevent-doc-1.4.13-4.el6.noarch.rpmlibevent-headers-1.4.13-4.el6.noarch.rpm
./configure
make && make install
cd /etc/php.d/
cp mysql.ini gearman.ini
vim gearman.ini

650) this.width=650;" src="https://s5.51cto.com/wyfs02/M00/8F/80/wKioL1jiVPHQWQMoAAA6rd766f8147.png-wh_500x0-wm_3-wmp_4-s_906316560.png" title="2.png" alt="wKioL1jiVPHQWQMoAAA6rd766f8147.png-wh_50" />
/etc/init.d/php-fpm restart

查看php已經安裝過的擴充：
php -m | grep gearman
php -m | grep mysql
php -m | grep redis

650) this.width=650;" src="https://s2.51cto.com/wyfs02/M01/8F/82/wKiom1jiVP3AkwqyAAE6Ur-8rOA619.png-wh_500x0-wm_3-wmp_4-s_215290316.png" title="3.png" alt="wKiom1jiVP3AkwqyAAE6Ur-8rOA619.png-wh_50" />
cd redis/
scp gearman-mysql-udf-0.6.tar.gz 172.25.42.12
scp lib_mysqludf_json-master.zip 172.25.42.12:
scp libevent-* libgearman-* 172.25.42.12:


dd3:
/etc/init.d/mysqld start
tar zxf gearman-mysql-udf-0.6.tar.gz
yum install mysql-devel -y
rpm -ivh libgearman-devel-1.1.8-2.el6.x86_64.rpmlibevent-devel-1.4.13-4.el6.x86_64.rpm libevent-doc-1.4.13-4.el6.noarch.rpmlibevent-headers-1.4.13-4.el6.noarch.rpm
cd gearman-mysql-udf-0.6
./configure --libdir=/usr/lib64/mysql/plugin/
make && make install
cd /usr/lib64/mysql/plugin/

進入mysql環境:
註冊 UDF 函數
mysql> CREATE FUNCTION gman_do_background RETURNS STRING SONAME
‘libgearman_mysql_udf.so‘;
mysql> CREATE FUNCTION gman_servers_set RETURNS STRINGSONAME
‘libgearman_mysql_udf.so‘;
查看函數
mysql> select * from mysql.func;
650) this.width=650;" src="https://s2.51cto.com/wyfs02/M02/8F/82/wKiom1jiVQjgvfssAAEbDvW25nE368.png-wh_500x0-wm_3-wmp_4-s_242840592.png" title="4.png" alt="wKiom1jiVQjgvfssAAEbDvW25nE368.png-wh_50" />
安裝 lib_mysqludf_json
lib_mysqludf_jsonUDF 庫函數將關係資料對應為 JSON 格式。通常,資料庫中的資料對應為 JSON 格式,是通過程式來轉換的
unzip lib_mysqludf_json-master.zip
cd lib_mysqludf_json-maste
gcc $(mysql_config --cflags) -shared -fPIC -olib_mysqludf_json.so
lib_mysqludf_json.c
查看 mysql 的模組目錄:
mysql> show global variables like ‘plugin_dir‘
拷貝 lib_mysqludf_json.so 模組:
cp lib_mysqludf_json.so /usr/lib64/mysql/plugin/
註冊 UDF 函數
mysql> CREATE FUNCTION json_object RETURNS STRING SONAME
‘lib_mysqludf_json.so‘
查看函數
mysql> select * from mysql.func;
650) this.width=650;" src="https://s4.51cto.com/wyfs02/M01/8F/80/wKioL1jiVRfDGZoFAAEhotf5aeQ938.png-wh_500x0-wm_3-wmp_4-s_4199231214.png" title="5.png" alt="wKioL1jiVRfDGZoFAAEhotf5aeQ938.png-wh_50" />
指定 gearman 的服務資訊
mysql>SELECT gman_servers_set(‘172.25.42.10:4730‘);
編寫 mysql 觸發器 test.sql

650) this.width=650;" src="https://s4.51cto.com/wyfs02/M02/8F/82/wKiom1jiVSLBrhlQAADbVMv5G4Y467.png-wh_500x0-wm_3-wmp_4-s_2661811817.png" title="6.png" alt="wKiom1jiVSLBrhlQAADbVMv5G4Y467.png-wh_50" />
mysql < test.sql
查看觸發器
mysql> SHOW TRIGGERS FROM test

650) this.width=650;" src="https://s2.51cto.com/wyfs02/M02/8F/80/wKioL1jiVTCyNak7AADmPj-JVY0142.png-wh_500x0-wm_3-wmp_4-s_420658226.png" style="float:none;" title="7.png" alt="wKioL1jiVTCyNak7AADmPj-JVY0142.png-wh_50" />

650) this.width=650;" src="https://s1.51cto.com/wyfs02/M02/8F/80/wKioL1jiVTGwnIDwAADcWSWDWd8026.png-wh_500x0-wm_3-wmp_4-s_1226200224.png" style="float:none;" title="8.png" alt="wKioL1jiVTGwnIDwAADcWSWDWd8026.png-wh_50" />

dd1:編寫 gearman 的 worker 端
nohup php worker.php &>/dev/null & (後台運行)
650) this.width=650;" src="https://s5.51cto.com/wyfs02/M00/8F/82/wKiom1jiVT2QsUEvAADXLRi999U109.png-wh_500x0-wm_3-wmp_4-s_508594494.png" title="9.png" alt="wKiom1jiVT2QsUEvAADXLRi999U109.png-wh_50" />
測試:
更新 mysql 中的資料

650) this.width=650;" src="https://s4.51cto.com/wyfs02/M00/8F/80/wKioL1jiVUjzbLpLAACsg-Qocfo076.png-wh_500x0-wm_3-wmp_4-s_3616601199.png" title="10.png" alt="wKioL1jiVUjzbLpLAACsg-Qocfo076.png-wh_50" />
mysql> update test set name=‘test1‘ where id=1
650) this.width=650;" src="https://s3.51cto.com/wyfs02/M01/8F/82/wKiom1jiVVPBxiRDAABnC_p9V1A094.png-wh_500x0-wm_3-wmp_4-s_1812792019.png" title="11.png" alt="wKiom1jiVVPBxiRDAABnC_p9V1A094.png-wh_50" />
mysql> update test set name=‘dangdang‘ where id=1

650) this.width=650;" src="https://s1.51cto.com/wyfs02/M01/8F/80/wKioL1jiVWPCRvODAACcboIViRE263.png-wh_500x0-wm_3-wmp_4-s_1547998764.png" style="float:none;" title="12.png" alt="wKioL1jiVWPCRvODAACcboIViRE263.png-wh_50" />

650) this.width=650;" src="https://s1.51cto.com/wyfs02/M00/8F/82/wKiom1jiVWTx_rUeAABspmQhL8M437.png-wh_500x0-wm_3-wmp_4-s_1954906733.png" style="float:none;" title="13.png" alt="wKiom1jiVWTx_rUeAABspmQhL8M437.png-wh_50" />

Varnish:高速http緩衝:
dd1:
varnish-3.0.5-1.el6.x86_64.rpm varnish-libs-3.0.5-1.el6.x86_64.rpm
rpm –ivh

650) this.width=650;" src="https://s2.51cto.com/wyfs02/M00/8F/80/wKioL1jiVXDjA21yAACrKkeZn28769.png-wh_500x0-wm_3-wmp_4-s_2633634656.png" title="14.png" alt="wKioL1jiVXDjA21yAACrKkeZn28769.png-wh_50" />
vim /etc/sysconfig/varnish

650) this.width=650;" src="https://s3.51cto.com/wyfs02/M02/8F/82/wKiom1jiVX7wNlciAAEarwUXq2A945.png-wh_500x0-wm_3-wmp_4-s_417131323.png" title="15.png" alt="wKiom1jiVX7wNlciAAEarwUXq2A945.png-wh_50" />
cd /etc/varnish/
vim default.vcl

650) this.width=650;" src="https://s4.51cto.com/wyfs02/M02/8F/80/wKioL1jiVYmiR8ntAADu8nFQKTU032.png-wh_500x0-wm_3-wmp_4-s_3993461897.png" title="16.png" alt="wKioL1jiVYmiR8ntAADu8nFQKTU032.png-wh_50" />
/etc/init.d/varnish start

dd3:/etc/init.d/httpd start
650) this.width=650;" src="https://s5.51cto.com/wyfs02/M00/8F/82/wKiom1jiVZOAJhUeAAAtZcVsNqo596.png-wh_500x0-wm_3-wmp_4-s_2347266704.png" title="17.png" alt="wKiom1jiVZOAJhUeAAAtZcVsNqo596.png-wh_50" />
### 通過 varnishadm 手動清除緩衝
# varnishadm ban.url .*$
#清除所有
# varnishadm ban.url /index.html
#清除 index.html 頁面緩衝
# varnishadm ban.url /admin/$
#清除 admin 目錄緩衝

vim default.vcl
###查看快取命中情況
sub vcl_deliver {
if (obj.hits > 0) {
set resp.http.X-Cache = "HIT from westos cache";
}
else {
set resp.http.X-Cache = "MISS from westos cache";
}
return (deliver);
}

測試快取命中:

650) this.width=650;" src="https://s4.51cto.com/wyfs02/M01/8F/82/wKiom1jiVaTToGcAAACRkAiKGY8831.png-wh_500x0-wm_3-wmp_4-s_2932784238.png" style="float:none;" title="18.png" alt="wKiom1jiVaTToGcAAACRkAiKGY8831.png-wh_50" />

650) this.width=650;" src="https://s4.51cto.com/wyfs02/M01/8F/80/wKioL1jiVaXz7UF8AACSVny81gU745.png-wh_500x0-wm_3-wmp_4-s_3306518526.png" style="float:none;" title="19.png" alt="wKioL1jiVaXz7UF8AACSVny81gU745.png-wh_50" />

定義多個不同網域名稱網站的後端伺服器:
backend default {
  .host = "172.25.42.12";
  .port = "80";
}

backend web {
  .host = "172.25.42.11";
  .port = "80";
}

sub vcl_recv {
if (req.http.host ~ "^(www.)?dd.org") {
set req.http.host = "www.dd.org";
set req.backend = default;
} elsif (req.http.host ~ "^bbs.dd.org") {
set req.backend = web;
} else {error 404 "westos cache";
}
測試：www.dd.org 和 dd.org 是同一個緩衝

650) this.width=650;" src="https://s3.51cto.com/wyfs02/M00/8F/80/wKioL1jiVbKjXO78AAAqHZNhKpQ827.png-wh_500x0-wm_3-wmp_4-s_3106528680.png" title="20.png" alt="wKioL1jiVbKjXO78AAAqHZNhKpQ827.png-wh_50" /> 

而bbs.dd.org是一個緩衝

650) this.width=650;" src="https://s3.51cto.com/wyfs02/M01/8F/82/wKiom1jiVb2TXb0WAAA2cw9AiAg006.png-wh_500x0-wm_3-wmp_4-s_3635239460.png" title="21.png" alt="wKiom1jiVb2TXb0WAAA2cw9AiAg006.png-wh_50" />

#當訪問 www.dd.org 網域名稱時從 default 上取資料,訪問 bbs.dd.org 網域名稱時到web 取資料,訪問其他頁面報錯 //注意：測試主機加解析

負載平衡:（網域名稱負載平衡）
訪問www.dd.org 時 兩台主機響應它
避免再開一台虛擬機器 給dd2 做虛擬機器主機(一個ip 多個網域名稱)
650) this.width=650;" src="https://s2.51cto.com/wyfs02/M01/8F/80/wKioL1jiVcrS6yRSAAD6bR8ezJ4470.png-wh_500x0-wm_3-wmp_4-s_2230962861.png" title="22.png" alt="wKioL1jiVcrS6yRSAAD6bR8ezJ4470.png-wh_50" />
dd1:

vim default.vcl
director lb round-robin {
{.backend = default;}
{.backend = web;}
}

sub vcl_recv {
if (req.http.host ~ "^(www.)?dd.org") {
set req.http.host = "www.dd.org";
set req.backend = lb;
return (pass); (測試用)
} elsif (req.http.host ~ "^bbs.dd.org") {
set req.backend = web;
} else {error 404 "westos cache";
}
}

650) this.width=650;" src="https://s1.51cto.com/wyfs02/M02/8F/82/wKiom1jiVd_Q52y4AACj8ez_h5s125.png-wh_500x0-wm_3-wmp_4-s_386218033.png" style="float:none;" title="23.png" alt="wKiom1jiVd_Q52y4AACj8ez_h5s125.png-wh_50" />

650) this.width=650;" src="https://s4.51cto.com/wyfs02/M02/8F/82/wKiom1jiVd_RFvoGAAAvlvNMdRU852.png-wh_500x0-wm_3-wmp_4-s_1082170786.png" style="float:none;" title="24.png" alt="wKiom1jiVd_RFvoGAAAvlvNMdRU852.png-wh_50" />

650) this.width=650;" src="https://s4.51cto.com/wyfs02/M02/8F/80/wKioL1jiVd_Ruf9HAAAu6VTqix8443.png-wh_500x0-wm_3-wmp_4-s_4166690004.png" style="float:none;" title="25.png" alt="wKioL1jiVd_Ruf9HAAAu6VTqix8443.png-wh_50" />

varnish cdn 推送平台 （清空緩衝）
#需要安裝 php 支援
# unzip bansys.zip -d /var/www/html
# vi /var/www/html/bansys/config.php
#只保留如下設定,其餘注釋掉
<?php
$var_group1 = array(
‘host‘ => array(‘172.25.42.10‘),
‘port‘ => ‘80‘,
);
//varnish 群組定義
//對主機列表進行綁定
$VAR_CLUSTER = array(
‘www.dd.org‘ => $var_group1,
);

vim /etc/varnish/default.vcl
/etc/init.d/varnish reload

測試：

650) this.width=650;" src="https://s2.51cto.com/wyfs02/M00/8F/82/wKiom1jiVfDwBJBpAABVRaS5GXM386.png-wh_500x0-wm_3-wmp_4-s_965929217.png" title="26.png" alt="wKiom1jiVfDwBJBpAABVRaS5GXM386.png-wh_50" />

mysql 觸發器 Varnish:高速http緩衝 varnish cdn 推送平台