一 环境
1.1 结构
192.168.132.125 mycat
192.168.132.121 master
192.168.132.122 slave
主从已经配置完成
1.2 安装mycat
192.168.132.125安装mycat
[root@mycat ~]# cd /usr/local/src/
[root@mycat src]# wget http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
[root@mycat src]# tar -xf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
[root@mycat src]# cd mycat/
[root@mycat mycat]# ll
1drwxr-xr-x. 2 root root 190 Jul 12 08:50 bin 2drwxrwxrwx. 2 root root 6 Feb 29 2016 catlet 3drwxrwxrwx. 4 root root 4096 Jul 12 08:50 conf 4drwxr-xr-x. 2 root root 4096 Jul 12 08:50 lib 5drwxrwxrwx. 2 root root 6 Oct 28 2016 logs 6-rwxrwxrwx. 1 root root 217 Oct 28 2016 version.txt
1.3 配置mycat
[root@mycat mycat]# cd conf/
[root@mycat conf]# ll
1-rwxrwxrwx. 1 root root 88 Oct 28 2016 autopartition-long.txt 2-rwxrwxrwx. 1 root root 48 Oct 28 2016 auto-sharding-long.txt 3-rwxrwxrwx. 1 root root 62 Oct 28 2016 auto-sharding-rang-mod.txt 4-rwxrwxrwx. 1 root root 334 Oct 28 2016 cacheservice.properties 5-rwxrwxrwx. 1 root root 439 Oct 28 2016 ehcache.xml 6-rwxrwxrwx. 1 root root 2132 Oct 28 2016 index_to_charset.properties 7-rwxrwxrwx. 1 root root 1246 Oct 10 2016 log4j2.xml 8-rwxrwxrwx. 1 root root 178 Oct 28 2016 migrateTables.properties 9-rwxrwxrwx. 1 root root 246 Oct 28 2016 myid.properties 10-rwxrwxrwx. 1 root root 15 Oct 28 2016 partition-hash-int.txt 11-rwxrwxrwx. 1 root root 102 Oct 28 2016 partition-range-mod.txt 12-rwxrwxrwx. 1 root root 4794 Oct 28 2016 rule.xml 13-rwxrwxrwx. 1 root root 4219 Oct 28 2016 schema.xml #主要配置这个文件 14-rwxrwxrwx. 1 root root 413 Oct 28 2016 sequence_conf.properties 15-rwxrwxrwx. 1 root root 75 Oct 28 2016 sequence_db_conf.properties 16-rwxrwxrwx. 1 root root 27 Oct 28 2016 sequence_distributed_conf.properties 17-rwxrwxrwx. 1 root root 51 Oct 28 2016 sequence_time_conf.properties 18-rwxrwxrwx. 1 root root 3717 Oct 28 2016 server.xml 19-rwxrwxrwx. 1 root root 16 Oct 28 2016 sharding-by-enum.txt 20-rwxrwxrwx. 1 root root 4182 Oct 28 2016 wrapper.conf 21drwxrwxrwx. 2 root root 4096 Jul 12 08:50 zkconf 22drwxrwxrwx. 2 root root 36 Jul 12 08:50 zkdownload
[root@mycat conf]# cat schema.xml
1<?xml version="1.0"?> 2<!DOCTYPE mycat:schema SYSTEM "schema.dtd"> 3<mycat:schema xmlns:mycat="http://io.mycat/"> 4<!‐‐ TESTDB 表示mycat 的逻辑数据库名称当schema 节点没有子节点table 的时候，一定要有dataNode 属性存在‐‐> 5 <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"> 6 </schema> 7 8 <dataNode name="dn1" dataHost="localhost1" database="master1" /> 9 10 <dataHost name="localhost1" maxCon="1000" minCon="10" balance="3" 11 writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> 12 <heartbeat>select user()</heartbeat> 13 <!-- can have multi write hosts --> 14 <writeHost host="hostM1" url="192.168.132.121:3306" user="root" 15 password="123456"> 16 <!-- can have multi read hosts --> 17 <readHost host="hostS2" url="192.168.132.122:3306" user="root" password="123456" /> 18 </writeHost> 19 20 </dataHost> 21 22</mycat:schema>
注意 dataHost 节点的下面三个属性balance， switchType， writeType
balance="0", 不开启读写分离机制，所有读操作都发送到当前可用的writeHost 上。
balance="1"，全部的readHost 与stand by writeHost 参与select语句的负载均衡，简单的说，当双主双从模式(M1‐>S1，M2‐>S2，并且M1 与M2 互为主备)，正常情况下，M2,S1,S2都参与select 语句的负载均衡。
balance="2"，所有读操作都随机的在writeHost、readhost 上分发。
balance="3"，所有读请求随机的分发到writeHost 下的readhost 执行，writeHost 不负担读压力
writeType 表示写模式
writeType="0"，所有的操作发送到配置的第一个writehost
writeType="1"，随机发送到配置的所有writehost
writeType="2"，不执行写操作
switchType 指的是切换的模式，目前的取值也有4 种：
switchType=‘‐1‘ 表示不自动切换
switchType=‘1‘ 默认值，表示自动切换
switchType=‘2‘ 基于MySQL 主从同步的状态决定是否切换,心跳语句为show slave status
switchType=‘3‘基于MySQL galary cluster 的切换机制（适合集群）（1.4.1），心跳语句为show status like ‘wsrep%‘。
配置端口，用户名
[root@mycat conf]# vim 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="handleDistributedTransactions">0</property> 34 <!-- off heap for merge/order/group/limit 1开启 0关闭 --> 35 <property name="useOffHeapForMerge">1</property> 36 <!-- 单位为m --> 37 <property name="memoryPageSize">1m</property> 38 <!-- 单位为k --> 39 <property name="spillsFileBufferSize">1k</property> 40 <property name="useStreamOutput">0</property> 41 <!-- 单位为m --> 42 <property name="systemReserveMemorySize">384m</property> 43 <!--是否采用zookeeper协调切换 --> 44 <property name="useZKSwitch">true</property> 45 </system> 46 <!-- 全局SQL防火墙设置 --> 47 <!-- 48 <firewall> 49 <whitehost> 50 <host host="127.0.0.1" user="mycat"/> 51 <host host="127.0.0.2" user="mycat"/> 52 </whitehost> 53 <blacklist check="false"> 54 </blacklist> 55 </firewall> 56 --> 57 <user name="root"> 58 <property name="password">123456</property> 59 <property name="schemas">TESTDB</property> 60 <!-- 表级 DML 权限设置 --> 61 <!-- 62 <privileges check="false"> 63 <schema name="TESTDB" dml="0110" > 64 <table name="tb01" dml="0000"></table> 65 <table name="tb02" dml="1111"></table> 66 </schema> 67 </privileges> 68 --> 69 </user> 70 <user name="user"> 71 <property name="password">user</property> 72 <property name="schemas">TESTDB</property> 73 <property name="readOnly">true</property> 74 </user> 75</mycat:server
二 验证
2.1 启动Mycat
[root@mycat conf]# ../bin/mycat start
2.2 日志
[root@mycat conf]# cat /usr/local/src/mycat/logs/wrapper.log
1STATUS | wrapper | 2019/07/12 10:44:55 | --> Wrapper Started as Daemon 2STATUS | wrapper | 2019/07/12 10:44:55 | Launching a JVM... 3ERROR | wrapper | 2019/07/12 10:45:12 | Unable to start JVM: No such file or directory (2) #没有JVM，安装java 4ERROR | wrapper | 2019/07/12 10:45:12 | JVM exited while loading the application. 5FATAL | wrapper | 2019/07/12 10:45:12 | There were 5 failed launches in a row, each lasting less than 300 seconds. Giving up. 6FATAL | wrapper | 2019/07/12 10:45:12 | There may be a configuration problem: please check the logs. 7STATUS | wrapper | 2019/07/12 10:45:12 | <-- Wrapper Stopped
[root@mycat conf]# yum -y install java
[root@mycat conf]# ../bin/mycat start
[root@mycat conf]# tail -f /usr/local/src/mycat/logs/wrapper.log
1INFO | jvm 1 | 2019/07/12 11:02:46 | 2019-07-12 11:02:46,389 [INFO ][$_NIOREACTOR-0-RW] connectionAcquired MySQLConnection [id=12, lastTime=1562943766373, user=root, schema=master1, old shema=master1, borrowed=false, fromSlaveDB=true, threadId=1428, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.132.122, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] (io.mycat.backend.mysql.nio.handler.NewConnectionRespHandler:NewConnectionRespHandler.java:45) 2INFO | jvm 1 | 2019/07/12 11:02:46 | MyCAT Server startup successfully. see logs in logs/mycat.log
2.3 连接，端口为8066
[root@mycat ~]# mysql -uroot -p1234567 -h192.168.132.125 -P8066
1MySQL [(none)]> show databases; 2+----------+ 3| DATABASE | 4+----------+ 5| TESTDB | 6+----------+
2.4 写入数据测试
1MySQL [(none)]> use TESTDB; 2MySQL [TESTDB]> show tables; 3MySQL [TESTDB]> select * from test; 4MySQL [TESTDB]> insert into test values (555); 5MySQL [TESTDB]> select * from test; 6+------+ 7| id | 8+------+ 9| 1 | 10| 2 | 11| 2 | 12| 2 | 13| 11 | 14| 12 | 15| 111 | 16| 222 | 17| 333 | 18| 444 | 19| 555 | 20+------+ 21mater查看 22mysql> select * from master1.test; 23+------+ 24| id | 25+------+ 26| 1 | 27| 2 | 28| 2 | 29| 2 | 30| 11 | 31| 12 | 32| 111 | 33| 222 | 34| 333 | 35| 444 | 36| 555 | 37+------+ 38slave查看 39mysql> select * from master1.test; 40+------+ 41| id | 42+------+ 43| 1 | 44| 2 | 45| 2 | 46| 2 | 47| 11 | 48| 12 | 49| 111 | 50| 222 | 51| 333 | 52| 444 | 53| 555 | 54+------+
成功写入数据
2.5 验证读写分离
修改日志级别为debug
[root@mycat conf]# vim log4j2.xml
1<Loggers> 2 <!--<AsyncLogger name="io.mycat" level="info" includeLocation="true" additivity="false">--> 3 <!--<AppenderRef ref="Console"/>--> 4 <!--<AppenderRef ref="RollingFile"/>--> 5 <!--</AsyncLogger>--> 6 <asyncRoot level="debug" includeLocation="true"> 7 8 <AppenderRef ref="Console" /> 9 <AppenderRef ref="RollingFile"/> 10 11 </asyncRoot> 12 </Loggers>
[root@mycat conf]# /usr/local/src/mycat/bin/mycat stop
[root@mycat conf]# /usr/local/src/mycat/bin/mycat start
写入一个数据
MySQL [TESTDB]> insert into test values (666);
[root@mycat conf]# tail -f /usr/local/src/mycat/logs/wrapper.log
1INFO | jvm 1 | 2019/07/12 11:15:06 | 2019-07-12 11:15:06,662 [DEBUG][$_NIOREACTOR-0-RW] con need syn ,total syn cmd 1 commands SET names utf8;schema change:false con:MySQLConnection [id=7, lastTime=1562944506662, user=root, schema=master1, old shema=master1, borrowed=true, fromSlaveDB=false, threadId=14906, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{insert into test values (666)}, respHandler=SingleNodeHandler [node=dn1{insert into test values (666)}, packetId=0], host=192.168.132.121, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true] (io.mycat.backend.mysql.nio.MySQLConnection:MySQLConnection.java:448) 2INFO | jvm 1 | 2019/07/12 11:15:06 | 2019-07-12 11:15:06,668 [DEBUG][$_NIOREACTOR-1-RW] release connection MySQLConnection [id=7, lastTime=1562944506646, user=root, schema=master1, old shema=master1, borrowed=true, fromSlaveDB=false, threadId=14906, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{insert into test values (666)}, respHandler=SingleNodeHandler [node=dn1{insert into test values (666)}, packetId=1], host=192.168.132.121, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true] (io.mycat.server.NonBlockingSession:NonBlockingSession.java:341) 3INFO | jvm 1 | 2019/07/12 11:15:06 | 2019-07-12 11:15:06,669 [DEBUG][$_NIOREACTOR-1-RW] release channel MySQLConnection [id=7, lastTime=1562944506646, user=root, schema=master1, old shema=master1, borrowed=true, fromSlaveDB=false, threadId=14906, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.132.121, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] (io.mycat.backend.datasource.PhysicalDatasource:PhysicalDatasource.java:442) 4INFO | jvm 1 | 2019/07/12 11:15:14 | 2019-07-12 11:15:13,968 [DEBUG][Timer1] con query sql:select user() to con:MySQLConnection [id=3, lastTime=1562944513968, user=root, schema=master1, old shema=master1, borrowed=true, fromSlaveDB=false, threadId=14907, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.132.121, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] (io.mycat.sqlengine.SQLJob:SQLJob.java:88)
发现在192.168.132.121 主数据库上面
查找数据
MySQL [TESTDB]> select * from test;
1INFO | jvm 1 | 2019/07/12 11:20:13 | 2019-07-12 11:20:13,419 [DEBUG][$_NIOREACTOR-0-RW] con need syn ,total syn cmd 1 commands SET names utf8;schema change:false con:MySQLConnection [id=15, lastTime=1562944813419, user=root, schema=master1, old shema=master1, borrowed=true, fromSlaveDB=true, threadId=1596, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{select * from test}, respHandler=SingleNodeHandler [node=dn1{select * from test}, packetId=0], host=192.168.132.122, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] (io.mycat.backend.mysql.nio.MySQLConnection:MySQLConnection.java:448) 2INFO | jvm 1 | 2019/07/12 11:20:13 | 2019-07-12 11:20:13,421 [DEBUG][$_NIOREACTOR-0-RW] release connection MySQLConnection [id=15, lastTime=1562944813407, user=root, schema=master1, old shema=master1, borrowed=true, fromSlaveDB=true, threadId=1596, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{select * from test}, respHandler=SingleNodeHandler [node=dn1{select * from test}, packetId=16], host=192.168.132.122, port=3306, statusSync=io.mycat.backend.mysql.nio.MySQLConnection$StatusSync@3055d901, writeQueue=0, modifiedSQLExecuted=false] (io.mycat.server.NonBlockingSession:NonBlockingSession.java:341)
在salve192.168.132.122从数据库上面，读和写在不同的服务器上面
2.6 问题
但这种方式有个问题，即master 挂了以后，slave 也不能提供读和写服务Slave 挂了 不能查询，但是可以插入。
停掉主数据库
[root@master1 ~]# systemctl stop mysqld
[root@mycat ~]# mysql -uroot -p1234567 -h192.168.132.125 -P8066 TESTDB
1MySQL [TESTDB]> select * from test; 2ERROR 1184 (HY000): Connection refused 3MySQL [TESTDB]> insert into test values (777); 4ERROR 1184 (HY000): Connection refused
已经不能查询，也不可以插入
停掉slave
不可以查，但是可以写
[root@slave ~]# systemctl stop mysqld
1MySQL [TESTDB]> select * from test; 2ERROR 1184 (HY000): Connection refused 3MySQL [TESTDB]> insert into test values (777); 4Query OK, 1 row affected (0.00 sec)
读写分离配置完成
这种结构过于简单，但是实现了读写分离的功能，生产中需要综合考虑单点故障的问题，需要更可靠的架构！