将MySQL数据映射到Memcached中

作者:张立冰 出处: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,安装因个人细好而定，省略许多与测试无关的编译细节及参数。

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

省略列出安装memcached和libevent的相关命令，具体可按照实际情况安装，测试时我将libevent默认安装，memcached安装于/usr/local/memcached目录下。
启动memcached.

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

编译安装libmemcache.

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

编译安装Memcache UDFs for MySQL.

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

编译完成后将编译好的库文件复制到mysql的插件目录下，以便于加载使用。

cp <span>/</span>usr<span>/</span>local<span>/</span>lib<span>/</span>libmemcached_functions_mysql<span>*</span> <span>/</span>usr<span>/</span>local<span>/</span>mysql51<span>/</span>lib<span>/</span>mysql<span>/</span>plugin<span>/</span>

进入memcached_functions_mysql的源码目录，在目录下有相关添加UDF的SQL文件用于初始化。

<span>[</span>root@localhost ~<span>]</span><span># mysql <sql></sql></span>

注：如果对这些UDFs不熟悉或者不懂，可进行源码目录参看README，里边有相应的说明。

至此，相关软件的编译和安装完成，进行测试，我们要达到的目的是当MySQL有新记录插入时，同时插入到Memcached中，当记录更新时同步更 新Memcached中的记录，删除时同时也删除Memcached相关的记录，为此创建三个触发器来实现，如果对MySQL的触发程序不熟悉可以参考 MySQL手册第21章，下面SQL中的memcached为需要操作的表名，SQL如下：

<span>#插入数据时插入Memcached</span><br>create trigger mysqlmmci after insert on memcached <span>for</span> each row set @tmp <span>=</span> memc_set<span>(</span>NEW.<span>key</span><span>,</span> NEW.<span>value</span><span>)</span><span>;</span><br><span>#更新记录时更新Memcached</span><br>create trigger mysqlmmcu after update on memcached <span>for</span> each row set @tmp <span>=</span> memc_set<span>(</span>NEW.<span>key</span><span>,</span> NEW.<span>value</span><span>)</span><span>;</span><br><span>#删除记录时删除Memcached相应的记录</span><br>create trigger mysqlmmcd before delete on memcached <span>for</span> each row set @tmp <span>=</span> memc_delete<span>(</span>OLD.<span>key</span><span>)</span><span>;</span>

以下为测试记录，在对MySQL操作的同时操作Memcached来查看情况，当然你也可以在启动Memcached的时候带-vv参数来查看相关信息.

MySQL操作相关的记录：

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

Memcache查看时的记录：

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

至此，我们基本实现的将MySQL的数据同步到Memcached中，性能暂时还没有测试，当然上面只是简单的实现的数据映射的功能，如果在实现的 生产环境中，则需要考虑名字空间，高可靠性的问题，这些都是可以通过数据库名-表名-关键字的方面能达到KEY唯一的目的，而高可靠性则是一个比较大的问 题。

您可能还喜欢

• 数据的并行压缩
• Random Tips
• 将MySQL数据映射到Memcached
• 提高 Linux 上 socket 性能
• PHP5 像使用数组一样使用Memcache