[文章作者:张宴 本文版本:v1.1 最后修改:2008.09.09 转载请注明原文链接:http://blog.zyan.cc/post/357/]

  鉴于国内外还没有人撰写如何安装Memcache_engine的文章,于是,我根据自己的编译安装步骤,写下此文。

  Memcache_engine是一个MySQL 5.1数据库的存储引擎,它能够让用户通过标准的SQL语句(SELECT/UPDATE/INSERTE/DELETE)访问Memcached(还支持新浪的Memcachedbdbcached)中存放的数据。

  限制:
  1、Memcache表必须有主键。
  2、只能使用主键去查询,即只能使用SELECT ... FROM ... WHERE id = ... 方式去查询。
  3、不支持自增ID。

  安装与使用:
  1、编译安装memcache_engine的步骤:
cd /tmp
wget http://dev.mysql.com/get/Downloads/MySQL-5.1/mysql-5.1.26-rc.tar.gz/from/http://mirror.x10.com/mirror/mysql/
tar zxvf mysql-5.1.26-rc.tar.gz
#安装、配置MySQL的步骤省略,注意不要以静态方式编译安装。

wget http://download.tangent.org/libmemcached-0.23.tar.gz
tar zxvf libmemcached-0.23.tar.gz
cd libmemcached-0.23/
./configure --prefix=/usr/local/memcache_engine
make
make install
cd ../

wget http://xmlsoft.org/sources/libxml2-2.6.32.tar.gz
tar zxvf libxml2-2.6.32.tar.gz
cd libxml2-2.6.32/
./configure --prefix=/usr/local/memcache_engine
make
make install
cd ../

wget http://download.tangent.org/libxmlrow-0.2.tar.gz
tar zxvf libxmlrow-0.2.tar.gz
cd libxmlrow-0.2/
export PKG_CONFIG_PATH=/usr/local/memcache_engine/lib/pkgconfig/
./configure --prefix=/usr/local/memcache_engine
make
make install
cd ../

wget http://download.tangent.org/memcache_engine-0.7.tar.gz
tar zxvf memcache_engine-0.7.tar.gz
cd memcache_engine-0.7/
sed -i "s#uint16_t#uint32_t#g" ./src/ha_memcache.cc
export PKG_CONFIG_PATH=/usr/local/memcache_engine/lib/pkgconfig/
./configure --prefix=/usr/local/memcache_engine --with-mysql=/tmp/mysql-5.1.26-rc
make
make install
cd ../

  注意:红色标记部分为MySQL 5.1.22以上版本的源码路径。

  2、拷贝libmemcache_engine.so到MySQL默认插件目录(假设MySQL安装在/usr/local/mysql目录下):
mkdir -p /usr/local/mysql/lib/mysql/plugin/
cp /usr/local/memcache_engine/lib/libmemcache_engine.so.0.0.0 /usr/local/mysql/lib/mysql/plugin/libmemcache_engine.so


  3、安装libmemcache_engine.so插件的SQL语句:
INSTALL PLUGIN memcache SONAME 'libmemcache_engine.so';


  4、查看libmemcache_engine.so插件是否安装成功的SQL语句:
SELECT * FROM mysql.plugin;
SHOW PLUGINS;


  5、创建一张memcache_engine表的SQL语句:
CREATE TABLE `table` (
`id` int(11) NOT NULL DEFAULT '0',
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MEMCACHE DEFAULT CHARSET=latin1
CONNECTION='localhost:11211';





技术大类 » Cache与存储 | 评论(63) | 引用(0) | 阅读(72186)
lphy Homepage
2008-7-20 23:35
每次来看都有新东西~!grin
apu
2008-7-21 08:02
在Memcache前又增加MySQL,不知道具体的效果怎样,不知作者有没有一些数据供参考。grin
yorkane Email
2008-7-21 11:59
非常的好的文章
有没有测试过,这样的组合效率如何?
有没有谁用memcached_engine做过应用啊?
king'space
2008-7-22 14:43
看了很久你的博客

很多都是关于性能优化。

张兄能否把自己的博客 优化的速度快些?或是我网通访问慢。
张宴 回复于 2008-7-23 08:38
我的博客是租用的虚拟主机,不是自己的服务器。不过最近会换空间。
kevin
2008-9-2 19:48
引用:
创建一张memcache_engine表的SQL语句:
CREATE TABLE `table` (
`id` int(11) NOT NULL DEFAULT '0',
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MEMCACHE DEFAULT CHARSET=latin1
CONNECTION='localhost:11211';

张老师,请问,这在mysql里使用哪个库呀?
我在按你所做的时候,出现如下错误:
mysql> CREATE TABLE `table` (
   -> `id` int(11) NOT NULL DEFAULT '0',
   -> `a` int(11) DEFAULT NULL,
   -> `b` int(11) DEFAULT NULL,
   -> PRIMARY KEY (`id`)
   -> ) ENGINE=MEMCACHE DEFAULT CHARSET=latin1
   -> CONNECTION='localhost:11211';
ERROR 1046 (3D000): No database selected

刚编译安装完的mysql数据库的表有:
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

请问,执行你的那段代码时,是使用哪个库或是新建一个库?
张宴 回复于 2008-9-2 21:20
自行新建一个库
kevin
2008-9-3 09:33
呵,谢了,写得不错!
kevin
2008-9-3 09:55
引用

mysql> create database memcache;
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> use memcache;
Database changed
mysql>
mysql> CREATE TABLE `table` (
   -> `id` int(11) NOT NULL DEFAULT '0',
   -> `a` int(11) DEFAULT NULL,
   -> `b` int(11) DEFAULT NULL,
   -> PRIMARY KEY (`id`)
   -> ) ENGINE=MEMCACHE DEFAULT CHARSET=latin1
   -> CONNECTION='localhost:11211';
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> show tables;
+--------------------+
| Tables_in_memcache |
+--------------------+
| table              |
+--------------------+
1 row in set (0.00 sec)
mysql> select * from table;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table' at line 1
mysql>
mysql> select * from table where id=1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table where id=1' at line 1


请问张老师,我这样的查询,是哪里出了问题了吗?
kevin Email
2008-9-3 10:28
引用

mysql> create database memcache;
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> use memcache;
Database changed
mysql>
mysql> CREATE TABLE `table` (
  -> `id` int(11) NOT NULL DEFAULT '0',
  -> `a` int(11) DEFAULT NULL,
  -> `b` int(11) DEFAULT NULL,
  -> PRIMARY KEY (`id`)
  -> ) ENGINE=MEMCACHE DEFAULT CHARSET=latin1
  -> CONNECTION='localhost:11211';
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> show tables;
+--------------------+
| Tables_in_memcache |
+--------------------+
| table              |
+--------------------+
1 row in set (0.00 sec)
mysql> select * from table;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table' at line 1
mysql>
mysql> select * from table where id=1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table where id=1' at line 1
请问张老师,我这样的查询,是哪里出了问题了吗?


引用

找到解决方法了,如下所示:
先启动memcache
#memcached -d -p 11211 -u nobody -m 256 -c 1024 -P /tmp/memcached.pid
#mysql
mysql> use memcache;
Database changed
mysql>
mysql> create table `memdata` (
   ->   `id` int(11) not null default '0',
   ->   `name` char(50) default null,
   ->   `info` varchar(255) default null,
   ->   primary key (`id`)
   -> ) engine=memcache default charset=latin1
   -> connection='localhost:11211';
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> insert into memdata (id, name, info) values (1, 'kevin', 'system');
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> select * from memdata;
Empty set (0.00 sec)
mysql>
mysql> select * from memdata where id=1;
+----+-------+-----------+
| id | name  | info      |
+----+-------+-----------+
|  1 | kevin | system |
+----+-------+-----------+
1 row in set (0.00 sec)
mysql>quit;

从上面的实验可以看出:
只能使用主键去查询,即只能使用SELECT ... FROM ... WHERE id = ... 方式去查询。


呵,班门弄斧了一下,请张老师指点一下!
冰山
2008-9-28 11:01
table 为mysql的关键字. 如果要用table做具体的表名, 在使用表时须用`table`.
www
2008-10-23 16:39
张老师,给你提个建议,你是实力派,你长得那么...,就不要把近照放在博客上嘛,换个远一点照片的比较好。每次看你的博客都被这张照片搞得很很压抑。
lll0905
2008-12-6 14:34
在freebsd下安装memcache-engine,按照你的做法,该装的都装了

[root@dbmemcache /usr/src/memcache_engine-0.7]==>./configure --prefix=/usr/local/memcache_engine \
> --with-mysql=/usr/src/mysql-5.1.30

但是make时报错:

In file included from mysql_priv.h:652,
                from ha_memcache.cc:30:
/usr/src/mysql-5.1.30/sql/table.h:502: error: use of enum 'enum_table_ref_type' without previous declaration
/usr/src/mysql-5.1.30/sql/table.h: In member function 'int st_table_share::get_table_ref_type() const':
/usr/src/mysql-5.1.30/sql/table.h:505: error: 'TABLE_REF_VIEW' was not declared in this scope
/usr/src/mysql-5.1.30/sql/table.h:508: error: 'TABLE_REF_BASE_TABLE' was not declared in this scope
/usr/src/mysql-5.1.30/sql/table.h:510: error: 'TABLE_REF_I_S_TABLE' was not declared in this scope
/usr/src/mysql-5.1.30/sql/table.h:512: error: 'TABLE_REF_TMP_TABLE' was not declared in this scope
/usr/src/mysql-5.1.30/sql/table.h: At global scope:
/usr/src/mysql-5.1.30/sql/table.h:1370: error: 'enum_query_type' has not been declared
/usr/src/mysql-5.1.30/sql/table.h:1488: error: use of enum 'enum_table_ref_type' without previous declaration
In file included from mysql_priv.h:670,
                from ha_memcache.cc:30:
/usr/src/mysql-5.1.30/sql/item.h:783: error: 'enum_query_type' has not been declared
还有很多..............................省略.

研究了一会.无奈.
求助!
黑糊糊
2009-3-15 21:06
原来libmemcached是要和5.1匹配啊,怪不得我装了以后,5.0总说找不到so呢。
wolf Email
2010-5-20 10:38
HI...请教你一个问题。。。。。
我在配置mysql memcache_engine时。出现mysql crashing
http://bugs.mysql.com/bug.php?id=43892  这个里面推荐使用libmemcache-0.26。但我换了还一样。。


key_buffer_size=8388600
read_buffer_size=131072
max_used_connections=1
max_threads=151
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 338299 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0xebbb060
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0x4157e0e0 thread_stack 0x40000
/app/java/mysql/libexec/mysqld(my_print_stacktrace+0x24) [0x756ce4]
/app/java/mysql/libexec/mysqld(handle_segfault+0x322) [0x587772]
/lib64/libpthread.so.0 [0x366000e7c0]
/app/java/mysql/libexec/mysqld [0x768a73]
/app/java/mysql/libexec/mysqld(my_hash_insert+0x229) [0x74be99]
/app/java/mysql/lib/mysql/plugin/libmemcache_engine.so(ha_memcache::open(char const*, int, unsigned int)+0x189) [0x2aaaab3b4959]
/app/java/mysql/libexec/mysqld(handler::ha_open(st_table*, char const*, int, int)+0x3f) [0x671d6f]
/app/java/mysql/libexec/mysqld(open_table_from_share(THD*, st_table_share*, char const*, unsigned int, unsigned int, unsigned int, st_table*, bool)+0x4dc) [0x5de2bc]
/app/java/mysql/libexec/mysqld [0x5d7ad0]
/app/java/mysql/libexec/mysqld(open_table(THD*, TABLE_LIST*, st_mem_root*, bool*, unsigned int)+0x79a) [0x5da37a]
/app/java/mysql/libexec/mysqld(open_tables(THD*, TABLE_LIST**, unsigned int*, unsigned int)+0x64a) [0x5daf6a]
/app/java/mysql/libexec/mysqld(open_normal_and_derived_tables(THD*, TABLE_LIST*, unsigned int)+0x1e) [0x5db22e]
/app/java/mysql/libexec/mysqld(mysqld_list_fields(THD*, TABLE_LIST*, char const*)+0x22) [0x68de82]
/app/java/mysql/libexec/mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int)+0x834) [0x59dd54]
/app/java/mysql/libexec/mysqld(do_command(THD*)+0xe4) [0x59ea84]
/app/java/mysql/libexec/mysqld(handle_one_connection+0x5d7) [0x58f727]
/lib64/libpthread.so.0 [0x36600064a7]
/lib64/libc.so.6(clone+0x6d) [0x365f8d3c2d]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0xec11758 =
thd->thread_id=1
thd->killed=NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
servers localhost:11211
        localhost : 11211


请问你有遇到过类似问题吗??
xiaogt
2010-5-20 17:11
小弟现在想把memory引擎的数据表转换成memcache引擎的表  可是没想到的装好了memcache引擎
转的时候报这样错请大虾们帮忙分析一下  
也可以把数据导出在导进去。不过试了也不行。有没有什么好的办法和思路?


mysql> alter table t engine=MEMCACHE;
ERROR 1005 (HY000): Can't create table 'zhu.#sql-baa_3' (errno: 1429)


引擎是安装了
mysql> SHOW PLUGINS;
+------------+----------+----------------+-----------------------+---------+
| Name       | Status   | Type           | Library               | License |
+------------+----------+----------------+-----------------------+---------+
| binlog     | ACTIVE   | STORAGE ENGINE | NULL                  | GPL     |
| partition  | ACTIVE   | STORAGE ENGINE | NULL                  | GPL     |
| ARCHIVE    | ACTIVE   | STORAGE ENGINE | NULL                  | GPL     |
| BLACKHOLE  | ACTIVE   | STORAGE ENGINE | NULL                  | GPL     |
| CSV        | ACTIVE   | STORAGE ENGINE | NULL                  | GPL     |
| FEDERATED  | DISABLED | STORAGE ENGINE | NULL                  | GPL     |
| MEMORY     | ACTIVE   | STORAGE ENGINE | NULL                  | GPL     |
| InnoDB     | ACTIVE   | STORAGE ENGINE | NULL                  | GPL     |
| MyISAM     | ACTIVE   | STORAGE ENGINE | NULL                  | GPL     |
| MRG_MYISAM | ACTIVE   | STORAGE ENGINE | NULL                  | GPL     |
| ndbcluster | DISABLED | STORAGE ENGINE | NULL                  | GPL     |
| MEMCACHE   | ACTIVE   | STORAGE ENGINE | libmemcache_engine.so | GPL     |
+------------+----------+----------------+-----------------------+---------+
12 rows in set (0.00 sec)
xueli101068 Email Homepage
2010-11-30 11:35
去哪里办文凭?办理文凭 请找  www.lovexueli.com


办学历 办上网学历   办专科学历  办理本科及研究生学历   北京办统招学历 真学历 办北京上网学历; 可在教育网上查询 www.chsi.com.cn         有意者请登录我们的网址www.lovexueli.com 、具体流程请在网站查看
whatUwant Homepage
2010-12-12 23:12
今天尝试安装了memcache_engine,感觉还是比较麻烦的。
各个软件包的选择都要小心。
一开始libmemcached、libxml我选了最新的版本,导致memcache_engine在make的时候出错。
后来选用了老一点的版本,终于正常了。
windy
2011-3-7 14:40
mysql>  insert into memdata (id, name, info) values (1, 'kevin', 'system');ERROR 1030 (HY000): Got error -1 from storage engine帮忙看下这个出错。
不点不通
2011-4-14 16:49
你好。张老师 我按照你的方法安装了一切正常但是到最后 执行 insert into memdata (id, name, info) values (1, 'kevin', 'system');这句的时候报错了,具体如下:ERROR 2013 (HY000): Lost connection to MySQL server during query  
我执行SHOW PLUGINS;
+------------+----------+----------------+-----------------------+---------+
| Name       | Status   | Type           | Library               | License |
+------------+----------+----------------+-----------------------+---------+
| binlog     | ACTIVE   | STORAGE ENGINE | NULL                  | GPL     |
| partition  | ACTIVE   | STORAGE ENGINE | NULL                  | GPL     |
| ARCHIVE    | ACTIVE   | STORAGE ENGINE | NULL                  | GPL     |
| BLACKHOLE  | ACTIVE   | STORAGE ENGINE | NULL                  | GPL     |
| CSV        | ACTIVE   | STORAGE ENGINE | NULL                  | GPL     |
| FEDERATED  | DISABLED | STORAGE ENGINE | NULL                  | GPL     |
| MEMORY     | ACTIVE   | STORAGE ENGINE | NULL                  | GPL     |
| InnoDB     | ACTIVE   | STORAGE ENGINE | NULL                  | GPL     |
| MyISAM     | ACTIVE   | STORAGE ENGINE | NULL                  | GPL     |
| MRG_MYISAM | ACTIVE   | STORAGE ENGINE | NULL                  | GPL     |
| ndbcluster | DISABLED | STORAGE ENGINE | NULL                  | GPL     |
| MEMCACHE   | ACTIVE   | STORAGE ENGINE | libmemcache_engine.so | GPL     |
+------------+----------+----------------+-----------------------+---------+
12 rows in set (0.00 sec)  都能看到的  为什么呢?请指导? 谢谢
不点不通
2011-4-15 13:40
报错信息如下:
110415 11:52:13 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql//var
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
110415 11:52:13  InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
110415 11:52:14  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
110415 11:52:14  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
110415 11:52:14  InnoDB: Started; log sequence number 0 0
110415 11:52:15 [Note] Event Scheduler: Loaded 0 events
110415 11:52:15 [Note] /usr/local/mysql/libexec/mysqld: ready for connections.
Version: '5.1.26-rc-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
110415 11:52:16 [Note] /usr/local/mysql/libexec/mysqld: Normal shutdown

110415 11:52:16 [Note] Event Scheduler: Purging the queue. 0 events
110415 11:52:17  InnoDB: Starting shutdown...
110415 11:52:18  InnoDB: Shutdown completed; log sequence number 0 46409
110415 11:52:18 [Note] /usr/local/mysql/libexec/mysqld: Shutdown complete

110415 11:52:18 mysqld_safe mysqld from pid file /usr/local/mysql//var/gate.pid ended
louis vuitton uk Email Homepage
2011-11-23 09:02
This louis vuitton uk for sale belongs to the sounding just what are termed as Louis Vuitton vintage best sellers, many other products and services for the reason that range appearing companies.You will easily notice the unfold zippers of this coach outlet store online. That is the decoration. There are some inside pockets for you as well. They are easy to match your clothes and to carry.Let us inspire your inner beauty with fine christian louboutin sale. Purse the elegance in bridal wedding. Enjoy the fashion.
分页: 1/4 第一页 1 2 3 4 下页 最后页
发表评论
表情
emotemotemotemotemot
emotemotemotemotemot
emotemotemotemotemot
emotemotemotemotemot
emotemotemotemotemot
打开HTML
打开UBB
打开表情
隐藏
记住我
昵称   密码   游客无需密码
网址   电邮   [注册]