mycat下载
http://www.mycat.io
下载完后解压文件并进入到bin目录下
mycat安装及启动关闭
mycat 配置用户信息
打开conf目录下的server.xml
配置schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>
<!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
/> -->
<dataNode name="dn1" dataHost="host1" database="test" />
<!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" />
<dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" />
<dataNode name="jdbc_dn2" dataHost="jdbchost" database="db2" />
<dataNode name="jdbc_dn3" dataHost="jdbchost" database="db3" /> -->
<dataHost name="host1" maxCon="1000" minCon="10" balance="2"
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.5.169:3306" user="root"
password="root">
<!-- can have multi read hosts -->
<readHost host="hostS1" url="192.168.5.182:3306" user="root" password="root" />
</writeHost>
</dataHost>
</mycat:schema>
balance配置参数
- balance=“0”,不开启读写分离机制,所有的读操作都发送到当前可用的writeHost上
- balance=“1”,全部的readHost与stand by writeHost 参与 select 语句的负载均衡,简单的说,当双主双从模式(M1->S1,M2->S2,并且M1与M2互为主备),正常情况下,M2,S1,S2都参与 select语句的负载均衡
- balance=“2”,所有的读操作都随机的在writeHost、readHost上分发
- balance=“3”,所有读请求随机分发到readHost执行,writeHost不负担读压力
连接mycat
8066:数据端口 9066:后台管理端口
mycat分库
在192.168.5.169(localhost)、192.168.5.182的创建新的全新的数据库
配置schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
<table name="customer" dataNode="dn2"></table>
</schema>
<!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
/> -->
<dataNode name="dn1" dataHost="host1" database="order20200223" />
<dataNode name="dn2" dataHost="host2" database="order20200223" />
<!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" />
<dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" />
<dataNode name="jdbc_dn2" dataHost="jdbchost" database="db2" />
<dataNode name="jdbc_dn3" dataHost="jdbchost" database="db3" /> -->
<dataHost name="host1" 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="192.168.5.169:3306" user="root"
password="root">
<!-- can have multi read hosts -->
</writeHost>
</dataHost>
<dataHost name="host2" 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="hostM2" url="192.168.5.182:3306" user="root"
password="root">
<!-- can have multi read hosts -->
</writeHost>
</dataHost>
</mycat:schema>
连接mycat并插入customer表
customer表在182上
创建其他表
这些表只有在localhost上出现
说明分库是成功的
水平分表
添加
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
<table name="customer" dataNode="dn2"></table>
<table name="orders" dataNode="dn1,dn2" rule="mod_rule"></table>
</schema>
<!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
/> -->
<dataNode name="dn1" dataHost="host1" database="order20200223" />
<dataNode name="dn2" dataHost="host2" database="order20200223" />
<!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" />
<dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" />
<dataNode name="jdbc_dn2" dataHost="jdbchost" database="db2" />
<dataNode name="jdbc_dn3" dataHost="jdbchost" database="db3" /> -->
<dataHost name="host1" 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="192.168.5.169:3306" user="root"
password="root">
<!-- can have multi read hosts -->
</writeHost>
</dataHost>
<dataHost name="host2" 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="hostM2" url="192.168.5.182:3306" user="root"
password="root">
<!-- can have multi read hosts -->
</writeHost>
</dataHost>
</mycat:schema>
修改rule.xml配置文件
<tableRule name="mod_rule">
<rule>
<columns>customer_id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
要保证两台机子上都有orders表
重启mycat并插入部分数据
查看数据库就会发现已经分在不同的表了
关联表做分表
保证分库中包含所有的分表
配置schema.xml
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
<table name="customer" dataNode="dn2"></table>
<table name="orders" dataNode="dn1,dn2" rule="mod_rule">
<childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id"/>
</table>
</schema>
<!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
/> -->
<dataNode name="dn1" dataHost="host1" database="order20200223" />
<dataNode name="dn2" dataHost="host2" database="order20200223" />
<!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" />
<dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" />
<dataNode name="jdbc_dn2" dataHost="jdbchost" database="db2" />
<dataNode name="jdbc_dn3" dataHost="jdbchost" database="db3" /> -->
<dataHost name="host1" 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="192.168.5.169:3306" user="root"
password="root">
<!-- can have multi read hosts -->
</writeHost>
</dataHost>
<dataHost name="host2" 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="hostM2" url="192.168.5.182:3306" user="root"
password="root">
<!-- can have multi read hosts -->
</writeHost>
</dataHost>
全局表
保持表一致
在schema.xml添加如下
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
<table name="customer" dataNode="dn2"></table>
<table name="orders" dataNode="dn1,dn2" rule="mod_rule">
<childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id"/>
</table>
<table name="orders_type" dataNode="dn1,dn2" type="global"></table>
</schema>
<!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
/> -->
<dataNode name="dn1" dataHost="host1" database="order20200223" />
<dataNode name="dn2" dataHost="host2" database="order20200223" />
<!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" />
<dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" />
<dataNode name="jdbc_dn2" dataHost="jdbchost" database="db2" />
<dataNode name="jdbc_dn3" dataHost="jdbchost" database="db3" /> -->
<dataHost name="host1" 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="192.168.5.169:3306" user="root"
password="root">
<!-- can have multi read hosts -->
</writeHost>
</dataHost>
<dataHost name="host2" 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="hostM2" url="192.168.5.182:3306" user="root"
password="root">
<!-- can have multi read hosts -->
</writeHost>
</dataHost>
</mycat:schema>
然后在orders_type表中插入数据就会在不同的库中都有数据
全局序列
1、建库序列脚本
DROP TABLE IF EXISTS MYCAT_SEQUENCE;
CREATE TABLE MYCAT_SEQUENCE (name VARCHAR(50) NOT NULL,current_value INT NOT NULL,increment INT NOT NULL DEFAULT 100, PRIMARY KEY(name)) ENGINE=InnoDB;
DROP FUNCTION IF EXISTS mycat_seq_currval;
DELIMITER $
CREATE 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 ;
DROP FUNCTION IF EXISTS mycat_seq_setval;
DELIMITER $
CREATE 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 ;
DROP FUNCTION IF EXISTS mycat_seq_nextval;
DELIMITER $
CREATE 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 ;
2、在新建的MYCAT_SEQUENCE表中新建一条初始化数据
INSERT INTO MYCAT_SEQUENCE(name,current_value,increment) VALUES ('orders', 1, 100);
注:
name:这个序列号的名字(比如给orders的序列,可以取名orders)
current_value: 当前序列开始值
increment :一次给多少(步长)
3、修改sequence_db_conf.properties
修改server.xml
注:sequnceHandlerType
0 : 本地文件
1 : 数据库方式
2 : 时间戳方式
3 : 自主生成
重启mycat并插入数据
INSERT INTO `orders`(id,order_type,customer_id,amount) VALUES (next value for MYCATSEQ_ORDERS, 40001,40001, 40001);