create user 'root'@'%'identified with mysql_native_password by '666666';
grant all priviliges on *.* to 'root'@'%';
create database zzc;
五.水平分表
schema.xml配置如下：
<?xml version="1.0"?><!DOCTYPEmycat:schema SYSTEM"schema.dtd"><mycat:schema xmlns:mycat="http://io.mycat/"><schema name="USERDB" checkSQLschema="false" sqlMaxLimit="100"><table name="zzc" dataNode="dn1,dn2,dn3" rule="mod-long"/><table name="tb_01" dataNode="dn1,dn2,dn3" rule="mod-long"/></schema><dataNode name="dn1" dataHost="localhost1" database="zzc"/><dataNode name="dn2" dataHost="localhost2" database="zzc"/><dataNode name="dn3" dataHost="localhost3" database="zzc"/><dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100"><heartbeat>select user()</heartbeat><!-- can have multi write hosts --><writeHost host="hostM1" url="jdbc:mysql://192.168.112.83:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root"
password="666666"></writeHost></dataHost><dataHost name="localhost2" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100"><heartbeat>select user()</heartbeat><!-- can have multi write hosts --><writeHost host="hostM1" url="jdbc:mysql://192.168.112.68:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root"
password="666666"></writeHost></dataHost><dataHost name="localhost3" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100"><heartbeat>select user()</heartbeat><!-- can have multi write hosts --><writeHost host="hostM1" url="jdbc:mysql://192.168.112.82:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root"
password="666666"></writeHost></dataHost></mycat:schema>
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,-WITHOUTWARRANTIESORCONDITIONSOFANYKIND, either express or implied.- See the
License for the specific language governing permissions and - limitations
under the License.--><!DOCTYPEmycat: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="sequnceHandlerPattern">(?:(s*nexts+values+fors*MYCATSEQ_(w+))(,|)|s)*)+</property>--><!--必须带有MYCATSEQ_或者 mycatseq_进入序列匹配流程 注意MYCATSEQ_有空格的情况--><property name="sequnceHandlerPattern">(?:(s*nexts+values+fors*MYCATSEQ_(w+))(,|)|s)*)+</property><property name="subqueryRelationshipCheck">false</property><!-- 子查询中存在关联查询的情况下,检查关联字段中是否有分片字段 .默认 false--><!--<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">64k</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>--><!--如果为 true的话 严格遵守隔离级别,不会在仅仅只有select语句的时候在事务中切换连接--><property name="strictTxIsolation">false</property><property name="useZKSwitch">true</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="root" defaultAccount="true"><property name="password">666666</property><property name="schemas">USERDB</property><!-- 表级 DML 权限设置 --><!--<privileges check="false"><schema name="USERDB" dml="1111"><table name="zzc" dml="1010"></table><table name="tb_01" dml="1010"></table></schema></privileges>--></user><user name="user"><property name="password">666666</property><property name="schemas">USERDB</property><property name="readOnly">true</property></user></mycat:server>
启动mycat
注：启动前先console检测是否正常若显示successfully，start
查看是否启动 :
lsof -i:8066
错误日志:
tail -f /logs/wrapper.log
登录mycat：
mysql -h 192.168.112.83 -P 8066 -uroot -p
USE USERDB;
SHOW TABLES;#可以看到schema下的tb_01
create table tb_01(id int auto_increment primary key, name char(10), age tinyint unsigned, salary int not null );
|
2019-05-17
微信扫一扫关注我们
