将MySQL数据映射到Memcached中

川长思鸟来 2022-08-12 16:05 169阅读 0赞

原贴:http://www.libing.name/2009/02/06/mysql-map-data-to-memcached.html

## [将MySQL数据映射到Memcached中][MySQL_Memcached] ##

### 2月 6th, 2009 ###

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

差不多在一年前，写过一篇文章介绍[将MySQL数据映射到Memcached][MySQL_Memcached 1]，当时MySQL和Memcached Functions for MySQL都还不够成熟，时过一年，[Memcached Functions for MySQL][]升级到了0.8版本，而MySQL也发布了GA版本，加上很多朋友反应前一篇文章中的实现他们因种种原因没能成功，于是便有了这篇文章，就当是上一篇文章的升级版本吧。

测试环境在Linux下进行，版本系统为CentOS5.  
以下为相关软件，包括其版本和下载地址：

mysql-5.1.30 [下载][Link 1]  
memcached-1.2.6 [下载][Link 2]  
libevent-1.4.7-stable [下载][Link 3]  
memcached\_functions\_mysql-0.8 [下载][Link 4]  
libmemcached-0.26 [下载][Link 5]

编译安装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 <sql/install_functions.sql

注：如果对这些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唯一的目的，而高可靠性则是一个比较大的问 题。

### 您可能还喜欢 ###

*  [数据的并行压缩][Link 6]
 *  [Random Tips][]
 *  [将MySQL数据映射到Memcached][MySQL_Memcached 1]
 *  [提高 Linux 上 socket 性能][Linux _ socket]
 *  [PHP5 像使用数组一样使用Memcache ][PHP5 _Memcache]

Posted by libing Filed in [Linux][], [MySQL][], [c/c++][c_c]

### One Response to “将MySQL数据映射到Memcached中” ###

1.  [yurtdisi egitim][] Says:  
    [2月 23rd, 2009 at 12:26 上午][2_ 23rd_ 2009 at 12_26] 
    
    thanks but it would be better with English support..

[MySQL_Memcached]: http://www.libing.name/2009/02/06/mysql-map-data-to-memcached.html
[MySQL_Memcached 1]: http://www.libing.name/2008/02/26/mysql-map-memcached.html
[Memcached Functions for MySQL]: http://tangent.org/586/Memcached_Functions_for_MySQL.html
[Link 1]: http://dev.mysql.com/get/Downloads/MySQL-5.1/mysql-5.1.30.tar.gz/from/http://mysql.byungsoo.net/
[Link 2]: http://danga.com/memcached/dist/memcached-1.2.6.tar.gz
[Link 3]: http://monkey.org/%7Eprovos/libevent-1.4.7-stable.tar.gz
[Link 4]: http://download.tangent.org/memcached_functions_mysql-0.8.tar.gz
[Link 5]: http://download.tangent.org/libmemcached-0.26.tar.gz
[README]: http://hg.tangent.org/memcached_functions_mysql/file/724eeccc55bc/README
[Link 6]: http://www.libing.name/2008/06/14/multi-threaded-compression.html
[Random Tips]: http://www.libing.name/2008/04/06/random-tips.html
[Linux _ socket]: http://www.libing.name/2008/07/15/socket-linux-perofrmace.html
[PHP5 _Memcache]: http://www.libing.name/2008/03/13/php5-memcache-array.html
[Linux]: http://www.libing.name/category/linux
[MySQL]: http://www.libing.name/category/mysql
[c_c]: http://www.libing.name/category/cc
[yurtdisi egitim]: http://www.desk-egitim.com/
[2_ 23rd_ 2009 at 12_26]: http://www.libing.name/2009/02/06/mysql-map-data-to-memcached.html#comment-136