Friday, April 2, 2010

[Level 3] Install Memcached Storage Engine for MySQL on CentOS 5.4

Prepare the package first for build the memcached storage engine.

Download libmemacached 0.34 and install:
# wget http://download.tangent.org/libmemcached-0.34.tar.gz
# tar zxvf ./libmemcached-0.34.tar.gz
# cd ./libmemcached-0.34
# export PKG_CONFIG_PATH=/usr/local/lib/pkgconfig
# ./configure && make && make install

Download libxml2 and install:
# wget ftp://xmlsoft.org/libxml2/libxml2-2.7.7.tar.gz
# tar zxvf libxml2-2.7.7.tar.gz
# cd ./libxml2-2.7.7
# ./configure && make && make install

Download  libxmlrow and install:
# 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/lib/pkgconfig
# ./configure && make && make install 


Download  memcached storage and install:
# export PKG_CONFIG_PATH=/usr/local/lib/pkgconfig
# sed -i "s#uint16_t#uint32_t#g" ./src/ha_memcache.cc
# ./configure --with-mysql=/usr/src/redhat/SOURCES/mysql-5.1.44 --libdir=/usr/local/mysq/lib/mysql
# make && make install
# cp /usr/local/mysq/lib/mysql/libmemcache_engine.so.0.0.0 /usr/local/mysql/lib/mysql/plugin/libmemcache_engine.so


Install plugin storage:
mysql> INSTALL PLUGIN memcache SONAME 'libmemcache_engine.so';
mysql> show plugins;
+------------+--------+----------------+-----------------------+---------+
| Name       | Status | Type           | Library               | License |
+------------+--------+----------------+-----------------------+---------+
| binlog     | ACTIVE | STORAGE ENGINE | NULL                  | GPL     |
| partition  | ACTIVE | STORAGE ENGINE | NULL                  | GPL     |
| CSV        | ACTIVE | 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     |
| MEMCACHE   | ACTIVE | STORAGE ENGINE | libmemcache_engine.so | GPL     |
+------------+--------+----------------+-----------------------+---------+
8 rows in set (0.00 sec)

Test it:
mysql> CREATE TABLE `test`.`testMemSE` (
`id` int(11) NOT NULL DEFAULT '0',
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL
) ENGINE=MEMCACHE DEFAULT CHARSET=latin1
CONNECTION='192.168.56.101:11211,192.168.56.102:11211';

ERROR 1173 (42000): This table type requires a primary key
mysql> CREATE TABLE `test`.`testMemSE` (
`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='192.168.56.101:11211,192.168.56.102:11211';

mysql> insert into test.testMemSE values (1,2,3);
ERROR 1598 (HY000): Binary logging not possible. Message: Statement-based format required for this statement, but not allowed by this combination of engines

## after stop bin-log
mysql> insert into test.testMemSE values (1,1,1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test.testMemSE where id=1;

...
Wish this helps.

regards,
Stanley Huang