博客

mycat 分库配置(mybatis连接mycat)

  • Post author:
  • Post category:其他


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的实现即可


版权声明：本文为varyall原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。