作者:张立冰 出处:http://www.libing.name/2009/02/06/mysql-map-data-to-memcachedmysql-map-data-to-memcached.html 差不多在一年前，写过一篇文章介绍将mysql数据映射到memcached，当时mysql和memcached functions for mysql都还不够成熟，时过一年，memc
作者:张立冰
出处:http://www.libing.name/2009/02/06/mysql-map-data-to-memcachedmysql-map-data-to-memcached.html
差不多在一年前，写过一篇文章介绍将mysql数据映射到memcached，当时mysql和memcached functions for mysql都还不够成熟，时过一年，memcached functions for mysql
测试环境在linux下进行，版本系统为centos5.
以下为相关软件，包括其版本和下载地址：
mysql-5.1.30 下载
memcached-1.2.6 下载
libevent-1.4.7-stable 下载
memcached_functions_mysql-0.8 下载
libmemcached-0.26 下载
编译安装mysql,安装因个人细好而定，省略许多与测试无关的编译细节及参数。
[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的相关命令，具体可按照实际情况安装，测试时我将libevent默认安装，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
编译完成后将编译好的库文件复制到mysql的插件目录下，以便于加载使用。
cp /usr/local/lib/libmemcached_functions_mysql* /usr/local/mysql51/lib/mysql/plugin/
进入memcached_functions_mysql的源码目录，在目录下有相关添加udf的sql文件用于初始化。
[root@localhost ~]# mysql
注：如果对这些udfs不熟悉或者不懂，可进行源码目录参看readme，里边有相应的说明。
至此，相关软件的编译和安装完成，进行测试，我们要达到的目的是当mysql有新记录插入时，同时插入到memcached中，当记录更新时同步更新memcached中的记录，删除时同时也删除memcached相关的记录，为此创建三个触发器来实现，如果对mysql的触发程序不熟悉可以参考mysql手册第21章，下面sql中的memcached为需要操作的表名，sql如下：
#插入数据时插入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相应的记录
create trigger mysqlmmcd before delete on memcached for each row set @tmp = memc_delete(old.key);
以下为测试记录，在对mysql操作的同时操作memcached来查看情况，当然你也可以在启动memcached的时候带-vv参数来查看相关信息.
mysql操作相关的记录：
[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.
至此，我们基本实现的将mysql的数据同步到memcached中，性能暂时还没有测试，当然上面只是简单的实现的数据映射的功能，如果在实现的生产环境中，则需要考虑名字空间，高可靠性的问题，这些都是可以通过数据库名-表名-关键字的方面能达到key唯一的目的，而高可靠性则是一个比较大的问题。
您可能还喜欢数据的并行压缩random tips将mysql数据映射到memcached提高 linux 上 socket 性能php5 像使用数组一样使用memcache