Mycat分库分表案例demo

Mycat分库分表实战案例demo

环境准备

两台虚拟机

Host1 : 192.168.12.66

Host2 : 192.168.12.88

MySQL 5.7

Mycat 1.6

分库分表规则

mycat定义两个逻辑库，分别为db_user、db_store

表db_user按照模分成2个片,分别落在host1、host2上

表db_store为主从复制模式，分别落在host1、host2上

创建数据库和表结构

1. 分别在HOST1、HOST2执行db_user.sql

CREATE DATABASE db_user DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

use db_user;

-- ----------------------------
-- Table structure for data_dictionary
-- ----------------------------
DROP TABLE IF EXISTS `data_dictionary`;
CREATE TABLE `data_dictionary` (
  `dataDictionaryID` int(11) NOT NULL COMMENT '数据字典ID',
  `displayName` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '显示名称',
  `value` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '数据字典取值',
  `createTime` datetime DEFAULT NULL COMMENT '创建时间',
  `lastUpdate` datetime DEFAULT NULL COMMENT '最后更新时间',
  PRIMARY KEY (`dataDictionaryID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Table structure for user_address
-- ----------------------------
DROP TABLE IF EXISTS `user_address`;

CREATE TABLE `user_address` (
  `addressID` int(11) NOT NULL COMMENT '地址ID',
  `receiver` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '收货人',
  `addressDetail` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '地址详细',
  `userID` int(11) NOT NULL COMMENT '用户ID',
  `createTime` datetime DEFAULT NULL COMMENT '创建时间',
  `lastUpdate` datetime DEFAULT NULL COMMENT '最后更新时间',
  PRIMARY KEY (`addressID`,`userID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `userID` int(11) NOT NULL COMMENT '用户ID',
  `username` varchar(16) COLLATE utf8_bin DEFAULT NULL COMMENT '用户名',
  `phoneNum` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '手机号码',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `ddID` int(11) DEFAULT NULL COMMENT '所属会员类型',
  `createTime` datetime DEFAULT NULL COMMENT '注册时间',
  `lastUpdate` datetime DEFAULT NULL COMMENT '最后更新时间',
  PRIMARY KEY (`userID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

1. 只需要在HOST1(192.168.12.66)上执行db_store.sql，HOST2主从复制会自动同步数据

CREATE DATABASE  db_store DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

use db_store;
-- ----------------------------
-- Table structure for employee
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
  `employeeID` int(11) NOT NULL,
  `userName` varchar(16) COLLATE utf8_bin DEFAULT NULL,
  `phoneNum` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `createTime` datetime DEFAULT NULL,
  `lastUpdate` datetime DEFAULT NULL,
  PRIMARY KEY (`employeeID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Table structure for store
-- ----------------------------
DROP TABLE IF EXISTS `store`;
CREATE TABLE `store` (
  `storeID` int(11) NOT NULL,
  `storeName` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `storeAddress` varchar(256) COLLATE utf8_bin DEFAULT NULL,
  `createTime` datetime DEFAULT NULL,
  `lastUpdate` datetime DEFAULT NULL,
  PRIMARY KEY (`storeID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

执行完毕后如下

配置conf文件

两台host主机Mycat的conf目录下配置三个配置文件

schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

	<schema name="db_store" checkSQLschema="false" sqlMaxLimit="100">
		<table name="store" dataNode="db_store_dataNode" primaryKey="storeID"/>
		<table name="employee" dataNode="db_store_dataNode" primaryKey="employeeID"/>
	</schema>
	
	
	<schema name="db_user" checkSQLschema="false" sqlMaxLimit="100">
		<table name="data_dictionary" type="global" dataNode="db_user_dataNode1,db_user_dataNode2" primaryKey="dataDictionaryID"/>
		<table name="users" dataNode="db_user_dataNode$1-2"  rule="mod-userID-long" primaryKey="userID">
			<childTable name="user_address"  joinKey="userID" parentKey="userID" primaryKey="addressID"/>
		</table>
	</schema>
	
	
	<!-- 节点配置 -->
	<!-- db_store -->
	<dataNode name="db_store_dataNode" dataHost="db_storeHOST" database="db_store" />
	
	<!-- db_user -->
	<dataNode name="db_user_dataNode1" dataHost="db_userHOST1" database="db_user" />
	<dataNode name="db_user_dataNode2" dataHost="db_userHOST2" database="db_user" />
	
	
	
	
	<!-- 节点主机配置 -->
	<!-- 配置db_store的节点主机 -->
	<dataHost name="db_storeHOST" maxCon="1000" minCon="10" balance="1"
			  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<!-- can have multi write hosts -->
		<writeHost host="hostM1" url="192.168.12.66:3306" user="root"  password="123456">
			<!-- can have multi read hosts -->
			<readHost host="hostS1" url="192.168.12.88:3306" user="root" password="123456" />
		
		
		</writeHost>
		
	</dataHost>
	
	
	<!-- 配置db_user的节点主机 -->
	<dataHost name="db_userHOST1" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100"<