spider存储引擎布署

当前平台:centos5.8, x86_64

1. 下载地址:http://spiderformysql.com/index.html,

     当前下载的文件名:mysql-5.5.34-spider-3.2-vp-1.1-hs-1.2-q4m-0.95.tgz  (源码安装)

2. 安装cmake软件,如果可以直接用yum install cmake (不要装与系统版本不适合版本避免兼容性和编译时的一些报错)

3.解压后安装

#tar -zxvf mysql-5.5.34-spider-3.2-vp-1.1-hs-1.2-q4m-0.95.tgz

#cd mysql-5.5.34-spider-3.2-vp-1.1-hs-1.2-q4m-0.95

# cmake .
# make
# make install

#中间可能会报一些由于缺少安装gcc的包而引起的报错。yum install gcc gcc-c

#正常编译通过的结尾部份:

-- Running cmake version 2.6.4
-- MySQL 5.5.34
-- Packaging as: mysql-5.5.34-Linux-x86_64
-- Configuring done
-- Generating done
-- Build files have been written to: /root/mysql-5.5.34-spider-3.2-vp-1.1-hs-1.2-q4m-0.95

#如果出一些类似 的警告问题，证明编译非正常结束，建议先修复，如以下：

-- Performing Test HAVE_PEERCRED
-- Performing Test HAVE_PEERCRED - Success
Warning: Bison executable not found in PATH
-- Configuring done
-- Generating done
-- Build files have been written to: /root/mysql-5.5.34-spider-3.2-vp-1.1-hs-1.2-q4m-0.95

==>解决方式yum install bison

4.启动前先初始化环境:

#根据实际情况调整

/usr/local/mysql/scripts/mysql_install_db \
--defaults-file=/etc/my.cnf \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data \
--user=mysql

5.选择其中一种方式启动(这里mysql.server)

修改mysql.server里边的变量

basedir=/usr/local/mysql
datadir=/usr/local/mysql/data

#修改完成后./mysql.server start启动库

6.执行bin目录下的mysql 连接库和创建spider引擎

mysql -uroot -p

source /usr/local/mysql/share/install_spider.sql

mysql> source /usr/local/mysql/share/install_spider.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

ERROR 1436 (HY000): Thread stack overrun:  11296 bytes used of a 131072 byte stack, and 128000 bytes needed.  Use 'mysqld --thread_stack=#' to specify a bigger stack.
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

ERROR 1436 (HY000): Thread stack overrun:  11296 bytes used of a 131072 byte stack, and 128000 bytes needed.  Use 'mysqld --thread_stack=#' to specify a bigger stack.
Query OK, 0 rows affected (0.00 sec)

==>解决办法 ：修改/etc/my.cnf里边的变量:  thread_stack=256000,重新执行脚本一次

| Engine             | Support | Comment                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| SPIDER             | YES     | Spider storage engine                                      | YES          | YES  | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                         | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+

示例 ：当前3台主机，1台建spider引擎（ip:192.168.56.51)，另两台里边包含的分区表所在库，ip分别为192.168.56.52和53

示例 参考地址:https://wenku.baidu.com/view/40094a91b8f67c1cfbd6b859.html

（1）、52，53机器上分别建立表tbl_a

mysql> show create table tbl_a\G
*************************** 1. row ***************************
       Table: tbl_a
Create Table: CREATE TABLE `tbl_a` (
  `col_a` int(11) NOT NULL,
  `col_b` varchar(20) DEFAULT NULL,
  `col_c` int(11) NOT NULL,
  PRIMARY KEY (`col_a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.05 sec)

(2)、51机器

CREATE SERVER backend1
     FOREIGN DATA WRAPPER mysql
OPTIONS(
    HOST '192.168.56.52',
    DATABASE 'test',
    USER 'root',
    PASSWORD '123456789',
    PORT 3306
);

CREATE SERVER backend2
     FOREIGN DATA WRAPPER mysql
OPTIONS(
    HOST '192.168.56.53',
    DATABASE 'test',
    USER 'root',
    PASSWORD '123456789',
    PORT 3306
);

create table test.tbl_a(
  col_a int not null,
  col_b varchar(20),
  col_c int not null,
  primary key(col_a)
)engine=spider connection='wrapper "mysql", user "root", password "123456789",table "tbl_a", port "3306"'
partition by key(col_a)
(partition pt1 comment='srv"backend1"' engine=spider, partition pt2 comment='srv"backend2"' engine=spider);

mysql> select * from tbl_a;
Empty set (0.12 sec)

mysql> insert into tbl_a values(1,'a',1);
Query OK, 1 row affected (0.05 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tbl_a values(2,'a',2);
Query OK, 1 row affected (0.06 sec)

mysql> insert into tbl_a values(3,'a',3);
Query OK, 1 row affected (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tbl_a;
+-------+-------+-------+
| col_a | col_b | col_c |
+-------+-------+-------+
|     1 | a     |     1 |
|     3 | a     |     3 |
|     2 | a     |     2 |
+-------+-------+-------+
3 rows in set (0.03 sec)

(3)、52，53的机器上分别查询:

52机器:

mysql> select * from tbl_a;
+-------+-------+-------+
| col_a | col_b | col_c |
+-------+-------+-------+
|     1 | a     |     1 |
|     3 | a     |     3 |
+-------+-------+-------+

53机器:

mysql> select * from tbl_a;
+-------+-------+-------+
| col_a | col_b | col_c |
+-------+-------+-------+
|     2 | a     |     2 |
+-------+-------+-------+
1 row in set (0.00 sec)

小结：证明从表51上插入的行已按分区方式自动分散到两个分区。