傳統架構在應用代碼中雙寫 DB 與 Cache,邏輯分散且易出現不一致。利用 MySQL UDF(用戶定義函數)+ Trigger,可在數據變更瞬間自動同步 Memcached,業務代碼零侵入,保證強一致性,適合「讀多寫少、鍵值固定、結構簡單」的緩存場景(如 URL 短鏈、配置表、計數器)。
| 組件 | 推薦版本 | 說明 |
|---|---|---|
| MySQL | 5.1+ (需 5.1.73 以上支援新版 xtrabackup) | Server 與 Client 均需滿足版本要求 |
| libevent | 1.2 穩定版 | memcached 依賴 |
| libmemcached | 0.37 | 關鍵:0.42+ API 變更導致編譯失敗('memcached_st' has no member named 'hosts') |
| memcached_functions_mysql | 0.9 | UDF 插件,封裝 libmemcached |
groupadd mysql && useradd -g mysql mysql
tar zxvf mysql-VERSION.tar.gz && cd mysql-VERSION
./configure --prefix=/usr/local/mysql
make && make install
cp support-files/my-medium.cnf /etc/my.cnf
cd /usr/local/mysql
bin/mysql_install_db --user=mysql
chown -R mysql:mysql /usr/local/mysql
bin/mysqld_safe --user=mysql &
# libevent
wget https://www.monkey.org/~provos/libevent-1.2.tar.gz
tar zxvf libevent-1.2.tar.gz && cd libevent-1.2
./configure --prefix=/usr/local/libevent && make && make install
# memcached
tar zxvf memcached-VERSION.tar.gz && cd memcached-VERSION
./configure --prefix=/usr/local/memcached --with-libevent=/usr/local/libevent
make && make install
# 啟動多實例測試
/usr/local/memcached/bin/memcached -d -m 20 -u zhangy -p 12000 -P ./memcached.pid
/usr/local/memcached/bin/memcached -d -m 20 -u zhangy -p 13000 -P ./mem.pid
tar zxvf libmemcached-0.37.tar.gz && cd libmemcached-0.37
./configure --prefix=/usr/local/libmemcached37 --with-memcached
make && make install
tar xzf memcached_functions_mysql-0.9.tar.gz && cd memcached_functions_mysql-0.9
./configure --prefix=/usr/local/memcache_mysql \
--with-mysql-config=/usr/local/mysql/bin/mysql_config \
--with-libmemcached=/usr/local/libmemcached37
make && make install
cp /usr/local/memcache_mysql/lib/libmemcached_functions* /usr/local/mysql/lib/mysql/plugin/
# 導入 UDF 函數定義
/usr/local/mysql/bin/mysql < sql/install_functions.sql
memc_servers_set、memc_servers_version、memc_server_countmemc_set/get/add/replace/append/prepend/delete、_by_key 變體(支援一致性哈希)memc_cas、memc_cas_by_keymemc_increment/decrementmemc_servers_behavior_set/get、memc_behavior_set/getmemc_stats、memc_stat_get_keys/valueDROP TABLE IF EXISTS urls;
CREATE TABLE urls (
id INT(3) NOT NULL,
url VARCHAR(64) NOT NULL DEFAULT '',
PRIMARY KEY (id)
);
SELECT memc_servers_set('127.0.0.1:12000,127.0.0.1:13000');
SELECT memc_set('urls:sequence', 0);
DELIMITER |
DROP TRIGGER IF EXISTS url_mem_insert |
CREATE TRIGGER url_mem_insert
BEFORE INSERT ON urls
FOR EACH ROW BEGIN
SET NEW.id = memc_increment('urls:sequence');
SET @mm = memc_set(CONCAT('urls:', NEW.id), NEW.url);
END |
DELIMITER ;
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(CONCAT('urls:', OLD.id), NEW.url);
END |
DELIMITER ;
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(CONCAT('urls:', OLD.id));
END |
DELIMITER ;
INSERT INTO urls (url) VALUES ('https://google.com'), ('https://baidu.com');
SELECT * FROM urls;
SELECT memc_get('urls:1'), memc_get('urls:2');
UPDATE urls SET url='https://mysql.com/sun' WHERE id=2;
SELECT memc_get('urls:2');
DELETE FROM urls WHERE id=1;
SELECT memc_get('urls:1'); -- 應返回 NULL
;,必須臨時修改分隔符(如 |)。MySQL UDF + Trigger 實現 Memcached 同步,將緩存維護下沉到存儲層,簡化應用架構並保證數據一致性。關鍵在於版本兼容性驗證(特別是 libmemcached 0.37)、觸發器邏輯輕量化、以及對 UDF 非事務性的認知。適合鍵值固定、高頻讀取、低頻變更的緩存場景。