Mycat--实践--19--垂直分库

Mycat–实践–19–垂直分库

1、概念

我们上面讲的分片，都是水平分库。也就是将一个表的数据，放到多个库中保存。

垂直分库就是将多个表，放到多个库中保存。

举例如下

2、操作

期望

当前数据库有用户表，商品表，我现在想将

1. 用户表放到用户库中（M1S1主机）
2. 商品表放到商品库中（M1S2主机）
3. 对外提供一个统一的访问地址

步骤1、创建数据

M1S1主机

DROP DATABASE IF EXISTS user_db ;
CREATE DATABASE user_db CHARACTER SET UTF8 ;
use user_db ;


CREATE TABLE user(
  id INT PRIMARY KEY ,
  name VARCHAR(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;




INSERT INTO user(id,name) VALUES(1,"用户1");
INSERT INTO user(id,name) VALUES(1,"用户2");
INSERT INTO user(id,name) VALUES(1,"用户3");
INSERT INTO user(id,name) VALUES(1,"用户4");
INSERT INTO user(id,name) VALUES(1,"用户5");
INSERT INTO user(id,name) VALUES(1,"用户6");
INSERT INTO user(id,name) VALUES(1,"用户7");
INSERT INTO user(id,name) VALUES(1,"用户8");

M1S2主机

DROP DATABASE IF EXISTS good_db ;
CREATE DATABASE good_db CHARACTER SET UTF8 ;
use good_db ;



CREATE TABLE goods(
  id INT PRIMARY KEY ,
  name VARCHAR(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



INSERT INTO goods(id,name) VALUES(1,"商品1");
INSERT INTO goods(id,name) VALUES(2,"商品2");
INSERT INTO goods(id,name) VALUES(3,"商品3");
INSERT INTO goods(id,name) VALUES(4,"商品4");
INSERT INTO goods(id,name) VALUES(5,"商品5");
INSERT INTO goods(id,name) VALUES(6,"商品6");
INSERT INTO goods(id,name) VALUES(7,"商品7");
INSERT INTO goods(id,name) VALUES(8,"商品8");

步骤2、修改 schema.xml配置

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

	 
	
	
	<!-- 逻辑库 用户库-->
	<schema name="USERDB" checkSQLschema="false" sqlMaxLimit="100"  dataNode="dn1"> 
	 
	</schema>
 
 
 	<!-- 逻辑库 商品库-->
	<schema name="GOODDB" checkSQLschema="false" sqlMaxLimit="100"  dataNode="dn2"> 
		 
	</schema>
	
	 
  <!-- 
	 数据节点 
	 
	 -->
	<dataNode name="dn1" dataHost="localhost1" database="user_db" />
 	<dataNode name="dn2" dataHost="localhost2" database="good_db" />
  
	<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
			  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<!-- 心跳检测语句 -->
		<heartbeat>select user()</heartbeat> 
		<!-- M1主节点 -->
		<writeHost host="M1S1" url="192.168.187.130:3308" user="root" password="root"> </writeHost>  
	</dataHost> 
	
	
	<dataHost name="localhost2" maxCon="1000" minCon="10" balance="1"
			  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<!-- 心跳检测语句 -->
		<heartbeat>select user()</heartbeat> 
		<!-- M1主节点 -->
		<writeHost host="M1S2" url="192.168.187.130:3309" user="root" password="root"> </writeHost>  
	</dataHost>
	
	 
</mycat:schema>
 

步骤3、修改 server.xml配置

步骤4、重启mycat

/usr/local/mycat/bin/mycat  restart

步骤5、查看