2.Mysql集群------Mycat读写分离

前言:

Mycat:

一个彻底开源的,面向企业应用开发的大数据库集群

支持事务、ACID、可以替代MySQL的加强版数据库

一个可以视为MySQL集群的企业级数据库,用来替代昂贵的Oracle集群

一个融合内存缓存技术、NoSQL技术、HDFS大数据的新型SQL Server

结合传统数据库和新型分布式数据仓库的新一代企业级数据库产品

一个新颖的数据库中间件产品

......

关于mycat更多的介绍,及文档,请点击Mycat官网

准备工作:

1、安装JDK:参考

2、安装Mycat:参考

3、配置数据库的主从同步:参考

开始搬砖:

1.配置/conf/schema.xml

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

    <!-- 配置逻辑库XMDB -->
    <schema name="XMDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1" />
    <!-- 映射到你实际数据库中的库名:xm -->
    <dataNode name="dn1" dataHost="node1" database="xm"/>
    <!-- 配置Host,设置数据库的参数 -->
    <dataHost name="node1" maxCon="1000" minCon="10" balance="1" dbType="mysql" dbDriver="native">
        <!-- 心跳检测,来看下面的host是否还可用 -->
        <heartbeat>select user()</heartbeat> 
        <!-- 读写分离 -->
        <writeHost host="192.168.40.3" url="192.168.40.3:3306" user="root" password="123456">
            <readHost  host="192.168.40.3" url="192.168.40.3:3316" user="root" password="123456" />
        </writeHost>
        <writeHost host="192.168.40.3" url="192.168.40.3: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:@127.0.0.1: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>

 

2.配置/conf/server.xml

  1 <?xml version="1.0" encoding="UTF-8"?>
  2 <!-- - - Licensed under the Apache License, Version 2.0 (the "License"); 
  3     - you may not use this file except in compliance with the License. - You 
  4     may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 
  5     - - Unless required by applicable law or agreed to in writing, software - 
  6     distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT 
  7     WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the 
  8     License for the specific language governing permissions and - limitations 
  9     under the License. -->
 10 <!DOCTYPE mycat:server SYSTEM "server.dtd">
 11 <mycat:server xmlns:mycat="http://io.mycat/">
 12     <system>
 13     <property name="useSqlStat">0</property>  <!-- 1为开启实时统计、0为关闭 -->
 14     <property name="useGlobleTableCheck">0</property>  <!-- 1为开启全加班一致性检测、0为关闭 -->
 15 
 16         <property name="sequnceHandlerType">2</property>
 17       <!--  <property name="useCompression">1</property>--> <!--1为开启mysql压缩协议-->
 18         <!--  <property name="fakeMySQLVersion">5.6.20</property>--> <!--设置模拟的MySQL版本号-->
 19     <!-- <property name="processorBufferChunk">40960</property> -->
 20     <!-- 
 21     <property name="processors">1</property> 
 22     <property name="processorExecutor">32</property> 
 23      -->
 24         <!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena-->
 25         <property name="processorBufferPoolType">0</property>
 26         <!--默认是65535 64K 用于sql解析时最大文本长度 -->
 27         <!--<property name="maxStringLiteralLength">65535</property>-->
 28         <!--<property name="sequnceHandlerType">0</property>-->
 29         <!--<property name="backSocketNoDelay">1</property>-->
 30         <!--<property name="frontSocketNoDelay">1</property>-->
 31         <!--<property name="processorExecutor">16</property>-->
 32         <!--
 33             <property name="serverPort">8066</property> <property name="managerPort">9066</property> 
 34             <property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property> 
 35             <property name="frontWriteQueueSize">4096</property> <property name="processors">32</property> -->
 36         <!--分布式事务开关,0为不过滤分布式事务,1为过滤分布式事务(如果分布式事务内只涉及全局表,则不过滤),2为不过滤分布式事务,但是记录分布式事务日志-->
 37         <property name="handleDistributedTransactions">0</property>
 38         
 39             <!--
 40             off heap for merge/order/group/limit      1开启   0关闭
 41         -->
 42         <property name="useOffHeapForMerge">1</property>
 43 
 44         <!--
 45             单位为m
 46         -->
 47         <property name="memoryPageSize">1m</property>
 48 
 49         <!--
 50             单位为k
 51         -->
 52         <property name="spillsFileBufferSize">1k</property>
 53 
 54         <property name="useStreamOutput">0</property>
 55 
 56         <!--
 57             单位为m
 58         -->
 59         <property name="systemReserveMemorySize">384m</property>
 60 
 61 
 62         <!--是否采用zookeeper协调切换  -->
 63         <property name="useZKSwitch">true</property>
 64 
 65 
 66     </system>
 67     
 68     <!-- 全局SQL防火墙设置 -->
 69     <!-- 
 70     <firewall> 
 71        <whitehost>
 72           <host host="127.0.0.1" user="mycat"/>
 73           <host host="127.0.0.2" user="mycat"/>
 74        </whitehost>
 75        <blacklist check="false">
 76        </blacklist>
 77     </firewall>
 78     -->
 79 
 80     <!-- 配置用户 -->
 81     
 82     <user name="root">
 83         <property name="password">123456</property>
 84         <property name="schemas">XMDB</property>
 85         
 86         <!-- 表级 DML 权限设置 -->
 87         <!--         
 88         <privileges check="false">
 89             <schema name="TESTDB" dml="0110" >
 90                 <table name="tb01" dml="0000"></table>
 91                 <table name="tb02" dml="1111"></table>
 92             </schema>
 93         </privileges>        
 94          -->
 95     </user>
 96 
 97     <user name="user">
 98         <property name="password">user</property>
 99         <property name="schemas">XMDB</property>
100         <property name="readOnly">true</property>
101     </user>
102 
103 </mycat:server>

 

3.开启mycat服务

:mycat start

4.查看开启状况/logs/wrapper.log

日志里会描述开启状况,及异常错误。

 

 开启成功。

5.连接客户端测试读写分离。

 

posted @ 2018-09-06 15:28  零度微笑  阅读(516)  评论(0编辑  收藏  举报