环境
- Mycat 下载地址 http://dl.mycat.io/1.6.7.3/20190927161129/
- java 1.8.0_171
- mysql 5.7.22
步骤
- 把下载到/root/mycat 并解压
cd /root/mycat
tar -zxvf Mycat-server-1.6.7.3-release-20190927161129-linux.tar.gz
- 设置mycat环境变量
#编辑环境变量
vim /etc/profile
#变更内容
JAVA_HOME=/usr/java/jdk1.8.0_171
CLASSPATH=$JAVA_HOME/lib/
PATH=$PATH:$JAVA_HOME/bin
MYCAT_HOME=/root/mycat/mycat
export PATH=$PATH:/usr/local/git/bin:$MYCAT_HOME/bin
#变量生效
source /etc/profile
- host绑定
#绑定host
vim /etc/hosts
#绑定内容
机器ip mycat-server
127.0.0.1 mycat-server
4.设置 wrapper.java.command 的java 路径
#设置路径
vim /root/mycat/mycat/conf/wrapper.conf
#编辑内容
wrapper.java.command=%JAVA_HOME%/bin/java
5.修改servce.xml文件
#修改配置文件
cd /root/mycat/mycat/conf
vim server.xml
<property name="useSqlStat">1</property> <!-- 1为开启实时统计、0为关闭 -->
<!--去掉如下的注释，使其生效-->
<property name="maxStringLiteralLength">65535</property>
<property name="sequnceHandlerType">0</property>
<property name="backSocketNoDelay">1</property>
<property name="frontSocketNoDelay">1</property>
6.修改schema.xml文件
#修改配置文件
cd /root/mycat/mycat/conf
vim schema.xml
# 修改内容
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100">
<!-- auto sharding by id (long) -->
<table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
<!-- <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="mycat" />
<dataNode name="dn2" dataHost="localhost2" database="mycat" />
<dataNode name="dn3" dataHost="localhost3" database="mycat" />
<!--<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="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="127.0.0.1:3306" user="root"
password="password">
</writeHost>
<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
</dataHost>
<dataHost name="localhost2" 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="127.0.0.1:3306" user="root"
password="password">
</writeHost>
<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
</dataHost>
<dataHost name="localhost3" 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="password:3306" user="root"
password="password">
</writeHost>
<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
</dataHost>
</mycat:schema>
7.修改日志级别
#修改配置文件
cd /root/mycat/mycat/conf
vim log4j2.xml
#编辑内容
<asyncRoot level="debug" includeLocation="true">
8.启动服务
#启动
mycat start
#查看启用状态
mycat status
验证
查询
首先同一台服务器上分库分表
分别创建mycat1、mycat2、mycat3 三个数据库并同时创建表mycat1
并各插入一条数据：
然后登陆mycat进行查询（端口8806）
select * from mycat1
mycat查询
结果显示能查询出来。
再试试带条件执行：
select * from mycat1 where id =1
mycat带条件查询
带条件执行也是没有问题的
插入
看表mycat1配置的插入规则
#schema配置
<table name="mycat1" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
#rule配置
<tableRule name="auto-sharding-long">
<rule>
<columns>id</columns>
<algorithm>rang-long</algorithm>
</rule>
</tableRule>
<function name="rang-long"
class="io.mycat.route.function.AutoPartitionByLong">
<property name="mapFile">autopartition-long.txt</property>
</function>
#autopartition-long.txt 配置
#K=1000,M=10000.
0-500M=0
500M-1000M=1
1000M-1500M=2
插入数据
#插入数据库mycat1
INSERT into mycat1 VALUES(100,'100name',1,'100desc');
#插入数据库mycat2
INSERT into mycat1 VALUES(6000000,'6000000name',2,'6000000desc');
#插入数据库mycat3
INSERT into mycat1 VALUES(11000000,'11000000name',3,'11000000desc');
但是执行报错：
插入数据报错
提示带上所有字段，那我们带上字段再执行
INSERT into mycat1(id,name,pid,desc) VALUES(100,'100name',1,'100desc');
INSERT into mycat1(id,name,pid,desc) VALUES(6000000,'6000000name',2,'5000000desc');
INSERT into mycat1(id,name,pid,desc) VALUES(11000000,'11000000name',3,'11000000desc');
还是报错
插入还是报错
看出是表字段 dec 冲突了，那我们把dec换成 description
再执行插入语句
INSERT into mycat1(id,name,pid,description) VALUES(100,'100name',1,'100desc');
INSERT into mycat1(id,name,pid,description) VALUES(5000000,'5000000name',2,'5000000desc');
INSERT into mycat1(id,name,pid,description) VALUES(11000000,'11000000name',3,'11000000desc');
成功
插入成功
验证数据是否存入预想的库
100存入mycat1
6000000存入mycat
11000000存入mycat3
好奇：如果id大于1500M（15000000）会出入哪里：
INSERT into mycat1(id,name,pid,description) VALUES(16000000,'16000000name',3,'16000000desc');
超出范围、插入失败
这个时候需要在配置文件指定defaultNode，默认节点
#修改rule.xml文件
vim rule.xml
#制定defaultNode
<function ndefaultNodeame="rang-long"
class="io.mycat.route.function.AutoPartitionByLong">
<property name="mapFile">autopartition-long.txt</property>
<property name="defaultNode">0</property>
</function>
重启mycat再插入超出范围数据
#重启mycat
mycat reatart
#重新插入查处范围的数据
INSERT into mycat1(id,name,pid,description) VALUES(16000000,'16000000name',3,'16000000desc');
插入成功并且存储到制定的第一节点
超出范围的数据存储到指定的默认节点
