本文由陈超允修改于2017-09-23
本文作者:陈超允chenchaoyun0
- 前置条件
前一篇文章写过mysql的主从同步,在此基础上搭建mysql中间件
mysql安装在两台服务器:ccy001、ccy002,端口都为3306。ccy001为主库,ccy002为从库
- 安装mycat
#下载
cd /u01/soft
#解压
tar -zxvf Mycat-server-1.5.1-RELEASE-20161130213509-linux.tar.gz
mv mycat /u01/
cd /u01/mycat/
#创建启停、查看日志脚本
touch start.sh stop.sh tailLog.sh
vim start.sh
cd /u01/mycat/bin
./mycat start
vim stop.sh
cd /u01/mycat/bin/
./mycat stop
vim tailLog.sh
cd /u01/mycat
tail -100f logs/wrapper.log
- 修改基本配置
cd /u01/mycat/conf/
#vim schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">
<!-- schema 中可以有多个table节点,列几个在Navicat中就能看到几个,库里有这里没有的话客户端中也看不到 -->
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>
<!-- 数据节点 -->
<dataNode
name="dn1"
dataHost="ccy001"
database="bookmanager" />
<!-- 节点主机信息 -->
<dataHost
name="ccy001"
maxCon="1000"
minCon="10"
balance="0"
writeType="0"
dbType="mysql"
dbDriver="native"
switchType="1"
slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- 指定写入数据的主机 -->
<writeHost
host="hostM1"
url="127.0.0.1:3306"
user="root"
password="root123">
<!-- 指定读取数据的主机,可以有多个 -->
<readHost
host="hostS1"
url="118.89.222.115:3306"
user="root"
password="root123" />
</writeHost>
</dataHost>
<!-- <dataHost name="ccy002" maxCon="1000" minCon="10" balance="0" writeType="0"
dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select
user()</heartbeat> <writeHost host="hostM2" url="127.0.0.1:3306" user="root"
password="root123"> can have multi read hosts <readHost host="hostS2" url="123.206.68.97:3306"
user="root" password="root123" /> </writeHost> </dataHost> -->
</mycat:schema>
#vim server.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://org.opencloudb/">
<system>
<property name="defaultSqlParser">druidparser</property>
<!-- <property name="useCompression">1</property>--> <!--1为开启mysql压缩协议-->
<!-- <property name="processorBufferChunk">40960</property> -->
<!--
<property name="processors">1</property>
<property name="processorExecutor">32</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="mutiNodeLimitType">1</property> 0:开启小数量级(默认) ;1:开启亿级数据排序
<property name="mutiNodePatchSize">100</property> 亿级数量排序批量
<property name="processors">32</property> <property name="processorExecutor">32</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> -->
</system>
<user name="admin">
<property name="password">admin</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="mycat">
<property name="password">mycat</property>
<property name="schemas">TESTDB</property>
<property name="readOnly">true</property>
</user>
<user name="ccyusr">
<property name="password">ccyusr</property>
<property name="schemas">TESTDB</property>
<property name="readOnly">false</property>
</user>
<!--
<quarantine>
<whitehost>
<host host="127.0.0.1" user="mycat"/>
<host host="127.0.0.2" user="mycat"/>
</whitehost>
<blacklist check="false"></blacklist>
</quarantine>
-->
</mycat:server>
#有可能出现启动异常,内存不够,修改
vim wrapper.conf
# 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=-Xmx1G
wrapper.java.additional.11=-Xms200M
#启动
sh start.sh
#查看日志
sh tailLog.sh
- 测试
用navicat连接,插入数据
分别在ccy001 ccy002 数据库查看
mycat很多详细配置需要学习下
关于配置文件,conf目录下主要以下三个需要熟悉。
server.xml是Mycat服务器参数调整和用户授权的配置文件
schema.xml是逻辑库定义和表以及分片定义的配置文件
rule.xml是分片规则的配置文件
连接
mysql -hccy001 -uccyusr -p -P 8066
插入数据 读取数据测试即可