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.连接客户端测试读写分离。
