spider存储引擎
2024-10-20 13:28:23
1.spider 安装
1.1.MariaDB 安装
1.1.1 下载MariaDB wget https://mirrors.tuna.tsinghua.edu.cn/mariadb//mariadb-10.3.16/bintar-linux-x86_64/mariadb-10.3.16-linux-x86_64.tar.gz
https://mariadb.com/downloads/
1.1.2 解压安装
[root@localhost: /usr/local ] #tar -zxvf mariadb-10.3.16-linux-x86_64.tar.gz -C /usr/local/ [root@localhost: /usr/local ] #ln -s mariadb-10.3.16-linux-x86_64 mariadb [root@localhost: /usr/local ] #cd mariadb [root@localhost: /usr/local/mariadb ] #cp /etc/my.cnf /etc/my_3320.cnf ####生成my.cnf文件 与原生mysql差不多,有个别参数不存在。 [root@localhost: /usr/local/mariadb ] #./scripts/mysql_install_db --defaults-file=/etc/my_3320.cnf --basedir=/usr/local/mariadb/ [root@localhost: /usr/local/mariadb ] #chown -R mysql:mysql /data/mysql_3320/ [root@localhost: /usr/local/mariadb ] #/usr/local/mariadb-10.3.16-linux-x86_64/bin/mysqld_safe --defaults-file=/etc/my_3320.cnf & |
1.2.安装spider 引擎
[root@localhost: /usr/local/mariadb ] #mysql -uroot -p < /usr/local/mariadb/share/install_spider.sql root@localhost 19:46: [(none)]> select * from information_schema.engines where engine= 'SPIDER' ; |
2.spider的使用实战
2.1 创建spider引擎表方法1
#mysql node1 CREATE TABLE s( id INT NOT NULL AUTO_INCREMENT, code VARCHAR (10), PRIMARY KEY (id)); #spider node CREATE TABLE s( id INT NOT NULL AUTO_INCREMENT, code VARCHAR (10), PRIMARY KEY (id) ) ENGINE=SPIDER COMMENT 'host "10.204.10.20", user "work", password "123456", port "3306", database "lucky_order"' ; |
2.2 创建spider引擎表方法2
#创建spider到后端DB server的配置 CREATE SERVER lucky_test1 FOREIGN DATA WRAPPER mysql OPTIONS ( HOST '10.212.22.22' , DATABASE 'lucky_test1' , USER 'admintools' , PASSWORD '111111' , PORT 3306 ); CREATE SERVER lucky_test2 FOREIGN DATA WRAPPER mysql OPTIONS ( HOST '10.204.28.1' , DATABASE 'lucky_test2' , USER 'admintools' , PASSWORD '111111' , PORT 3336 ); 或者直接插入mysql.servers表 INSERT INTO mysql.servers(Server_name,HOST,Db,Username, PASSWORD ,PORT,SOCKET,WRAPPER,OWNER) VALUES ( 'lucky_test1' , '10.212.22.22' , 'lucky_test1' , 'work' , '111111' ,3306, '' , 'mysql' , '' ); INSERT INTO mysql.servers(Server_name,HOST,Db,Username, PASSWORD ,PORT,SOCKET,WRAPPER,OWNER) VALUES ( 'lucky_test2' , '10.204.55.72' , 'lucky_test2' , 'work' , '111111' ,3306, '' , 'mysql' , '' ); #mysql node1 CREATE TABLE lucky_test1.sbtest1 ( id int (10) unsigned NOT NULL AUTO_INCREMENT, k int (10) unsigned NOT NULL DEFAULT '0' , c char (120) NOT NULL DEFAULT '' , pad char (60) NOT NULL DEFAULT '' , PRIMARY KEY (id), KEY k (k) ) ENGINE=InnoDB; #mysql node2 CREATE TABLE lucky_test2.sbtest2 ( id int (10) unsigned NOT NULL AUTO_INCREMENT, k int (10) unsigned NOT NULL DEFAULT '0' , c char (120) NOT NULL DEFAULT '' , pad char (60) NOT NULL DEFAULT '' , PRIMARY KEY (id), KEY k (k) ) ENGINE=InnoDB; #spider node CREATE TABLE test.sbtest1 ( id int (10) unsigned NOT NULL AUTO_INCREMENT, k int (10) unsigned NOT NULL DEFAULT '0' , c char (120) NOT NULL DEFAULT '' , pad char (60) NOT NULL DEFAULT '' , PRIMARY KEY (id), KEY k (k) ) ENGINE=spider COMMENT= 'wrapper "mysql",srv "lucky_test1"' ; CREATE TABLE test.sbtest2 ( id int (10) unsigned NOT NULL AUTO_INCREMENT, k int (10) unsigned NOT NULL DEFAULT '0' , c char (120) NOT NULL DEFAULT '' , pad char (60) NOT NULL DEFAULT '' , PRIMARY KEY (id), KEY k (k) ) ENGINE=spider COMMENT= 'wrapper "mysql",srv "lucky_test2"' ; SELECT * FROM sbtest1 a JOIN sbtest2 b ON a.id=b.id LIMIT 10; DROP SERVER lucky_test0; DROP TABLE test.sbtest1; drop spider上的表,不会 drop 后端DB server上的表。 |
2.3 创建hash分区表
CREATE TABLE sbtest ( id INT (10) UNSIGNED NOT NULL AUTO_INCREMENT, k INT (10) UNSIGNED NOT NULL DEFAULT '0' , c CHAR (120) NOT NULL DEFAULT '' , pad CHAR (60) NOT NULL DEFAULT '' , PRIMARY KEY (id), KEY k (k) ) ENGINE=SPIDER DEFAULT CHARSET=utf8 COMMENT= 'wrapper "mysql", table "sbtest1"' PARTITION BY HASH (id) ( PARTITION pt1 COMMENT= 'wrapper "mysql",srv "lucky_test1"' , PARTITION pt2 COMMENT= 'wrapper "mysql",srv "lucky_test0"' ) ; |
2.4 创建range分区表
CREATE TABLE sbtest_range ( id INT (10) UNSIGNED NOT NULL AUTO_INCREMENT, k INT (10) UNSIGNED NOT NULL DEFAULT '0' , c CHAR (120) NOT NULL DEFAULT '' , pad CHAR (60) NOT NULL DEFAULT '' , PRIMARY KEY (id), KEY k (k) ) ENGINE=SPIDER DEFAULT CHARSET=utf8 COMMENT= 'wrapper "mysql", table "sbtest1"' PARTITION BY range columns (id) ( PARTITION pt1 values less than (100000) COMMENT= 'wrapper "mysql",srv "lucky_test1"' , PARTITION pt2 values less than (200000) COMMENT= 'wrapper "mysql",srv "lucky_test0"' ) ; |
2.5 创建list分区表
CREATE TABLE sbtest_list ( id INT (10) UNSIGNED NOT NULL AUTO_INCREMENT, k INT (10) UNSIGNED NOT NULL DEFAULT '0' , c CHAR (120) NOT NULL DEFAULT '' , pad CHAR (60) NOT NULL DEFAULT '' , PRIMARY KEY (id), KEY k (k) ) ENGINE=SPIDER DEFAULT CHARSET=utf8 COMMENT= 'wrapper "mysql", table "sbtest1"' PARTITION BY list columns (id) ( PARTITION pt1 values in (1,3,5,7,9) COMMENT= 'wrapper "mysql",srv "lucky_test1"' , PARTITION pt2 values in (2,4,6,8,10) COMMENT= 'wrapper "mysql",srv "lucky_test0"' ) ; |
最新文章
- 费用流 ZOJ 3933 Team Formation
- centos JDK安装
- In p = new Fred(), does the Fred memory “leak” if the Fred constructor throws an exception?
- Huffman树的编码译码
- POJ 3276
- BrnShop开源网上商城第六讲:扩展视图功能
- hdoj 2063 过山车 【双边匹配匈牙利算法】
- ext panel 它们的定义图像刷新
- 201521123004《Java程序设计》第4周学习总结
- ATS缓存数据结构
- studio-3t 配置文件位置
- python网络爬虫笔记(五)
- Docker容器常用命令
- 【PCA】
- mongodb原生node驱动
- 沉淀再出发:ELK使用初探
- [Android 新特性] 改进明显 Android 4.4系统新特性解析
- 使用Ansible自动配置JDK环境
- spring mvc静态资源访问的配置
- 台湾ML笔记--1.2 formalize the learning probelm
热门文章
- hhhhh臭不要脸//捂脸)多不好意思啊you进步惹
- Linux 权限规划ACL
- jmap -heap 查看堆内存
- 转:sql 经典50题--可能是你见过的最全解析
- Empirical Analysis of Beam Search Performance Degradation in Neural Sequence Models
- Learning Context Graph for Person Search
- Windows10纯净原版系统安装方法
- 如何下载官网最新版 win10 系统?
- 服务器推送(Server push)技术总结
- 【Ubuntu升级python3.5到python3.6】dpkg-deb: error: subprocess paste was killed by signal (Broken pipe) Errors were encountered while processing: E: Sub-process /usr/bin/dpkg returned an error code (1) 问题解决