Spider存储引擎尝试

安装

升级mariadb

卸载旧的

1
2
3

yum remove mariadb
rm -f /etc/my.cnf
rm -rf /var/lib/mysql/

添加源

1	vim /etc/yum.repos.d/Mariadb.repo

添加：

[mariadb]
name = MariaDB
baseurl = https://mirrors.ustc.edu.cn/mariadb/yum/10.2/centos7-amd64
gpgkey=https://mirrors.ustc.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1

1 2	yum clean all yum makecache all

安装

1
2
3

yum install MariaDB-server MariaDB-client -y
systemctl start mariadb.service
systemctl enable mariadb.service

出现下面的无法启动的问题：

1	ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=1/FAILURE)

1 2	mysql_install_db chmod -R 777 /var

初始化

1 2	/usr/bin/mysql_secure_installation (enter: yynyy)

安装spider

安装spider

1 2	// install_spider.sql 在share/下 mysql -uroot -p < install_spider.sql

改命令实际创建了表

spider_link_failed_log
spider_link_mon_servers
spider_tables
spider_xa
spider_xa_failed_log
spider_xa_member

查看是否安装成功

1	select * from infomation_scheme.engines

使用

准备

准备实际存储数据的数据库

// 在从数据库添加主数据库的访问权限
grant all on *.* to root@'104.198.*.*' identified by 'password';
// 创建测试表
create table test_spider (
id int,
username varchar(20),
address varchar(128),
primary key (id),
key (username)
) engine=InnoDB default charset=utf8 comment 'spider test base table';

测试使用

在主服务器创建访问从远程服务器的信息

1
2
3

create server backend foreign data wrapper mysql options (host '35.220.*.*', database 'test', user 'root', password 'password', port 3306);
create server backend foreign data wrapper mysql options (host '104.198.*.*', database 'backtext', user 'root', password 'password', port 3306);
select * from mysql.servers;

创建单张垂直表

create table test_spider (
id int,
username varchar(20),
address varchar(128),
primary key (id),
key (username)
) ENGINE=SPIDER DEFAULT CHARSET=utf8 COMMENT='server "backend"';

hash分区表

create table test_spider_hash (
id int,
username varchar(20),
address varchar(128),
primary key (id),
key (username)
) ENGINE=SPIDER DEFAULT CHARSET=utf8 COMMENT='wrapper "mysql", table "test_spider"'
PARTITION BY HASH (id)
( PARTITION pt1 COMMENT = 'srv "backend"',
PARTITION pt2 COMMENT = 'srv "backend1"') ;

range分区表

create table test_spider_range (
id int,
username varchar(20),
address varchar(128),
primary key (id),
key (username)
) ENGINE=SPIDER DEFAULT CHARSET=utf8 COMMENT='wrapper "mysql", table "test_spider"'
PARTITION BY range columns (id)
( PARTITION pt1 values less than (100000) COMMENT = 'srv "backend"',
PARTITION pt2 values less than (200000) COMMENT = 'srv "backend1"') ;

list分区表

create table test_spider_list (
id int,
username varchar(20),
address varchar(128),
primary key (id),
key (username)
) ENGINE=SPIDER DEFAULT CHARSET=utf8 COMMENT='wrapper "mysql", table "test_spider"'
PARTITION BY list columns (id)https://www.jianshu.com/p/ab539e9a7955
( PARTITION pt1 values in (1,3,5,7,9) COMMENT = 'srv "backend"',
PARTITION pt2 values in (2,4,6,8,10) COMMENT = 'srv "backend1"') ;

参考点这里