將MySQL資料對應到Memcached

文章目錄

• 2月 26th, 2008

記錄&分享

將MySQL資料對應到Memcached2月 26th, 2008

作者:張立冰
出處:http://www.libing.name/2008/02/26/mysql-map-memcached.html

本文介紹利用MySQL UDFS For Memcached與MySQL觸發程式結合實現將MySQL的資料對應到Memcached的一種方法，能實現當MySQL資料更新時，即時更新Memcache資料的效果，從而減少開發相應程式來維護Memcached資料。

MySQL UDFS For Memcached的官方介紹：

This is a set of MySQL UDFs (user defined functions) to work with memcached using libmemcached. With these functions you get, set, append, prepend, delete, increment, decrement objects in memcached, as well as set which servers to use and which behavior the server connections will use. Combine these functions with MySQL triggers and you can manage your memcached cache. The library makes use of libmemcached. You can also use memcached as a global sequence generator for MySQL by making use of the increment function. These functions are compatible with all versions of MySQL.

在測試之間需要安裝以下軟體及開發包：

MySQL 5.0+
Memcached （libevent…)
Libmemcached

下載UDFs:http://download.tangent.org/memcached_functions_mysql-0.2.tar.gz

編譯安裝：

tar xzvf memcached_functions_mysql-0.2.tar.gz
cd memcached_functions_mysql-0.2
./configure –with-mysql=MYSQL_DIR/bin/mysql_config –libdir=MYSQL_DIR/lib/mysql/
make
make install

安裝完成後將UDFs載入到MySQL中：

memc_servers_set()
mysql> create function memc_servers_set returns int soname “libmemcached_functions_mysql.so”;

memc_set()
mysql> create function memc_set returns int soname “libmemcached_functions_mysql.so”;

memc_get()
mysql> create function memc_get returns string soname “libmemcached_functions_mysql.so”;

memc_delete()
mysql> create function memc_delete returns string soname “libmemcached_functions_mysql.so”;

此處省略多行……
測試UDF是否安裝成功：
先添加Memcached，可以添加多台。

mysql> select memc_servers_set(’127.0.0.1′);
mysql> select memc_set(’libing’, ‘roast’);
+—————————-+
| memc_set(’libing’, ‘roast’) |
+—————————-+
| 0 |
+—————————-+
1 row in set (0.00 sec)
mysql> select memc_get(’libing’);
+———————————+
| memc_get(’libing’) |
+———————————+
| roast |
+———————————+
1 row in set (0.00 sec)

測試將MySQL資料對應到Memcached中，如果對MySQL的觸發程式不熟悉可以參考MySQL手冊第21章。

mysql> create table memcached (`key` int, `value` varchar(100));
Query OK, 0 rows affected (0.02 sec)

mysql> create trigger mysqlmmc before insert on memcached for each row
> set @tmp = memc_set(NEW.key, NEW.value);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into memcached values(9,’roast’);
Query OK, 1 row affected (0.01 sec)

mysql> select memc_servers_set(’127.0.0.1′);
+——————————-+
| memc_servers_set(’127.0.0.1′) |
+——————————-+
| 58360307675824128 |
+——————————-+
1 row in set (0.00 sec)

mysql> select memc_get(’9′);
+—————+
| memc_get(’9′) |
+—————+
| roast |
+—————+
1 row in set (0.00 sec)

[root@nd-zf-mx ~]# telnet 127.0.0.1 11211
Trying 127.0.0.1…
Connected to nd (127.0.0.1).
Escape character is ‘^]’.
get 9
VALUE 9 0 5
roast
END

這裡只實現了當資料庫的新記錄產生時候，同步更新Memcached的觸發程式。
當資料庫有更新和刪除操作時都需要同步更新Memcached從而達到映射的關係。