mycat数据库中间件的配置实践

Nelly32P 9年前

来自： http://my.oschina.net/u/1014681/blog/610442


看了mycat权威指南，今天也实践了下，下载Mycat-server-1.4.1-dev-20151216110027-win版本做测试

新建三个数据库分片

db1脚本

/*

Navicat MySQL Data Transfer


Source Server         : mysql

Source Server Version : 50527

Source Host           : localhost:3306

Source Database       : db1


Target Server Type    : MYSQL

Target Server Version : 50527

File Encoding         : 65001


Date: 2016-01-27 15:48:40

*/


SET FOREIGN_KEY_CHECKS=0;


-- ----------------------------

-- Table structure for `company`

-- ----------------------------

DROP TABLE IF EXISTS `company`;

CREATE TABLE `company` (

  `ID` bigint(20) NOT NULL DEFAULT '0',

  PRIMARY KEY (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ----------------------------

-- Records of company

-- ----------------------------


-- ----------------------------

-- Table structure for `customer`

-- ----------------------------

DROP TABLE IF EXISTS `customer`;

CREATE TABLE `customer` (

  `ID` bigint(20) NOT NULL DEFAULT '0',

  `sharding_id` bigint(20) DEFAULT NULL,

  PRIMARY KEY (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ----------------------------

-- Records of customer

-- ----------------------------

INSERT INTO `customer` VALUES ('1', null);

INSERT INTO `customer` VALUES ('4', '10000');


-- ----------------------------

-- Table structure for `employee`

-- ----------------------------

DROP TABLE IF EXISTS `employee`;

CREATE TABLE `employee` (

  `ID` bigint(20) NOT NULL DEFAULT '0',

  PRIMARY KEY (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ----------------------------

-- Records of employee

-- ----------------------------


-- ----------------------------

-- Table structure for `goods`

-- ----------------------------

DROP TABLE IF EXISTS `goods`;

CREATE TABLE `goods` (

  `ID` bigint(20) NOT NULL DEFAULT '0',

  PRIMARY KEY (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ----------------------------

-- Records of goods

-- ----------------------------

INSERT INTO `goods` VALUES ('11');


-- ----------------------------

-- Table structure for `hotnews`

-- ----------------------------

DROP TABLE IF EXISTS `hotnews`;

CREATE TABLE `hotnews` (

  `ID` bigint(20) NOT NULL DEFAULT '0',

  PRIMARY KEY (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ----------------------------

-- Records of hotnews

-- ----------------------------


-- ----------------------------

-- Table structure for `mycat_sequence`

-- ----------------------------

DROP TABLE IF EXISTS `mycat_sequence`;

CREATE TABLE `mycat_sequence` (

  `name` varchar(50) NOT NULL,

  `current_value` int(11) NOT NULL,

  `increment` int(11) NOT NULL DEFAULT '100',

  PRIMARY KEY (`name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ----------------------------

-- Records of mycat_sequence

-- ----------------------------

INSERT INTO `mycat_sequence` VALUES ('GLOBAL', '100400', '100');


-- ----------------------------

-- Table structure for `orders`

-- ----------------------------

DROP TABLE IF EXISTS `orders`;

CREATE TABLE `orders` (

  `ID` bigint(20) NOT NULL DEFAULT '0',

  `customer_id` bigint(20) DEFAULT NULL,

  PRIMARY KEY (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ----------------------------

-- Records of orders

-- ----------------------------

INSERT INTO `orders` VALUES ('1', '1');

INSERT INTO `orders` VALUES ('4', '4');


-- ----------------------------

-- Table structure for `travelrecord`

-- ----------------------------

DROP TABLE IF EXISTS `travelrecord`;

CREATE TABLE `travelrecord` (

  `ID` bigint(20) NOT NULL DEFAULT '0',

  PRIMARY KEY (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ----------------------------

-- Records of travelrecord

-- ----------------------------

INSERT INTO `travelrecord` VALUES ('1');

INSERT INTO `travelrecord` VALUES ('4');

INSERT INTO `travelrecord` VALUES ('10001');

INSERT INTO `travelrecord` VALUES ('100400');


-- ----------------------------

-- Function structure for `mycat_seq_currval`

-- ----------------------------

DROP FUNCTION IF EXISTS `mycat_seq_currval`;

DELIMITER ;;

CREATE DEFINER=`root`@`%` FUNCTION `mycat_seq_currval`(seq_name VARCHAR(50)) RETURNS varchar(64) CHARSET utf8

    DETERMINISTIC

BEGIN DECLARE retval VARCHAR(64); 

SET retval="-999999999,null"; 

SELECT concat(CAST(current_value AS CHAR),",",CAST(increment AS CHAR)) INTO retval FROM MYCAT_SEQUENCE WHERE name = seq_name; 

RETURN retval; 

END

;;

DELIMITER ;


-- ----------------------------

-- Function structure for `mycat_seq_nextval`

-- ----------------------------

DROP FUNCTION IF EXISTS `mycat_seq_nextval`;

DELIMITER ;;

CREATE DEFINER=`root`@`%` FUNCTION `mycat_seq_nextval`(seq_name VARCHAR(50)) RETURNS varchar(64) CHARSET utf8

    DETERMINISTIC

BEGIN UPDATE MYCAT_SEQUENCE SET current_value = current_value + increment WHERE name = seq_name; 

RETURN mycat_seq_currval(seq_name); 

END

;;

DELIMITER ;


-- ----------------------------

-- Function structure for `mycat_seq_setval`

-- ----------------------------

DROP FUNCTION IF EXISTS `mycat_seq_setval`;

DELIMITER ;;

CREATE DEFINER=`root`@`%` FUNCTION `mycat_seq_setval`(seq_name VARCHAR(50),value INTEGER) RETURNS varchar(64) CHARSET utf8

    DETERMINISTIC

BEGIN UPDATE MYCAT_SEQUENCE SET current_value = value WHERE name = seq_name;

RETURN mycat_seq_currval(seq_name);

END

;;

DELIMITER ;

db2脚本

/*

Navicat MySQL Data Transfer


Source Server         : mysql

Source Server Version : 50527

Source Host           : localhost:3306

Source Database       : db2


Target Server Type    : MYSQL

Target Server Version : 50527

File Encoding         : 65001


Date: 2016-01-27 15:48:50

*/


SET FOREIGN_KEY_CHECKS=0;


-- ----------------------------

-- Table structure for `company`

-- ----------------------------

DROP TABLE IF EXISTS `company`;

CREATE TABLE `company` (

  `ID` bigint(20) NOT NULL DEFAULT '0',

  PRIMARY KEY (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ----------------------------

-- Records of company

-- ----------------------------


-- ----------------------------

-- Table structure for `customer`

-- ----------------------------

DROP TABLE IF EXISTS `customer`;

CREATE TABLE `customer` (

  `ID` bigint(20) NOT NULL DEFAULT '0',

  `sharding_id` bigint(20) DEFAULT NULL,

  PRIMARY KEY (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ----------------------------

-- Records of customer

-- ----------------------------

INSERT INTO `customer` VALUES ('2', null);

INSERT INTO `customer` VALUES ('5', '10010');


-- ----------------------------

-- Table structure for `employee`

-- ----------------------------

DROP TABLE IF EXISTS `employee`;

CREATE TABLE `employee` (

  `ID` bigint(20) NOT NULL DEFAULT '0',

  PRIMARY KEY (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ----------------------------

-- Records of employee

-- ----------------------------


-- ----------------------------

-- Table structure for `goods`

-- ----------------------------

DROP TABLE IF EXISTS `goods`;

CREATE TABLE `goods` (

  `ID` bigint(20) NOT NULL DEFAULT '0',

  PRIMARY KEY (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ----------------------------

-- Records of goods

-- ----------------------------

INSERT INTO `goods` VALUES ('11');


-- ----------------------------

-- Table structure for `hotnews`

-- ----------------------------

DROP TABLE IF EXISTS `hotnews`;

CREATE TABLE `hotnews` (

  `ID` bigint(20) NOT NULL DEFAULT '0',

  PRIMARY KEY (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ----------------------------

-- Records of hotnews

-- ----------------------------

INSERT INTO `hotnews` VALUES ('1');


-- ----------------------------

-- Table structure for `orders`

-- ----------------------------

DROP TABLE IF EXISTS `orders`;

CREATE TABLE `orders` (

  `ID` bigint(20) NOT NULL DEFAULT '0',

  `customer_id` bigint(20) DEFAULT NULL,

  PRIMARY KEY (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ----------------------------

-- Records of orders

-- ----------------------------

INSERT INTO `orders` VALUES ('2', '2');

INSERT INTO `orders` VALUES ('5', '5');


-- ----------------------------

-- Table structure for `travelrecord`

-- ----------------------------

DROP TABLE IF EXISTS `travelrecord`;

CREATE TABLE `travelrecord` (

  `ID` bigint(20) NOT NULL DEFAULT '0',

  PRIMARY KEY (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ----------------------------

-- Records of travelrecord

-- ----------------------------

INSERT INTO `travelrecord` VALUES ('2');

db3脚本

/*

Navicat MySQL Data Transfer


Source Server         : mysql

Source Server Version : 50527

Source Host           : localhost:3306

Source Database       : db3


Target Server Type    : MYSQL

Target Server Version : 50527

File Encoding         : 65001


Date: 2016-01-27 15:48:58

*/


SET FOREIGN_KEY_CHECKS=0;


-- ----------------------------

-- Table structure for `company`

-- ----------------------------

DROP TABLE IF EXISTS `company`;

CREATE TABLE `company` (

  `ID` bigint(20) NOT NULL DEFAULT '0',

  PRIMARY KEY (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ----------------------------

-- Records of company

-- ----------------------------


-- ----------------------------

-- Table structure for `hotnews`

-- ----------------------------

DROP TABLE IF EXISTS `hotnews`;

CREATE TABLE `hotnews` (

  `ID` bigint(20) NOT NULL DEFAULT '0',

  PRIMARY KEY (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ----------------------------

-- Records of hotnews

-- ----------------------------

INSERT INTO `hotnews` VALUES ('2');


-- ----------------------------

-- Table structure for `travelrecord`

-- ----------------------------

DROP TABLE IF EXISTS `travelrecord`;

CREATE TABLE `travelrecord` (

  `ID` bigint(20) NOT NULL DEFAULT '0',

  PRIMARY KEY (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ----------------------------

-- Records of travelrecord

-- ----------------------------

INSERT INTO `travelrecord` VALUES ('3');


schema.xml配置：

<?xml version="1.0"?>

<!DOCTYPE mycat:schema SYSTEM "schema.dtd">

<mycat:schema xmlns:mycat="http://org.opencloudb/">


<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">

<!-- auto sharding by id (long) -->

<table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />


<!-- global table is auto cloned to all defined data nodes ,so can join 

with any table whose sharding node is in the same data node -->

<table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />

<table name="goods" primaryKey="ID" type="global" dataNode="dn1,dn2" />

<!-- random sharding using mod sharind rule -->

<table name="hotnews" primaryKey="ID" dataNode="dn1,dn2,dn3"

rule="mod-long" />

<!-- <table name="dual" primaryKey="ID" dataNode="dnx,dnoracle2" type="global" 

needAddLimit="false"/> <table name="worker" primaryKey="ID" dataNode="jdbc_dn1,jdbc_dn2,jdbc_dn3" 

rule="mod-long" /> -->

<table name="employee" primaryKey="ID" dataNode="dn1,dn2"

rule="sharding-by-intfile" />

<table name="customer" primaryKey="ID" dataNode="dn1,dn2"

rule="sharding-by-intfile">

<childTable name="orders" primaryKey="ID" joinKey="customer_id"

parentKey="id">

<!--<childTable name="order_items" joinKey="order_id"

parentKey="id" />-->

</childTable>

<!--<childTable name="customer_addr" primaryKey="ID" joinKey="customer_id"

parentKey="id" />-->

</table>

<table name="mycat_sequence" dataNode="dn1" />

<!-- <table name="oc_call" primaryKey="ID" dataNode="dn1$0-743" rule="latest-month-calldate" 

/> -->

</schema>

<!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743" 

/> -->

<dataNode name="dn1" dataHost="localhost1" database="db1" />

<dataNode name="dn2" dataHost="localhost1" database="db2" />

<dataNode name="dn3" dataHost="localhost1" database="db3" />

<!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" />

<dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" /> 

<dataNodename="jdbc_dn2" dataHost="jdbchost" database="db2" /> 

<dataNode name="jdbc_dn3" dataHost="jdbchost" database="db3" /> -->

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"

writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">

<heartbeat>select user()</heartbeat>

<!-- can have multi write hosts -->

<writeHost host="hostM1" url="localhost:3306" user="root"

password="123456">

<!-- can have multi read hosts -->


</writeHost>

<!--<writeHost host="hostS1" url="localhost:3316" user="root"

password="123456" />-->

<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->

</dataHost>

<!--

<dataHost name="sequoiadb1" maxCon="1000" minCon="1" balance="0" dbType="sequoiadb" dbDriver="jdbc"> 

<heartbeat> </heartbeat>

<writeHost host="hostM1" url="sequoiadb://1426587161.dbaas.sequoialab.net:11920/SAMPLE" user="jifeng" password="jifeng"></writeHost> 

</dataHost>

 <dataHost name="oracle1" maxCon="1000" minCon="1" balance="0" writeType="0" dbType="oracle" dbDriver="jdbc"> <heartbeat>select 1 from dual</heartbeat> 

<connectionInitSql>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'</connectionInitSql> 

<writeHost host="hostM1" url="jdbc:oracle:thin:@127.0.0.1 :1521:nange" user="base" password="123456" > </writeHost> </dataHost> 

<dataHost name="jdbchost" maxCon="1000" minCon="1" balance="0" writeType="0" dbType="mongodb" dbDriver="jdbc"> 

<heartbeat>select user()</heartbeat> 

<writeHost host="hostM" url="mongodb://192.168.0.99/test" user="admin" password="123456" ></writeHost> </dataHost> 

<dataHost name="sparksql" maxCon="1000" minCon="1" balance="0" dbType="spark" dbDriver="jdbc"> 

<heartbeat> </heartbeat>

<writeHost host="hostM1" url="jdbc:hive2://feng01:10000" user="jifeng" password="jifeng"></writeHost> </dataHost> -->


<!-- <dataHost name="jdbchost" maxCon="1000" minCon="10" balance="0" dbType="mysql" 

dbDriver="jdbc"> <heartbeat>select user()</heartbeat> <writeHost host="hostM1" 

url="jdbc:mysql://localhost:3306" user="root" password="123456"> </writeHost> 

</dataHost> -->

</mycat:schema>

sequence_db_conf.properties配置：

#sequence stored in datanode

GLOBAL=dn1

COMPANY=dn1

CUSTOMER=dn1

ORDERS=dn1

server.xml增加

<property name="sequnceHandlerType">1</property>

查看mycat，汇集了三个库的所有数据

测试：

insert into travelrecord(id) values(next value for MYCATSEQ_GLOBAL);

[SQL] insert into travelrecord(id) values(next value for MYCATSEQ_GLOBAL);

受影响的行: 1

时间: 0.053s

travelrecord表按照auto-sharding-long分片成功

customer和orders表按ShareJoin分片Join成功。

查看order表

db1库

db2库

mycat