mycat 配置以及java 连mycat

三台服务器：

192.168.192.128 mycat服务器

192.168.192.129 mysql主服务器

192.168.192.132 mysq从服务器

 

schema.xml配置

[root@node1 conf]# vim schema.xml
 

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="USERDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
        </schema>

        <dataNode name="dn1" dataHost="node1" database="test3" />

        <dataHost name="node1" maxCon="1000" minCon="10" balance="1"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="192.168.192.129" url="192.168.192.129:3306" user="root" password="root">
                        <!-- can have multi read hosts -->
                        <readHost host="192.168.192.132" url="192.168.192.132:3306" user="root" password="root" />
                </writeHost>
                <writeHost host="192.168.192.132" url="192.168.192.132:3306" user="root"  password="root" />
                <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
        </dataHost>
</mycat:schema>
~                

 

 

 

 

 

<?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">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-->
                <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> -->
                <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">123456</property>
                <property name="schemas">USERDB</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="mc_user">
                <property name="password">user</property>
                <property name="schemas">USERDB</property>
                <property name="readOnly">true</property>
        </user>

</mycat:server>

看user配置， mycat链接主库，和从库

 

 

配置好后，进入bin,重启

[root@node1 mycat]# bin/mycat stop

 

[root@node1 mycat]# bin/mycat start
 

 

 

java连mycat 

server:
  port: 8777

spring:
  datasource:
    #driver-class-name: com.mysql.cj.jdbc.Driver
    driver-class-name: com.mysql.jdbc.Driver
    first:
#      jdbc-url: jdbc:mysql://192.168.192.129:3306/test3?useSSL=false&allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&tinyInt1isBit=false
#      username: root
#      password: root
      jdbc-url: jdbc:mysql://192.168.192.128:8066/USERDB?useSSL=false&allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&tinyInt1isBit=false
      username: root
      password: 123456
      hikari:
        pool-name: beijingData
        minimum-idle: 2
        idle-timeout: 600000

    second:
#      jdbc-url: jdbc:mysql://192.168.192.192:3306/test3?useSSL=false&allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&tinyInt1isBit=false
#      username: root
#      password: root

      jdbc-url: jdbc:mysql://192.168.192.128:8066/USERDB?useSSL=false&allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&tinyInt1isBit=false
      username: mc_user
      password: user
      pool-name: erpData

 

8066是mycat默认端口

上面两个数据源，一个是mycat连主库，一个连从库

 

连接时可能有报错： java.sql.SQLNonTransientConnectionException: CLIENT_PLUGIN_AUTH is required

这时需要把mysql的版本降低

 

但又带来了一个问题，navicat 工具连上mycat，但访问不了具体的表内容，可能navicat问题。

 

注意

一定要关闭防火墙!一定要关闭防火墙!一定要关闭防火墙!一定要关闭防火墙! ，要不然最后链接测试时会失败