通過UDFs實作Memcached與Mysql的自動更新
一:UDFs使用簡介:
UDFs是User Defined Functions的縮寫,表示Mysql的使用者定義函數,應用程式可以利用這些函數從Mysql5.0以上的版本的資料庫中通路Memcached寫入或者擷取資料。此外,Mysql從5.1版本開始支援觸發器,進而可以在觸發器中使用UDFs直接更新Memcached的内容,這種方式降低了應用程式設計與編寫的複雜性。
二:UDFs的安裝:
安裝UDFs需要在資料庫伺服器上安裝兩個包,分别是libmemcached和memcached-functions-mysql,這兩個包可以從http://download.tangent.org/下載下傳。
1:源碼包編譯安裝Mysql
安裝cmake
MySQL從5.5版本開始,通過./configure進行編譯配置方式已經被取消,取而代之的是cmake工具。
是以,我們首先要在系統中源碼編譯安裝cmake工具。
[root@memcached ~]# wget http://www.cmake.org/files/v2.8/cmake-2.8.4.tar.gz
[root@memcached ~]# tar zxvf cmake-2.8.4.tar.gz
[root@memcached ~]# cd cmake-2.8.4
[root@memcached cmake-2.8.4]# ./configure
[root@memcached cmake-2.8.4]# make && make install
確定一下軟體已安裝:
通過 rpm -qa | grep name 的方式驗證以下軟體包是否已全部安裝。
gcc* gcc-c++* autoconf* automake* zlib* libxml* ncurses-devel* libgcrypt* libtool*
安裝前的系統設定
建立mysql安裝目錄及資料存放目錄
[root@memcached ~]# mkdir /usr/local/mysql
[root@memcached ~]# mkdir /usr/local/mysql/data
建立使用者及組:
[root@memcached ~]# groupadd mysql
[root@memcached ~]# useradd -g mysql mysql
賦予資料存放目錄的權限:
[root@memcached ~]# chown -R mysql:mysql /usr/local/mysql/data/
從configure變更為cmake:
[root@memcached ~]# tar zxvf mysql-5.5.20.tar.gz
[root@memcached ~]# cd mysql-5.5.20
[root@memcached mysql-5.5.20]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DSYSCONFDIR=/usr/local/mysql/etc \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DMYSQL_TCP_PORT=3306 \
-DMYSQL_UNIX_ADDR=/tmp/mysqld.sock \
-DMYSQL_USER=mysql \
-DEXTRA_CHARSETS=all \
-DWITH_READLINE=1 \
-DWITH_SSL=system \
-DWITH_EMBEDDED_SERVER=1 \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1
[root@memcached mysql-5.5.20]# make && make install
配置并初始化資料庫
[root@memcached ~]# mkdir /usr/local/mysql/log
[root@memcached ~]# mkdir /usr/local/mysql/etc
[root@memcached mysql-5.5.20]# cp support-files/my-medium.cnf /usr/local/mysql/etc/my.cnf
初始化資料庫
執行前需賦給scripts/mysql_install_db檔案執行權限:
[root@memcached mysql-5.5.20]# chmod 755 scripts/mysql_install_db
[root@memcached mysql-5.5.20]# scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/
建立管理MySQL資料庫的shell腳本:
[root@memcached mysql-5.5.20]# mkdir /usr/local/mysql/init.d
[root@memcached mysql-5.5.20]# cp support-files/mysql.server /usr/local/mysql/init.d/mysql
賦予shell腳本可執行權限:
[root@memcached mysql-5.5.20]# chmod +x /usr/local/mysql/init.d/mysql
啟動mysql:
[root@memcached mysql-5.5.20]# /usr/local/mysql/init.d/mysql start
Starting MySQL.. [ OK ]
通過指令行登入管理MySQL伺服器(提示輸入密碼時直接回車):
[root@memcached ~]# /usr/local/mysql/bin/mysql -uroot -p -S /tmp/mysqld.sock
輸入以下SQL語句,建立一個具有root權限的使用者(admin)和密碼(12345678):
mysql> GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' IDENTIFIED BY '12345678';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'admin'@'127.0.0.1' IDENTIFIED BY '12345678';
為root帳戶設定初始密碼:
[root@memcached ~]# /usr/local/mysql/bin/mysqladmin -uroot password 'centos'
删除本機匿名連接配接的空密碼帳号:
[root@memcached ~]# /usr/local/mysql/bin/mysql -uroot -pcentos mysql
mysql> select Host,User,Password from user; //看下有哪些是空密碼的賬戶
mysql> delete from user where password=''; //執行删除
mysql> select Host,User,Password from user; //确認下
OK了,mysql安裝完畢。
------------------------------------------------------------------------------------------
安裝libevent+memcache:
Memcached的安裝:
1:安裝Memcached前需要先安裝libevent:
[root@memcached ~]# wget -b http://www.monkey.org/~provos/libevent-1.4.4-stable.tar.gz
[root@memcached ~]# tail -5 wget-log |sed '/^$/d' ##檢視是否下載下傳正确
[root@memcached ~]# tar zxf libevent-1.4.4-stable.tar.gz
[root@memcached ~]# cd libevent-1.4.4-stable
[root@memcached libevent-1.4.4-stable]# ./configure
[root@memcached libevent-1.4.4-stable]# make && make install
2:安裝Memcached的過程:
[root@memcached ~]# wget -b http://memcached.googlecode.com/files/memcached-1.4.5.tar.gz
[root@memcached ~]# tail -5 wget-log.1 |sed '/^$/d'
[root@memcached ~]# tar zxf memcached-1.4.5.tar.gz
[root@memcached ~]# cd memcached-1.4.5
[root@memcached memcached-1.4.5]# ./configure
[root@memcached memcached-1.4.5]# make && make install
##安裝完成後,Memcached的預設目錄為/usr/local/bin/memcached
3:啟動Memcached:
[root@memcached ~]# /usr/local/bin/memcached -m 100m -p 11211 -d -u root -P /var/run/memcached.pid -c 512
有時會出現找不到libevent檔案,此時将/usr/local/lib 加入到/etc/ld.so.conf中即可。
[root@memcached ~]# echo "/usr/local/lib" >> /etc/ld.so.conf
[root@memcached ~]# ldconfig
4:關閉Memcached:
[root@memcached ~]# kill `cat /var/run/memcached.pid`
————————————————————————————————————
安裝libmencached:
[root@memcached ~]# wget http://download.tangent.org/libmemcached-0.34.tar.gz
[root@memcached ~]# tar zxvf libmemcached-0.34.tar.gz
[root@memcached ~]# cd libmemcached-0.34
[root@memcached libmemcached-0.34]# ./configure --prefix=/usr/local/libmemcached34 --with-memcached=/usr/local/bin/memcached
[root@memcached libmemcached-0.34]# make && make install
[root@memcached libmemcached-0.34]# echo "/usr/local/libmemcached34/" >> /etc/ld.so.conf
[root@memcached libmemcached-0.34]# ldconfig
-------------------------------------------------------------------------------
安裝memcached_functions_mysql:
[root@memcached ~]# wget http://download.tangent.org/memcached_functions_mysql-0.9.tar.gz
[root@memcached ~]# tar zxf memcached_functions_mysql-0.9.tar.gz
[root@memcached ~]# cd memcached_functions_mysql-0.9
[root@memcached memcached_functions_mysql-0.9]# ./configure --prefix=/usr/local/memcache_mysql --with-mysql=/usr/local/mysql/bin/mysql_config --with-libmemcached=/usr/local/libmemcached34
[root@memcached memcached_functions_mysql-0.9]# make && make install
拷貝lib檔案到mysql的plugin下面:
[root@memcached memcached_functions_mysql-0.9]# cp /usr/local/memcache_mysql/lib/libmemcached_functions* /usr/local/mysql/lib/plugin
添加memcache UDF 函數:
[root@memcached memcached_functions_mysql-0.9]# /usr/local/mysql/bin/mysql -uroot -pcentos
mysql> source sql/install_functions.sql;
mysql> select * from mysql.func;
+------------------------------+-----+---------------------------------+----------+
| name | ret | dl | type |
| memc_add | 2 | libmemcached_functions_mysql.so | function |
| memc_add_by_key | 2 | libmemcached_functions_mysql.so | function |
| memc_servers_set | 2 | libmemcached_functions_mysql.so | function |
| memc_server_count | 2 | libmemcached_functions_mysql.so | function |
| memc_set | 2 | libmemcached_functions_mysql.so | function |
| memc_set_by_key | 2 | libmemcached_functions_mysql.so | function |
| memc_cas | 2 | libmemcached_functions_mysql.so | function |
| memc_cas_by_key | 2 | libmemcached_functions_mysql.so | function |
| memc_get | 0 | libmemcached_functions_mysql.so | function |
| memc_get_by_key | 0 | libmemcached_functions_mysql.so | function |
| memc_delete | 2 | libmemcached_functions_mysql.so | function |
| memc_delete_by_key | 2 | libmemcached_functions_mysql.so | function |
| memc_append | 2 | libmemcached_functions_mysql.so | function |
| memc_append_by_key | 2 | libmemcached_functions_mysql.so | function |
| memc_prepend | 2 | libmemcached_functions_mysql.so | function |
| memc_prepend_by_key | 2 | libmemcached_functions_mysql.so | function |
| memc_increment | 2 | libmemcached_functions_mysql.so | function |
| memc_decrement | 2 | libmemcached_functions_mysql.so | function |
| memc_replace | 2 | libmemcached_functions_mysql.so | function |
| memc_replace_by_key | 2 | libmemcached_functions_mysql.so | function |
| memc_servers_behavior_set | 2 | libmemcached_functions_mysql.so | function |
| memc_servers_behavior_get | 0 | libmemcached_functions_mysql.so | function |
| memc_behavior_set | 2 | libmemcached_functions_mysql.so | function |
| memc_behavior_get | 0 | libmemcached_functions_mysql.so | function |
| memc_list_behaviors | 0 | libmemcached_functions_mysql.so | function |
| memc_list_hash_types | 0 | libmemcached_functions_mysql.so | function |
| memc_list_distribution_types | 0 | libmemcached_functions_mysql.so | function |
| memc_udf_version | 0 | libmemcached_functions_mysql.so | function |
| memc_libmemcached_version | 0 | libmemcached_functions_mysql.so | function |
| memc_stats | 0 | libmemcached_functions_mysql.so | function |
| memc_stat_get_keys | 0 | libmemcached_functions_mysql.so | function |
| memc_stat_get_value | 0 | libmemcached_functions_mysql.so | function |
32 rows in set (0.00 sec)
-------------------------------------------------------------------------------------------------
memcached_functions_mysql應用執行個體:
下面通過一個具體的執行個體來示範memcached_functions_mysql的使用方法:
1:建立兩張表:
建立兩張表:urls和results,更新urls表的内容,使系統自動更新Memcached的内容。results用來記錄更新Memcached失敗的記錄。
mysql> use tests;
mysql> drop table if exists urls;
mysql> create table urls(
-> id int(10) NOT NULL,
-> url varchar(255) NOT NULL DEFAULT '',
-> PRIMARY KEY (id)
-> );
mysql> drop table if exists results;
mysql> create table results(
-> result varchar(255) NOT NULL DEFAULT 'error',
-> time timestamp NULL DEFAULT CURRENT_TIMESTAMP,
------------------------------------------------------------------------------------
2:建立3個trigger
當向urls表中插入資料時,對Memcached執行set操作。trigger的代碼如下:
mysql> DELIMITER //
mysql> 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 //
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> DELIMITER ;
當對urls表中的資料進行更新時,對Memcached執行replace操作。trigger代碼如下:
mysql> DROP TRIGGER IF EXISTS url_mem_update;
-> CREATE TRIGGER url_mem_update
-> BEFORE UPDATE ON urls
-> set @mm = memc_replace(OLD.id,NEW.url);
-> insert into results(id) values(OLD.id);
Query OK, 0 rows affected (0.01 sec)
mysql>DELIMITER ;
當對urls表中的資料進行删除操作時,對Memcached執行delete操作。trigger的代碼如下:
mysql> DROP TRIGGER IF EXISTS url_mem_delete;
-> CREATE TRIGGER url_mem_delete
-> BEFORE DELETE ON urls
-> set @mm = memc_delete(OLD.ID);
-> END if;
---------------------------------------------------------------------------------------
3:設定Memcached的相關參數:
設定UDFs操作Memcached伺服器的IP位址和端口。
mysql> select memc_servers_set('192.168.0.166:11900');
+-----------------------------------------+
| memc_servers_set('192.168.0.166:11900') |
| 0 |
mysql> select memc_server_count();
+---------------------+
| memc_server_count() |
| 1 1
在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_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
MEMCACHED_HASH_DEFAULT
MEMCACHED_HASH_MD5
MEMCA
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 |
mysql> select memc_servers_behavior_set('MEMCACHED_BEHAVIOR_TCP_NODELAY','1');
+-----------------------------------------------------------------+
| memc_servers_behavior_set('MEMCACHED_BEHAVIOR_TCP_NODELAY','1') |
| 0 |
---------------------------------------------------------------------------------------------
4:對memcached_functions_mysql的簡單功能進行測試
1):向表urls中插入資料,然後檢視Memcached是否對資料執行set操作。
mysql> insert into urls (id,url) values (1,'http://www.51newcar.com');
mysql> select memc_get('1');
+-------------------------+
| memc_get('1') |
| http://www.51newcar.com |
[root@memcached ~]# telnet 192.168.0.166 11900
Trying 192.168.0.166...
Connected to memcached.51auto.com (192.168.0.166).
Escape character is '^]'.
get 1
VALUE 1 0 23
http://www.51newcar.com
END
----------------------------------------------------------------------------
2)更新表urls裡面的資料,然後查詢Memcached中是否也進行了更新。
mysql> update test.urls set url='http://blog.kuchuli.com' where id =1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select memc_replace('1','http://blog.kuchuli.com');
+---------------------------------------------+
| memc_replace('1','http://blog.kuchuli.com') |
| 0 |
| http://blog.kuchuli.com |
http://blog.kuchuli.com
------------------------------------------------------------------------------
3)删除urls中的資料,然後檢視Memcached是否也将該資料删除了。
mysql> delete from test.urls where id=2;
mysql> select memc_get('2');
+---------------+
| memc_get('2') |
| NULL |
get 2
-----------------------------------------------------------------------------------------
OK Done!!!總之源碼包有很多有bag。
本文轉自 kuchuli 51CTO部落格,原文連結:http://blog.51cto.com/lgdvsehome/820974,如需轉載請自行聯系原作者