MySQL Memcache_engine的安装与使用[原创]

MySQL中文全文索引插件 mysqlcft 1.0.0 安装使用文档[原创]

sourceforge.net被GFW挡住后的访问方法

大 | 中 | 小

[

2008-7-20 13:58 | by 张宴 ]

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

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

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

　　限制：
　　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与存储 | 评论(64) | 引用(0) | 阅读(74130)

lphy

2008-7-20 23:35

每次来看都有新东西～！ grin

apu

2008-7-21 08:02

在Memcache前又增加MySQL,不知道具体的效果怎样，不知作者有没有一些数据供参考。 grin

yorkane

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

2008-9-3 10:28

引用

找到解决方法了，如下所示：
先启动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

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

xueli101068

2010-11-30 11:35

去哪里办文凭？办理文凭请找 www.lovexueli.com

办学历办上网学历办专科学历办理本科及研究生学历北京办统招学历真学历办北京上网学历；可在教育网上查询 www.chsi.com.cn 有意者请登录我们的网址www.lovexueli.com 、具体流程请在网站查看

whatUwant

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

不点不通

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

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