如果你下载已经打包好的mycat 1.6.5压缩包.tar.gz是永远也连接不了mysql 8的,我们需要的是mycat 1.6.5的源代码Mycat-Server-1.6.zip的源代码,这个可以自行去下载.下载完之后,第一次加载需要加载他里面的jar包,这个时间比较漫长.
打开他的pom文件,我们需要改动mysql的驱动,改为
mysql mysql-connector-java 8.0.11
修改源代码class SystemConfig,包为package io.mycat.config.model;
public static final String[] MySQLVersions = { "5.5", "5.6", "5.7", "8.0" }; 加入"8.0"版本,
// 是否使用HandshakeV10Packet来与client进行通讯, 1:是 , 0:否(使用HandshakePacket)// 使用HandshakeV10Packet为的是兼容高版本的jdbc驱动, 后期稳定下来考虑全部采用HandshakeV10Packet来通讯private int useHandshakeV10 = 1;
这个原本是0,改成1.
然后用maven打包,记得跳过测试.会得到这样一些文件.
而我们要的是-linux.tar.gz这个.
上传,解压,修改/conf目录下的三个文件server.xml,schema.xml,rule.xml
假如我们有一个userbetorder表要分成5张表,如图
server.xml的配置为:
<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License"); - you may not use this file except in compliance with the License. - You may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 - - Unless required by applicable law or agreed to in writing, software - distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the License for the specific language governing permissions and - limitations under the License. --> <!DOCTYPE mycat:server SYSTEM "server.dtd"> <mycat:server xmlns:mycat="http://io.mycat/"> <system> <property name="nonePasswordLogin">0</property> <!-- 0为需要密码登陆、1为不需要密码登陆 ,默认为0，设置为1则需要指定默认账户--> <property name="useHandshakeV10">1</property> <property name="useSqlStat">0</property> <!-- 1为开启实时统计、0为关闭 --> <property name="useGlobleTableCheck">0</property> <!-- 1为开启全加班一致性检测、0为关闭 --><property name="sequnceHandlerType">2</property>
<!-- <property name="useCompression">1</property>--> <!--1为开启mysql压缩协议--> <!-- <property name="fakeMySQLVersion">5.6.20</property>--> <!--设置模拟的MySQL版本号--> <!-- <property name="processorBufferChunk">40960</property> --> <!-- <property name="processors">1</property> <property name="processorExecutor">32</property> --> <!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena | type 2 NettyBufferPool --> <property name="processorBufferPoolType">0</property> <!--默认是65535 64K 用于sql解析时最大文本长度 --> <!--<property name="maxStringLiteralLength">65535</property>--> <!--<property name="sequnceHandlerType">0</property>--> <!--<property name="backSocketNoDelay">1</property>--> <!--<property name="frontSocketNoDelay">1</property>--> <!--<property name="processorExecutor">16</property>--> <!-- <property name="serverPort">8066</property> <property name="managerPort">9066</property> <property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property> <property name="frontWriteQueueSize">4096</property> <property name="processors">32</property> --> <!--分布式事务开关，0为不过滤分布式事务，1为过滤分布式事务（如果分布式事务内只涉及全局表，则不过滤），2为不过滤分布式事务,但是记录分布式事务日志--> <property name="handleDistributedTransactions">0</property> <!-- off heap for merge/order/group/limit 1开启 0关闭 --> <property name="useOffHeapForMerge">0</property><!--
单位为m --> <property name="memoryPageSize">1m</property><!--
单位为k --> <property name="spillsFileBufferSize">1k</property><property name="useStreamOutput">0</property>
<!--
单位为m --> <property name="systemReserveMemorySize">384m</property> <!--是否采用zookeeper协调切换 --> <property name="useZKSwitch">false</property><!-- XA Recovery Log日志路径 -->
<!--<property name="XARecoveryLogBaseDir">./</property>--><!-- XA Recovery Log日志名称 -->
<!--<property name="XARecoveryLogBaseName">tmlog</property>--></system>
<!-- 全局SQL防火墙设置 --> <!--白名单可以使用通配符%或着*--> <!--例如<host host="127.0.0.*" user="root"/>--> <!--例如<host host="127.0.*" user="root"/>--> <!--例如<host host="127.*" user="root"/>--> <!--例如<host host="1*7.*" user="root"/>--> <!--这些配置情况下对于127.0.0.1都能以root账户登录--> <!-- <firewall> <whitehost> <host host="1*7.0.0.*" user="root"/> </whitehost> <blacklist check="false"> </blacklist> </firewall> --><user name="facebook1" defaultAccount="true">
<property name="password">facebook</property> <property name="schemas">game</property> <!-- 表级 DML 权限设置 --> <!-- <privileges check="false"> <schema name="TESTDB" dml="0110" > <table name="tb01" dml="0000"></table> <table name="tb02" dml="1111"></table> </schema> </privileges> --> </user><user name="facebook2">
<property name="password">facebook</property> <property name="schemas">game</property> <property name="readOnly">true</property> </user></mycat:server>
修改你们自己的
<user name="facebook1" defaultAccount="true">
<property name="password">facebook</property> <property name="schemas">game</property>就可以了schemas的内容要与schema.xml里面相同
schema.xml配置
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"><schema name="game" checkSQLschema="true" sqlMaxLimit="100">
<!-- auto sharding by id (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 --> <!-- random sharding using mod sharind rule --> <table name="UserBetOrder" primaryKey="id" autoIncrement="false" dataNode="dn1" subTables="UserBetOrder$1-5" 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="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="dh1" database="cloud_game" /> <!--<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="dh1" maxCon="1000" minCon="10" balance="0" maxRetryCount="3" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100"> <heartbeat>select 1</heartbeat> <!-- can have multi write hosts --> <writeHost host="hostM1" url="jdbc:mysql://192.168.5.182:3307?useSSL=FALSE&serverTimezone=GMT" user="root" password="goalya"> <!-- can have multi read hosts --> <readHost host="hostS2" url="jdbc:mysql://192.168.5.182:3307?useSSL=FALSE&serverTimezone=GMT" user="root" password="goalya" /> </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::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>要改的地方
<table name="UserBetOrder" primaryKey="id" autoIncrement="false" dataNode="dn1" subTables="UserBetOrder$1-5"
rule="mod-long" />改成你们自己的逻辑表名和分表名用$1-X表示<dataNode name="dn1" dataHost="dh1" database="cloud_game" />database后面带你们的数据库名.
<writeHost host="hostM1" url="jdbc:mysql://192.168.5.182:3307?useSSL=FALSE&serverTimezone=GMT" user="root"
password="goalya"> <!-- can have multi read hosts --> <readHost host="hostS2" url="jdbc:mysql://192.168.5.182:3307?useSSL=FALSE&serverTimezone=GMT" user="root" password="goalya" /> </writeHost>带上你们自己的连接数据库的IP,端口,用户名,密码rule.xml里面只要改一个地方
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<!-- how many data nodes --> <property name="count">5</property> </function>这个count里面改成你的分表数,我这里是5.
剩下的就是在/bin目录下启动 ./mycat start就可以了
用你的数据库连接工具连接进去,操作一个表,如插入,查找等于操作5个表,每插入一次,他会随机插入到一个分表中
另外不要使用select * from 逻辑表;来进行查找,一定要带你设置的索引(group by,order by都可以),否则会一次找出5的倍数来.
最后祝大家程序员节日快乐!