Spider Storage Engine安装手顺

MySQL数据库的记录数达到两千万后，性能会急剧下降。所以在未到达之前，就要考虑新的办法。Spider Storage Engine可以对MySQL进行分区，因此进行了相关的调查。

1.下载MySQL源代码、Spider源代码、condition pushdown安装包（用于将Spider服务器的查询条件传递到远端服务器）
MySQL源代码下载地址：http://dev.mysql.com/downloads/mysql/#downloads
Spider源代码下载地址：https://launchpad.net/spiderformysql/+download
condition pushdown安装包下载地址：https://edge.launchpad.net/partitionconditionpushdownformysql/+download
都放置于/home/peng目录下。

2.安装Spider
解压MySQL源代码：

cd /home/peng
mkdir spider
mkdir /usr/src/redhat      # MySQL源代码会被解压到这里
cd spider
rpm -ivh --nodeps --force /home/peng/MySQL-community-5.1.46-1.rhel5.src.rpm

执行成功后，会在/usr/src/redhat/SOURCES下出现MySQL源文件的压缩包：mysql-5.1.46.tar.gz

解压上述压缩包：

tar -xzf /usr/src/redhat/SOURCES/mysql-5.1.46.tar.gz

同样在/home/peng/spider目录下解压Spider源代码以及文档：

tar -xzf /home/peng/spider-src-2.19-for-5.1.44.tgz.tar
tar -xzf /home/peng/spider-doc-2.19-for-5.1.44.tgz.tar

同样在/home/peng/spider目录下解压condition pushdown安装包(版本推荐5.1.36)：

tar -xzf /home/peng/partition_cond_push-0.1-for-5.1.36.tgz

spider storage engine源码向MySQL中移行：

mv spider mysql-5.1.46/storage/

spider storage engine和MySQL集成编译：

cd mysql-5.1.46
patch -p2 < ../mysql-5.1.44.spider.diff
patch -p2 < ../mysql-5.1.36.partition_cond_push.diff
autoconf  # autoconf安装手顺请参照下面
automake  # automake安装手顺请参照下面

./configure --enable-thread-safe-client \
--enable-local-infile \
--with-pic --with-fast-mutexes \
--with-client-ldflags=-static \
--with-mysqld-ldflags=-static --with-zlib-dir=bundled \
--with-big-tables --with-ssl --with-readline \
--with-embedded-server --with-partition \
--with-innodb --without-ndbcluster \
--without-archive-storage-engine \
--without-blackhole-storage-engine \
--with-csv-storage-engine \
--without-example-storage-engine \
--without-federated-storage-engine \
--with-extra-charsets=complex && make

编译成功时的信息

引用

g++ -DMYSQL_INSTANCE_MANAGER -DMYSQL_SERVER -I. -I../../include -I../../zlib -I../../include -I../../include    -O3    -fno-implicit-templates -fno-exceptions -fno-rtti -MT mysqlmanager-guardian.o -MD -MP -MF .deps/mysqlmanager-guardian.Tpo -c -o mysqlmanager-guardian.o `test -f 'guardian.cc' || echo './'`guardian.cc
mv -f .deps/mysqlmanager-guardian.Tpo .deps/mysqlmanager-guardian.Po
g++ -DMYSQL_INSTANCE_MANAGER -DMYSQL_SERVER -I. -I../../include -I../../zlib -I../../include -I../../include    -O3    -fno-implicit-templates -fno-exceptions -fno-rtti -MT mysqlmanager-parse_output.o -MD -MP -MF .deps/mysqlmanager-parse_output.Tpo -c -o mysqlmanager-parse_output.o `test -f 'parse_output.cc' || echo './'`parse_output.cc
mv -f .deps/mysqlmanager-parse_output.Tpo .deps/mysqlmanager-parse_output.Po
g++ -DMYSQL_INSTANCE_MANAGER -DMYSQL_SERVER -I. -I../../include -I../../zlib -I../../include -I../../include    -O3    -fno-implicit-templates -fno-exceptions -fno-rtti -MT mysqlmanager-user_management_commands.o -MD -MP -MF .deps/mysqlmanager-user_management_commands.Tpo -c -o mysqlmanager-user_management_commands.o `test -f 'user_management_commands.cc' || echo './'`user_management_commands.cc
mv -f .deps/mysqlmanager-user_management_commands.Tpo .deps/mysqlmanager-user_management_commands.Po
g++ -DMYSQL_INSTANCE_MANAGER -DMYSQL_SERVER -I. -I../../include -I../../zlib -I../../include -I../../include    -O3    -fno-implicit-templates -fno-exceptions -fno-rtti -MT mysqlmanager-angel.o -MD -MP -MF .deps/mysqlmanager-angel.Tpo -c -o mysqlmanager-angel.o `test -f 'angel.cc' || echo './'`angel.cc
mv -f .deps/mysqlmanager-angel.Tpo .deps/mysqlmanager-angel.Po
/bin/sh ../../libtool --preserve-dup-deps --tag=CXX   --mode=link g++  -O3    -fno-implicit-templates -fno-exceptions -fno-rtti  -rdynamic  -o mysqlmanager mysqlmanager-command.o mysqlmanager-mysqlmanager.o mysqlmanager-manager.o mysqlmanager-log.o mysqlmanager-thread_registry.o mysqlmanager-listener.o mysqlmanager-protocol.o mysqlmanager-mysql_connection.o mysqlmanager-user_map.o mysqlmanager-messages.o mysqlmanager-commands.o mysqlmanager-instance.o mysqlmanager-instance_map.o mysqlmanager-instance_options.o mysqlmanager-buffer.o mysqlmanager-parse.o mysqlmanager-guardian.o mysqlmanager-parse_output.o mysqlmanager-user_management_commands.o mysqlmanager-angel.o -static liboptions.la libnet.a ../../vio/libvio.a ../../mysys/libmysys.a ../../strings/libmystrings.a ../../dbug/libdbug.a ../../extra/yassl/src/libyassl.la               ../../extra/yassl/taocrypt/src/libtaocrypt.la ../../zlib/libzlt.la -lpthread -lcrypt -lnsl -lm  -lpthread
libtool: link: g++ -O3 -fno-implicit-templates -fno-exceptions -fno-rtti -rdynamic -o mysqlmanager mysqlmanager-command.o mysqlmanager-mysqlmanager.o mysqlmanager-manager.o mysqlmanager-log.o mysqlmanager-thread_registry.o mysqlmanager-listener.o mysqlmanager-protocol.o mysqlmanager-mysql_connection.o mysqlmanager-user_map.o mysqlmanager-messages.o mysqlmanager-commands.o mysqlmanager-instance.o mysqlmanager-instance_map.o mysqlmanager-instance_options.o mysqlmanager-buffer.o mysqlmanager-parse.o mysqlmanager-guardian.o mysqlmanager-parse_output.o mysqlmanager-user_management_commands.o mysqlmanager-angel.o  ./.libs/liboptions.a -lpthread -lpthread -lpthread -lpthread libnet.a ../../vio/libvio.a ../../mysys/libmysys.a ../../strings/libmystrings.a ../../dbug/libdbug.a ../../extra/yassl/src/.libs/libyassl.a -lpthread -lpthread -lpthread -lpthread ../../extra/yassl/taocrypt/src/.libs/libtaocrypt.a -lpthread -lpthread -lpthread -lpthread ../../zlib/.libs/libzlt.a -lpthread -lcrypt -lnsl -lm -lpthread
make[2]: Leaving directory `/var/lib/mysql/spider/mysql-5.1.46/server-tools/instance-manager'
make[1]: Leaving directory `/var/lib/mysql/spider/mysql-5.1.46/server-tools'
Making all in win
make[1]: Entering directory `/var/lib/mysql/spider/mysql-5.1.46/win'
make[1]: Nothing to be done for `all'.
make[1]: Leaving directory `/var/lib/mysql/spider/mysql-5.1.46/win'

3.创建二进制安装包

./scripts/make_binary_distribution

成功时的信息：

引用

mysql-5.1.46-linux-i686/share/man/man1/my_print_defaults.1
mysql-5.1.46-linux-i686/share/man/man1/mysqld_safe.1
mysql-5.1.46-linux-i686/share/man/man1/mysqlshow.1
mysql-5.1.46-linux-i686/share/man/man1/mysql_config.1
mysql-5.1.46-linux-i686/share/man/man1/mysqltest.1
mysql-5.1.46-linux-i686/share/man/man1/mysqlslap.1
mysql-5.1.46-linux-i686/share/man/man8/
mysql-5.1.46-linux-i686/share/man/man8/mysqlmanager.8
mysql-5.1.46-linux-i686/share/man/man8/mysqld.8
mysql-5.1.46-linux-i686/share/aclocal/
mysql-5.1.46-linux-i686/share/aclocal/mysql.m4
mysql-5.1.46-linux-i686/share/info/
mysql-5.1.46-linux-i686/share/info/mysql.info
mysql-5.1.46-linux-i686.tar.gz created
Removing temporary directory

4.安装集成了Spider引擎的MySQL（利用sandbox可以在一台服务器上安装多个MySQL实例，安装方法请参照后边）

make_sandbox $PWD/mysql-5.1.46-linux-i686.tar.gz --sandbox_directory=spider_main

安装成功时的信息：

引用

unpacking /var/lib/mysql/spider/mysql-5.1.46/mysql-5.1.46-linux-i686.tar.gz
Executing low_level_make_sandbox --basedir=/var/lib/mysql/spider/mysql-5.1.46/5.1.46 \
        --sandbox_directory=msb_5_1_46 \
        --install_version=5.1 \
        --sandbox_port=5146 \
        --no_ver_after_name \
        --sandbox_directory=spider_main \
        --my_clause=log-error=msandbox.err
    The MySQL Sandbox,  version 3.0.09
    (C) 2006-2010 Giuseppe Maxia
installing with the following parameters:
upper_directory                = /root/sandboxes
sandbox_directory              = spider_main
sandbox_port                   = 5146
check_port                     =
no_check_port                  =
datadir_from                   = script
install_version                = 5.1
basedir                        = /var/lib/mysql/spider/mysql-5.1.46/5.1.46
tmpdir                         =
my_file                        =
operating_system_user          = root
db_user                        = msandbox
db_password                    = msandbox
my_clause                      = log-error=msandbox.err
prompt_prefix                  = mysql
prompt_body                    =  [\h] {\u} (\d) >
force                          =
no_ver_after_name              = 1
verbose                        =
load_grants                    = 1
no_load_grants                 =
no_run                         =
no_show                        =
do you agree? ([Y],n) Y
loading grants
... sandbox server started
Your sandbox server was installed in $HOME/sandboxes/spider_main

5.设定：
①Spider引擎设定（从Spider作者博客下载了spider_setup.sql，但是不好用，以为是编译的问题费了好长时间，后来在日文博客里找到了可以用的设定。）

cd $HOME/sandboxes/spider_main
wget http://dl.iteye.com/topics/download/ce85be56-696a-36e9-9c6f-4db53dac528b
mv install_spider.zip install_spider.sql
./use < install_spider.sql

②检查Spider引擎

./use 
select engine,support,transactions,xa from information_schema.engines;

执行成功时会出现的信息：

+------------+---------+--------------+------+
| engine     | support | transactions | xa   |
+------------+---------+--------------+------+
| SPIDER     | YES     | YES          | YES  |
| MRG_MYISAM | YES     | NO           | NO   |
| CSV        | YES     | NO           | NO   |
| MyISAM     | DEFAULT | NO           | NO   |
| InnoDB     | YES     | YES          | YES  |
| MEMORY     | YES     | NO           | NO   |
+------------+---------+--------------+------+

编译时遇到的问题及解决办法
①编译时发生错误：

引用

configure:3615: error: in `/var/lib/mysql/spider/mysql-5.1.46':
configure:3618: error: no acceptable C compiler found in $PATH

经查阅，需要安装GCC组件。GCC组件的安装方法可参照下面的手顺。

②装完GCC后，再次MySQL命令时，又发生了如下的错误：

引用

configure:7121: /lib/cpp  conftest.c
In file included from /usr/include/bits/posix1_lim.h:153,
                 from /usr/include/limits.h:145,
                 from /usr/lib/gcc/i386-redhat-linux/4.1.2/include/limits.h:122,
                 from /usr/lib/gcc/i386-redhat-linux/4.1.2/include/syslimits.h:7,
                 from /usr/lib/gcc/i386-redhat-linux/4.1.2/include/limits.h:11,
                 from conftest.c:11:
/usr/include/bits/local_lim.h:36:26: error: linux/limits.h: No such file or directory
configure:7128: $? = 1
configure: failed program was:
| /* confdefs.h.  */
| #define PACKAGE_NAME "MySQL Server"
| #define PACKAGE_TARNAME "mysql"
| #define PACKAGE_VERSION "5.1.46"
| #define PACKAGE_STRING "MySQL Server 5.1.46"
| #define PACKAGE_BUGREPORT ""
| #define PACKAGE "mysql"
| #define VERSION "5.1.46"
| /* end confdefs.h.  */
| #ifdef __STDC__
| # include <limits.h>
| #else
| # include <assert.h>
| #endif
|                    Syntax error
configure:7190: error: in `/var/lib/mysql/spider/mysql-5.1.46':
configure:7193: error: C preprocessor "/lib/cpp" fails sanity check
See `config.log' for more details.

解决办法：

ln -s /usr/src/linux/include/linux /usr/include/.

③继续执行上述编译命令，出现如下的错误：

引用

configure: error: No curses/termcap library found

经调查，需要安装 ncurses-devel。
执行远程安装命令 yum install ncurses-devel 命令安装即可解决该问题。

④继续执行上述编译命令命令，出现如下的错误：

引用

In file included from /usr/include/bits/errno.h:25,
                 from /usr/include/errno.h:36,
                 from zutil.h:38,
                 from crc32.c:29:
/usr/include/linux/errno.h:4:23: error: asm/errno.h: No such file or directory
make[1]: *** [crc32.lo] Error 1
make[1]: Leaving directory `/var/lib/mysql/spider/mysql-5.1.46/zlib'
make: *** [all-recursive] Error 1

解决办法如下：

cd /usr/include
ln -s /usr/src/linux/include/asm-i386 /usr/include/asm
ln -s /usr/src/linux/include/asm-generic /usr/include/asm-generic

⑤继续执行上述编译命令，出现如下的错误：

引用

mv -f .deps/handler.Tpo .deps/handler.Po
g++ -DMYSQL_SERVER -DDEFAULT_MYSQL_HOME="\"/usr/local\"" -DMYSQL_DATADIR="\"/usr/local/var\"" -DSHAREDIR="\"/usr/local/share/mysql\"" -DPLUGINDIR="\"/usr/local/lib/mysql/plugin\"" -DHAVE_EVENT_SCHEDULER -DHAVE_CONFIG_H -I. -I../include -I../zlib -I../include -I../include -I../regex -I.     -O3    -fno-implicit-templates -fno-exceptions -fno-rtti -MT ha_partition.o -MD -MP -MF .deps/ha_partition.Tpo -c -o ha_partition.o ha_partition.cc
ha_partition.cc:6575: error: no "const COND* ha_partition::cond_push(const COND*)" member function declared in class "ha_partition"
ha_partition.cc:6595: error: no "void ha_partition::cond_pop()" member function declared in class "ha_partition"
ha_partition.cc:6608: error: redefinition of "const COND* ha_partition::cond_push(const COND*)"
ha_partition.cc:6575: error: "const COND* ha_partition::cond_push(const COND*)" previously defined here
ha_partition.cc:6628: error: redefinition of "void ha_partition::cond_pop()"
ha_partition.cc:6595: error: "void ha_partition::cond_pop()" previously defined here
make[3]: *** [ha_partition.o] Error 1
make[3]: Leaving directory `/var/lib/mysql/spider/mysql-5.1.46/sql'
make[2]: *** [all-recursive] Error 1
make[2]: Leaving directory `/var/lib/mysql/spider/mysql-5.1.46/sql'
make[1]: *** [all] Error 2
make[1]: Leaving directory `/var/lib/mysql/spider/mysql-5.1.46/sql')

condition pushdown使用5.1.36版本可以回避这个问题。

工具安装
autoconf安装
下载地址：http://download.chinaunix.net/download/0001000/653.shtml

cd /home/peng
tar -xzf autoconf-2.63.tar.gz
cd autoconf-2.63/
./configure --prefix=/usr
make
make install

automake安装
下载地址：http://ftp.gnu.org/gnu/automake/

cd /home/peng
tar -xzf automake-1.11.1.tar.gz
cd automake-1.11.1
./configure --prefix=/usr
make
make check  (耗时很长，非必须！！！)
make install

gcc安装
CentOS的DVD系统光盘，然后进入CentOS目录内，取得如下的安装包，安装顺序如下：

rpm -ivh cpp-4.1.2-42.el5.i386.rpm
rpm -ivh kernel-headers-2.6.6-1.i386.rpm
rpm -ivh glibc-2.5-24.i386.rpm
rpm -ivh glibc-headers-2.5-24.i386.rpm
rpm -ivh glibc-devel-2.5-24.i386.rpm
rpm -ivh libgomp-4.1.2-42.el5.i386.rpm
rpm -ivh gcc-4.1.2-42.el5.i386.rpm

g++安装
CentOS的DVD系统光盘，然后进入CentOS目录内，取得如下的安装包，安装顺序如下：

rpm -ivh libstdc++-devel-4.1.2-42.el5.i386.rpm
rpm -ivh gcc-c++-4.1.2-42.el5.i386.rpm

安装MySQLSandbox
下载地址：https://launchpad.net/mysql-sandbox/+download

cd /home/peng
tar -xzf MySQL-Sandbox-3.0.09.tar.gz
cd MySQL-Sandbox-3.0.09
perl Makefile.PL
make
sudo make install
export SANDBOX_AS_ROOT=1 (如果是root用户，此处必须是0以外的值；如果是root以外用户，设不不需要执行这行。)

MySQL的卸载方法
查找已经安装的MySQL

引用

rpm -qa | grep MySQL
MySQL-server-community-5.1.28-0.rhel5
MySQL-client-community-5.1.28-0.rhel5

卸载现有的MySQL

引用

rpm -e --nodeps MySQL-client-community-5.1.28-0.rhel5
rpm -e --nodeps MySQL-server-community-5.1.28-0.rhel5

参考
http://nippondanji.blogspot.com/2010/04/spider.html