企业级应用，持久层架构方案四（一主多从，读写分离中篇）
这是企业级应用，持久层架构方案的第四篇。在上一篇：企业级应用，持久层架构方案三（一主多从，读写分离上篇）中。已经实现mysql服务一主两从。本篇我们将借助mycat实现读写分离，在主服务器A库承担写操作，在从服务器C库、D库实现读操作。从而达到读写分离，提升并发读的能力。
1.安装mycat
1.1.下载mycat
下载地址：http://dl.mycat.io/1.6-RELEASE/
1.2.上传mycat并解压
1.3.mycat目录介绍
#目录介绍：
bin:管理脚本
conf:配置文件
lib:依赖包
logs:日志
2.配置读写分离
2.1.schema.xml
<?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <!-- MYOA 是mycat的逻辑库名称--> <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema> <!-- dataNode配置： name：指定逻辑数据节点名称 dataHost：指定逻辑数据节点物理主机节点名称 database：指定物理主机节点上，数据库名称(如果一个节点上有多个库，可使用表达式db$0-99,表示指定0-99这100个数据库) --> <dataNode name="dn1" dataHost="localhost1" database="oa" /> <!--dataHost配置： name：物理主机节点名称 maxCon：指定物理主机服务最大支持1000个连接 minCon：指定物理主机服务最小保持10个连接 balance：指定物理主机服务的负载模式 0,不开启读写分离机制 1,全部的readHost与stand by writeHost参与select语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且M1与M2互为主备)，正常情况下，M2,S1,S2都参与select语句的负载均衡 2,所有的readHost与writeHost都参与select语句的负载均衡，也就是说，当系统的写操作压力不大的情况下，所有主机都可以承担负载均衡 3,所有读请求随机的分发到writeHost对应的readHost执行，writeHost不负担读压力 writeType：指定写入类型 0，只在writeHost节点写入 1，在所有节点都写入。慎重开启，多节点写入顺序为默认写入根据配置顺序，第一个挂掉切换另一个 dbType：指定数据库类型 dbDriver：指定数据库驱动 switchType：指定切换的模式 -1,表示不自动切换 1,默认值，表示自动切换 2,基于MySQL主从同步的状态决定是否切换,心跳语句为 show slave status 3,基于MySQL galary cluster的切换机制（适合集群）（1.4.1），心跳语句为 show status like 'wsrep%' --> <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <!--主库：用于写--> <writeHost host="hostM1" url="192.168.80.30:3306" user="root" password="123456"> <!--从库1：用于读--> <readHost host="hostS1" url="192.168.80.32:3306" user="root" password="123456"></readHost> <!--从库2：用于读--> <readHost host="hostS2" url="192.168.80.33:3306" user="root" password="123456"></readHost> </writeHost> <!--主库备库：用于读，当hostM1 down了，自动切换为主，读写都可以--> <writeHost host="hostM2" url="192.168.80.31:3306" user="root" password="123456"></writeHost> </dataHost> </mycat:schema>
2.2.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">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> --> <!--分布式事务开关，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">123456</property> <property name="schemas">TESTDB</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">TESTDB</property> <property name="readOnly">true</property> </user> </mycat:server>
2.3.wrapper.conf
#********************************************************************
# Wrapper Properties
#********************************************************************
# Java Application
wrapper.java.command=java
wrapper.working.dir=..
# Java Main class. This class must implement the WrapperListener interface
# or guarantee that the WrapperManager class is initialized. Helper
# classes are provided to do this for you. See the Integration section
# of the documentation for details.
wrapper.java.mainclass=org.tanukisoftware.wrapper.WrapperSimpleApp
set.default.REPO_DIR=lib
set.APP_BASE=.
# Java Classpath (include wrapper.jar) Add class path elements as
# needed starting from 1
wrapper.java.classpath.1=lib/wrapper.jar
wrapper.java.classpath.2=conf
wrapper.java.classpath.3=%REPO_DIR%/*
# Java Library Path (location of Wrapper.DLL or libwrapper.so)
wrapper.java.library.path.1=lib
# Java Additional Parameters
#wrapper.java.additional.1=
wrapper.java.additional.1=-DMYCAT_HOME=.
wrapper.java.additional.2=-server
wrapper.java.additional.3=-XX:MaxPermSize=64M
wrapper.java.additional.4=-XX:+AggressiveOpts
wrapper.java.additional.5=-XX:MaxDirectMemorySize=2G
wrapper.java.additional.6=-Dcom.sun.management.jmxremote
wrapper.java.additional.7=-Dcom.sun.management.jmxremote.port=1984
wrapper.java.additional.8=-Dcom.sun.management.jmxremote.authenticate=false
wrapper.java.additional.9=-Dcom.sun.management.jmxremote.ssl=false
wrapper.java.additional.10=-Xmx512M
wrapper.java.additional.11=-Xms256M
# Initial Java Heap Size (in MB)
#wrapper.java.initmemory=3
# Maximum Java Heap Size (in MB)
#wrapper.java.maxmemory=64
# Application parameters. Add parameters as needed starting from 1
wrapper.app.parameter.1=io.mycat.MycatStartup
wrapper.app.parameter.2=start
#********************************************************************
# Wrapper Logging Properties
#********************************************************************
# Format of output for the console. (See docs for formats)
wrapper.console.format=PM
# Log Level for console output. (See docs for log levels)
wrapper.console.loglevel=INFO
# Log file to use for wrapper output logging.
wrapper.logfile=logs/wrapper.log
# Format of output for the log file. (See docs for formats)
wrapper.logfile.format=LPTM
# Log Level for log file output. (See docs for log levels)
wrapper.logfile.loglevel=INFO
# Maximum size that the log file will be allowed to grow to before
# the log is rolled. Size is specified in bytes. The default value
# of 0, disables log rolling. May abbreviate with the 'k' (kb) or
# 'm' (mb) suffix. For example: 10m = 10 megabytes.
wrapper.logfile.maxsize=0
# Maximum number of rolled log files which will be allowed before old
# files are deleted. The default value of 0 implies no limit.
wrapper.logfile.maxfiles=0
# Log Level for sys/event log output. (See docs for log levels)
wrapper.syslog.loglevel=NONE
#********************************************************************
# Wrapper Windows Properties
#********************************************************************
# Title to use when running as a console
wrapper.console.title=Mycat-server
#********************************************************************
# Wrapper Windows NT/2000/XP Service Properties
#********************************************************************
# WARNING - Do not modify any of these properties when an application
# using this configuration file has been installed as a service.
# Please uninstall the service before modifying this section. The
# service can then be reinstalled.
# Name of the service
wrapper.ntservice.name=mycat
# Display name of the service
wrapper.ntservice.displayname=Mycat-server
# Description of the service
wrapper.ntservice.description=The project of Mycat-server
# Service dependencies. Add dependencies as needed starting from 1
wrapper.ntservice.dependency.1=
# Mode in which the service is installed. AUTO_START or DEMAND_START
wrapper.ntservice.starttype=AUTO_START
# Allow the service to interact with the desktop.
wrapper.ntservice.interactive=false
wrapper.ping.timeout=120
configuration.directory.in.classpath.first=conf
3.启动mycat
#进入目录 cd /usr/local/develop/mycat/bin #启动|停止|查看状态 ./mycat start|stop|status #查看日志 cd /usr/local/develop/mycat/logs tail -f mycat.log
我们唯一能够控制的是自己的脾气和努力
