USING SPIDER (Simple case)

利用spider storage engine的简单实例：

 

Preparing remote servers

1.Create three servers using MySQL Sandbox
任意目录下执行命令：
make_multiple_sandbox \
--group_directory=spider_dir \
--sandbox_base_port=6000 \
--check_base_port 5.1.44

执行上述命令时出现了错误：
installing node 1
/opt/mysql does not exist
error installing node 1

(512 )

解决办法：
cd /opt
mkdir mysql
cd mysql
ln -s /home/spider/mysql-5.1.44/5.1.44 /opt/mysql/5.1.44

创建成功的信息：
installing node 1
installing node 2
installing node 3
group directory installed in $HOME/sandboxes/spider_dir

2.Check the port numbers(下面的~/即相当于/root/)
~/sandboxes/spider_dir/use_all \
"show variables like 'port'"

Check成功的信息：
# server: 1:
Variable_name Value
port 6001
# server: 2:
Variable_name Value
port 6002
# server: 3:
Variable_name Value
port 6003

3.create table definition (tablea.sql)
在$HOME/sandboxes/spider_dir下创建tablea.sql文件，内容如下:
drop database if exists myspider;
create database myspider;
use myspider;
Create table tbl_a(
col_a int,
col_b int,
primary key(col_a)
);

4.create table in remote servers
cd $HOME/sandboxes/spider_dir
./use_all "source tablea.sql"

Setting the main server

1.create table definition (tablea_main.sql)
在$HOME/sandboxes/spider_main下创建tablea_main.sql文件，内容如下:
drop database if exists myspider;
create database myspider;
use myspider;
Create table tbl_b(
col_a int,
col_b int,
primary key(col_a)
) engine = Spider
Connection ' table "tbl_a", user "msandbox", password "msandbox" '
partition by range( col_a ) (
partition pt1 values less than (1000)
 comment 'host "127.0.0.1", port "6001"',
partition pt2 values less than (2000)
 comment 'host "127.0.0.1", port "6002"',
partition pt3 values less than (MAXVALUE)
 comment 'host "127.0.0.1", port "6003"'
);

2.create table
cd $HOME/sandboxes/spider_main
./use < tablea_main.sql

执行上述SQL文时，出现了MySQL Sandbox停止，并且不能重新启动的错误：sandbox server not started yet......
经过艰难的调查，依然未果！！！

 

解决办法一：

cd $HOME/sandboxes/spider_main
./clear
./start

解决办法二：

①cd /home/spider/mysql-5.1.44/
②然后执行如下命令：
make_sandbox \
$PWD/mysql-5.1.44-linux-i686.tar.gz \
--sandbox_directory=spider_main --force
③然后执行(spider环境搭建)中的(spider storage engine の setup)即可。

 

以上两种方法会导致数据库全部丢失，因此请慎用。

 

解决办法之三：
cd $HOME/sandboxes/spider_main/data
然后删除data目录了下的ib_logfile0、ib_logfile1、tc.log文件。
然后重启即可。

(Finally) using it

1.in the main server  --- using it (1)
cd $HOME/sandboxes/spider_main
./use myspider
insert into tbl_b values (500,1), \
(1500,2), (5000,3);
#Query OK, 3 rows affected (0.01 sec)
#Records: 3 Duplicates: 0 Warnings: 0

2.in the main server  --- using it (2)
select * from tbl_b;

+---------------+------------------+

|     col_a       |        col_b       |

+---------------+------------------+

|      500        |                        |

|    1500        |                        |

|     5000       |                        |

+---------------+------------------+

#3 rows in set (0.01 sec)

Looking for the data

1.in the "remote" servers

 

cd $HOME/sandboxes/spider_dir

./use_all "select * from myspider.tbl_a"

# server: 1:
col_a col_b
500 1
# server: 2:
col_a col_b
1500 2
# server: 3:
col_a col_b
5000 3