通過MySQL的UDFs和Trigger操作Memcached薦

UltraSQL發表於2015-05-04

通過MySQL的UDFs和Trigger操作Memcached

UDFs是User Defined Functions的縮寫，表示MySQL的使用者定義函式，應用程式可以利用這些函式從MySQL 5.0以上版本的資料庫中訪問Memcached寫入或者獲取的資料。此外，MySQL從5.1版本開始支援觸發器，從而可以在觸發器中使用UDFs直接更新Memcached的內容，這種方式降低了應用程式設計和編寫的複雜性。下面簡單介紹UDFs的安裝和使用。

UDFs安裝

安裝UDFs，需要依次安裝MySQL、libevent、memcached、libmemcached、memcached_functions_sql。

1. 安裝mysql-5.6.24.tar.gz

參照之前的博文《InnoDB memcached外掛部署》中MySQL安裝部分。

2. 安裝libevent-2.0.22-stable.tar.gz和安裝memcached-1.4.22.tar.gz

參照之前的博文《Memcached 1.4.22安裝和配置》。

3. 安裝libmemcached-0.34.tar.gz

memcached functions for mysql官網釋出的1.1版本是09年的，只相容libmemcached0.34版本。

mkdir -p /usr/local/libmemcached
wget https://launchpad.net/libmemcached/1.0/0.34/+download/libmemcached-0.34.tar.gz
tar zxvf libmemcached-0.34.tar.gz
cd libmemcached-0.34
./configure --prefix=/usr/local/libmemcached --with-memcached=/usr/local/bin/memcached
make
make install

4. 安裝memcached_functions_mysql

wget https://launchpad.net/memcached-udfs/trunk/1.1/+download/memcached_functions_mysql-1.1.tar.gz
tar xvf memcached_functions_mysql-1.1.tar.gz
cd memcached_functions_mysql-1.1
./configure --prefix=/usr/local/memcache_mysql --with-mysql=/usr/local/mysql/bin/mysql_config --with-libmemcached=/usr/local/libmemcached
make
make install

5. 拷貝lib檔案到mysql的plugin下面

cp /usr/local/memcache_mysql/lib/libmemcached_functions* /usr/local/mysql/lib/plugin

6. 配置動態連結庫

echo "/usr/local/lib" >> /etc/ld.so.conf
ldconfig

7. 新增memcache UDF 函式

mysql < ./sql/install_functions.sql

備註：

首次需要使用CREATE FUNCTION來初始化使用者定義函式。有兩種方法可以初始化所有MySQL提供的使用者定義函式：

第一種方法是在MySQL的SQL命令列中執行memcached_functions_mysql原始碼目錄下的sql/install_functions.sql。第二種方法是執行memcached_functions_mysql原始碼目錄下的utils/install.pl這個Perl指令碼，把memcache function作為UDFs加入MySQL。

8. 執行下面SQL命令，檢視安裝是否成功。

mysql> select name,dl from mysql.func;
+------------------------------+---------------------------------+
| name | dl |
+------------------------------+---------------------------------+
| memc_add | libmemcached_functions_mysql.so |
| memc_add_by_key | libmemcached_functions_mysql.so |
| memc_servers_set | libmemcached_functions_mysql.so |
| memc_server_count | libmemcached_functions_mysql.so |
| memc_set | libmemcached_functions_mysql.so |
| memc_set_by_key | libmemcached_functions_mysql.so |
| memc_cas | libmemcached_functions_mysql.so |
| memc_cas_by_key | libmemcached_functions_mysql.so |
| memc_get | libmemcached_functions_mysql.so |
| memc_get_by_key | libmemcached_functions_mysql.so |
| memc_get_cas | libmemcached_functions_mysql.so |
| memc_get_cas_by_key | libmemcached_functions_mysql.so |
| memc_delete | libmemcached_functions_mysql.so |
| memc_delete_by_key | libmemcached_functions_mysql.so |
| memc_append | libmemcached_functions_mysql.so |
| memc_append_by_key | libmemcached_functions_mysql.so |
| memc_prepend | libmemcached_functions_mysql.so |
| memc_prepend_by_key | libmemcached_functions_mysql.so |
| memc_increment | libmemcached_functions_mysql.so |
| memc_decrement | libmemcached_functions_mysql.so |
| memc_replace | libmemcached_functions_mysql.so |
| memc_replace_by_key | libmemcached_functions_mysql.so |
| memc_servers_behavior_set | libmemcached_functions_mysql.so |
| memc_servers_behavior_get | libmemcached_functions_mysql.so |
| memc_behavior_set | libmemcached_functions_mysql.so |
| memc_behavior_get | libmemcached_functions_mysql.so |
| memc_list_behaviors | libmemcached_functions_mysql.so |
| memc_list_hash_types | libmemcached_functions_mysql.so |
| memc_list_distribution_types | libmemcached_functions_mysql.so |
| memc_udf_version | libmemcached_functions_mysql.so |
| memc_libmemcached_version | libmemcached_functions_mysql.so |
| memc_stats | libmemcached_functions_mysql.so |
| memc_stat_get_keys | libmemcached_functions_mysql.so |
| memc_stat_get_value | libmemcached_functions_mysql.so |
+------------------------------+---------------------------------+
34 rows in set (0.00 sec)

使用Trigger操作Memcached

1. 建立兩張表

新建兩張表：urls和results，更新urls表中的內容，使系統自動更新Memcached的內容。results用來記錄更新Memcached失敗的記錄。

SQL程式碼如下：

use test;
drop table if exists urls;
CREATE TABLE `urls` (
`id` int(10) NOT NULL,
`url` varchar(255) NOT NULL DEFAULT ``,
PRIMARY KEY (`id`)
);
drop table if exists results;
CREATE TABLE `results` (
`id` int(10) NOT NULL,
`result` varchar(255) NOT NULL DEFAULT `error`,
`time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
);

2. 建立3個trigger

當向urls表中插入資料時，對Memcached執行set操作。trigger的程式碼如下：

DELIMITER //
DROP TRIGGER IF EXISTS url_mem_insert;
CREATE TRIGGER url_mem_insert
BEFORE INSERT ON urls
FOR EACH ROW BEGIN
set @mm = memc_set(NEW.id, NEW.url);
if @mm <> 0 then
insert into results(id) values(NEW.id);
end if;
END //
DELIMITER ;

當對urls表中的資料進行更新時，對Memcached執行replace操作。trigger程式碼如下：

DELIMITER //
DROP TRIGGER IF EXISTS url_mem_update;
CREATE TRIGGER url_mem_update
BEFORE UPDATE ON urls
FOR EACH ROW BEGIN
set @mm = memc_replace(OLD.id,NEW.url);
if @mm <> 0 then
insert into results(id) values(OLD.id);
end if;
END //
DELIMITER ;

當對urls表中的資料進行刪除操作時，對Memcached執行delete操作。trigger程式碼如下：

DELIMITER //
DROP TRIGGER IF EXISTS url_mem_delete;
CREATE TRIGGER url_mem_delete
BEFORE DELETE ON urls
FOR EACH ROW BEGIN
set @mm = memc_delete(OLD.ID);
if @mm <> 0 then
insert into results(id) values(OLD.id);
end if;
END //
DELIMITER ;

3. 設定Memcached相關引數

設定UDFs操作Memcaced伺服器的IP地址和埠。

mysql> SELECT memc_servers_set(`192.168.11.52:11211`);
+-----------------------------------------+
| memc_servers_set(`192.168.11.52:11211`) |
+-----------------------------------------+
| 0 |
+-----------------------------------------+
1 row in set (0.07 sec)
mysql>select memc_server_count();
+---------------------+
| memc_server_count() |
+---------------------+
| 1 |
+---------------------+
1 row in set (0.00 sec)

在MySQL命令列中列出可以修改Memcached引數的行為，執行的命令和輸出結果如下：

mysql>select memc_list_behaviors()G
*************************** 1. row ***************************
memc_list_behaviors():
MEMCACHED SERVER BEHAVIORS
MEMCACHED_BEHAVIOR_SUPPORT_CAS
MEMCACHED_BEHAVIOR_NO_BLOCK
MEMCACHED_BEHAVIOR_TCP_NODELAY
MEMCACHED_BEHAVIOR_HASH
MEMCACHED_BEHAVIOR_CACHE_LOOKUPS
MEMCACHED_BEHAVIOR_SOCKET_SEND_SIZE
MEMCACHED_BEHAVIOR_SOCKET_RECV_SIZE
MEMCACHED_BEHAVIOR_BUFFER_REQUESTS
MEMCACHED_BEHAVIOR_KETAMA
MEMCACHED_BEHAVIOR_POLL_TIMEOUT
MEMCACHED_BEHAVIOR_RETRY_TIMEOUT
MEMCACHED_BEHAVIOR_DISTRIBUTION
MEMCACHED_BEHAVIOR_BUFFER_REQUESTS
MEMCACHED_BEHAVIOR_USER_DATA
MEMCACHED_BEHAVIOR_SORT_HOSTS
MEMCACHED_BEHAVIOR_VERIFY_KEY
MEMCACHED_BEHAVIOR_CONNECT_TIMEOUT
MEMCACHED_BEHAVIOR_KETAMA_WEIGHTED
MEMCACHED_BEHAVIOR_KETAMA_HASH
MEMCACHED_BEHAVIOR_BINARY_PROTOCOL
MEMCACHED_BEHAVIOR_SND_TIMEOUT
MEMCACHED_BEHAVIOR_RCV_TIMEOUT
MEMCACHED_BEHAVIOR_SERVER_FAILURE_LIMIT
MEMCACHED_BEHAVIOR_IO_MSG_WATERMARK
MEMCACHED_BEHAVIOR_IO_BYTES_WATERMARK
1 row in set (0.00 sec)

設定MEMCACHED_BEHAVIOR_NO_BLOCK為開啟狀態，這樣在Memcached出現問題（不能連線）時，資料可以繼續插入MySQL中，但有報錯提示；如果不設定此值，那麼Memcached失敗時，資料需要等到Memcached失敗超時後才可以插入到表中。

通過下面的設定，可以避免這種情況的發生。

mysql>select memc_servers_behavior_set(`MEMCACHED_BEHAVIOR_NO_BLOCK`,`1`);
+--------------------------------------------------------------+
| memc_servers_behavior_set(`MEMCACHED_BEHAVIOR_NO_BLOCK`,`1`) |
+--------------------------------------------------------------+
| 0 |
+--------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>select memc_servers_behavior_set(`MEMCACHED_BEHAVIOR_TCP_NODELAY`,`1`);
+-----------------------------------------------------------------+
| memc_servers_behavior_set(`MEMCACHED_BEHAVIOR_TCP_NODELAY`,`1`) |
+-----------------------------------------------------------------+
| 0 |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)

4. 對memcached_functions_mysql的簡單功能進行測試

1）向表urls中插入資料，然後檢視Memcached是否對資料執行set操作。

mysql>insert into urls (id,url) values (1, `http://www.test.com.cn`);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select memc_get(`1`);
+------------------------+
| memc_get(`1`) |
+------------------------+
| http://www.test.com |
+------------------------+
1 row in set (0.00 sec)

1>telnet 192.168.11.52 11211
Trying 192.168.11.52...
Connected to 192.168.11.52 (192.168.11.52).
Escape character is `^]`.
get 1
VALUE 1 0 22
http://www.test.com.cn
END

2）更新表urls裡面的資料，然後查詢Memcached中是否也進行了更新。

mysql>update test.urls set url=`http://blog.test.com.cn` where id=1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select memc_replace(`1`,`http://blog.test.com.cn`);
+---------------------------------------------+
| memc_replace(`1`,`http://blog.test.com.cn`) |
+---------------------------------------------+
| 0 |
+---------------------------------------------+
1 row in set (0.00 sec)
mysql> select memc_get(`1`);
+-------------------------+
| memc_get(`1`) |
+-------------------------+
| http://blog.test.com.cn |
+-------------------------+
1 row in set (0.00 sec)

1>telnet 192.168.11.52 11211
Trying 192.168.11.52...
Connected to 192.168.11.52(192.168.11.52).
Escape character is `^]`.
get 1
VALUE 1 0 23
http://blog.test.com.cn
END

3）刪除表urls中的資料，然後檢視Memcached是否也將該資料刪除了。

mysql>delete from test.urls where id=1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select memc_get(`1`);
+---------------+
| memc_get(`1`) |
+---------------+
| NULL |
+---------------+
1 row in set (0.00 sec)

1>telnet 192.168.11.52 11211
Trying 192.168.11.52...
Connected to 192.168.11.52 (192.168.11.52).
Escape character is `^]`.
get 1
END

5. 使用memcached_functions_mysql的經驗與技巧

memcached_functions_mysql使用起來比較簡單，但是由於環境的差別，在實踐過程中可能會遇到諸多的問題。下面總結了一些在使用memcached_functions_mysq過程中可能出現的問題和注意事項。

1) MySQL重啟問題

如果MySQL伺服器出現重啟，需要重新設定連線Memcached關係（SELECT memc_servers_set(`192.168.11.52:11211`))。

2) 程式BUG問題

memcached_functions_mysql的源程式有可能存在bug，並且會導致MySQL的失敗。針對這個問題，讀者要儘量選擇源程式的穩定版本。

3) 網路因素

網路因素是指MySQL和Memcached是否處在同一個IDC、它們之間的網路效能是否很好。網路效能越好，則速度越快。使用本機的Memcached可以適當減少網路開銷。

4) 插入的資料量

插入資料量的大小包含兩個方面：向MySQL插入每條記錄的大小，向Memcached中更新資料的大小。更新MySQL、Memcached的資料越大，更新的速度越慢。因此，要做好前期規劃。

5) 延時問題

如果MySQL所在的機器使用的資源比較大，會導致更新Memcached過於緩慢，即出現類似m/s的延時問題。

6) 容災問題

如果MySQL和Memcached中有當機情況出現時，需要考慮怎麼恢復，根據前一小節的測試可以這樣考慮：建一張錯誤表，如果在更新mc時出現問題，自動把更新錯誤的記錄插到這張表中，通過查詢這張表，可以知道哪些資料在什麼時間出現過更新錯誤。

如果memcached_functions_mysql應用於生產環境，需要考慮監控和出現問題時的恢復工作（寫好指令碼以完善這個工作）。

7) MySQL自身因素

如執行的MySQL語句的效率以及連線MySQL的client程式（php）的連線開銷等，這些問題都需要考慮。

Mysql with memcached 啟動與操作
2015-07-05
MySql
通過Cache::Memcached::Fast方式
2012-06-10
AST
通過trigger禁止使用者刪除或修改等DDL操作
2012-01-10
通過例項來理解MySQL索引薦
2014-09-25
MySql索引
通過誤操作帶來的grub選單丟失薦
2007-10-26
通過Cache::Memcached方式例項物件應用
2012-06-10
物件
通過shell指令碼批量操作mysql資料庫
2019-07-14
指令碼MySql資料庫
Mysql Trigger
2020-08-19
MySql
memcached的學習過程
2021-09-09
memcached 命令操作詳解
2013-06-28
Tomcat通過Memcached實現session共享的完整部署記錄
2017-02-14
TomcatSession
oracle 通過trigger解決drop許可權問題
2009-11-26
Oracle
談談通過軟考的感受，包括通過軟考的經驗薦
2009-12-24
MySQL如何通過分析binlog日誌找出操作頻繁的表
2018-07-11
MySql
如何通過C#操作Access，本人親測通過
2017-10-23
C#
memcached原始碼分析-----雜湊表基本操作以及擴容過程
2017-02-01
原始碼
通過MySQL儲存原理來分析排序和鎖
2019-04-10
MySql排序
redis和memcached
2017-07-24
Redis
通過shrink操作重整表的實驗
2010-05-08
通過命令curl 操作ElasticSearch指南
2018-12-31
Elasticsearch
Redis和Memcached的區別
2017-07-20
Redis
Memcached和Redis的區別？
2024-03-18
Redis
透過trigger禁止使用者刪除或修改等DDL操作
2017-01-13
PHP memcached 其他常用操作方法
2018-02-02
PHP
mysql 通過IP連線
2015-04-17
MySql
MySQL的原始碼安裝及使用UDFs進行資料自動更新的教程
2021-09-09
MySql原始碼
PHP memcached 利用遠端終端連線、操作memcached伺服器
2018-02-02
PHP伺服器
通過 ES6 Promise 和 jQuery Deferred 的異同學習 Promise薦
2018-01-18
PromisejQuery
java操作Excel(從我的CSDN搬過來的)薦
2012-10-16
JavaExcel
mysql通過ssl的方式生成祕鑰
2013-12-06
MySql
融會貫通學習trigger
2014-11-26
Mysql之觸發器trigger
2017-05-17
MySql觸發器
通過跳板機連線MySQL
2020-09-24
MySql
sybase終於支援SQL UDFs了
2007-05-29
SQL
通過HTTP的HEADER完成各種騷操作
2018-08-19
HTTPHeader
Python 通過List 實現佇列的操作
2020-09-29
Python佇列
如何通過簡單的shell指令碼操作MongoDB
2016-07-05
指令碼MongoDB
SAP的通過支票進行客戶清帳操作
2015-01-05

通過MySQL的UDFs和Trigger操作Memcached薦

相關文章