mycat 垂直切分和水平切分配置示例
来源:互联网 发布:个人考勤软件 编辑:程序博客网 时间:2024/05/17 09:35
mycat 垂直切分和水平切分配置示例
首先是数据库表的分布:
192.168.0.83:db1数据库 test1表;db3数据库 user表 user_detail表
192.168.0.165:db1数据库 test2表;db3数据库 user表 user_detail表
sql:
/*Navicat MySQL Data TransferSource Server : localhostSource Server Version : 50713Source Host : localhost:3306Source Database : db1Target Server Type : MYSQLTarget Server Version : 50713File Encoding : 65001Date: 2016-08-26 17:23:56*/SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for `test1`-- ----------------------------DROP TABLE IF EXISTS `test1`;CREATE TABLE `test1` ( `id` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ------------------------------ Records of test1-- ----------------------------/*Navicat MySQL Data TransferSource Server : localhostSource Server Version : 50713Source Host : localhost:3306Source Database : db3Target Server Type : MYSQLTarget Server Version : 50713File Encoding : 65001Date: 2016-08-26 17:24:04*/SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for `user`-- ----------------------------DROP TABLE IF EXISTS `user`;CREATE TABLE `user` ( `id` int(11) NOT NULL, `user_id` varchar(255) DEFAULT NULL, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ------------------------------ Records of user-- ----------------------------/*Navicat MySQL Data TransferSource Server : localhostSource Server Version : 50713Source Host : localhost:3306Source Database : db3Target Server Type : MYSQLTarget Server Version : 50713File Encoding : 65001Date: 2016-08-26 17:24:18*/SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for `user_detail`-- ----------------------------DROP TABLE IF EXISTS `user_detail`;CREATE TABLE `user_detail` ( `id` int(11) NOT NULL, `user_id` varchar(255) DEFAULT NULL, `detail` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ------------------------------ Records of user_detail-- ----------------------------/*Navicat MySQL Data TransferSource Server : songSource Server Version : 50713Source Host : 192.168.0.165:3306Source Database : db1Target Server Type : MYSQLTarget Server Version : 50713File Encoding : 65001Date: 2016-08-26 17:24:27*/SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for `test2`-- ----------------------------DROP TABLE IF EXISTS `test2`;CREATE TABLE `test2` ( `id` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ------------------------------ Records of test2-- ----------------------------
schema.xml文件:
<?xml version="1.0"?><!DOCTYPE mycat:schema SYSTEM "schema.dtd"><mycat:schema xmlns:mycat="http://org.opencloudb/"> <schema name="qs" checkSQLschema="false" sqlMaxLimit="100"> <!-- 垂直切分 --> <table name="test1" primaryKey="id" type="global" dataNode="nodeTest1" /> <table name="test2" primaryKey="id" type="global" dataNode="nodeTest2" /> <!-- 全局表的配置如下(比如配置文件的数据,数据不大很少变动的,但是经常用到查询的) --> <!-- <table name="t_area" primaryKey="id" type="global" dataNode="dn1,dn2" /> --> <!-- 水平切分 --> <!-- ER分片表:如user表和user_detail表的关联字段user_id;保证相同user_id的数据在同一块片区上 --> <table name="user" primaryKey="user_id" dataNode="nodeUser01,nodeUser02" rule="mod-long"> <childTable name="user_detail" primaryKey="id" joinKey="user_id" parentKey="user_id" /> </table> </schema> <dataNode name="nodeUser01" dataHost="dataHost01" database="db3" /> <dataNode name="nodeUser02" dataHost="dataHost02" database="db3" /> <dataNode name="nodeTest1" dataHost="dataHost01" database="db1" /> <dataNode name="nodeTest2" dataHost="dataHost02" database="db1" /> <dataHost name="dataHost01" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native"> <heartbeat>select user()</heartbeat> <writeHost host="hostM1" url="192.168.0.83:3306" user="root" password="root"/> </dataHost> <dataHost name="dataHost02" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native"> <heartbeat>select user()</heartbeat> <writeHost host="hostM1" url="192.168.0.165:3306" user="root" password="root"/> </dataHost></mycat:schema>
注: rule=”mod-long” 分片规则是根据分片字段求模运算;
分片规则有:固定分片hash,范围约定,一致性hash,按日期范围等。
详见官网文档 http://www.mycat.org.cn/document/Mycat_V1.6.0.pdf
测试:
C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -uroot -proot -hlocalhost -P8066 -Dqs
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.5.8-mycat-1.4-release-20151019230038 MyCat Server (OpenCloundDB)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> show databases;
+———-+
| DATABASE |
+———-+
| qs |
+———-+
1 row in set (0.00 sec)
mysql> show tables;
+————–+
| Tables in qs |
+————–+
| test1 |
| test2 |
| user |
| user_detail |
+————–+
4 rows in set (0.00 sec)
- mycat 垂直切分和水平切分配置示例
- mycat是什么鬼?垂直切分?水平切分?
- 数据库的垂直切分和水平切分
- 数据库的垂直切分和水平切分
- 数据库的垂直切分和水平切分
- 数据库的垂直切分和水平切分
- 数据库的垂直切分和水平切分
- 数据库的垂直切分和水平切分
- 数据库的垂直切分和水平切分
- MySQL垂直和水平切分
- 数据库垂直和水平切分
- MySQL垂直和水平切分
- MySQL垂直和水平切分
- 数据库垂直和水平切分
- 水平切分与垂直切分
- mycat水平切分
- 数据库的水平切分和垂直切分概念
- MySQL垂直和水平切分-转载整理
- 【BZOJ】3016: [Usaco2012 Nov]Clumsy Cows
- Java transient关键字使用小记
- leetcode :Binary Search:Dungeon Game(174)
- 《数学之美》读书笔记3
- 大话直播,做直播看我就够了
- mycat 垂直切分和水平切分配置示例
- 移动端判断是否处于横屏还是竖屏状态
- 摘录的一些调度算法
- C#之静态函数的使用
- css3-flex-ONE
- Linux命令之"cat"
- JDK动态代理
- Poj 3189 Steady Cow Assignment【二分+多重匹配】
- 模仿Flickr 使用GPUImage的实时相机滤镜(iOS源代码)