memcached 穿透mysql_将MySQL数据映射到Memcached中

吕俊美
2023-12-01

作者:张立冰

出处: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升级到了0.8版本,而MySQL也发布了GA版本,加上很多朋友反应前一篇文章中的实现他们因种种原因没能成功,于是便有了这篇文章,就当是上一篇文章的升级版本吧。

测试环境在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唯一的目的,而高可靠性则是一个比较大的问题。

您可能还喜欢

 类似资料: