mycat 分库配置(mybatis连接mycat)

mycat是个支持分库分表，读写分离的分布式数据库中间件，以下是配置过程以及操作内容.

创建用户

CREATE USER 'user'@'%' IDENTIFIED BY 'user';

用户授权

GRANT ALL ON *.* TO 'user'@'%';

grant all PRIVILEGES on *.* to user@'%'  identified by 'user';

grant all PRIVILEGES on *.* to root@'%'  identified by 'root';

%MYCAT_HOMEW%/conf/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="useSqlStat">0</property> <!-- 1为开启实时统计、0为关闭 -->

<property name="useGlobleTableCheck">0</property> <!-- 1为开启全加班一致性检测、0为关闭 -->

<property name="sequnceHandlerType">0</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-->

<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">1</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">true</property>

</system>

<!-- 全局SQL防火墙设置 -->

<!--

<firewall>

<whitehost>

<host host="127.0.0.1" user="mycat"/>

<host host="127.0.0.2" user="mycat"/>

</whitehost>

<blacklist check="false">

</blacklist>

</firewall>

-->

<user name="root">

<property name="password">root</property>

<property name="schemas">distribute1</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="user">

<property name="password">user</property>

<property name="schemas">distribute1</property>

<property name="readOnly">true</property>

</user>

</mycat:server>

%MYCAT_HOMEW%/conf/schema.xml

<?xml version="1.0"?>

<!DOCTYPE mycat:schema SYSTEM "schema.dtd">

<mycat:schema xmlns:mycat="http://io.mycat/">

<schema name="distribute1" checkSQLschema="false" sqlMaxLimit="100">

<!-- auto sharding by id (long) -->

<table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-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 -->

<table name="company" primaryKey="ID" dataNode="dn1,dn2,dn3" rule="mod-long"/>

<table name="goods" primaryKey="ID" type="global" dataNode="dn1,dn2" />

<!-- random sharding using mod sharind rule -->

<!-- <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="employee" primaryKey="ID" dataNode="dn1,dn2"

rule="sharding-by-intfile" />

<table name="customer" primaryKey="ID" dataNode="dn1,dn2"

rule="sharding-by-intfile">

<childTable name="orders" primaryKey="ID" joinKey="customer_id"

parentKey="id">

<childTable name="order_items" joinKey="order_id"

parentKey="id" />

</childTable>

<childTable name="customer_addr" primaryKey="ID" joinKey="customer_id"

parentKey="id" />

</table>

<!-- <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="distribute1" />

<dataNode name="dn2" dataHost="localhost2" database="distribute1" />

<dataNode name="dn3" dataHost="localhost3" database="distribute1" />

<dataHost name="localhost1" maxCon="100" 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="172.16.2.133:3306" user="root"

password="root">

</writeHost>

</dataHost>

<dataHost name="localhost2" maxCon="100" minCon="10" balance="0"

writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

<heartbeat>select user()</heartbeat>

<writeHost host="hostM2" url="172.16.2.134:33066" user="root"

password="root">

</writeHost>

</dataHost>

<dataHost name="localhost3" maxCon="100" minCon="10" balance="0"

writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

<heartbeat>select user()</heartbeat>

<writeHost host="hostM3" url="172.16.2.115:3306" user="root"

password="root">

</writeHost>

</dataHost>

</mycat:schema>

8066：mycat的提供服务端口

9066：mycat的控制台端口

可以直接通过8066连接mysql，像是我们通过3306来连接一样

SELECT next value for MYCATSEQ_GLOBAL

MYCATSEQ_GLOBAL 可以在conf目录下面配置 sequence_conf.properties, 默认就可以，这个是使用本地配置文件来实现全局序列化主键id。

[heika_superadmin@172-16-2-146 ~]$ mysql -h 172.16.2.146 -uroot -proot -P8066

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

mysql> show databases;

+-------------+

| DATABASE |

+-------------+

| distribute1 |

+-------------+

1 row in set (0.00 sec)

mysql> select * from company where id=10003;

ERROR 3000 (HY000): No MyCAT Database selected

mysql> use distribute1;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select * from company where id=10003;

Empty set (0.02 sec)

mysql> insert into company(id,name,sharding_id) values(next value for MYCATSEQ_GLOBAL,'hp',10000);

Query OK, 1 row affected (0.02 sec)

mysql> insert into company(id,name,sharding_id) values(next value for MYCATSEQ_GLOBAL,'hp',10000);

Query OK, 1 row affected (0.01 sec)

mysql> insert into company(id,name,sharding_id) values(next value for MYCATSEQ_GLOBAL,'hp',10000);

Query OK, 1 row affected (0.01 sec)

mysql> insert into travelrecord(id,name) values(next value for MYCATSEQ_GLOBAL,'hp');

Query OK, 1 row affected (0.00 sec)

mysql> insert into travelrecord(id,name) values(next value for MYCATSEQ_GLOBAL,'hp');insert into travelrecord(id,name) values(next value for MYCATSEQ_GLOBAL,'hp');

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)

mysql> select * from company com left join travelrecord tre on com.`name`=tre.`name` where com.`name`='hp' ;

+-------+------+-------------+-------+------+

| id | name | sharding_id | id | name |

+-------+------+-------------+-------+------+

| 10012 | hp | 10000 | NULL | NULL |

| 10011 | hp | 10000 | 10014 | hp |

| 10011 | hp | 10000 | 10015 | hp |

| 10011 | hp | 10000 | 10016 | hp |

| 10013 | hp | 10000 | NULL | NULL |

+-------+------+-------------+-------+------+

5 rows in set (0.01 sec)

mysql>

Mybatis实现的话，就需要改一个地方

<insert id="insert" parameterType="com.abin.lee.distribute.mycat.model.Team"  useGeneratedKeys="true" keyProperty="id" keyColumn="id">
  <!--<selectKey resultType="java.lang.Integer" keyProperty="id" order="AFTER" >-->
    <!--SELECT LAST_INSERT_ID()-->
  <!--</selectKey>-->
  insert into team (id, team_name, create_time, update_time,
    version)
  values (next value for MYCATSEQ_GLOBAL, #{teamName,jdbcType=VARCHAR}, #{createTime,jdbcType=TIMESTAMP}, #{updateTime,jdbcType=TIMESTAMP},
    #{version,jdbcType=INTEGER})
</insert>

主键id使用mycat的实现即可