將MySQL數(shù)據(jù)映射到Memcached中

來源：懂視網(wǎng) 責(zé)編：小采時間：2020-11-09 15:40:08

將MySQL數(shù)據(jù)映射到Memcached中

將MySQL數(shù)據(jù)映射到Memcached中:作者:張立冰出處:http://www.libing.name/2009/02/06/mysql-map-data-to-memcachedmysql-map-data-to-memcached.html 差不多在一年前，寫過一篇文章介紹將MySQL數(shù)據(jù)映射到Memcached，當(dāng)時MySQL和Memcached Functions

推薦度：

點擊下載本文 文檔為doc格式

導(dǎo)讀將MySQL數(shù)據(jù)映射到Memcached中:作者:張立冰出處:http://www.libing.name/2009/02/06/mysql-map-data-to-memcachedmysql-map-data-to-memcached.html 差不多在一年前，寫過一篇文章介紹將MySQL數(shù)據(jù)映射到Memcached，當(dāng)時MySQL和Memcached Functions

作者:張立冰出處:http://www.libing.name/2009/02/06/mysql-map-data-to-memcachedmysql-map-data-to-memcached.html 差不多在一年前，寫過一篇文章介紹將MySQL數(shù)據(jù)映射到Memcached，當(dāng)時MySQL和Memcached Functions for MySQL都還不夠成熟，時過一年，Memc

作者:張立冰
出處:http://www.libing.name/2009/02/06/mysql-map-data-to-memcachedmysql-map-data-to-memcached.html

差不多在一年前，寫過一篇文章介紹將MySQL數(shù)據(jù)映射到Memcached，當(dāng)時MySQL和Memcached Functions for MySQL都還不夠成熟，時過一年，Memcached Functions for MySQL

測試環(huán)境在Linux下進行，版本系統(tǒng)為CentOS5.
以下為相關(guān)軟件，包括其版本和下載地址：

mysql-5.1.30 下載
memcached-1.2.6 下載
libevent-1.4.7-stable 下載
memcached_functions_mysql-0.8 下載
libmemcached-0.26 下載

編譯安裝MySQL,安裝因個人細(xì)好而定，省略許多與測試無關(guān)的編譯細(xì)節(jié)及參數(shù)。

[root@localhost ~]#tar xzf mysql-5.1.30.tar_001.gz
[root@localhost ~]#cd mysql-5.1.30
[root@localhost ~]#./configure --prefix=/usr/local/mysql51
[root@localhost ~]#make
[root@localhost ~]#make install
[root@localhost ~]#./scripts/mysql_install_db --user=mysql --skip-name-resolve
[root@localhost ~]#/usr/local/mysql51/bin/mysqld_safe

省略列出安裝memcached和libevent的相關(guān)命令，具體可按照實際情況安裝，測試時我將libevent默認(rèn)安裝，memcached安裝于/usr/local/memcached目錄下。
啟動memcached.

/usr/local/memcached/bin/memcached -d -m 50 -u root -p 11211

編譯安裝libmemcache.

[root@localhost ~]#tar xzf libmemcached-0.26.tar.gz
[root@localhost ~]#cd libmemcached-0.26
[root@localhost ~]#./configure --with-memcached=/usr/local/memcached/bin/memcached
[root@localhost ~]# make && make install

編譯安裝Memcache UDFs for MySQL.

[root@localhost ~]# tar xzf memcached_functions_mysql-0.8.tar.gz
[root@localhost ~]# cd memcached_functions_mysql-0.8
[root@localhost ~]# ./configure --with-mysql-config=/usr/local/mysql51/bin/mysql_config
[root@localhost ~]# make && make install

編譯完成后將編譯好的庫文件復(fù)制到mysql的插件目錄下，以便于加載使用。

cp /usr/local/lib/libmemcached_functions_mysql* /usr/local/mysql51/lib/mysql/plugin/

進入memcached_functions_mysql的源碼目錄，在目錄下有相關(guān)添加UDF的SQL文件用于初始化。

[root@localhost ~]# mysql

注：如果對這些UDFs不熟悉或者不懂，可進行源碼目錄參看README，里邊有相應(yīng)的說明。

至此，相關(guān)軟件的編譯和安裝完成，進行測試，我們要達到的目的是當(dāng)MySQL有新記錄插入時，同時插入到Memcached中，當(dāng)記錄更新時同步更新Memcached中的記錄，刪除時同時也刪除Memcached相關(guān)的記錄，為此創(chuàng)建三個觸發(fā)器來實現(xiàn)，如果對MySQL的觸發(fā)程序不熟悉可以參考 MySQL手冊第21章，下面SQL中的memcached為需要操作的表名，SQL如下：

#插入數(shù)據(jù)時插入Memcached
create trigger mysqlmmci after insert on memcached for each row set @tmp = memc_set(NEW.key, NEW.value);
#更新記錄時更新Memcached
create trigger mysqlmmcu after update on memcached for each row set @tmp = memc_set(NEW.key, NEW.value);
#刪除記錄時刪除Memcached相應(yīng)的記錄
create trigger mysqlmmcd before delete on memcached for each row set @tmp = memc_delete(OLD.key);

以下為測試記錄，在對MySQL操作的同時操作Memcached來查看情況，當(dāng)然你也可以在啟動Memcached的時候帶-vv參數(shù)來查看相關(guān)信息.

MySQL操作相關(guān)的記錄：

[root@localhost ~]#mysql -S /tmp/mysql51.sock 
Welcome to the MySQL monitor. Commands end with ; or /g.
Your MySQL connection id is 6
Server version: 5.1.30 Source distribution
 
Type 'help;' or '/h' for help. Type '/c' to clear the buffer.
 
mysql> use test;
Database changed
 
mysql> create table `memcached` (`key` varchar(10), `value` varchar(100)); 
Query OK, 0 rows affected (0.00 sec)
 
mysql> create trigger mysqlmmci after insert on memcached for each row set @tmp = memc_set(NEW.key, NEW.value);
Query OK, 0 rows affected (0.00 sec)
 
mysql> create trigger mysqlmmcu after update on memcached for each row set @tmp = memc_set(NEW.key, NEW.value);
Query OK, 0 rows affected (0.00 sec)
 
mysql> create trigger mysqlmmcd before delete on memcached for each row set @tmp = memc_delete(OLD.key);
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into memcached values("keyi", "valuei"),("keyu","valueu"),("keyd", "valued"); 
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
 
mysql> update memcached set `value`="update" where `key`="keyu";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
 
mysql> delete from memcached where `key`="keyd";
Query OK, 1 row affected (0.00 sec)
 
mysql> quit
Bye

Memcache查看時的記錄：

[root@localhost ~]#telnet 127.0.0.1 11211
Trying 127.0.0.1...
Connected to 127.0.0.1.
Escape character is '^]'.
get keyi
VALUE keyi 0 6
valuei
END
get keyu
VALUE keyu 0 6
valueu
END
get keyd
VALUE keyd 0 6
valued
END
get keyu
VALUE keyu 0 6
update
END
get keyd
END
quit
Connection closed by foreign host.

至此，我們基本實現(xiàn)的將MySQL的數(shù)據(jù)同步到Memcached中，性能暫時還沒有測試，當(dāng)然上面只是簡單的實現(xiàn)的數(shù)據(jù)映射的功能，如果在實現(xiàn)的生產(chǎn)環(huán)境中，則需要考慮名字空間，高可靠性的問題，這些都是可以通過數(shù)據(jù)庫名-表名-關(guān)鍵字的方面能達到KEY唯一的目的，而高可靠性則是一個比較大的問題。